SQL Procedure Language

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 8.  Stored Procedures

---

SQL Procedure Language

The SQL Procedure Language is a procedural language that was designed only for writing stored procedures and is available across the entire DB2 family, including the AS/400. It is based on the ANSI/ISO standard language SQL/PSM. Its major benefit is that users can create stored procedures very quickly, using a simple, easily understood language, without the headaches of precompilers, compilers, link editors, binding, and special authorizations. This is because stored procedures written using SQL Procedure Language will be mostly managed by DB2; this automates the process and allows programmers and users to simply write the logic and pass if off to DB2.

SQL procedures support multiple parameters for input, output, and returning output results sets to clients . SQL procedures are defined in the DB2 catalog, and the source can be stored there also. The following shows an SQL procedure that was created from a client workstation using the Stored Procedure Builder (SPB) tool. It accepts one input parameter and returns a result set to the client.

 CREATE PROCEDURE DW.SQLProc1 (IN gender char(6))      SPECIFIC DW.Genders       RESULT SETS 1     LANGUAGE SQL P1: BEGIN     DECLARE gender_value CHAR(1);     DECLARE bad_gender CONDITION FOR SQLSTATE '99001';     CASE gender        WHEN 'MALE' THEN            SET gender_value = 'M';        WHEN 'FEMALE' THEN            SET gender_value = 'F';        ELSE SIGNAL bad_gender;     END CASE;     -- Declare cursor     DECLARE cursor1 CURSOR WITH RETURN FOR         SELECT E.EMPNO, E.LASTNAME, E.HIREDATE, D.DEPTNO,                D.DEPTNAME         FROM DW.DEPARTMENT D, DW.EMPLOYEE E         WHERE E.WORKDEPT = D.DEPTNO           AND E.SEX = :gender_value         ORDER BY E.WORKDEPT;     -- Cursor left open for client application     OPEN cursor1; END P1 

The language itself is primarily SQL (DML and DDL) with local variables , cursors , assignment statements, flow control, and signal/resignal conditions. The real difference with the SQL Procedure Language is how it becomes a stored procedure, since all the programmer does is write a CREATE PROCEDURELANGUAGE SQLname: BEGINEND name DDL statement. All the code is in the body of the create statement. The procedure body includes

• Compound statements

• Declaration statements

• Assignment statements

• Conditional statements

• Iterative control structures

  - Loop

  - Repeat

  - While

• Exception handling

• Calling another stored procedure

The primary language statements that show the strength of this approach are

• IF, CASE, and LEAVE

• LOOP, REPEAT, and WHILE

• FOR, CALL, and RETURN

• GET DIAGNOSTICS

• SIGNAL and RESIGNAL

Developing SQL Stored Procedures

DB2 implements SQL stored procedures as external C programs. The DB2 precompiler can take an SQL procedure as input and write out a C source module. An SQL procedure is created using one of the following three methods :

• SPB

• Directly invoking DSNTPSMP

• Using JCL or CLIST to prepare

When an SQL procedure is built using the SPB tool (discussed later in this chapter), all the "code" is developed on the client workstation, and the completed procedure is passed to the DSNTPSMP stored procedure. DSNTPSMP is the OS/390 SQL Procedure Processor, a REXX stored procedure, and it is also fully customizable. This processor invokes the following steps:

• SQL precompile

• C precompile

• C compile

• Prelink

• Link

• Procedure definition

• Bind

Once the procedure is built in this fashion, it is immediately executable. When using the other two methods for developing an SQL procedure, they basically just bypass the SPB and either directly invoke DSNTPSMP or execute the steps listed above under manual control.

---

Team-Fly

Top