Learning Visually with Examples [Electronic resources]

7.14. Stored Procedures

Stored procedures are programs whose executable binaries reside at the database server. They serve as subroutines to calling applications, and they normally wrap multiple SQL statements with flow logic. Figure 7.27 depicts a situation in which stored procedures are useful.

In the figure,

Program 1 and stored procedure

mysp execute the same set of SQL statements.

Program 1 , however, does not perform as well as

Program 2 because of the extra overhead of sending each SQL statement through the network and waiting for its return. On the other hand,

Program 2 only needs to call the stored procedure

mysp once and then wait for its return. Because

mysp performs all the SQL statements within the database server, there is minimal network overhead.

Besides improving response time for applications running on a different server than the database server, stored procedures also provide a central location to store database application logic. This allows for a single place to maintain your code.

external procedures. For this type of procedure, the

CREATE PROCEDURE statement simply registers the procedure to DB2. The executable code is normally kept under the sqllib\function\routine subdirectory.

In the case of SQL PL stored procedures, the source code is included with the

CREATE PROCEDURE statement. Moreover, executing the

CREATE PROCEDURE statement will compile the code, bind the SQL statements, and create the necessary packages.

NOTE

The following is an example of an SQL PL stored procedure created in the database

sample (which is provided with DB2).

CREATE PROCEDURE CSMMGR.NEW_SALARY (IN  p_empno   CHAR(6), 
OUT p_empName VARCHAR(30) ) 
LANGUAGE SQL 
--------------------------------------------------------------------- 
-- SQL Stored Procedure used to update the salary of an employee 
--------------------------------------------------------------------- 
P1: BEGIN 
DECLARE v_firstName VARCHAR(12); 
DECLARE v_lastName  VARCHAR(15); 
UPDATE employee SET salary = salary * 1.05 
WHERE empno = p_empno; 
SELECT lastname, firstnme INTO v_lastName, v_firstName 
FROM employee WHERE empno = p_empno; 
SET p_empName = v_lastName || ', ' || v_firstName; 
END P1 

In this example, the procedure name is

CSMMGR.NEW_SALARY . This procedure takes an input parameter

p_empno and an output parameter

p_empName . The procedure will increase by 5% the value in the

salary column of table

employee for the employee with employee number

p_empno . It will then return the name of the employee who received the increase in the format

lastname, firstname . Figure 7.28 shows the Development Center tool used to develop the procedure. At the bottom of the figure, you can see the result of its execution.