SQL Procedure Language

The SQL Procedure Language was designed only for writing stored procedures and is available across the entire DB2 family, including the iSeries systems. This procedural language 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. Stored procedures written using SQL Procedure Language will be managed mostly 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 DB2 Development Center. 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; all the programmer does is write a CREATE PROCEDURE…LANGUAGE SQL…name: BEGIN…END name DDL statement. All the code is in the body of the create statement. The procedure body includes compound, declaration, assignment, and conditional statements; iterative control structuresLOOP, REPEAT, WHILEexception handling; and 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