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]

					

where:

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:

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.

Routine Privileges

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:

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:

One cursor is required per result set that is to be returned.