CREATE PROCEDURE creates a procedure and saves its definition in the database.
Procedures and their use in SESAM/SQL are described in detail in chapter "Routines".
Each routine which is called in the procedure must already exist. Nested calls of routines are thus possible, but rerecursive calls are not.
The current authorization identifier must own the schema to which the procedure belongs. It must also, for all tables and columns which are addressed in the procedure, have the privileges which are required to execute the DML statements contained in the procedure.
The current authorization identifier must have the EXECUTE privilege for each routine called in the procedure. It must also, for all tables and columns which are addressed in the procedure, have the privileges which are required to execute the DML statements contained in the procedure.
The current authorization identifier automatically obtains the EXECUTE privilege for the procedure created. If it even has authorization to pass on the relevant privileges, it may also pass on the EXECUTE privilege to other authorization identifiers.
The procedure and the objects which are addressed in the procedure must belong to the same database. The names of these objects may possibly be complemented by the procedure's database and schema names.
CREATE PROCEDURE procedure ([ procedure_parameter_definition [, procedure_parameter_definition ] ...])
{ MODIFIES SQL DATA | READS SQL DATA | CONTAINS SQL }
{ routine_sql_statement | compound_statement }
procedure ::= routine
procedure_parameter_definition ::= [IN | OUT | INOUT] routine_parameter data_type
procedure
Name of the procedure (maximum length: 31 characters). The unqualified procedure name must be unique within the routine names of the schema. You can qualify the table name with a database and schema name.
If the CREATE PROCEDURE statement is specified in a CREATE SCHEMA statement, the procedure name may be qualified only with the database and schema names from the CREATE SCHEMA statement.
([ procedure_parameter_definition [{, procedure_parameter_definition }...]])
List of the procedure parameters. Any number of procedure parameters is possible. It is limited only by the maximum statement length. If no parameter is defined, the list consists only of the parentheses.
procedure_parameter_definition
Definition of a procedure parameter.
Procedure parameters have no indicator variable.
IN: The procedure parameter is an input parameter.
OUT: The procedure parameter is an output parameter.
INOUT: The procedure parameter is an input and output parameter.
routine_parameter
Name of the procedure parameter. The names of the procedure parameters must differ from each other.
data_type
Data type of the procedure parameter.
Only unqualified procedure parameters are permitted.
dimension may not be specified.
MODIFIES SQL DATA
The procedure can contain SQL statements for updating data.
READS SQL DATA
The procedure can contain SQL statements for reading data, but no SQL statements for updating data. This information is checked. In the event of an error, the statement is rejected with SQLSTATE.
CONTAINS SQL
The procedure contains neither SQL statements for reading data nor for updating data. This information is checked. In the event of an error, the statement is rejected with SQLSTATE.
Called routines of this procedure may not contain the MODIFIES SQL DATA and READS SQL DATA specifications.
routine_sql_statement
A procedure contains precisely one atomic or non-atomic SQL statement. The non-atomic SQL statements in SESAM/SQL are COMPOUND (without specification of ATOMIC), CASE, FOR, IF, LOOP, REPEAT, and WHILE. They can contain other (atomic or non-atomic) SQL statements. Atomic SQL statements are the other SQL statements permissible in a routine.
No privileges are checked before an SQL statement is executed.
An SQL statement in a procedure may access the parameters of the procedure and (if the statement is part of a COMPOUND statement) local variables, but not host variables.
The syntax and meaning of routine_sql_statement are described centrally in section "SQL statements in routines". The SQL statements named there may not be used with the exception of RETURN.
compound_statement
COMPOUND statement which contains multiple SQL statements and possibly defines common local data, cursors, and exception handling routines for these, see section "COMPOUND - Execute SQL statements in a common context".
Conditions
SESAM/SQL offers the SQL statements COMPOUND, CASE, FOR, IF, ITERATE, LEAVE, LOOP, REPEAT, SET, and WHILE for controlling routines. These SQL statements are also referred to as control statements.
You obtain diagnostic information in routines with the diagnostic statements GET DIAGNOSTICS, SIGNAL, and RESIGNAL.
In SESAM/SQL, nested calls of routines are permitted. The CALL statement is therefore one of the statements permitted in a routine.
A routine may not contain any SQL statements for transaction management (see "SQL statements for transaction management"). Local cursors can therefore not be accessed on a cross-transaction basis. STORE or RESTORE statements are not statements which are permitted in a routine; their use in a routine makes no sense.
A routine may not contain any dynamic SQL statements or cursor descriptions, see section "Dynamic SQL".
A routine can be called in a dynamic SQL statement. If a procedure contains parameters of the type OUT or INOUT, the corresponding arguments must be specified in a dynamic CALL statement in the form of placeholders.
Example
The GetCurrentYear procedure below returns the current year as a number. It contains no SQL statements for reading or updating data.
CREATE PROCEDURE ProcSchema.GetCurrentYear (OUT current_year INTEGER) CONTAINS SQL SET current_year = EXTRACT (YEAR FROM CURRENT_DATE)
You will find further examples in chapter "Routines" and in the demonstration database of SESAM/SQL (see the “ Core manual”).
See also
CALL, DROP PROCEDURE, COMPOUND, CASE, FOR, IF, ITERATE, LEAVE, LOOP, REPEAT, SET, WHILE, SELECT, INSERT, UPDATE, DELETE, MERGE, OPEN, FETCH, UPDATE, DELETE, CLOSE, GET DIAGNOSTICS, SIGNAL, RESIGNAL