Following is the syntax for the CREATE PROCEDURE statement:
Note: SQL-parameter-name is required for SQL Procedures and, though optional for all others, is recommended for all others as well. See the separate proc-body syntax diagram that follows.
┌─ , ──────────────────────┐ ►►─ CREATE PROCEDURE ─ proc-name ─ ( ─▼─┬──────────────────────┬─┴─ ) ────────► ├─┬─ IN ◄ ──┬──────────┤ │ ├─ OUT ───┤ │ │ └─ INOUT ─┘ │ ├─ SQL-parameter-name ─┤ └─ datatype ───────────┘ ►─┬───────────────────┬─ proc-body ──────────────────────────────────────────► └─ proc-attributes ─┘ ►─┬────────────────────────────────────┬─────────────────────────────────────►◄ └─ RUN OPTIONS ─ run-options-string ─┘
Expansion of Where proc-attributes is defined as
┌────────────────────────────────┐ ├──▼─┬─ language ─────────────────┬─┴──────────────────────────────────────────┤ ├─ parameter-style ──────────┤ ├─ SPECIFIC ─ name ──────────┤ ├─┬───────┬ ─ DETERMINISTIC ─┤ │ └─ NOT ─┘ │ └─ data-access ──────────────┘
Expansion of Where language is defined as
├── LANGUAGE ─┬─ COBOL ─────┬──────────────────────────────────────────────────┤ ├─ PLI ───────┤ ├─ C ─────────┤ ├─ ASSEMBLER ─┤ └─ SQL ───────┘
Expansion of Where parameter-style is defined as
├── PARAMETER STYLE ─┬─ GENERAL ────────────┬──────────────────────────────────┤ ├─ GENERAL WITH NULLS ─┤ ├─ DATACOM SQL ────────┤ └─ SQL ────────────────┘
Expansion of Where data-access is defined as
├──┬─ MODIFIES SQL DATA ─┬─────────────────────────────────────────────────────┤ ├─ READS SQL DATA ────┤ ├─ CONTAINS SQL ──────┤ └─ NO SQL ────────────┘
Note: All of the proc-attributes choices can be used together, but each can only be used once. If you are using SQL Procedures, data-access and parameter-style do not apply.
You can specify the parameter style only once, either here as part of the procedure attribute syntax or as part of the EXTERNAL syntax. Parameter style applies only to External Procedures. It does not apply to SQL Procedures.
Note: The proc-external clause is only used for External Procedures.
The optional parameter-style clause is only used for External Procedures.
The proc-SQL-stmt clause is only used for SQL Procedures.
►►─┬─ proc-external ─┬───────────────────┬─┬──────────────────────────────────►◄ │ └─ parameter-style ─┘ │ └─ proc-SQL-stmt ───────────────────────┘
Expansion of Where proc_external is defined as
├── EXTERNAL ─┬───────────────────┬────────────────────────────────────────────┤ └─ NAME ─ ext-name ─┘
Expansion of Where proc-SQL-stmt is defined as
├──┬─ executable-DML-stmt ─┬───────────────────────────────────────────────────┤ ├─ DDL-stmt ────────────┤ ├─ control-stmt ────────┤ └─ diagnostics-stmt ────┘
Expansion of Where diagnostics-stmt is defined as
├──┬─ signal-stmt ──────────┬──────────────────────────────────────────────────┤ ├─ get-diagnostics-stmt ─┤ ├─ datacom-dump-stmt ────┤ ├─ raise-error-stmt ─────┤ └─ resignal-stmt ────────┘
Expansion of Where control-stmt is defined as
├──┬─ call-proc ──────────────┬────────────────────────────────────────────────┤ ├─ execute-proc ───────────┤ └─ proc-only-control-stmt ─┘
Expansion of Where proc-only-control-stmt is defined as
├──┬─ compound-stmt ─────────────────────────┬─────────────────────────────────┤ └─ compound-stmt-only-control-statements ─┘
Note: Syntax for the compound-stmt-only-control-statement is given in a separate diagram on the next page.
Note: The executable-DML-stmt is any DML statement except for DECLARE CURSOR (DECLARE CURSOR is supported as part of the compound statement, see Compound Statement). For a list of DML statements, see SQL Statements.
See the separate diagnostics-stmt syntax diagram that follows and see the GET DIAGNOSTICS statement on GET DIAGNOSTICS Statement.
►►────────────────────────────────────────────────────────────────────────────►◄
Expansion of Where compound-stmt-only-control-statements are defined as
├──┬─ assignment-stmt ─┬───────────────────────────────────────────────────────┤ ├─ case-stmt ───────┤ ├─ if-then-stmt ────┤ ├─ iterate-stmt ────┤ ├─ leave-stmt ──────┤ ├─ loop-stmt ───────┤ ├─ repeat-stmt ─────┤ └─ while-do-stmt ───┘
Following is the description of the CREATE PROCEDURE syntax:
(Required) For the proc-name specify the SQL-name of the procedure name. The name can be qualified with an authorization ID. This name is an SQL-identifier.
We recommend that you use unique procedure names with regard to the names of any record, table, synonym, view, or constraint. If you code and reference a procedure parameter, for example, whose name is identical to a column name that is also referenced inside a particular SQL Procedure, uniqueness in naming would allow you to use the procedure name and table name to distinguish a procedure parameter reference from a reference to the database-table column.
ANSI supports overloading of the procedure name, with duplicate procedure names resolved to procedure definitions using the layout of the parameter list. For syntax compatibility purposes, a second, specific name may be given to a procedure to uniquely identify it, but it must match the nonspecific name, and the nonspecific name must be unique.
(Optional) Part of the SQL parameter definition. Specifies to SQL whether this parameter is used for input (IN), output (OUT), or both (INOUT). If this parameter is not specified, the default (IN) is used.
IN, OUT, INOUT
IN
(Optional) The SQL-parameter-name is required for SQL Procedures and, though optional for all others, is recommended for all others as well. The SQL-parameter-name is the name of a parameter passed to a SQL Procedure (a LANGUAGE SQL procedure). When used in a SQL Procedure containing an SQL variable with a conflicting (matching) name, or a table or view reference where the table or view contains a conflicting column name, the name should be qualified by using the procedure name.
Part of the SQL parameter definition. Specify a datatype.
(No default)
Part of the procedure attributes definition.
Specify COBOL, PLI, C, or ASSEMBLER as the programming language when writing a External Procedure.
Specify SQL when writing a SQL Procedure. When you create a LANGUAGE SQL procedure, CA Datacom/DB creates a plan to hold the SQL statements contained by the procedure. The plan name that is created consists of the first 18 bytes of the procedure name. We recommend creating a SQL Procedure using a method such as DBSQLPR. We support but do not recommend creating an SQL procedure from a preprocessed program.
See the note about language conformance in External Procedures.
Note: For all except LANGUAGE SQL, the program source code for the procedure must be preprocessed before the CREATE PROCEDURE statement is allowed to run.
COBOL, PLI, C, ASSEMBLER, SQL
(No default)
Part of the procedure attributes definition.
ANSI supports overloading of the procedure name, with duplicate procedure names resolved to procedure definitions using the layout of the parameter list. For syntax compatibility purposes, a second, specific name may be given to a procedure to uniquely identify it, but it must match the nonspecific name, and the nonspecific name must be unique.
Part of the procedure attributes definition. This is informational only, indicating whether the procedure always returns the same output, given a certain input.
Defines how parameters are passed into and out of the procedure program you write, and how errors are handled.
Note: Parameter style applies only to External Procedures. It does not apply to SQL Procedures.
See Parameter Styles and Error Handling for detailed information.
You can specify PARAMETER STYLE only once, either as:
Parameter style applies only to External Procedures. It does not apply to SQL Procedures.
PARAMETER STYLE GENERAL
This parameter style means that the user parameter list is passed to the procedure devoid of null indicators (nulls are not allowed). Since no formal method is provided for passing error information back to the caller, the success or failure of the CALL procedure statement is determined by the contents of SQL's internal SQLCODE variable following the last SQL request made by the procedure. This also applies to parameter style GENERAL WITH NULLS (see following).
PARAMETER STYLE GENERAL WITH NULLS
This parameter style differs from GENERAL only in that a null indicator is passed to the procedure for each user parameter.
PARAMETER STYLE DATACOM SQL
This parameter style passes nulls to the procedure as does GENERAL WITH NULLS and SQL, but it also passes some additional parameters. These parameters are modeled after those passed for the ANSI SQL3 parameter style SQL but with this difference: instead of a SQLSTATE, DATACOM SQL passes an SQLCODE in the corresponding parameter. Following are the additional parameters (the first four are modeled after SQL3):
PARAMETER STYLE SQL
When a procedure is created using PARAMETER STYLE SQL in the CREATE PROCEDURE statement, the SQLSTATE status indicator is returned in the SQLCA. For detailed information about the SQLSTATE status indicator, see the CA Datacom/DB Message Reference Guide.
Parameter style SQL passes nulls to the procedure as does GENERAL WITH NULLS and DATACOM SQL. It also passes these additional four parameters that are added to the end of the parameter/null indicator list:
Unlike style DATACOM SQL, the CA Datacom/DB external and internal return codes are not a part of this parameter list but are encoded in the generated SQLSTATE value. For example, the SQLSTATE that equates to SQL return code -117 is Seeii, and the SQLSTATE that equates to SQL return code -118 is Reeii, where ee represents the 2-byte external CA Datacom/DB return code, and ii is the CA Datacom/DB internal return code in hexadecimal characters.
GENERAL, GENERAL WITH NULLS, DATACOM SQL, SQL
DATACOM SQL
How procedure errors are handled depends on the PARAMETER STYLE specified in the CREATE PROCEDURE statement. See the parameter style information in the error handling section in Parameter Styles and Error Handling.
Data access is part of the proc-attributes (procedure attributes) definition. If you are using SQL Procedures, data-access is ignored.
MODIFIES SQL DATA, READS SQL DATA, CONTAINS SQL, NO SQL
(No default)
A proc-SQL-stmt is part of the proc-body syntax. The proc-SQL-stmt is composed of SQL Procedure statements that are coded and executed directly. Use the compound statement to create program-like logic. For details about compound statements, see Compound Statement.
In statements that are within SQL Procedures, that is, not External Procedures, SQL parameters and SQL variables can be referenced anywhere expressions are allowed. Colons are not allowed in these references. When a parameter or variable is referenced in a context containing or within the scope of an identically named variable or column, you must disambiguate the name as follows:
For parameters, use the following:
authid.SQL-proc-name.SQL-parameter-name
The authid is optional if the procedure name is unique in this particular context. For variables, use the start-label of the compound statement as follows:
start-label.variable-name
If your procedure fails to avoid ambiguity as required, CA Datacom/DB attempts to resolve the ambiguity in the following way. If the statement involves a table or view, an attempt is made to find a matching column name. All currently executing contexts are next searched for a matching SQL variable, inner-most context first (inner-most active compound-statement, including active condition handlers). The inner-most SQL parameter list is then searched.
We recommend you do not label a compound statement using the SQL Procedure name. If you do so, know that we do not guarantee any specific result or its consistency over time.
Recursive procedure calls are supported. Each occurrence has separate copies of parameters and SQL variables.
The statements in a proc-SQL-stmt are as follows:
The executable-DML-stmt is any DML (Data Manipulation Language) statement, except for DECLARE CURSOR. Use DML statements to access and manipulate data in SQL tables.
The DDL-stmt (Data Definition Language statements) are used to define SQL objects such as tables and views. DDL statements are only allowed when they do not interfere with the preparation, execution, or rebinding of your procedure.
Part of the proc-SQL-stmt clause that is used only with SQL Procedures.
Following are the control statements you can choose:
call-proc Part of the control-stmt in the proc-SQL-stmt that is used only with SQL Procedures.
execute-proc Part of the control-stmt in the proc-SQL-stmt that is used only with SQL Procedures.
proc-only-control-stmt Part of the control-stmt in the proc-SQL-stmt that is used only with SQL Procedures. The proc-only-control-stmt can be a compound statement (compound-stmt) or compound statement control statements (compound-stmt-only-control-statements).
compound-stmt For information about compound statements, see Compound Statement.
compound-stmt-only-control-statements Compound statement only control statements can appear only within the context of a compound statement. They can be nested. They can appear immediately inside a compound statement, or inside other statements that are themselves contained by a compound statement. Choices include the following statements:
assignment-stmt (see Assignment Statement)
case-stmt (not a case-expression, see CASE Statement)
if-then-stmt (see IF-THEN Statement)
iterate-stmt (see ITERATE Statement)
leave-stmt (see LEAVE Statement)
loop-stmt (see LOOP Statement)
while-do-stmt (see WHILE Statement)
repeat-stmt (see REPEAT-UNTIL Statement)
Notice the recursive nature of these definitions. Each of these proc-only-control-stmt statements qualify indirectly as a proc-SQL-stmt (each proc-only-control-stmt is a control-stmt), but many of them can also have lists of proc-SQL-stmt statements embedded in them. This means that the various statement types may be nested within each other to an unlimited depth. Notice however the following limitations.
DDL statements (Data Definition) are only allowed when they do not interfere with the preparation, execution, or rebinding of your procedure. For example, if your procedure creates a table and then uses it, the statements that use the table might fail with TABLE NOT FOUND or a similar error during the table resolution process of the prepare phase, unless that table is manually created before the CREATE PROCEDURE is prepared (it can be dropped afterwards). If your procedure uses and then ALTERs or DROPs a table, the procedure could be marked invalid as it executes, then attempt an auto-rebind (which could fail, depending on the change you made and how the table is used by the procedure).
Part of the proc-SQL-stmt clause that is used only with SQL Procedures. See the GET DIAGNOSTICS statement on GET DIAGNOSTICS Statement.
Begins the external body syntax that can be used to specify a name (NAME ext-name) for the program you write that constitutes the body of the procedure (see following for more information on NAME ext-name). EXTERNAL is only used for External Procedures, not for SQL Procedures.
A parameter style can also be specified as part of the EXTERNAL syntax but only if you have not specified a parameter style as part of the proc-attributes (procedure attribute) syntax. Parameter style is only used for External Procedures, not for SQL Procedures.
(Optional) If you specify EXTERNAL NAME ext-name, the external name you specify as the ext-name must match both the load-module name and PROGRAM-ID (CA Datacom Datadictionary PROGRAM entity-occurrence name) of the program. We recommend you specify a NAME ext-name to avoid confusion. NAME ext-name is only used for External Procedures, not for SQL Procedures.
A valid external name that meets the listed requirements
If you do not specify a NAME, the SQL name of the procedure is used as the default which in this case must be unique even without an AUTHID, and must be a valid load-module name.
(Optional) Specifying RUN OPTIONS run-options-string provides control over the environment in which the procedure executes. The run-options-string is a quoted string constant of up to 128 characters in length containing run options for use by the IBM OS/390 Language Environment (LE), which controls procedure execution. Following is an example:
RUN OPTIONS 'MSGFILE(SYSOUT,FBA,121,0,ENQ)'
Using the MSGFILE option prevents unmanaged contention for the SYSOUT data set (SYSLST in z/VSE) from procedures running concurrently. The ENQ parameter allows competing procedure executions (running in distinct LE environments) to share the data set by single-threading access.
The destination of print statements (for example, printf in C or DISPLAY in COBOL), executed in procedures across the Multi-User Facility, tends to be a single data set, usually DDNAME SYSOUT (SYSLST in z/VSE), identified by the Multi-User Facility startup JCL. For that reason, we recommend using these statements with caution. Assuming no print statements have been coded, use of MSGFILE is still useful, however, in enqueuing the messages that LE itself occasionally produces.
A quoted string constant of up to 128 characters in length containing run options for use by the IBM OS/390 Language Environment
Note: Details about the contents of the string are documented in the IBM OS/390 Language Environment for OS/390 & VM Programming Reference.
(No default)
|
Copyright © 2014 CA.
All rights reserved.
|
|