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 about triggers, see the section SQL Triggers.
Although you can nest compound statements within other compound statements, only the outermost compound statement can contain DECLARE statements.
For more information about the syntax of compound statements, refer to the following topic in SQL Engine Reference: BEGIN [ATOMIC].
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 more information about the syntax of the IF statement, refer to the following topic in SQL Engine Reference: IF.
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 more information about the syntax of the LEAVE statement, refer to the following topic in SQL Engine Reference: LEAVE.
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 Pervasive PSQL 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 more information about the syntax of the LOOP statement, refer to the following topic in SQL Engine Reference: LOOP.
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;
Pervasive PSQL evaluates the Boolean value expression. If it is true, then Pervasive PSQL 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, Pervasive PSQL 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 more information about the syntax of the WHILE statement, refer to the following topic in SQL Engine Reference: WHILE.