SQL Script Procedure Header
A procedure declaration in SQL Script defines the input parameters and output parameters of the procedure. To call a procedure, see
CALL.
Syntax
PROCEDURE <procedureName> ( [<parameterList>] )]
<statement>
The parentheses in the procedure’s syntax are optional. If there are parentheses, they can be empty or they can contain a list of parameters.
Remarks
• A parameter list (<paramList>) is a comma-separated list of parameters of the form:
{ IN | INOUT | OUT } <parameterName> <dataType>
• The data type of a parameter (
<dataType>) can be any type listed in
Data Types, except ROW.
• You can use any PUBLIC data type defined in the main compound statement within the procedure declaration (indicated by <compoundStatement> in the syntax for a procedure). This way a parameter can be defined to be of a named type instead of always being primitive.
Examples
PROCEDURE init_table (IN employee_id INTEGER)
BEGIN
INSERT INTO T (empid) VALUES (employee_id);
END
PROCEDURE cur_month (OUT x INTEGER)
BEGIN
SET x = MONTH (CURRENT_DATE() );
END
PROCEDURE inc (INOUT x INTEGER)
BEGIN
SET x = x + 1;
END
PROCEDURE inc (IN x INTEGER)
BEGIN
SET x = 5; -- Error
END
PIPE Modifier
A modifier named PIPE is used in SQL Script for streaming a cursor. It can be used only in procedure parameter declarations, and its purpose is to pipeline the output.
Syntax
IN <parameterName> PIPE <cursorDataType>
OUT <parameterName> PIPE <cursorDataType>
Remarks
• The PIPE modifier can be applied to any IN or OUT cursor data type.
• The PIPE modifier cannot be used on INOUT parameters or on any noncursor data type.
• An IN parameter with the PIPE modifier can be passed any PIPE variable that comes from an IN or OUT parameter of the current procedure.
• An OUT parameter with the PIPE modifier must be passed a cursor variable with the same schema as the PIPE.
• Within a PROCEDURE, a PIPE variable (either IN or OUT) can be used in INSERT statements. For details, see
INSERT.
• Procedures with a PIPE modifier on an IN parameter do not run in a separate thread.
• Any procedure with the PIPE modifier on an OUT parameter runs in a separate thread. The calling procedure continues execution as soon as the pipelined procedure begins execution. The calling procedure finds the OUT cursor already initialized, and opens the cursor and can fetch from it. (For details, see
FETCH.) If the calling procedure accesses any non-PIPE OUT parameter, however, the calling procedure blocks until the pipelined procedure ends execution. This is because the final values of non-PIPE outputs are not known until the procedure completes.
• A PIPE modifier can be in an INSERT statement within an EXECUTE IMMEDIATE statement.
Example
The following procedure returns a cursor with all of the names reversed.
PROCEDURE reverse_all (OUT result PIPE (rev_name VARCHAR))
BEGIN
DECLARE c CURSOR FOR SELECT name FROM /shared/T;
DECLARE name VARCHAR;
OPEN c;
REPEAT
FETCH c INTO name;
CALL /shared/reverse(name, name);
INSERT INTO result (rev_name) VALUES (name);
UNTIL NOT c.FOUND
END REPEAT;
END