Storing Logic
The following topics explain how to store SQL procedures for future use and how to create triggers. For information about stored views, see
Retrieving Data.
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, see
SQL Triggers.
You can use SQL variable statements within stored procedures to store values internally from statement to statement. See
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, see
SQL Control Statements.
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
BEGIN
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
FOR UPDATE;
OPEN degdel;
FETCH NEXT FROM degdel INTO :alpha,:beta
DELETE WHERE CURRENT OF degdel;
CLOSE degdel ;
END
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, see
CREATE PROCEDURE in
SQL Engine Reference.
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 syntax information, see
CALL in
SQL Engine Reference.
Deleting Stored Procedures
To delete a stored procedure, use the DROP PROCEDURE statement.
DROP PROCEDURE EnrollStudent;
For syntax information, see
DROP PROCEDURE in
SQL Engine Reference.
SQL Variable Statements
SQL variables use SET to assign values that are accessible from statement to statement. You can use SET statements inside stored procedures. These statements are called assignment statements.
Procedure-Owned Variables
An SQL variable you define inside a stored procedure is a procedure-owned variable. Its scope is the procedure in which it is declared, so you can reference it only within that procedure. If a procedure calls another procedure, the procedure-owned variable of the calling procedure cannot be directly used in the called procedure and instead must be passed in a parameter. You cannot declare a procedure-owned variable more than once in the same stored procedure.
If a compound statement is the body of a stored procedure, then no SQL variable name declared in that procedure can be identical to a parameter name in the parameter list of that procedure. For more information, see
Compound Statement.
Assignment Statements
An assignment statement initializes or changes the values of SQL variables. The value may be a computed expression involving constants, operators, and this or other SQL variables.
SET :CourseName = 'HIS305';
The value expression may also be a SELECT statement.
SET :MaxEnrollment = (SELECT Max_Size FROM Class
WHERE ID = classId);
For syntax information, see
SET in
SQL Engine Reference.
SQL Control Statements
You can only use control statements in the body of a stored procedure. These statements control the execution of the procedure. The control statements include the following:
• Compound statement (BEGIN...END)
• IF statement (IF...THEN...ELSE)
• LEAVE statement
• Loop statements (LOOP and WHILE)
Compound Statement
A compound statement groups other statements together.
BEGIN
DECLARE :NumEnrolled INTEGER;
DECLARE :MaxEnrollment INTEGER;
DECLARE :failEnrollment CONDITION
FOR SQLSTATE '09000';
SET :NumEnrolled = (SELECT COUNT (*)
FROM Enrolls
WHERE Class_ID = classId);
SET :MaxEnrollment = (SELECT Max_Size
FROM Class
WHERE ID = classId);
IF (:NumEnrolled >= :MaxEnrollment) THEN
SIGNAL :failEnrollment ELSE
SET :NumEnrolled = :NumEnrolled + 1;
END IF;
END
You can use a compound statement in the body of a stored procedure or a trigger. For more information, see
SQL Triggers.
Although you can nest compound statements within other compound statements, only the outermost compound statement can contain DECLARE statements.
For information about compound statement syntax, see
BEGIN [ATOMIC] in
SQL Engine Reference.
IF Statement
An IF statement provides conditional execution based on the truth value of a condition.
IF (:counter = :NumRooms) THEN
LEAVE Fetch_Loop;
END IF;
For syntax information, see
IF in
SQL Engine Reference.
LEAVE Statement
A LEAVE statement continues execution by leaving a compound statement or loop statement.
LEAVE Fetch_Loop
A LEAVE statement must appear inside a labeled compound statement or a labeled loop statement. The statement label from the LEAVE statement must be identical to the label of a labeled statement containing LEAVE. This label is called the corresponding label.
Note: A compound statement can contain a loop statement; therefore, since you can embed loop statements, the statement label in a LEAVE statement can match the label of any of the embedded loops or the label of the body of the stored procedure.
For syntax information, see
LEAVE in
SQL Engine Reference.
LOOP Statement
A LOOP statement repeats the execution of a block of statements.
FETCH_LOOP:
LOOP
FETCH NEXT cRooms INTO CurrentCapacity;
IF (:counter = :NumRooms) THEN
LEAVE FETCH_LOOP;
END IF;
SET :counter = :counter + 1;
SET :TotalCapacity = :TotalCapacity + :CurrentCapacity;
END LOOP;
If each statement in the SQL statement list executes without error and Zen does not encounter a LEAVE statement or invoke a handler, then execution of the LOOP statement repeats. A LOOP statement is similar to a WHILE statement in that execution continues while a given condition is true.
If a LOOP statement has a beginning label, it is called a labeled LOOP statement. If you specify the ending label, then it must be identical to the beginning label.
For syntax information, see
LOOP in
SQL Engine Reference.
WHILE Statement
A WHILE statement repeats the execution of a block of statements while a specified condition is true.
FETCH_LOOP:
WHILE (:counter < :NumRooms) DO
FETCH NEXT cRooms INTO :CurrentCapacity;
IF (SQLSTATE = '02000') THEN
LEAVE FETCH_LOOP;
END IF;
SET :counter = :counter + 1;
SET :TotalCapacity = :TotalCapacity + :CurrentCapacity;
END WHILE;
Zen evaluates the Boolean value expression. If it is true, then Zen executes the SQL statement list. If each statement in the SQL statement list executes without error and no LEAVE statement is encountered, then execution of the loop statement repeats. If the Boolean value expression is false or unknown, Zen terminates execution of the loop statement.
If a WHILE statement has a beginning label, it is called a
labeled WHILE statement. If you specify an ending label, it must be identical to the beginning label. For syntax information, see
WHILE in
SQL Engine Reference.
SQL Triggers
Triggers are actions defined on a table that you can use to enforce consistency rules for a database. They are dictionary objects that identify the appropriate action for the DBMS to perform when a user executes a SQL data modification statement on that table.
To declare a trigger, use the CREATE TRIGGER statement.
CREATE TRIGGER CheckCourseLimit;
The maximum size for a trigger name is 30 characters.
To delete a trigger, use the DROP TRIGGER statement.
DROP TRIGGER CheckCourseLimit;
You cannot invoke a trigger directly; they are invoked as a consequence of an INSERT, UPDATE, or DELETE action on a table with an associated trigger. For syntax information, see the following topics in SQL Engine Reference:
Note: In order to prevent circumvention of triggers, Zen stamps the data file containing a trigger as a bound data file. This restricts access to Btrieve users and prevents them from performing an action that would fire the trigger in a Zen database. For more information, see SQL Engine Reference.
Timing and Ordering of Triggers
Since triggers execute automatically for a given event, it is important to be able to specify when and in what order the trigger or triggers should execute. You specify time and order when you create the trigger.
Specifying the Triggered Action Time
When an event that is associated with a trigger occurs, the trigger must execute either before the event or after the event. For example, if an INSERT statement invokes a trigger, the trigger must execute either before the INSERT statement executes or after the INSERT statement executes.
CREATE TABLE Tuitionidtable (primary key(id), id ubigint)#
CREATE TRIGGER InsTrig
BEFORE INSERT ON Tuition
REFERENCING NEW AS Indata
FOR EACH ROW
INSERT INTO Tuitionidtable VALUES(Indata.ID);
You must specify either BEFORE or AFTER as the triggered action time. The triggered action executes once for each row. If you specify BEFORE, the trigger executes before the row operation; if you specify AFTER, the trigger executes after the row operation.
Note: Zen does not invoke a trigger by enforcing an RI constraint. Also, a table may not have a DELETE trigger defined if an RI constraint may also cause the system to perform cascaded deletes on that table.
Specifying Trigger Order
You may have situations in which an event invokes more than one trigger for the same specified time. For example, an INSERT statement may invoke two or more triggers that are defined to execute after the INSERT statement executes. Since these triggers cannot execute simultaneously, you must specify an order of execution for them.
Since the following CREATE TRIGGER statement specifies an order of 1, any subsequent BEFORE INSERT triggers that you define for the table must have a unique order number greater than 1.
CREATE TRIGGER CheckCourseLimit
BEFORE INSERT
ON Enrolls
ORDER 1
You designate the order value with an unsigned integer, which must be unique for that table, time, and event. If you anticipate inserting new triggers within the current order, leave gaps in the numbering to accommodate this.
If you do not designate an order for a trigger, then the trigger is created with a unique order value that is higher than that of any trigger currently defined for that table, time, and event.
Defining the Trigger Action
The trigger action executes once for each row. The syntax for the trigger action is as follows:
CREATE TRIGGER InsTrig
BEFORE INSERT ON Tuition
REFERENCING NEW AS Indata
FOR EACH ROW
INSERT INTO Tuitionidtable VALUES(Indata.ID);
If the triggered action contains a WHEN clause, then the triggered SQL statement executes if the Boolean expression is true. If the expression is not true, then the triggered SQL statement does not execute. If no WHEN clause is present, then the triggered SQL statement executes unconditionally.
The triggered SQL statement can be either a single SQL statement, including a stored procedure call (CALL procedure_name), or a compound statement (BEGIN...END).
Note: The triggered action must not change the subject table of the trigger.
When you need to reference a column of the old row image (in the case of DELETE or UPDATE) or a column of the new row image (in the case of INSERT or UPDATE) in the triggered action, you must add a REFERENCING clause to the trigger declaration, as follows:
REFERENCING NEW AS N
The REFERENCING clause allows you to maintain information about the data that the trigger modifies.