Overview of sql pl language elements modules
This presentation is the property of its rightful owner.
Sponsored Links
1 / 14

Overview of SQL PL Language Elements & Modules PowerPoint PPT Presentation


  • 86 Views
  • Uploaded on
  • Presentation posted in: General

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

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


Row data type

ROW data type


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 1 of 2

Associative arrays: Example (1 of 2)


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


  • Login