overview of sql pl language elements modules
Download
Skip this Video
Download Presentation
Overview of SQL PL Language Elements & Modules

Loading in 2 Seconds...

play fullscreen
1 / 14

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


  • 120 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
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
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)];

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