What is the role of stored procedure in DB2
- Workloads in a client/server environment can be moved from the client to the sever by creating one or more stored procedures.
- Once a stored procedure has been created and registered with a database (by executing the CREATE PROCEDURE SQL statement), that procedure can be invoked, either interactively, using a utility such as the Command Line Processor, or from a client application.
- Stored procedures are invoked by executing the CALL SQL statement.
DB2 Tutorial | Part-1 | Part-2 | Part-3
The basic syntax for this statement is:
ALL [ProcedureName](<[InputParameter]|
[OutputParameter] | NULL>, …)
Where:
ProcedureName- Identifies the name assigned to the procedure to be invoked.
InputValue -Identifies one or more parameter values that are to be passed to the procedure being invoked.
OutputParameter – Identifies one or more parameter markers or host variables that are to receive return values from the procedure being invoked.
Creating a Stored Procedure
CREATE PROCEDURE get_sales (IN quota INTEGER, OUT retcode CHAR(5)) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE SQLSTATE CHAR(5); DECLARE sales_results CURSOR WITH RETURN FOR SELECT sales_person, SUM(sales) AS total_sales FROM sales GROUP BY sales_person HAVING SUM(sales) > quota; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET retcode = SQLSTATE; OPEN sales_results; SET retcode = SQLSTATE; END
This procedure could be invoked from the Command Line Processor by connecting to the appropriate database and executing a CALL statement that looks like this:
CALL get_sales (25, ?)
If you created this procedure and executed this statement after connecting to the SAMPLE database provided with DB2, the value 25 would be passed to the input parameter named QUOTA, and a question mark (?) would be used as a place-holder for the value that will be returned in the output parameter named RETCODE.
The procedure would then execute the SQL statements contained in it and return information that looks something like this:
Value of output parameters --------------------------- Parameter Name : RETCODE Parameter Value : 00000 Result set 1 ----------- SALES_PERSON TOTAL_SALES ------------ ------------ GOUNOT 50 LEE 91 2 record(s) selected. Return Status = 0 ....
Srinimf 