5.2 Comparing SQL PL and inline SQL PL

< Day Day Up >

DB2 SQL procedures are created using a high level language called DB2 SQL Procedural Language (often called SQL PL) which has many direct equivalents and mappings to Oracle's PL/SQL language. Therefore, converting Oracle stored procedures to DB2 stored procedures is usually straight forward. For triggers, functions, and dynamic compound statements, DB2 uses a subset language called inline SQL PL. Before looking at code samples, it is important to clarify the difference between SQL PL and inline SQL PL.

The following is an excerpt from the book DB2 SQL Procedural Language for Linux, UNIX, and Windows by Paul Yip et. al.

"Although the language syntax for SQL PL is consistent throughout from an application development perspective, the language implementation in DB2 to support SQL PL for store procedures is completely different from that of triggers and UDFs.

When building an SQL procedure, DB2 will convert it into two components: a C language file and a DB2 bind file. The C file contains the procedural logic while the bind file contains the SQL for the procedure. The C code is then compiled into a library file that can be called by DB2 processes and the BIND file is bound to the database where it then becomes a package. When you execute the stored procedure, the library works hand in hand with the database package to execute SQL according to the stored procedure logic.

In contrast, SQL PL in triggers and UDFs are not implemented as libraries and packages. There is no compiled C component, nor is a bind file generated. When you use SQL PL in triggers and UDFs, the code is executed dynamically within the engine as a single dynamic compound statement.

The implementation differences result in some SQL PL elements that are supported in SQL procedures but not in triggers and UDFs. SQL PL support in triggers and UDFs is a subset of that in stored procedures and includes support for the following SQL PL elements:

DECLARE <variable>

FOR

GET DIAGNOSTICS

IF

ITERATE

LEAVE

SIGNAL

WHILE

SET

The following subsections discuss the basics of creating DB2 stored procedures, triggers, and user defined functions. We also compare variable declaration, conditional statements, and flow of control statements between Oracle PL/SQL and DB2 SQL PL. This chapter (section 5.2 onwards) has been organized to serve as a convenient quick reference for you when converting specific features of Oracle PL/SQL to DB2 SQL PL.

For the full documentation of features of SQL PL, please consult the DB2 UDB SQL Reference, Volume 1, SC09-4844, Volume 2, SC09-4845, or DB2 SQL Procedural Language for Linux, UNIX, and Windows.

Delimiter

In DB2 UDB, statements in trigger, function, and procedure are separated by a semi colon (;) which also is the default statement terminator when running a group of SQL statements in DB2 CLP. Therefore, in trigger, procedure, or function, a semi colon (;) cannot be used as the terminator. You can choose other characters such as @ or ! as the termination character. All the DB2 UDB examples in this chapter use ! as the termination character. To execute the file which contains the function, trigger, or procedure to create the object in DB2 UDB, use the following command:

 db2 -td<termination_character> -f file-name

Where termination_character = @ or ! etc. For example, if the termination character is ! and file-name is testproc.db2, the command will be:

 db2 -td! -f testproc.db2

5.2.1 Create procedure

Oracle uses the following syntax to create a stored procedure:

    CREATE OR REPLACE PROCEDURE process_withdrawal( Account_Id VARCHAR2                                                   ,Cheque_No VARCHAR2                                                   ,Amount NUMBER )    IS    ...

DB2 uses slightly different syntax:

    CREATE PROCEDURE process_withdrawal ( IN Account_Id VARCHAR(10)                                         ,IN Cheque_No VARCHAR(10)                                         ,IN Amount DECIMAL(10,2) )    LANGUAGE SQL    ...

Notes

DB2 does not support REPLACE clause in the create procedure statement, so you may want to drop the procedure before creating one. When developing procedures using the Development Center, procedures are automatically dropped for you if you choose to rebuild a procedure.
DB2 does not allow to specify data type without length, so you need to check declaration for the column (those parameters correspond to) to declare them properly. If converting with the migration toolkit; this is done for you automatically if your original code uses %TYPE.
LANGUAGE SQL is a mandatory statement in DB2 UDB V7.x, optional in V8.

5.2.2 Create trigger

In this section we give you a high-level overview of the differences in the trigger definition between Oracle and DB2 UDB. For a detailed description of triggers, please reference the DB2 UDB Application Development Guide and the SQL Reference.

Example 5-1 shows you a simple Oracle trigger, which has set a value to a table column before inserting a row.

Example 5-1: Simple Oracle trigger

 CREATE OR REPLACE TRIGGER connect_audit_trg    BEFORE INSERT ON connect_audit    FOR EACH ROW BEGIN    :new.timestamp := SYSDATE; END;

The Example 5-2 shows how to define the corresponding trigger in DB2.

Example 5-2: Simple DB2 trigger

 CREATE TRIGGER connect_audit_trg    NO CASCADE BEFORE INSERT ON connect_audit    REFERENCING NEW AS n    FOR EACH ROW    MODE DB2SQL BEGIN ATOMIC    SET n.timestamp = CURRENT TIMESTAMP; END!

Notes

DB2 does not support REPLACE clause in the create trigger statement, so you may want to drop the trigger before creating one.
The NO CASCADE statement is mandatory for BEFORE triggers. It specifies that the triggered action of the trigger will not cause other triggers to be activated.
With the REFERENCE NEW AS n clause, you can associate a qualifier n to the new values provided by the initiated insert statement.
MODE DB2SQL is a required clause that must be included as part of the definition.
DB2 UDB also supports BEFORE and AFTER trigger for UPDATE and DELETE (activated once FOR EACH ROW changed). AFTER triggers, which activate FOR EACH STATEMENT as well as INSTEAD OF triggers for INSERT, UPDATE, or DELETE statements, are also supported.

Example 5-3 is an Oracle trigger that inserts some values of a deleted row into a history table.

Example 5-3: Oracle trigger with DML command

 CREATE TRIGGER emp_history_trg    AFTER DELETE ON employees    FOR EACH ROW BEGIN    INSERT INTO emp_history( emp_id                            ,first_name                            ,last_name)    VALUES ( :old.emp_id            ,:old.first_name            ,:old.last_name ); END;

Example 5-4 shows how to define the corresponding trigger in DB2.

Example 5-4: DB2 trigger with DML command

 CREATE TRIGGER emp_history_trg    AFTER DELETE ON EMPLOYEES    REFERENCING OLD AS d    FOR EACH ROW    MODE DB2SQL BEGIN ATOMIC    INSERT INTO emp_history ( emp_id                             ,first_name                             ,last_name)    VALUES ( d.emp_id            ,d.first_name            ,d.last_name); END!

5.2.3 Create function

A function that is created by a user that is not one of the built-in functions in DB2 UDB is called a user-defined function or UDF. DB2 supports five different kinds of functions:

SQL scalar, table, or row function
Sourced or template function
OLE DB function
External table function
External scalar function

In this chapter, we discuss only the SQL functions. For a description of the other function types please see the SQL Reference.

The most common UDF definition for SQL functions looks like the following:

    CREATE FUNCTION function_name ( parameters )       RETURNS return_type       LANGUAGE SQL       READS SQL DATA       RETURN statement

Notes

Input parameters are optional. However, the brackets are mandatory.
The mandatory return type is one of the following kinds
- Scalar
  
  A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid.
- Table
  
  A table function may be used in a FROM clause and returns a table.
- Row
  
  A row function may be used as a transform function and returns a row.
LANGUAGE SQL is mandatory statement in DB2 UDB V7.x, optional in V8.
READS SQL DATA is optional and implied by default for SQL function.
Following to the RETURN keyword you have to specify the SQL function body.

5.2.4 Variables declaration and assignment

Oracle PL/SQL permits declaring variables in four different places:

In a parameter list of a stored procedure or function
Within the body of a stored procedure, function, or trigger
Within a package declaration
Within a package body declaration

DB2 does not have a notion of package to group functions and procedures, instead, DB2 uses schema to group procedures and functions. So, variables can be declared:

In a parameter list of a stored procedure or function
In the body of a stored procedure, function, or trigger

DB2 SQL PL supports native data types and user defined distinct type for the variable declaration. It requires the DECLARE clause, and uses the optional DEFAULT clause to initialize variables. So, for example, PL/SQL declaration:

    l_balance NUMBER(10,2) :=0.0;

This is converted to SQL PL as:

    DECLARE l_balance NUMERIC(10,2) DEFAULT 0.0;

Note

Variable declarations need to be placed in the BEGIN ... END block.

SQL PL uses SET statement to assign values to variables. So, for example, PL/SQL assignment

    l_balance := 19.99;

will be converted as:

    SET l_balance = 19.99;

Note

SET statement also can be used to assign a local variable with table column value:

 SET l_balance =(SELECT balance from account_info where account_no = actNo);

The SELECT statement should return one row only. Error will be returned if more then one row is selected. You can use a FETCH FIRST n ROW in SELECT statement to control the number of rows returned.

5.2.5 Conditional statements and flow control

SQL PL and PL/SQL provide similar functionality and syntax for conditional statements and a variety of LOOP statements to provide flow control. Table 5-1 maps Oracle PL/SQL statements to DB2 SQL PL.

Table 5-1: Mapping of conditional statements
Oracle PL/SQL	DB2 SQL PL
IF - THEN - END IF; IF - THEN - ELSE - END IF; IF - THEN - ELSIF - END IF;	IF - THEN - END IF; IF - THEN - ELSE - END IF; IF - THEN - ELSEIF - END IF;
LOOP statements; END LOOP;	[L1:] LOOP statements; LEAVE L1; END LOOP [L1];
WHILE condition LOOP statements; END LOOP;	WHILE condition DO statements; END WHILE;
LOOP statements; EXIT WHEN condition; END LOOP;	REPEAT statements; UNTIL condition; END REPEAT;
OPEN cursor_variable FOR select_statement;	FOR variable AS cursor_name CURSOR FOR select_statement DO statements; END FOR; Note: If cursor_variable is a REF cursor, DB2 would define the cursor and open it with RETURN TO CLIENT/CALLER.
FOR l_count IN lower_bound ..upper_bound LOOP statements; END LOOP;	No corresponding statements, but MTK will convert it as: SET l_count = lower_bound; WHILE l_count <= upper_bound DO statements; SET l_count = l_count + 1; END WHILE ;

Consider some conversion example that use condition statement IF THEN ELSE.

Oracle conditional statement:

    IF v_TotalStudents = 0 THEN        INSERT INTO temp_table (char_col)          VALUES ('There are no students registered');      ELSIF v_TotalStudents < 5 THEN        INSERT INTO temp_table (char_col)          VALUES ('There are only a few students registered');      ELSIF v_TotalStudents < 10 THEN        INSERT INTO temp_table (char_col)          VALUES ('There are a little more students registered');      ELSE        INSERT INTO temp_table (char_col)          VALUES ('There are many students registered');      END IF;

Will be converted as follow:

    IF v_TotalStudents = 0 THEN            INSERT INTO temp_table (char_col)                     VALUES ('There are no students registered');      ELSEIF v_TotalStudents < 5 THEN           INSERT INTO temp_table (char_col)                      VALUES ('There are only a few students registered');      ELSEIF v_TotalStudents < 10 THEN           INSERT INTO temp_table (char_col)                     VALUES ('There are a little more students registered');      ELSE           INSERT INTO temp_table (char_col)                    VALUES ('There are many students registered');      END IF;

Or, you can convert this statement using the CASE statement, supported by DB2:

    CASE       WHEN v_TotalStudent = 0 THEN            INSERT INTO temp_table (char_col)                   VALUES ('There are no students registered');       WHEN v_TotalStudent < 5 THEN            INSERT INTO temp_table (char_col)                   VALUES ('There are only a few students registered');       WHEN v_TotalStudent < 10 THEN            INSERT INTO temp_table (char_col)                   VALUES ('There are a little more students registered');       OTHER            INSERT INTO temp_table (char_col)                   VALUES ('There are many students registered');    END;

< Day Day Up >