How to construct an SQL procedure body statement

The body of an SQL procedure contains a set of SQL statements and programming constructs that manipulate data in the ObjectServer.

Syntax

CREATE [ OR REPLACE ] PROCEDURE procedure_name ([ procedure_parameter,... ])
 [ DECLARE variable_declaration;...[;] ]
 BEGIN
   procedure_body_statement;...[;]
END

This topic describes only the entries required for the body of a procedure (procedure_body_statement), which is enclosed between the keywords BEGIN and END.

In the body of a procedure, you must separate each statement, except the last one, by a semicolon.

Statements in the procedure can include SQL commands and additional programming constructs.

You can run the following SQL commands in a procedure:

ALTER FILE
ALTER SYSTEM BACKUP
ALTER SYSTEM SET
ALTER SYSTEM DROP CONNECTION
ALTER TRIGGER
ALTER TRIGGER GROUP
ALTER USER
UPDATE
INSERT
DELETE
WRITE INTO
RAISE SIGNAL
{ EXECUTE | CALL } PROCEDURE

The user creating the procedure must have appropriate permissions to run the commands in the procedure body.

Attention: You cannot have circular dependencies in procedures or triggers; for example, you must not create a procedure that calls a procedure which then calls the original procedure.
You can use the following additional programming constructs in the procedure body:
  • SET assignment statement
  • IF THEN ELSE statement
  • CASE WHEN statement
  • FOR EACH ROW loop
  • FOR loop