1 / 14

Overview of SQL PL Language Elements & Modules

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

benito
Download Presentation

Overview of SQL PL Language Elements & Modules

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Overview of SQL PL Language Elements & Modules

  2. 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

  3. DB2 Built-in Data Types

  4. 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

  5. ROW data type

  6. 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)];

  7. Associative arrays: Example (1 of 2)

  8. Associative arrays: Example (2 of 2)

  9. 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

  10. 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

  11. 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);

  12. ENDModules: Module implementation

  13. 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

  14. 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

More Related