There are different types of stored procedures available. They are SQL and external.
SQL Stored Procedures
As the name implies, an SQL stored procedure is composed entirely of SQL statements. SQL stored procedures are created by executing one form of the CREATE PROCEDURE SQL statement. The basic syntax for the form of the CREATE PROCEDURE statement that is used to create an SQL stored procedure looks something like this:
CREATE PROCEDURE [procedure_name] ( [param_type] [param_name] [data_type], ...) <SPECIFIC [specific_name]> <DYNAMIC RESULT SETS [num_result_sets]> <MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA> <DETERMINISTIC | NOT DETERMINISTIC> <CALLED ON NULL INPUT> <INHERIT SPECIAL REGISTERS> <[OLD | NEW] SAVEPOINT LEVEL> <LANGUAGE SQL> <<NO> EXTERNAL ACTION> [SQL_statement]
procedure_name identifies the name that is to be assigned to the procedure to be created.
param_type indicates whether the parameter identified by param_name is an input parameter (IN), an output parameter (OUT), or both an input and an output parameter (INOUT).
param_name identifies the name to be assigned to a procedure parameter.
data_type identifies the type of data the procedure expects to receive or send for the parameter identified by param_name.
specific_name identifies the specific name to be assigned to the procedure. This name can be used later to comment on the procedure or drop the procedure; however, it cannot be used to invoke the procedure.
num_result_sets specifies whether the procedure being created will return one or more result data sets and if so, how many. (The default value is 0.)
SQL_statement specifies an SQL statement or a compound SQL statement (i.e., two or more SQL statements enclosed with the keywords BEGIN ATOMIC and END and terminated with a semicolon) that is to be executed when the procedure is invoked.
Thus, a simple SQL stored procedure could be created by executing a CREATE PROCEDURE statement that looks something 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
The resulting SQL stored procedure, called GET_SALES, accepts an integer input value (in an input parameter called QUOTA) and returns a character value (in an output parameter called RETCODE) that reports the procedure's success or failure. The procedure body consists of a compound SQL statement that returns a result data set (i.e., an open cursor) containing the name and total sales figures for each salesperson whose total sales exceed the quota specified. This is done by:
Indicating the SQL procedure is to return a result data set by specifying the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE statement and assigning it the value 1.
Declaring a cursor within the procedure body (using the WITH RETURN FOR clause) for the result data set that is to be returned. (A cursor is a named control structure that points to a specific row within a result data set and is used to retrieve values for each row in the set.)
Opening the cursor (which produces the result data set that is to be returned).
Leaving the cursor open when the SQL procedure ends.
It is important to note that when an SQL stored procedure is used to implement a business rule, the logic used to apply that business rule can be incorporated into any application simply by invoking the stored procedure. Thus, the same business rule logic is guaranteed to be enforced across all applications. When business rules change, only the logic in the SQL stored procedure needs to be changed; applications that call the procedure do not have to be modified.
To be able to use a routine, a user must be granted with its associated EXECUTE privilege. As shown below, EXECUTE is the only routine privilege, but it applies to all types of routines: functions, methods, and stored procedures:
>>-GRANT EXECUTE ON--+-| function-designator |----------+-------> +-FUNCTION--+---------+--*---------+ | '-schema.-' | +-| method-designator |------------+ +-METHOD * FOR--+-type-name------+-+ | '-+---------+--*-' | | '-schema.-' | +-| procedure-designator |---------+ '-PROCEDURE--+---------+--*--------' '-schema.-' .-,---------------------------------. V | >--TO----+-+-------+--authorization-name-+-+--------------------> | +-USER--+ | | +-GROUP-+ | | '-ROLE--' | '-PUBLIC------------------------' >--+-------------------+--------------------------------------->< '-WITH GRANT OPTION-'
The following examples show how to grant and revoke routine privileges:
GRANT EXECUTE ON PROCEDURE salary_increase TO USER db2admin WITH GRANT OPTION; REVOKE EXECUTE ON PROCEDURE salary_increase FROM USER db2admin;
Returning result sets from SQL procedures
In SQL procedures, cursors can be used to do more than iterate through rows of a result set. They can also be used to return result sets to the calling program. Result sets can be retrieved by SQL procedures (in the case of a nested procedure calls) or client applications programmed in C using the CLI application programming interface, Java, CLI, or .NET CLR languages.
To return a result set from an SQL procedure, you must:
Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement
DECLARE the cursor using the WITH RETURN clause
Open the cursor in the SQL procedure
Keep the cursor open for the client application - do not close it
Here is an example of an SQL procedure that only returns a single result set:
CREATE PROCEDURE read_emp() SPECIFIC read_emp LANGUAGE SQL DYNAMIC RESULT SETS 1 Re: BEGIN DECLARE c_emp CURSOR WITH RETURN FOR SELECT salary, bonus, comm. FROM employee WHERE job != 'PRES'; OPEN c_emp; END Re
If the cursor is closed using the CLOSE statement prior to the return of the SQL procedure, the cursor result set will not be returned to the caller or client application.
Multiple result sets can be returned from an SQL procedure by using multiple cursors. To return multiple cursors the following must be done:
Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement. Specify the maximum possible number of result sets likely to be retuned. The number of results sets actually returned must not exceed this number.
Declare cursors for each of the result sets to be returned that specify the WITH RETURN clause.
Open the cursors to be returned.
Keep the cursors open for the client application - do not close them.
One cursor is required per result set that is to be returned.