DB2 Version 10.1 for Linux, UNIX, and Windows

CREATE PROCEDURE statement (PL/SQL)

The CREATE PROCEDURE statement defines a procedure that is stored in the database.

Invocation

This statement can be executed from the DB2® command line processor (CLP), any supported interactive SQL interface, an application, or a routine.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

If the schema name of the procedure does not exist, IMPLICIT_SCHEMA authority on the database
If the schema name of the procedure refers to an existing schema, CREATEIN privilege on the schema
DBADM authority

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the procedure body.

The authorization ID of the statement must be the owner of the matched procedure if OR REPLACE is specified (SQLSTATE 42501).

Syntax


>>-CREATE--+------------+--PROCEDURE---------------------------->
           '-OR REPLACE-'              

>--+-----------------------------------------------------------------------------+-->
   '-(--+-------------------------------------------------------------------+--)-'   
        | .-,-------------------------------------------------------------. |        
        | V                 .-IN-----.                                    | |        
        '---parameter-name--+--------+--data-type--+--------------------+-+-'        
                            +-OUT----+             '-| default-clause |-'            
                            '-IN OUT-'                                               

                               .-----------------.   
                               V                 |   
>--+----------------+--+-IS-+----+-------------+-+-------------->
   '-READS SQL DATA-'  '-AS-'    '-declaration-'     

          .-----------.   
          V           |   
>--BEGIN----statement-+----------------------------------------->

>--+----------------------------------------------------------------------------+-->
   |            .-------------------------------------------------------------. |   
   |            |                  .-------------------.        .-----------. | |   
   |            V                  V                   |        V           | | |   
   '-EXCEPTION----WHEN--exception----+---------------+-+--THEN----statement-+-+-'   
                                     '-OR--exception-'                              

>--END--+----------------+-------------------------------------><
        '-procedure-name-'

Description

PROCEDURE procedure-name: Specifies an identifier for the procedure. The unqualified form of procedure-name is an SQL identifier with a maximum length of 128. In dynamic SQL statements, the value of the CURRENT SCHEMA special register is used to qualify an unqualified object name. In static SQL statements, the QUALIFIER precompile or bind option implicitly specifies the qualifier for unqualified object names. The qualified form of procedure-name is a schema name followed by a period character and an SQL identifier. If a two-part name is specified, the schema name cannot begin with 'SYS'; otherwise, an error is returned (SQLSTATE 42939).
The name (including an implicit or explicit qualifier), together with the number of parameters, must not identify a procedure that is described in the catalog (SQLSTATE 42723). The unqualified name, together with the number of parameters, is unique within its schema, but does not need to be unique across schemas.
parameter-name: Specifies the name of a parameter. The parameter name must be unique for this procedure (SQLSTATE 42734).
data-type: Specifies one of the supported PL/SQL data types.
READS SQL DATA: Indicates that SQL statements that do not modify SQL data can be included in the procedure. This clause is a DB2 extension.
IS or AS: Introduces the procedure body definition.
declaration: Specifies one or more variable, cursor, or REF CURSOR type declarations.
BEGIN: Introduces the executable block. The BEGIN-END block can contain an EXCEPTION section.
statement: Specifies a PL/SQL or SQL statement. The statement must be terminated by a semicolon.
EXCEPTION: An optional keyword that introduces the exception section.
WHEN exception-condition: Specifies a conditional expression that tests for one or more types of exceptions.
statement: Specifies a PL/SQL or SQL statement. The statement must be terminated by a semicolon.
END: A mandatory keyword that ends the block. You can optionally specify the name of the procedure.

Notes

The CREATE PROCEDURE statement can be submitted in obfuscated form. In an obfuscated statement, only the procedure name is readable. The rest of the statement is encoded in such a way that it is not readable, but can be decoded by the database server. Obfuscated statements can be produced by calling the DBMS_DDL.WRAP function.

Examples

The following example shows a simple procedure that takes no parameters:

CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END simple_procedure;

The following example shows a procedure that takes an IN and an OUT parameter, and that has GOTO statements whose labels are of the standard PL/SQL form (<<label>>):

CREATE OR REPLACE PROCEDURE test_goto
( p1 IN INTEGER, out1 OUT VARCHAR2(30) )
IS
BEGIN
	<<LABEL2ABOVE>>
	IF p1 = 1 THEN
		out1 := out1 || 'one';
		GOTO LABEL1BELOW;
	END IF;
	if out1 IS NULL THEN
		out1 := out1 || 'two';
		GOTO LABEL2ABOVE;
	END IF;

	out1 := out1 || 'three';

	<<LABEL1BELOW>>
	out1 := out1 || 'four';

END test_goto;