- procedure-name
- Names the procedure. The combination of name, schema name, the number
of parameters must not identify a procedure that exists at the current server.
For SQL naming, the procedure will be created in the schema specified by the
implicit or explicit qualifier.
For system naming, the procedure will
be created in the schema specified by the qualifier. If no qualifier is specified:
- If the value of the CURRENT SCHEMA special register is *LIBL, the procedure
will be created in the current library (*CURLIB).
- Otherwise, the procedure will be created in the current schema.
The schema-name cannot be QSYS2, QSYS, QTEMP,
or SYSIBM.
- (parameter-declaration,...)
- Specifies the number of parameters of the procedure and the data type
of each parameter. A parameter for a procedure can be used only for input,
only for output, or for both input and output. Although not required, you
can give each parameter a name.
The maximum number of parameters
allowed in an SQL procedure is 1024.
- 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.
- OUT
- Identifies the parameter as an output parameter that is returned by
the procedure. If the parameter is not set within the procedure, the null
value is returned.
- INOUT
- Identifies the parameter as both an input and output parameter for the
procedure.
- parameter-name
- Names the parameter. The name cannot be the same as any other parameter-name for the procedure.
- data-type
- Specifies the data type of the parameter. The data type can be a built-in
data type or a distinct data type.
- built-in-type
- Specifies a built-in data type. For a more complete description of each
built-in data type, see CREATE TABLE.
- distinct-type-name
- Specifies a distinct type. The length, precision, or scale attributes
for the parameter are those of the source type of the distinct type (those
specified on CREATE DISTINCT TYPE). For more information on creating a distinct
type, see CREATE DISTINCT TYPE.
If the name of the distinct type is unqualified,
the database manager resolves the schema name by searching the schemas in
the SQL path.
If a CCSID is specified, the parameter will be converted to that
CCSID prior to passing it to the procedure. If a CCSID is not specified, the
CCSID is determined by the default CCSID at the current server at the time
the procedure is called.
- LANGUAGE SQL
- Specifies that this is an SQL procedure.
- DYNAMIC RESULT SETS integer
- Specifies the maximum number of result sets that can be returned from
the procedure. integer must be greater than or equal to zero and
less than 32768. If zero is specified, no result sets are returned. If the
SET RESULT SETS statement is issued, the number of results returned is the
minimum of the number of result sets specified on this keyword and the SET
RESULT SETS statement. If the SET RESULT SETS statement specifies a number
larger than the maximum number of result sets, a warning is returned. Note
that any result sets from cursors that have a RETURN TO CLIENT attribute are
included in the number of result sets of the outermost procedure.
The result
sets are scrollable if the cursor is used to return a result set and the cursor
is scrollable. If a cursor is used to return a result set, the result set
starts with the current position. Thus, if 5 FETCH NEXT operations have been
performed prior to returning from the procedure, the result set will start
with the 6th row of the result set.
Result sets are only
returned if the procedure is directly called or if the procedure is a RETURN
TO CLIENT procedure and is indirectly called from ODBC, JDBC, OLE DB, .NET,
the SQL Call Level Interface, or the iSeries Access Family Optimized SQL API. For more information
about result sets, see SET RESULT SETS.
- SPECIFIC specific-name
- Provides a unique name for the procedure. The name is implicitly or
explicitly qualified with a schema name. The name, including the schema name,
must not identify the specific name of another procedure or function that
exists at the current server. If unqualified, the implicit qualifier is the
same as the qualifier of the procedure name. If qualified, the qualifier must
be the same as the qualifier of the procedure name.
If specific-name is not specified, it is the same as the procedure name. If a function
or procedure with that specific name already exists, a unique name is generated
similar to the rules used to generate unique table names.
- DETERMINISTIC or NOT DETERMINISTIC
- Specifies whether the procedure returns the same results each time the
procedure is called with the same IN and INOUT arguments.
- NOT DETERMINISTIC
- The procedure may not return the same result each time the procedure
is called with the same IN and INOUT arguments, even when the referenced data
in the database has not changed.
- DETERMINISTIC
- The procedure always returns the same results each time the procedure
is called with the same IN and INOUT arguments, provided the referenced data
in the database has not changed.
- CONTAINS SQL, READS SQL DATA, or MODIFIES
SQL DATA
- Specifies which SQL statements may be executed in the procedure or any
routine called from this procedure. See Appendix B. Characteristics of SQL statements for a detailed
list of the SQL statements that can be executed under each data access indication.
- CONTAINS SQL
- Specifies that SQL statements that neither read nor modify SQL data
can be executed by the procedure.
- READS SQL DATA
- Specifies that SQL statements that do not modify SQL data can be included
in the procedure.
- MODIFIES SQL DATA
- Specifies that the procedure can execute any SQL statement except statements
that are not supported in procedures.
- CALLED ON NULL INPUT
- Specifies that the procedure is to be invoked, if any, or
all, argument values are null, making the procedure responsible for testing
for null argument values. The procedure can return a null or nonnull value.
- INHERIT SPECIAL REGISTERS
- Specifies that existing values of special registers are inherited upon
entry to the procedure.
- DISALLOW DEBUG MODE, ALLOW DEBUG MODE, or DISABLE DEBUG MODE
- Indicates whether the procedure is created so it can be debugged by
the Unified Debugger. If DEBUG MODE is specified, a DBGVIEW option in the SET OPTION
statement must not be specified.
- DISALLOW DEBUG MODE
- The procedure cannot be debugged by the Unified Debugger. When
the DEBUG MODE attribute of the procedure is DISALLOW, the procedure can be
subsequently altered to change the debug mode attribute.
- ALLOW DEBUG MODE
- The procedure can be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is ALLOW, the procedure can be subsequently altered
to change the debug mode attribute.
- DISABLE DEBUG MODE
- The procedure cannot be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is DISABLE, the procedure cannot be subsequently
altered to change the debug mode attribute.
If DEBUG MODE is not specified, but a DBGVIEW option
in the SET OPTION statement is specified, the procedure cannot be debugged
by the Unified Debugger, but may be debugged by the system debug facilities.
If neither DEBUG MODE nor a DBGVIEW option is specified, the debug mode used
is from the CURRENT DEBUG MODE special register.
- FENCED or NOT FENCED
- This parameter is allowed for compatibility with other products and
is not used by DB2 UDB for iSeries.
- OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL
- Specifies whether a new savepoint level is to be created on entry to
the procedure.
- OLD SAVEPOINT LEVEL
- A new savepoint level is not created. Any SAVEPOINT statements issued
within the procedure with OLD SAVEPOINT LEVEL implicitly or explicitly specified
on the SAVEPOINT statement are created at the same savepoint level as the
caller of the procedure. This is the default.
- NEW SAVEPOINT LEVEL
- A new savepoint level is created on entry to the procedure. Any savepoints
set within the procedure are created at a savepoint level that is nested deeper
than the level at which this procedure was invoked. Therefore, the name of
any new savepoint set within the procedure will not conflict with any existing
savepoints set in higher savepoint levels (such as the savepoint level of
the calling program) with the same name.
- COMMIT ON RETURN
- Specifies whether the database manager commits the transaction immediately on return
from the procedure.
- NO
- The database manager does not issue a commit when the procedure returns. NO is
the default.
- YES
- The database manager issues a commit if the procedure returns successfully. If
the procedure returns with an error, a commit is not issued.
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.
- SET OPTION-statement
- Specifies the options that will be used to create the procedure. For
example, to create a debuggable procedure, the following statement could be
included:
SET OPTION DBGVIEW = *SOURCE
For
more information, see SET OPTION.
The options CLOSQLCSR,
CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE PROCEDURE
statement.
- SQL-routine-body
- Specifies a single SQL statement, including a compound statement. See SQL control statements for more information about defining SQL procedures.
CONNECT, SET CONNECTION, RELEASE, DISCONNECT, and SET TRANSACTION statements
are not allowed in a procedure that is running on a remote application server. COMMIT
and ROLLBACK statements are not allowed in an ATOMIC SQL procedure or in a
procedure that is running on a connection to a remote application server.
ALTER PROCEDURE (SQL) with a REPLACE keyword is not allowed
in an SQL-routine-body.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.