Overview of sql pl language elements modules
Download
1 / 14

Overview of SQL PL Language Elements & Modules - PowerPoint PPT Presentation


  • 119 Views
  • Uploaded on

Overview of SQL PL Language Elements & Modules. Unit objectives. After completing this unit, you should be able to: Discuss DB2 data types Select the proper data types Work with user-defined data types Work with Row data type Work with Associative arrays

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Overview of SQL PL Language Elements & Modules' - benito


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Overview of sql pl language elements modules

Overview of SQL PL Language Elements & Modules


Unit objectives
Unit objectives

After completing this unit, you should be able to:

  • Discuss DB2 data types

  • Select the proper data types

  • Work with user-defined data types

  • Work with Row data type

  • Work with Associative arrays

  • Implement data type anchoring

  • Create Module

  • Replace Module

  • Alter Module

  • Drop Module


Db2 built in data types
DB2 Built-in Data Types


User defined distinct types
User-Defined Distinct Types

UDTs –User-Defined Data Types:

  • Defined on existing data types

    • Generates a function to cast between the distinct type and its source type

    • Generates a function to cast between the source type and its distinct type

  • Used to enforce business rulesCREATE



Array data type
ARRAY data type

  • Conventional array

    CREATE TYPE arrType AS INTEGER ARRAY[1000];

  • Associative array

    CREATE TYPE arrType2 AS INTEGER ARRAY[VARCHAR(100)];

    CREATE TYPE arrType3 AS myRowType ARRAY[VARCHAR(100)];



Associative arrays example 2 of 2
Associative arrays: Example (2 of 2)


Data type anchoring
Data Type Anchoring

  • Keep procedural variables in sync with table columns

  • Scalar anchoring DECLAREempSalaryANCHORemployee.salary;

  • Row anchoring

    • DECLARE emp ANCHOR ROWemployee;

      BEGIN

      DECLARE emp ANCHOR ROW employee;

      SETemp.empno= ‘000100’;

      SETemp.lastname= ‘McClung’;

      SETemp.firstname= ‘Naomi’;

      END


Modules overview
Modules: Overview

  • Module = bundle of several related objects:

    • SPs, UDFs, global variables and cursors, types, conditions

    • Similar to a class in OO languages (but single instance)•

  • Four main benefits:

    • Code organization/structure

    • Scoping

      • CALL mySchema.myModule.myProc()

    • Information hiding

      • Each object can be “public” or “private”

    • Global privilege control

      • Instead of granting/revoking on each SP, UDF or variable


Modules module specification
Modules: Module specification

  • Module that exportsa type, a Stored Procedure, and a User-Defined Function

    • CREATE OR REPLACE MODULE myMod;

    • ALTER MODULE myMod PUBLISH

      • TYPE myRowTypAS ANCHOR ROW myTab;

    • ALTER MODULE myMod PUBLISH

      • FUNCTION myFunc(val1 ANCHOR myTab.col1)

        • RETURNS myRowTyp;

    • ALTER MODULE myMod PUBLISH

      • PROCEDURE myProc(OUTparm1 ANCHOR myTab.col2);


End modules module implementation
ENDModules: Module implementation


Modules other statements
Modules: Other statements

  • DROP MODULE myMod;

    • Drops entire module

  • ALTER MODULE myMod DROP BODY;

    • Drop “implementation”, keeps “specification”

  • ALTER MODULE myMod DROP PROCEDURE myProc;

    • Drops module object

  • GRANT EXECUTE ON MODULE myMod TO joe;

    • Grants user joeexecute privilege on all routines and access to all variables and types in myModModules


Unit summary
Unit summary

Having completed this unit, you should be able to:

  • Discuss DB2 data types

  • Select the proper data types

  • Work with user-defined data types

  • Work with Row data type

  • Work with Associative arrays

  • Implement data type anchoring

  • Create Module

  • Replace Module

  • Alter Module

  • Drop Module


ad