Calling a Stored Procedure


In Chapter 4, "Working with Databases and Database Objects," we also saw that 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. The basic syntax for this statement is:

 CALL [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.

Suppose an SQL stored procedure named GET_SALES was created by executing a CREATE PROCEDURE statement that looks like this:

 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 




DB2 9 Fundamentals Certification Study Guide
DB2 9 Fundamentals: Certification Study Guide
ISBN: 1583470727
EAN: 2147483647
Year: 2007
Pages: 93

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net