Stored Procedures

Stored Procedures

Using stored procedures, you can group logically associated programming steps into a general process and then invoke that process with one statement. You can also execute this process using different values by passing parameters.

Once invoked, SQL stored procedures are executed in their entirety without internal communication between a host language program and the SQL engine. You can invoke them independently, and they can be invoked as part of the body of other procedures or triggers. For more information about triggers, refer to the section SQL Triggers.

You can use SQL variable statements within stored procedures to store values internally from statement to statement. See the section SQL Variable Statements for more information about these statements.

You can use SQL control statements in stored procedures to control the execution flow of the procedure. For more information about these statements, refer to the section SQL Control Statements later in this chapter.

Stored Procedure and Positioned Update

The following is an example of a stored procedure and positioned update:

DROP PROCEDURE curs1

CREATE PROCEDURE curs1 (in :Arg1 char(4) ) AS

DECLARE :alpha char(10) DEFAULT 'BA';

DECLARE :beta INTEGER DEFAULT 100;

DECLARE degdel CURSOR FOR

SELECT degree, cost_per_credit FROM tuition WHERE Degree = :Arg1 AND cost_per_credit = 100

FETCH NEXT FROM degdel INTO :alpha,:beta

DELETE WHERE CURRENT OF degdel;

CALL curs1('BA')

Declaring Stored Procedures

To define a stored procedure, use the CREATE PROCEDURE statement.

CREATE PROCEDURE EnrollStudent (in :Stud_id integer, in :Class_Id integer);

BEGIN
INSERT INTO Enrolls VALUES (:Stud_id, :Class_Id, 0.0);
END

The maximum size for a stored procedure name is 30 characters. Parentheses are required around the parameter list, and the parameter name may be any valid SQL identifier.

Stored procedures must have unique names in the dictionary.

For information about the syntax of the CREATE PROCEDURE statement, refer to the following topic in SQL Engine Reference: CREATE PROCEDURE.

Invoking Stored Procedures

To invoke a stored procedure, use the CALL statement.

CALL EnrollStudent (274410958, 50);

You must define a value for every parameter. You can assign a value to a parameter using the associated argument in the CALL statement or with the associated default clause in the CREATE PROCEDURE statement. An argument value for a parameter in a CALL statement overrides any associated default value.

You can specify calling values in a CALL statement using either of the following two ways:

•

Positional arguments—Allow you to specify parameter values implicitly based on the ordinal position of the parameters in the list when the procedure was created.

•

Keyword arguments—Allow you to specify parameter values explicitly by using the name of the parameter whose value is being assigned.

You cannot assign a parameter value twice in the argument list (either positional or keyword). If you use both positional arguments and keyword arguments in the same call, the keyword arguments must not refer to a parameter that receives its value through the positional arguments. When using keyword arguments, the same parameter name must not occur twice.

For more information about the syntax of the CALL statement, refer to the following topic in SQL Engine Reference: CALL.

Deleting Stored Procedures

To delete a stored procedure, use the DROP PROCEDURE statement.

DROP PROCEDURE EnrollStudent;

For more information about the syntax of this statement, refer to the following topic in SQL Engine Reference: DROP PROCEDURE.