ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 149
Constraints, Procedures, and Rules
Using Procedures
In procedures that are invoked by execution of rules, the following statements result in an
error:
BEGIN WORK
COMMIT WORK
ROLLBACK WORK
ROLLBACK WORK TO SAVEPOINT
SAVEPOINT
Another set of statements is provided for use only within procedures:
Assignment (=)
BEGIN...END
DECLARE Variable
GOTO
IF...THEN...ELSEIF...ELSE...ENDIF
Labeled Statements
PRINT
RETURN
WHILE...DO...ENDWHILE
Inside procedures, statements are terminated with a semicolon (;).
You can define parameters for passing information into and out of a procedure. In
addition, procedures let you store data in local variables, which are declared inside the
procedure with the DECLARE Variable statement.
Specifying Parameters
A parameter represents a value that is passed between a procedure and an invoking
application or rule. You define formal parameters with the CREATE PROCEDURE statement.
When executing a procedure directly, you pass input parameter values in the EXECUTE
PROCEDURE statement, and output parameter values are returned when the procedure
terminates. However, when using a procedure cursor, input parameter values must be set
before opening the cursor, and output parameter values are returned when the CLOSE
statement executes.
Within the body of the procedure, a parameter name is prefixed with a colon (:).
You can specify up to 1023 parameters of any SQL data type except the LONG data types.
Default values and nullability may be defined just as in a CREATE TABLE statement. If a
language is specified for a parameter defined as a CHAR or VARCHAR type, it must be
either the language of the DBEnvironment or else NATIVE 3000. The following shows a
procedure with a single parameter:
CREATE PROCEDURE Process10 (PartNumber CHAR(16)) AS
BEGIN
.
.
.
END;