Before you start
About this series
This series of six DB2 SQL Procedure Developer tutorials covers all the basic constructs and methods of the SQL Procedural Language (SQL PL) and shows how it can be used in stored procedures, user-defined functions, and triggers, including error handling and deployment. It also covers some DB2 9.5 advanced features like optimistic locking, hierarchical queries, and declared global temporary tables. This series provides specifics on how the stored procedure can be invoked and how you can share data between procedures and functions. It introduces the DB2 development tool, including IBM Data Studio. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam. More exam resources can be found in the Resources section of this tutorial.
About this tutorial
In this tutorial, learn about DB2 9.5 SQL Procedural Language, including a variable, condition, and handler declaration, flow of control and iterative statements, as well as error handling mechanism. This is the first in a series of six tutorials you can use to help prepare for the DB2 SQL Procedures Developer exam 735.
In this tutorial, you will:
- Learn basic elements of SQL PL
- Understand how to declare variables, conditions, and handlers
- Learn about the flow of control statements
- Learn cursor processing and how result sets can be returned
- Understand error-handling mechanism
To take the DB2 9.5 SQL Procedure Developer exam, you must have already passed the DB2 9 Family Fundamentals exam (Exam 730). You can use the "DB2 Family Fundamentals tutorial series" to prepare for that exam (see Resources). It is a very popular series that has helped many people understand the fundamentals of the DB2 family of products.
For this tutorial to be useful, you should have background knowledge of how a relational database works as well as basic knowledge of databases and database programming constructs.
You do not need a copy of DB2 9.5 to complete this tutorial. However, you will get more out of the tutorial if you download the free trial version of DB2 Express-C to work along with this tutorial.
DB2 SQL Procedural Language (SQL PL) is a subset of the SQL Persistent Stored Module language standard. This standard combines the ease of data access with SQL with the flow control you get from a programming language. The current set of SQL PL statements and language features allows you to develop comprehensive, high-level programs in SQL as functions, stored procedures, and triggers. It gives you the ability to encapsulate the business logic into database objects that are easy to maintain, thus improving the performance of your database application.
SQL PL supports local and global variables, including declaration and assignment, conditional statements and iterative statements, transfer of control statements, error management statements, and methods to return a result set. These topics are all discussed in this tutorial.
SQL procedures allow you to assign and retrieve SQL values using local variables to support any SQL logic. In an SQL procedure, you need to declare a local variable before using it in your code.
The diagram in Listing 1 demonstrates syntax for variable declaration:
Listing 1. Syntax for variable declaration
.-,-----------------. V | |--DECLARE----SQL-variable-name-+-------------------------------> .-DEFAULT NULL------. >--+-data-type--+-------------------+-+-------------------------| | '-DEFAULT--constant-' |
SQL-variable-name defines the name of the local variable. The name cannot be the same as another variable or as a parameter name. It also should not be the same as a column name.
Figure 1 shows supported DB2 data types:
Figure 1. DB2 data types
DEFAULT value – if it’s not specified, NULL will be assigned at declaration time.
Here are some examples of variable declaration:
- DECLARE v_salary DEC(9,2) DEFAULT 0.0;
- DECLARE v_status char(3) DEFAULT ‘YES’;
- DECLARE v_descrition VARCHAR(80);
- DECLARE v1, v2 INT DEFAULT 0;
Please note that combining multiple variables of the same data type into a single DECLARE statement has only been supported starting with DB2 version 9.5.
Array data type
SQL Procedures, starting from version 9.5, support variable and parameters of array types. In order to define a variable of array type, you need to create this type in your database and then declare it in your procedure or application. Arrays are transient values that you can manipulate in stored procedures and applications but cannot store in tables.
DB2 supports the following syntax to create array data type:
Listing 2. Syntax to create array data type
>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[----------> .-2147483647-------. >--+------------------+--]------------------------------------->< '-integer-constant-'
An array type name should be qualified with schema and be unique on the current server. LONG VARCHAR, LONG VARGRPAHIC, XML and user-defined types are not supported data types for array elements.
The following are examples of array types:
CREATE TYPE numbers as INTEGER ARRAY; CREATE TYPE names as VARCHAR(30) ARRAY; CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY;
Please note that the integer "constant" specifies the maximum cardinality of the array and is optional. Array elements can be referenced as ARRAY-VARIABLE(subindex) where subindex must be between 1 and the cardinality of the array.
Now you can use this data type in your SQL procedure:
Listing 3. Using array data type in the procedure
CREATE PROCEDURE PROC_VARRAY_test (out mynames names) BEGIN DECLARE v_pnumb numbers; SET v_pnumb = ARRAY[1,2,3,5,7,11]; SET mynames(1) =’MARINA’; … END
DB2 supports several methods to manipulate with arrays. For example, function CARDINALITY(myarray) returns the number of elements of an array.
Assigning of values
SQL PL provides the SET statement to assign values to variables and array elements.
The following is simplified syntax for a SET variable statement:
SET variable_name = value/expression/NULL;
The variable name could be a name of a local variable, global variable, or an array element.
Here are some examples:
Listing 4. Examples of SET statement
SET var1 = 10; SET total = (select sum(c1) from T1); SET var2 = POSSTR(‘MYTEST’,’TEST’); SET v_numb(10) = 20; -- assign value of 20 to the 10th element of the array v_numb SET v_numb = ARRAY[1,2,3,4]; -- fill up array with values
The other methods of assigning values to variables include:
VALUES INTO SELECT (or FETCH) INTO
The following examples illustrate the use of these methods:
Listing 5. Examples of VALUE INTO and SELECT INTO
VALUES 2 INTO v1; VALUES ‘TEST’ INTO var2; SELECT SUM(c1) INTO var1 FROM T1; SELECT POSSTR(‘MYTEST’,’TEST’) INTO v1 FROM SYSIBM.SYSDUMMY1;
A special register is a storage area that a DBA defines for use by an application process. The value in a special register can be accessed by and referenced from an SQL statement or an SQL PL statement. You can find all the special registers at the IBM DB2 database for Linux, UNIX, and Windows Information Center (see Resources).
The most commonly used special registers are:
- CURRENT DATE
- CURRENT TIME
- CURRENT TIMESTAMP
- CURRENT USER
- CURRENT PATH
All of these registers can be referenced with an underscore in the reference --- for example, CURRENT_DATE.
The following procedure returns current date and time:
Listing 6. Procedures to return current dates and time
CREATE PROCEDURE get_datetime (out cdate date, out ctime time ) P1: BEGIN VALUES CURRENT DATE INTO cdate; VALUES CURRENT TIME INTO ctime; END P1
As a result of its execution, this procedure returns:
Name Input Output cdate 2008-08-28 ctime 13:47:41
Some of the special register values could be updated with the SET statement. For example, to update the schema you are accessing, you would need to change special register CURRENT SCHEMA as below.
SET CURRENT_SCHEMA = MYSCHEMA
To change the default function path, you need to update the special register CURRENT PATH.
SQL PL provides the DECLARE cursor statement to define a cursor and to support, along with other statements, the return of the result set and cursor processing.
Here is the syntax for cursor declaration:
Listing 7. Syntax for cursor declaration
Select-statement is a valid SQL SELECT statement. You can specify the FOR UPDATE clause to use the cursor for a positioned update or delete.
The WITHOUT HOLD/WITH HOLD option defines cursor status (open/close) after a COMMIT operation. WITHOUT HOLD is the default. If a cursor has been defined using the WITH HOLD option, the cursor remains OPEN after the COMMIT operation. All cursors are closed after the ROLLBACK operation.
The following is an example of an explicit cursor declaration that can be used for iterative processing later in the procedure:
Listing 8. Example of cursor declaration
DECLARE mycur1 CURSOR FOR SELECT e.empno, e.lastname, e.job FROM employee e, department d WHERE e.workdept = d.deptno AND deptname =’PLANNING’;
While an SQL statement can not have a parameter marker, it can have a reference to a local variable that is declared before the cursor. For example:
Listing 9. Cursor declaration using local variable
DECLARE v_dept CHAR(3) DEAFULT ‘ ‘; DECLARE myres_set CURSOR FOR SELECT empno, lastname, job, salary, comm. FROM employee WHERE workdept = v_dept;
Cursors and result sets
In SQL procedures, cursors can be used to do more than iterations through rows of result sets. They can also be used to return result sets to the calling program or another procedure.
- The WITHOUT RETURN/WITH RETURN option specifies whether or not the result table of the cursor is intended to be used as a result set returned from a procedure.
- The WITH RETURN TO CALLER option specifies that the result set from the cursor is returned to the caller, which could be another procedure or a client application. This is the default.
- The WITH RETURN TO CLIENT option specifies that the result set from the cursor is returned to the client application, bypassing any intermediate nested procedures.
To return a result set from a procedure, you need to:
- Create a procedure with the DYNAMIC RESULT SETS clause specified
- Declare cursor WITH RETURN clause
- Open this cursor and keep it open
If you close the cursor, the result set will not be returned to the calling application.
Listing 10 demonstrates the declaration of the cursor to return a single result set from a procedure:
Listing 10. Cursor declaration to return a single result set
CREATE PROCEDURE emp_from_dept() DYNAMIC RESULT SETS 1 P1: BEGIN DECLARE c_emp_dept CURSOR WITH RETURN FOR SELECT empno, lastname, job, salary, comm. FROM employee WHERE workdept = ‘E21’; OPEN c_emp_dept; END P1
To process the results of a cursor with a procedure, you need to do the following:
- DECLARE the cursor in the beginning of the block of a stored procedure
- Open this cursor
- Fetch the result of the cursor into local variables that have been previously declared (except for implicit cursor processing that is explained in FOR statement below)
- Close this cursor (Note: if you do not close the cursor, it will be implicitly closed when procedure is terminated)
There are two types of conditional statements supported in SQL PL — the IF statement and the CASE statement.
The IF statement allows you to branch your logic based on the status of a
condition. The IF statement supports the use of optional
ELSEIF clauses and a default
ELSE clause. An
END IF clause is required to indicate the end of the IF
Listing 11 shows a sample IF statement.
Listing 11. Sample IF statement
IF years_of_serv > 30 THEN SET gl_sal_increase = 15000; ELSEIF years_of_serv > 20 THEN SET gl_sal_increase = 12000; ELSE SET gl_sal_increase = 10000; END IF;
SQL PL also supports two types of the CASE statement to branch logic based on the status of a condition:
- A simple case statement is used to enter into some logic based on a literal value.
- A searched case statement is used to enter into some logic based on the value of an expression.
Listing 12 shows an example of a stored procedure that is using the searched CASE statement.
Listing 12. Stored procedure using the searched CASE statement
CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6)) BEGIN DECLARE years_of_serv INT DEFAULT 0; DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0; SELECT YEAR(CURRENT DATE) - YEAR(hiredate) INTO years_of_serv FROM empl1 WHERE empno = empid; CASE WHEN years_of_serv > 30 THEN SET v_incr_rate = 0.08; WHEN years_of_serv > 20 THEN SET v_incr_rate = 0.07; WHEN years_of_serv > 10 THEN SET v_incr_rate = 0.05; ELSE SET v_incr_rate = 0.04; END CASE; UPDATE empl1 SET salary = salary+salary*v_incr_rate WHERE empno = empid; END
SQL PL supports several methods to repeatedly execute some logic, including a simple LOOP, a WHILE LOOP, a REPEAT LOOP, and a FOR LOOP:
- LOOP-- simple loop
- L1: LOOP
- SQL statements;
- LEAVE L1;
- END LOOP L1;
- WHILE loop -- check condition on entrance
- WHILE condition
- SQL statements
- END WHILE;
- REPEAT loop -- check condition before exit
- SQL statements;
- UNTIL condition
- END REPEAT;
- FOR loop -- implicit loop over result set
- FOR loop_name AS
- SELECT … FROM
- SQL statements;
- END FOR;
Please note that the FOR statement is distinct from the others because it is used to iterate over rows of a defined result set.
To demonstrate the use of these looping technique, let’s write a procedure that gets the last name, year-of-service and age of each employee from an EMPLOYEE table and inserts it into the new table REPORT_INFO_DEPT, declared as lname varchar(15), hiredate date, birthdate date.
Please note that the same can be done using a simple SQL statement, but, in this instance, we use three different loop statements.
Listing 13. Simple LOOP example
CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER) Ll: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE; DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1; OPEN c1; FETCH_LOOP: LOOP FETCH c1 INTO v_lastname, v_hired, v_birthd; IF v_at_end <> 0 THEN -- loop until last row of the cursor LEAVE FETCH_LOOP; END IF; SET v_counter = v_counter + 1; INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); END LOOP FETCH_LOOP; SET p_counter = v_counter; END Ll
Now let’s do the same using the WHILE loop statement.
Listing 14. WHILE LOOP example
CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER) Pl: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE; DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1; OPEN c1; FETCH c1 INTO v_lastname, v_hired, v_birthd; WHILE (v_at_end = 0) DO INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); SET v_counter = v_counter + 1; FETCH c1 INTO v_lastname, v_hired, v_birthd; END WHILE; SET p_counter = v_counter; END P1
The REPEAT loop is very similar to the WHILE loop, except that the condition is checked at the end (thus, it is really an UNTIL loop).
Now, let’s fill up REPORT_INFO_DEPT table using a procedure with the FOR loop statement.
Listing 15. FOR LOOP example
CREATE PROCEDURE DEPT_REPT1 (DEPTIN char(3), OUT p_counter INT) P1:BEGIN DECLARE v_counter INT DEFAULT 0; FOR dept_loop AS SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin DO INSERT INTO REPORT_INFO_DEPT values (dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate); SET v_counter = v_counter + 1; END FOR; SET p_counter = v_counter; END P1
Please note that the last procedure does not open, fetch or close the cursor — it is all done implicitly by the FOR loop statement. Also, you can reference the values implicitly fetched inside this loop, qualifying the column with name of the loop (for example, dept_loop.lastname) — without the use of local variables to store these values.
Exception handling mechanism
DECLARE named condition
SQL PL permits declarations of user-named conditions for a given SQLSTATE to be used later by error handling. A condition name must be unique in the compound statement and can only be referenced in the compound statement in which it is declared.
Listing 16 shows the syntax to declare a named condition.
Listing 16. Syntax to declare a named condition
|--DECLARE--condition-name--CONDITION--FOR----------------------> .-VALUE-. .-SQLSTATE--+-------+-. >--+---------------------+--string-constant---------------------|
Here are examples of condition declaration:
DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’; DECLARE overflow CONDITION FOR SQLSTATE '22003';
DECLARE condition handler
If an error occurs, the behavior of your stored procedure is determined based on the condition handlers. One or more condition handlers can be declared in your procedure for a general or named condition, and for a specific SQLSTATE. When an SQL statement raises an SQLEXCEPTION or SQLWARNING (SQLCODE <> 0), the control is transferred to the declared handler for a general exception or for the specific SQLSTATE value.
Listing 17 shows the syntax that is supported for handler declarations.
Listing 17. Syntax for handler declarations
|--DECLARE--+-CONTINUE-+--HANDLER--FOR--------------------------> +-EXIT-----+ '-UNDO-----' >--+-specific-condition-value-+--| SQL-procedure-statement |----| '-general-condition-value--' WHERE specific-condition-value .-,----------------------------------------. V .-VALUE-. | |----+-SQLSTATE--+-------+--string-constant-+-+-----------------| '-condition-name-----------------------'
Here are some examples to illustrate how it works. If the UPDATE statement fails in the following procedure, control is transferred to the EXIT handler. As a result, the procedure is terminated, but its output parameter contains the value for SQLCODE and SQLSTATE.
Listing 18. Example of handler to return SQLCODE and SQLSTATE
CREATE PROCEDURE simple_error (IN new_job CHAR(8), IN p_empno CHAR(6), OUT p_state_out CHAR(5),OUT p_code_out INT) SPECIFIC simple_error1 BEGIN DECLARE SQLCODE INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE, SQLCODE INTO p_sqlstate_out, p_sqlcode_out FROM SYSIBM.SYSDUMMY1; UPDATE EMPLOYEE SET job = new_job WHERE empno = p_empno; END
Please note that SQLCODE and SQLSTATE should be explicitly declared as local variables.
Listing 19 demonstrates a scenario when you do not want to exit the procedure, if a given error occurs, but prefer to continue with some action. To understand this example, please note the table TAB1 defined as (col1 int, col2 char(5)) and DB2 does not truncate a value by default, but raises SQLSTATE ‘22001’ if value overflow occurs.
Listing 19. Procedure that has CONTINUE handler
CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10)) P1: BEGIN DECLARE SQLCODE INTEGER default 0; DECLARE SQLSTATE CHAR(5) default ‘ ‘; DECLARE v_trunc INTEGER default 0; DECLARE overflow CONDITION FOR SQLSTATE '22001'; DECLARE CONTINUE HANDLER FOR overflow BEGIN INSERT INTO tab1 VALUES (num, substr (new_sataus,1,5)); SET v_trunc = 2; END; INSERT INTO tab1 VALUES(num, new_status); RETURN v_trunc; END P1
If you call this procedure with the new_status input parameter ‘Too many’, the SQLSTATE ‘22001’ is raised during INSERT statement execution and control is passed to CONDITION HANDLER. As a result, the v_trunc indicator will be set up to 2, the new row will be inserted into the TAB1 table with a truncated value for COL2, and your procedure will end successfully.
Forcing exception -- SIGNAL SQLSTATE
SQL PL supports a method to signal an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE, along with optional message text. Listing 20 shows the syntax for the SIGNAL statement.
Listing 20. Syntax for SIGNAL statement
You can signal a user-defined SQLSTATE as a character string constant with exactly five characters. It must start with the number 7,8, or 9 or the letters I through Z.
You can also raise a specific condition name, which must be declared within the compound statement that contains the SIGNAL statement as the listing below illustrates.
DECLARE condition overflow for SQLSTATE ‘22001’; …. SIGNAL overflow SET MESSAGE_TEXT = ‘Too many characters, truncated’;
Listing 21 demonstrates the use of a SIGNAL statement.
Listing 21. Use of a SIGNAL statement
CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10)) P1: BEGIN DECLARE SQLCODE INTEGER default 0; DECLARE SQLSTATE CHAR(5) default ''; IF length (new_status) > 5 THEN SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG'; END IF; INSERT INTO TAB1 VALUES (num, new_status); END P1
In this tutorial, you've learned about the SQL Procedural Language that is used to code procedures, user-defined functions, and triggers. You've learned all the basic element of SQL Procedure Language, including variable declaration and assignment, syntax and use, conditional and iterative statements to control the flow of procedure logic. You also have learned how to use error handling and result sets. This allows you to build customized and complex business logic that can be integrated into your overall database application.
- In Part 2 of the series, "DB2 SQL Procedures," learn about DB2 9.5 SQL procedures, including an introduction to stored procedures, the advantages of using stored procedures, and the differences between SQL procedures and external procedures.
- Part 3 of the series, "DB2 SQL functions," introduces you to user-defined functions and walks you through the basic steps used to construct user-defined functions. This tutorial also introduces you to the structure of SQL functions and covers the ins and outs of SQL function development.
- Part 5 of the series, "Advanced SQL features," learn about IBM DB2 temporary tables, ADMIN_CMD procedure, savepoints and other advanced SQL features.
- Visit the Test 735: DB2 9.5 SQL Procedure Developer page to get comprehensive resources related to the exam.
- The DB2 Family Fundamentals tutorial series gives you the resources you need to ace the DB2 Family Fundamentals test.
- Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
- The DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond IBM Redbook contains information about SQL Procedures.
- DB2 v9.5 Information Center: Learn more about DB2 SQL procedures.
Get products and technologies
- Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
- Download a free trial version of DB2 9 for Linux, UNIX, and Windows.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.