CREATE PROCEDURE (SQL)

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

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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

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

  • For the SYSPROCS catalog view and SYSPARMS catalog table:
    • The INSERT privilege on the table, and
    • The system authority *EXECUTE on library QSYS2
  • Database administrator authority

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

  • The following system authorities:
    • *USE on the Create Program (CRTPGM) command, and
  • Database administrator authority

If SQL names are specified and a user profile exists that has the same name as the library into which the procedure is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

  • The system authority *ADD to the user profile with that name
  • Database administrator authority

If a distinct type or array type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

  • For each distinct type or array type identified in the statement:
    • The USAGE privilege on the type, and
    • The system authority *EXECUTE on the library containing the distinct type or array type
  • Database administrator authority

To replace an existing procedure, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authority of *OBJMGT on the program or service program object associated with the procedure
    • All authorities needed to DROP the procedure
    • The system authority *READ to the SYSPROCS catalog view and SYSPARMS catalog table
  • Database administrator authority

For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure and Corresponding System Authorities When Checking Privileges to a Distinct Type.

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACEPROCEDUREprocedure-name(,parameter-declaration)procedure-definitionWRAPPEDobfuscated-statement-text
procedure-definition
Read syntax diagramSkip visual syntax diagram option-list SET OPTION-statementSQL-routine-body
parameter-declaration
Read syntax diagramSkip visual syntax diagram INOUTINOUT parameter-namedata-typedefault-clause
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-namearray-type-name
default-clause
Read syntax diagramSkip visual syntax diagramDEFAULT NULLconstantspecial-registerglobal-variable(expression)
option-list
Read syntax diagramSkip visual syntax diagram LANGUAGE SQL 1NOT DETERMINISTICDETERMINISTICMODIFIES SQL DATAREADS SQL DATACONTAINS SQLCALLED ON NULL INPUT INHERIT SPECIAL REGISTERS DYNAMIC RESULT SETS0DYNAMIC RESULT SETSintegerSPECIFICspecific-namePROGRAM NAMEexternal-program-nameDISALLOW DEBUG MODEALLOW DEBUG MODEDISABLE DEBUG MODEFENCEDNOT FENCEDPROGRAM TYPE MAINPROGRAM TYPE SUBOLD SAVEPOINT LEVELNEW SAVEPOINT LEVELCOMMIT ON RETURN NOCOMMIT ON RETURN YESAUTONOMOUSCONCURRENT ACCESS RESOLUTIONDEFAULTUSE CURRENTLY COMMITTEDUWAIT FOR OUTCOMEWSYSTEM_TIME SENSITIVE YESSYSTEM_TIME SENSITIVE NO
Notes:
  • 1 The optional clauses can be specified in a different order.
SQL-routine-body
Read syntax diagramSkip visual syntax diagramSQL-control-statementALLOCATE CURSOR-statementALLOCATE DESCRIPTOR-statementALTER FUNCTION-statementALTER MASK-statementALTER PERMISSION-statementALTER PROCEDURE-statementALTER SEQUENCE-statementALTER TABLE-statementALTER TRIGGER-statementASSOCIATE LOCATORS-statementCOMMENT-statementCOMMIT-statementCONNECT-statementCREATE ALIAS-statementCREATE FUNCTION (external scalar)-statementCREATE FUNCTION (external table)-statementCREATE FUNCTION (sourced)-statementCREATE INDEX-statementCREATE MASK-statementCREATE PERMISSION-statementCREATE PROCEDURE (external)-statementCREATE SCHEMA-statementCREATE SEQUENCE-statementCREATE TABLE-statementCREATE TYPE-statementCREATE VIEW-statementDEALLOCATE DESCRIPTOR-statementDECLARE GLOBAL TEMPORARY TABLE-statementDELETE-statementDESCRIBE-statementDESCRIBE CURSOR-statementDESCRIBE INPUT-statementDESCRIBE PROCEDURE-statementDESCRIBE TABLE-statementDISCONNECT-statement
SQL-routine-body (continued)
Read syntax diagramSkip visual syntax diagramDROP-statementEXECUTE IMMEDIATE-statementGET DESCRIPTOR-statementGRANT-statementINSERT-statementLABEL-statementLOCK TABLE-statementMERGE-statementREFRESH TABLE-statementRELEASE-statementRELEASE SAVEPOINT-statementRENAME-statementREVOKE-statementROLLBACK-statementSAVEPOINT-statementSELECT INTO-statementSET CONNECTION-statementSET CURRENT DEBUG MODE-statementSET CURRENT DECFLOAT ROUNDING MODE-statementSET CURRENT DEGREE-statementSET CURRENT IMPLICIT XMLPARSE OPTION-statementSET CURRENT TEMPORAL SYSTEM_TIME-statementSET DESCRIPTOR-statementSET ENCRYPTION PASSWORD-statementSET PATH-statementSET RESULT SETS-statementSET SCHEMA-statementSET TRANSACTION-statementTRANSFER OWNERSHIP-statementTRUNCATE-statementUPDATE-statementVALUES INTO-statement
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)normalize-clauseBINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)DATALINK(200)(integer)ccsid-clauseROWIDXML
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDintegernormalize-clause
normalize-clause
Read syntax diagramSkip visual syntax diagramNOT NORMALIZEDNORMALIZED

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 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-name
Names the procedure. The combination of name, schema name, and the number of parameters must not identify a procedure that exists at the current server unless OR REPLACE is specified.

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. Start of changeAll parameters are nullable.End of change

The maximum number of parameters allowed in an SQL procedure is Start of change2000End of change.

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.

A parameter that is an array of XML or LOB type is read only.

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. If the parameter is not set within the procedure, its input value is returned. If an INOUT parameter is defined with a default and the default is used when calling the procedure, no value for the parameter is returned.
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 TYPE). For more information on creating a distinct type, see CREATE TYPE (distinct).

If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.

array-type-name
Specifies an array type.

If the name of the array 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.

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 expression is any expression defined in Expressions, that does not include an aggregate function or column name. If a default value is not specified, the parameter has no default and cannot be omitted on invocation of the procedure. The maximum length of the expression string is 64K.

The default expression must not modify SQL data. The expression must be assignment compatible to the parameter data type. All objects referenced in a default expression must exist when the procedure is created.

Any comma in the default expression that is intended as a separator of numeric constants in a list must be followed by a space.

A default cannot be specified:
  • for an OUT parameter.
  • for a parameter of type array.
LANGUAGE SQL
Specifies that this procedure is written exclusively in SQL.
SPECIFIC specific-name
Specifies a unique name for the procedure. For more information on specific names, see Specifying a specific name for a procedure in CREATE PROCEDURE.
Start of changePROGRAM NAME external-program-nameEnd of change
Start of changeSpecifies the unqualified name of the program or service program to be created for the procedure. external-program-name must be a valid system name. End of change
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. The default is NOT DETERMINISTIC.
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.
MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL
Specifies the classification of SQL statements Start of changeand nested routinesEnd of change that this procedure can execute. The database manager verifies that the SQL statements issued by the procedure, Start of changeand all routines locally invoked by the procedure,End of change are consistent with this specification. Start of changeThe verification is not performed when nested remote routines are invoked.End of change For the classification of each statement, see Characteristics of SQL statements. The default is MODIFIES SQL DATA. This option applies to any parameter default expressions.
MODIFIES SQL DATA
Specifies that the procedure can execute any SQL statement except statements that are not supported in procedures.
READS SQL DATA
Specifies that the procedure can execute statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL.
CONTAINS SQL
Specifies that the procedure can only execute statements with a data access classification of CONTAINS SQL or NO SQL.
CALLED ON NULL INPUT
Specifies that the procedure is to be called if any or all argument values are null. This specification means that the procedure must be coded to test for null argument values.
INHERIT SPECIAL REGISTERS
Specifies that existing values of special registers are inherited upon entry to the procedure.
DYNAMIC RESULT SETS integer
Specifies the maximum number of result sets that can be returned from the procedure. The minimum value for integer is zero and the maximum value is 32767. The default is DYNAMIC RESULT SETS 0.

Result sets are returned in the order in which the corresponding cursors are opened, unless a SET RESULT SETS statement is executed in the procedure. If the number of cursors still open for result sets when the procedure ends exceeds the maximum number specified on the DYNAMIC RESULT SETS clause, a warning is returned on the CALL statement and the number of result sets specified on the DYNAMIC RESULT SETS clause is 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.

For more information about result sets, see SET RESULT SETS.

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® for i.
PROGRAM TYPE MAIN or PROGRAM TYPE SUB
Specifies whether the procedure is created as a program (*PGM) or a procedure in a service program (*SRVPGM).
PROGRAM TYPE MAIN
Specifies that the procedure is created as a program (*PGM).
PROGRAM TYPE SUB
Specifies that the procedure is created as a procedure in a service program (*SRVPGM).

PROGRAM TYPE SUB procedures usually perform slightly better than PROGRAM TYPE MAIN procedures.

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.

AUTONOMOUS
Specifies that the procedure is executed in a unit of work that is independent from the calling application. When this option is specified the database always commits or rolls back the autonomous procedure's transactional work based on the SQLSTATE that is returned from the procedure. A SQLSTATE indication of unqualified success or warning will cause the transaction to be committed. All other SQLSTATEs will cause the autonomous procedure's unit of work to be rolled back.
The invocation of any trigger, function, or procedure from within the autonomous procedure will be part of the autonomous procedure's unit of work unless the trigger, function, or procedure was explicitly created to run under a different activation group.
An autonomous procedure cannot be called directly or indirectly from another autonomous procedure.
If AUTONOMOUS is specified, DYNAMIC RESULT SETS 0 must be specified.
CONCURRENT ACCESS RESOLUTION
Specifies whether the database manager should wait for data that is in the process of being updated. DEFAULT is the default.
DEFAULT
Specifies that the concurrent access resolution is not explicitly set for this procedure. The value that is in effect when the procedure is called will be used.
WAIT FOR OUTCOME
Specifies that the database manager is to wait for the commit or rollback of data in the process of being updated.
USE CURRENTLY COMMITTED
Specifies that the database manager is to use the currently committed version of the data when encountering data that is in the process of being updated.
When the lock contention is between a read transaction and a delete or update transaction, the clause is applicable to scans with isolation level CS (but not for CS KEEP LOCKS).
Start of changeSYSTEM_TIME SENSITIVEEnd of change
Start of changeDetermines whether references to system-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. YES is the default.
YES
References to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
NO
References to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
End of change
WRAPPED obfuscated-statement-text
Specifies the encoded definition of the procedure. A CREATE PROCEDURE statement can be encoded using the WRAP scalar function.
SET OPTION-statement
Specifies the options that will be used to create the procedure. These options also apply to any default value expressions. For example, to create a debuggable procedure, the following statement could be included:
SET OPTION DBGVIEW = *SOURCE 
The default values for the options depend on the options in effect at create time. For more information, see SET OPTION.

The options CLOSQLCSR, CNULRQD, CNULIGN, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE PROCEDURE statement. The following options are used when processing default value expressions: ALWCPYDTA, CONACC, DATFMT, DATSEP, DECFLTRND, DECMPT, DECRESULT, DFTRDBCOL, LANGID, SQLCURRULE, SQLPATH, SRTSEQ, TGTRLS, TIMFMT, and TIMSEP.

SQL-routine-body
Specifies a single SQL-procedure-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), ALTER FUNCTION (SQL scalar), and ALTER FUNCTION (SQL Table) with a REPLACE keyword are not allowed in an SQL-routine-body.

Notes

General considerations for defining procedures: See CREATE PROCEDURE for general information on defining procedures.

Procedure ownership: If SQL names were specified:

  • If a user profile with the same name as the schema into which the procedure is created exists, the owner of the procedure is that user profile.
  • Otherwise, the owner of the procedure is the user profile or group user profile of the thread executing the statement.

If system names were specified, the owner of the procedure is the user profile or group user profile of the thread executing the statement.

Procedure authority: If SQL names are used, procedures are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, procedures are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the procedure is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the procedure.

REPLACE rules: When a procedure is recreated by REPLACE:
  • Any existing comment or label is discarded.
  • Authorized users are maintained. The object owner could change.
  • Current journal auditing is preserved.

Error handling in procedures: Consideration should be given to possible exceptions that can occur for each SQL statement in the body of a procedure. Any exception SQLSTATE that is not handled within the procedure using a handler within a compound statement results in the exception SQLSTATE being returned to the caller of the procedure. Values of arguments passed to a procedure that correspond to OUT parameters are undefined and those that correspond to INOUT parameters are unchanged when an error is returned by the procedure.

Creating the procedure: When an SQL procedure is created, SQL creates a temporary source file that will contain C source code with embedded SQL statements. A program or service program object is then created using the CRTPGM or CRTSRVPGM command. The SQL options used to create the program are the options that are in effect at the time the CREATE PROCEDURE statement is executed. If AUTONOMOUS is specified, the program or service program is created with ACTGRP(QSQAUTOAG). Otherwise, the program is created with ACTGRP(*CALLER).

When an SQL procedure is created, the procedure's attributes are stored in the created program or service program object. If the *PGM or *SRVPGM object is saved and then restored to this or another system, the attributes are used to update the catalogs.

Start of changeIf the PROGRAM NAME clause is provided, its name is used for the creation of the program object.End of change Otherwise, the specific procedure name is used as the name of the member in the source file and the name of the program object, if it is a valid system name. If the procedure name is not a valid system name, a unique name is generated. If a source file member with the same name already exists, the member is overlaid. If a module or a program with the same name already exists, the objects are not overlaid, and a unique name is generated. The unique names are generated according to the rules for generating system table names.

Invoking the procedure: If a DECLARE PROCEDURE statement defines a procedure with the same name as a created procedure, and a static CALL statement where the procedure name is not identified by a variable is executed from the same source program, the attributes from the DECLARE PROCEDURE statement will be used rather than the attributes from the CREATE PROCEDURE statement.

The CREATE PROCEDURE statement applies to static and dynamic CALL statements as well as to a CALL statement where the procedure name is identified by a variable.

SQL procedures must be called using the SQL CALL statement. When called, the SQL procedure runs in the activation group of the calling program.

SQL procedures are built with the *TERASPACE storage model. When an autonomous SQL procedure is invoked in a job, every procedure that runs in the QSQAUTOAG activation group must use the *TERASPACE storage model.

Obfuscated statements: A CREATE PROCEDURE statement can be executed in obfuscated form. In an obfuscated statement, only the procedure name and parameters are readable followed by the WRAPPED keyword. The rest of the statement is encoded in such a way that it is not readable but can be decoded by a database server that supports obfuscated statements. Obfuscated statements can be produced by invoking the WRAP scalar function. Any debug options that are specified when the procedure is created from an obfuscated statement are ignored. A procedure that is created from an obfuscated statement cannot be restored to a release where obfuscation is not supported.

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 the argument is specified as DEFAULT.

Dependent objects: An SQL routine is dependent on objects that are referenced in the SQL-routine-body. The names of the dependent objects are stored in catalog view SYSROUTINEDEP. If the object reference in the SQL-routine-body is a fully qualified name or, in SQL naming, if an unqualified name is qualified by the current schema, then the schema name of the object in SYSROUTINEDEP will be set to the specified name or the value of the current schema. Otherwise, the schema name is not set to a specific schema name. Start of changeUnqualified function names, variable names, and type names will have a schema name of CURRENT PATH.End of change If the name is not set to an actual schema name, then DROP and ALTER statements will not be able to determine whether the routine is dependent on the object being altered or dropped.

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

  • The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC and DETERMINISTIC.
  • The keywords NULL CALL can be used as synonyms for CALLED ON NULL INPUT.
  • DYNAMIC RESULT SET, RESULT SETS, and RESULT SET may be used as synonyms for DYNAMIC RESULT SETS.

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 DECIMAL(7,2))
        LANGUAGE SQL 
        DYNAMIC RESULT SETS 1
   BEGIN 
        DECLARE v_numRecords INTEGER DEFAULT 1;
        DECLARE v_counter INTEGER DEFAULT 0;
        DECLARE c1 CURSOR FOR 
           SELECT salary 
              FROM staff 
              ORDER BY salary;
        DECLARE c2 CURSOR WITH RETURN FOR 
           SELECT name, job, salary 
              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