o verview of sql pl l anguage e lements & m odules
TRANSCRIPT
![Page 1: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/1.jpg)
OVERVIEW OF SQL PL LANGUAGE ELEMENTS & MODULES
![Page 2: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/2.jpg)
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
![Page 3: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/3.jpg)
DB2 BUILT-IN DATA TYPES
![Page 4: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/4.jpg)
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
![Page 5: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/5.jpg)
ROW DATA TYPE
![Page 6: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/6.jpg)
ARRAY DATA TYPE
Conventional arrayCREATE TYPE arrType AS INTEGER ARRAY[1000];
Associative arrayCREATE TYPE arrType2 AS INTEGER ARRAY[VARCHAR(100)];
CREATE TYPE arrType3 AS myRowType ARRAY[VARCHAR(100)];
![Page 7: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/7.jpg)
ASSOCIATIVE ARRAYS: EXAMPLE (1 OF 2)
![Page 8: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/8.jpg)
ASSOCIATIVE ARRAYS: EXAMPLE (2 OF 2)
![Page 9: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/9.jpg)
DATA TYPE ANCHORING
Keep procedural variables in sync with table columns
Scalar anchoring DECLAREempSalaryANCHORemployee.salary;
Row anchoring DECLARE emp ANCHOR ROWemployee;
BEGINDECLARE emp ANCHOR ROW employee;SETemp.empno= ‘000100’; SETemp.lastname= ‘McClung’;SETemp.firstname= ‘Naomi’;
END
![Page 10: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/10.jpg)
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
![Page 11: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/11.jpg)
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);
![Page 12: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/12.jpg)
ENDMODULES: MODULE IMPLEMENTATION
![Page 13: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/13.jpg)
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
![Page 14: O VERVIEW OF SQL PL L ANGUAGE E LEMENTS & M ODULES](https://reader035.vdocuments.us/reader035/viewer/2022062712/56649c775503460f9492c32c/html5/thumbnails/14.jpg)
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