CREATE PROCEDURE (SQL) statement

The CREATE PROCEDURE (SQL) statement defines an SQL procedure at the current server.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

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

If the implicit or explicit 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.
If the schema name of the procedure refers to an existing schema, SCHEMAADM authority 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.

To replace an existing procedure, the authorization ID of the statement must be the owner of the existing procedure (SQLSTATE 42501).

Group privileges are not considered for any table or view specified in the CREATE PROCEDURE (SQL) statement.

Description

OR REPLACE

Specifies to replace the definition for the procedure if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the procedure are not affected. This option can be specified only by the owner of the object. This option is ignored if a definition for the procedure does not exist at the current server. To replace an existing procedure, the specific name and procedure name of the new definition must be the same as the specific name and procedure name of the old definition, or the signature of the new definition must match the signature of the old definition. Otherwise, a new procedure is created.

procedure-nameNames the procedure being defined. It is a qualified or unqualified name that designates a procedure. The unqualified form of procedure-name is an SQL identifier. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

The name, including the implicit or explicit qualifiers, together with the number of parameters, must not identify a procedure 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.

If a two-part name is specified, the schema-name cannot begin with 'SYS'; otherwise, an error is returned (SQLSTATE 42939).

(IN | OUT | INOUT parameter-name data-type default-clause,...)

Identifies the parameters of the procedure, and specifies the mode, name, data type, and optional default value of each parameter. One entry in the list must be specified for each parameter that the procedure will expect.

It is possible to register a procedure that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example:

   CREATE PROCEDURE SUBWOOFER() ...

No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. A duplicate signature raises an SQL error (SQLSTATE 42723).

For example, given the statements:

   CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
   CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

the second statement will fail because the number of parameters in the procedure is the same, even if the data types are not.

IN | OUT | INOUT

Specifies the mode of the parameter.

If an error is returned by the procedure, OUT parameters are undefined and INOUT parameters are unchanged.

IN: Identifies the parameter as an input parameter to the procedure. Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned. The default is IN.
OUT: Identifies the parameter as an output parameter for the procedure.
INOUT: Identifies the parameter as both an input and output parameter for the procedure.

parameter-nameSpecifies the name of the parameter. The parameter name must be unique for the procedure (SQLSTATE 42734).

data-typeSpecifies the data type of the parameter. A structured type or reference type cannot be specified (SQLSTATE 429BB).

built-in-typeSpecifies a built-in data type. For a more complete description of each built-in data type except BOOLEAN and CURSOR, which cannot be specified for a table, see "CREATE TABLE".

BOOLEAN: For a Boolean.
CURSOR: For a reference to an underlying cursor.

anchored-data-typeIdentifies another object used to define the data type. The data type of the anchor object has the same limitations that apply to specifying the data type directly, or in the case of a row, to creating a row type.

ANCHOR DATA TYPE TO

Indicates an anchored data type is used to specify the data type.

variable-nameIdentifies a global variable. The data type of the global variable is used as the data type for parameter-name.

table-name.column-nameIdentifies a column name of an existing table or view. The data type of the column is used as the data type for parameter-name.

ROW OF table-name or view-name

Specifies a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name. The data type of parameter-name is an unnamed row type.

ROW OF cursor-variable-name

Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following elements (SQLSTATE 428HS):

A global variable with a strongly typed cursor data type
A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.

If the cursor type of the cursor variable is not strongly-typed using a named row type, the data type of parameter-name is an unnamed row type.

array-type-name

Specifies the name of a user-defined array type. If array-type-name is specified without a schema name, the array type is resolved by searching the schemas in the SQL path.

cursor-type-name

Specifies the name of a cursor type. If cursor-type-name is specified without a schema name, the cursor type is resolved by searching the schemas in the SQL path.

distinct-type-name

Specifies the name of a distinct type. The length, precision, and scale of the parameter are, respectively, the length, precision, and scale of the source type of the distinct type. A distinct type parameter is passed as the source type of the distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.

row-type-name

Specifies the name of a user-defined row type. The fields of the parameter are the fields of the row type. If row-type-name is specified without a schema name, the row type is resolved by searching the schemas in the SQL path.

DEFAULT

Specifies a default value for the parameter. The default can be a constant, a special register, a global variable, an expression or the keyword NULL. The special registers that can be specified as the default are that same as those that can be specified for a column default (see default-clause in the CREATE TABLE statement). Other special registers can be specified as the default by using an expression.

The expression can be any expression of the type described in Expressions. If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64K bytes.

The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).

A default cannot be specified in the following situations:

For INOUT or OUT parameters (SQLSTATE 42601)
For a parameter of type ARRAY, ROW, or CURSOR (SQLSTATE 429BB)

SPECIFIC specific-name

Provides a unique name for the instance of the procedure that is being defined. This specific name can be used when altering, dropping, or commenting on the procedure. It can never be used to invoke the procedure. The unqualified form of specific-name is an SQL identifier. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another procedure instance that exists at the application server; otherwise an error (SQLSTATE 42710) is raised.

The specific-name can be the same as an existing procedure-name.

If no qualifier is specified, the qualifier that was used for procedure-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier for procedure-name, or an error (SQLSTATE 42882) is raised.

If specific-name is not specified, a unique name is generated by the database manager. The unique name is 'SQL' followed by a character timestamp: 'SQLyymmddhhmmssxxx'.

If you intend to archive the procedure by using the GET ROUTINE command, ensure the specific-name has a maximum length of 18 characters.

DYNAMIC RESULT SETS integer

Indicates the estimated upper bound of returned result sets for the procedure.

MODIFIES SQL DATA, CONTAINS SQL, READS SQL DATA

Specifies the classification of SQL statements that can be run by this procedure or any routine that is called by this procedure. The database manager verifies that the SQL statements issued by the procedure and all routines that are called by the procedure are consistent with this specification.

For the classification of each statement, see SQL statements that can be executed in routines and triggers.

The default is MODIFIES SQL DATA.

MODIFIES SQL DATA: Specifies that the procedure can run any SQL statement except statements that are not supported in procedures (SQLSTATE 38003 or 42985).
CONTAINS SQL: Specifies that the procedure can run only statements with a data access classification of CONTAINS SQL (SQLSTATE 38003 or 38004 or 42985).
READS SQL DATA: Specifies that the procedure can run statements with a data access classification of READS SQL DATA or CONTAINS SQL (SQLSTATE 38002 or 38003 or 42985).

If the BEGIN ATOMIC clause is used in a compound SQL procedure, the procedure can be created only if it is defined as MODIFIES SQL DATA.

DETERMINISTIC or NOT DETERMINISTIC

This clause specifies whether the procedure always returns the same results for given argument values (DETERMINISTIC) or whether the procedure depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC procedure must always return the same result from successive invocations with identical inputs.

This clause currently does not impact processing of the procedure.

CALLED ON NULL INPUT

CALLED ON NULL INPUT always applies to procedures. This means that the procedure is called regardless of whether any arguments are null. Any OUT or INOUT parameter can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the procedure.

COMMIT ON RETURN

Indicates whether a commit is to be issued on return from the procedure. The default is NO.

NO: A commit is not issued when the procedure returns.
YES: A commit is issued when the procedure returns if a positive SQLCODE is returned by the CALL statement

The commit operation includes the work that is performed by the calling application process and the procedure.

If the procedure returns result sets, the cursors that are associated with the result sets must have been defined as WITH HOLD to be usable after the commit.

AUTONOMOUS

Indicates the procedure should execute in its own autonomous transaction scope.

INHERIT SPECIAL REGISTERS

This optional clause specifies that updatable special registers in the procedure will inherit their initial values from the environment of the invoking statement. For a routine invoked in a nested object (for example a trigger or view), the initial values are inherited from the runtime environment (not inherited from the object definition).

No changes to the special registers are passed back to the caller of the procedure.

Non-updatable special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore set to their default values.

OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL

Specifies whether or not this procedure establishes a new savepoint level for savepoint names and effects. OLD SAVEPOINT LEVEL is the default behavior. For more information about savepoint levels, see Rules in SAVEPOINT.

LANGUAGE SQL

This clause is used to specify that the procedure body is written in the SQL language.

EXTERNAL ACTION or NO EXTERNAL ACTION

Specifies whether the procedure takes some action that changes the state of an object not managed by the database manager (EXTERNAL ACTION), or not (NO EXTERNAL ACTION). The default is EXTERNAL ACTION. If NO EXTERNAL ACTION is specified, the system can use certain optimizations that assume the procedure has no external impact.

PARAMETER CCSID

Specifies the encoding scheme to use for all string data passed into and out of the procedure. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.

ASCII: Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031).
UNICODE: Specifies that character data is in UTF-8, and that graphic data is in UCS-2. If the database is not a Unicode database, PARAMETER CCSID UNICODE cannot be specified (SQLSTATE 56031).

SQL-procedure-body

Specifies the SQL statement that is the body of the SQL procedure.

See SQL-procedure-statement in Compound SQL (Compiled) statement.

Rules

Autonomous routine restrictions: Autonomous routines cannot return result sets and do not support the following data types (SQLSTATE 428H2):
- User-defined cursor types
- User-defined structured types
- XML as IN, OUT, and INOUT parameters
Session variables of cursor types cannot be referenced within the autonomous scope.
Use of anchored data types: An anchored data type cannot refer to the following objects (SQLSTATE 428HS): a nickname, typed table, typed view, statistical view that is associated with an expression-based index, declared temporary table, row definition that is associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.
Use of cursor and row types: A procedure that uses a cursor type or row type for a parameter can be invoked only from within a compound SQL (compiled) statement (SQLSTATE 428H2), except for Java applications using JDBC, which can invoke a procedure with OUT parameters that have a cursor type. Invocation from Java external procedures is not supported.

Notes

Creating a procedure with a schema name that does not already exist will result in the implicit creation of that schema, provided that the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
A procedure that is called from within a compound SQL (inlined) statement will execute as if it were created specifying NEW SAVEPOINT LEVEL, even if OLD SAVEPOINT LEVEL was specified or defaulted to when the procedure was created.
Creating procedures that are initially invalid: If an object referenced in the procedure body does not exist or is marked invalid, or the definer temporarily doesn't have privileges to access the object, and if the database configuration parameter auto_reval is not set to DISABLED, then the procedure will still be created successfully. The procedure will be marked invalid and will be revalidated the next time it is invoked.
Setting of the default value: Parameters of a procedure that are defined with a default value are set to their default value when the procedure is invoked, but only if a value is not supplied for the corresponding argument, or is specified as DEFAULT, when the procedure is invoked.
Privileges: The definer of a procedure always receives the EXECUTE privilege WITH GRANT OPTION on the procedure, as well as the right to drop the procedure.
Rebinding dependent packages: Every SQL procedure has a dependent package. The package can be rebound at any time by running the REBIND_ROUTINE_PACKAGE procedure. Explicitly rebinding the dependent package does not revalidate an invalid procedure. An invalid procedure should be revalidated with automatic revalidation or by explicitly running the ADMIN_REVALIDATE_DB_OBJECTS procedure. Procedure revalidation automatically rebinds the dependent package.
Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2® and with other database products. These alternatives are non-standard and should not be used.
- RESULT SETS can be specified in place of DYNAMIC RESULT SETS.
- NULL CALL can be specified in place of CALLED ON NULL INPUT.
The following syntax is accepted as the default behavior:
- ASUTIME NO LIMIT
- NO COLLID
- STAY RESIDENT NO

Example

Create an SQL procedure that returns the median staff salary. Return a result set containing the name, position, and salary of all employees who earn more than the median salary.

   CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
     RESULT SETS 1
     LANGUAGE SQL
   BEGIN
     DECLARE v_numRecords INT DEFAULT 1;
     DECLARE v_counter INT DEFAULT 0;

     DECLARE c1 CURSOR FOR
       SELECT CAST(salary AS DOUBLE)
         FROM staff
         ORDER BY salary;
     DECLARE c2 CURSOR WITH RETURN FOR
       SELECT name, job, CAST(salary AS INTEGER)
         FROM staff
         WHERE salary > medianSalary
         ORDER BY salary;

     DECLARE EXIT HANDLER FOR NOT FOUND
       SET medianSalary = 6666;

     SET medianSalary = 0;
     SELECT COUNT(*) INTO v_numRecords
       FROM STAFF;
     OPEN c1;
     WHILE v_counter < (v_numRecords / 2 + 1)
     DO
       FETCH c1 INTO medianSalary;
       SET v_counter = v_counter + 1;
     END WHILE;
     CLOSE c1;
     OPEN c2;
   END