SQL PL
SQL Procedural Language (SQL PL) is an inline programming language that can be used inside SQL statements. It allows common programming language features such as variable assignments, looping, and conditional statements. SQL PL is useful for adding programming logic to UDFs, stored procedures, and compound SQL statements. The code is easy to use and highly portable. Stored procedures that are written in SQL PL are called SQL Stored Procedures. Users of SQL PL do not need a strong programming knowledge to take advantage of this functionality.
One of the easiest ways to learn how to use SQL PL is by using the DB2 Development Center. The Development Center wizard will prompt the user and will then generate a skeleton stored procedure or UDF written in SQL PL. This code can be later customized to meet the specific requirements. More information on the DB2 Development Center is available in Chapter 8.
SQL Stored Procedures
Like external stored procedure definitions, SQL procedure definitions provide the following information:
The procedure name
Parameter attributes
The language in which the procedure is written (for an SQL procedure, the language is SQL)
Other information about the procedure, such as the specific name of the procedure and the number of result sets returned by the procedure
Unlike a CREATE PROCEDURE statement for an external stored procedure, the CREATE PROCEDURE statement for an SQL procedure does not specify the EXTERNAL clause. Instead, an SQL procedure has a procedure body, which contains the source statements for the stored procedure.
The following example shows a CREATE PROCEDURE statement for a simple stored procedure. The procedure name, the list of parameters that are passed to or from the procedure, and the LANGUAGE parameter are common to all stored procedures.
However, the LANGUAGE value of SQL and the BEGIN...END block, which forms the procedure body, are particular to an SQL procedure.
CREATE PROCEDURE UPDATE_PRODUCT (1)
(IN PRODUCT_NUMBER INTEGER, (2)
IN PRODUCTS_RECEIVED INTEGER) (2)
LANGUAGE SQL (3)
BEGIN
UPDATE PRODUCT (4)
SET UNITS_IN_STOCK = UNITS_IN_STOCK + PRODUCTS_RECEIVED)
WHERE PRODUCT_ID = PRODUCT_NUMBER;
END
Notes for the previous example:
The stored procedure name is UPDATE_PRODUCT.
The two parameters have data types of INTEGER. Both are input parameters.
LANGUAGE SQL indicates that this is an SQL procedure, so a procedure body follows the other parameters.
The procedure body consists of a single SQL UPDATE statement, which updates rows in the product table.
Within the SQL procedure body, you cannot use OUT parameters as a value in any expression. You can only assign values to OUT parameters using the assignment statement, or as the target variable in the INTO clause of SELECT, VALUES, and FETCH statements. You cannot use IN parameters as the target of assignment or INTO clauses.
For a more detailed explanation of SQL Stored Procedures, see the DB2 UDB Application Development Guide.