CREATE PROCEDURE (SQL) statement
The CREATE PROCEDURE (SQL) statement defines an SQL procedure at the current server.
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).
- 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.
- 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.
- 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.
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:
the second statement will fail because the number of parameters in the procedure is the same, even if the data types are not.
CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ... CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...
- 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.
- 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.
- Identifies the parameter as an output parameter for the procedure.
- Identifies the parameter as both an input and output parameter for the procedure.
- Specifies the name of the parameter. The parameter name must be unique for the procedure (SQLSTATE 42734).
- Specifies the data type of the parameter. A
structured type or reference type cannot be specified (SQLSTATE 429BB).
- Specifies 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".
- For a Boolean.
- For a reference to an underlying cursor.
- Identifies 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
- ANCHOR DATA TYPE TO
- Indicates an anchored data type is used to specify the data type.
- Identifies a global variable. The data type of the global variable is used as the data type for parameter-name.
- Identifies 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.
- 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.
- 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.
- 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.
- 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.
- 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
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
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
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).
- 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
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.
- A commit is not issued when the procedure returns.
- 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.
- 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
- 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.
- 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).
- 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).
- Specifies the SQL statement that is the body
of the SQL procedure.
See SQL-procedure-statement in
Compound SQL (Compiled)statement.
- 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
- 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.
- 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.
alternatives: The following syntax is
supported for cross-product compatibility only. These alternatives are non-standard and should
not be used.
The following syntax is accepted as the default behavior:
- RESULT SETS can be specified in place of DYNAMIC RESULT SETS.
- NULL CALL can be specified in place of CALLED ON NULL INPUT.
- ASUTIME NO LIMIT
- NO COLLID
- STAY RESIDENT NO
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