Creating a procedure

A procedure is created using the SQL statement CREATE PROCEDURE, see "CREATE PROCEDURE - Create procedure". A procedure can also be created using the SQL statement CREATE SCHEMA, see "CREATE SCHEMA - Create schema".

Procedures can be defined with input, input/output, and output parameters.

Recommendation Parameter names should differ from column names (e.g. by assigning a prefix such as par_).

When a procedure is created, the current authorization identifier must have the EXECUTE privilege for the routines called directly 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 procedure text in SESAM/SQL is written entirely in the SQL programming language. The following SQL statements for data searching and data manipulation are permitted in procedures, see section "CREATE PROCEDURE - Create procedure":

SQL statement without a cursor	Function in the procedure	see
SELECT	Reads a single row	"SELECT - Read individual rows"
INSERT	Insert rows in a table	"INSERT - Insert rows in table"
UPDATE	Changes the columns of the rows in a table which satisfy a particular search condition	"UPDATE - Update column values"
DELETE	Deletes the rows in a table which satisfy a particular search condition	"DELETE - Delete rows"
MERGE	Depending on a particular condition, changes rows in a table or enters rows in a table	"MERGE - Insert rows in a table or update column values"
SQL statement with a cursor	Function in the procedure	see
OPEN	Opens a local cursor	"OPEN - Open cursor"
FETCH	Positions a local cursor and, if necessary, reads the current row	"FETCH - Position cursor and read row"
UPDATE	Changes the columns of the row in a table to which the cursor is positioned	"UPDATE - Update column values"
DELETE	Deletes the row in a table to which the cursor is positioned	"DELETE - Delete rows"
CLOSE	Closes a local cursor	"CLOSE - Close cursor"

Table 25: SQL statements for data manipulation in procedures (section 2 of 2)

In addition to the SQL statements mentioned above, a procedure can also contain control statements (see section "Control statements in routines") and diagnostic statements (see section "Diagnostic information in routines").

A procedure may not contain any dynamic SQL statements or cursor descriptions, see section "Dynamic SQL".

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.

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.

comments

Descriptive comments (see "Comments") can be inserted in a procedure as required.