|
|
|
Valid in: SQL, ESQL
The Create Procedure statement creates a database procedure.
|
|
|
The Create Procedure statement has the following format:
[EXEC SQL] [CREATE] PROCEDURE [schema.]proc_name
[[(set_param_name [=] SET OF]
([param_mode] param_name [=] param_type
[WITH | NOT DEFAULT] [WITH | NOT NULL]
{, [param_mode] param_name [=] param_type
[WITH | NOT DEFAULT] [WITH | NOT NULL]})[)]]
[RESULT ROW (result_type [WITH | NOT DEFAULT] [WITH | NOT NULL]
{, result_type [WITH | NOT DEFAULT] [WITH | NOT NULL]}) =|AS
[declare_section]
BEGIN
statement {; statement}[;]
END
Defines the name of the procedure. This must be a valid object name.
Defines the name of the set of parameter. This must be a valid object name. The set of parameters are referenced like base tables in the body of the procedure.
Defines the name of a procedure parameter. This must be a valid object name. Parameters can be passed by value or by reference.
Assigns one of the following modes to the procedure parameter:
Declares the parameter as an input only parameter.
Declares the parameter as an output only parameter.
Declares the parameter as one that passes a value into the procedure and returns it, possibly modified, to the calling program.
Specifies the data type of the associated parameter. The data type can be any legal SQL data type, and the with|not null clause can be part of the specification.
A list of local variables for use in the procedure. For details, see Declare.
Local variable assignments and any of the statements listed in the text of the create procedure description.
The data type of the associated entry in a return row statement. The data type can be any legal SQL data type, and the with | not null clause can be part of the specification. For details, see Return Row.
Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
select col1, ifnull(col2, 0), left(col4, 22) from t1:
|
|
|
The Create Procedure statement creates a database procedure that is managed as a named database object by the DBMS Server. A database procedure can be executed directly using the execute procedure statement or can be invoked by a rule.
A procedure that is directly executed can contain any of the following statements:
Procedures that are invoked by rules must not issue the COMMIT and ROLLBACK statements, and cannot use the RETURN statement to return values to an application. Procedures invoked by DELETE or UPDATE rules must not reference the old blob column values. Procedures invoked by rules can use the RAISE ERROR statement to signal error conditions.
A procedure cannot contain any data definition statements, such as CREATE TABLE, nor can a procedure create or drop another procedure. Database procedures can execute other database procedures.
The repeated clause cannot be used in a statement in the procedure body. However, database procedures confer the same performance benefits as the repeated clause.
In a procedure, SELECT statements must assign their results to local variables. Also, SELECT statements can return only a single row of data unless they are contained in a FOR statement. If more rows are returned, no error is issued, but only the first row retrieved is in the result variables.
Both procedure parameters and local variables can be used in place of any constant value in statements in the procedure body. The DBMS Server treats procedure parameters as local variables inside the procedure body, although they have an initial value assigned when the procedure is invoked. Preceding colons (:) are only necessary if the referenced name can be interpreted to see more than one object.
Assignment statements assign values to local variables and procedure parameters in the body of the procedure. Local variables are variables that are declared using the DECLARE statement in the database procedure. The scope of these variables is the database procedure in which they are declared. Variable assignment statements use the '=' or ':=' operator to assign values to local variables. The value assigned can be a constant or the result of the evaluation of an expression. The data types of the value and the local variable must be compatible. For a description of assignment operations, see the chapter "Understanding the Elements of SQL Statements."
Procedure parameters explicitly declared with the INOUT or OUT modes pass their values back to the calling procedure.
All statements, except a statement preceding an END, ENDFOR, or ENDIF, must be terminated with a semicolon.
If working interactively, the BEGIN and END keywords can be replaced with braces { }, but the terminating semicolon must follow the closing brace if another statement is entered after the CREATE PROCEDURE statement and before committing the transactions.
|
|
|
By default, parameters to a database procedure are INPUT only. Though the parameter value may be updated in the body of the procedure, the changed value is not passed back to the calling application, rule, or procedure. The BYREF designation used in a calling application can be used to force the return of the modified parameter value. For more information on BYREF, see Execute Procedure.
For database procedures called from other database procedures or by the firing of a rule, the INOUT and OUT modes can be coded in a parameter declaration to return the modified value of the parameter back to the caller of the procedure. This allows results to be passed from one procedure to another and column values to be changed by BEFORE rules defined on a particular table.