Procedural Constructs

Compound Statements

Refers to a set of SQL statements written between begin and end.

Loops

while

declare n integer default 0; 
while n < 10 do 
  set n = n + 1 
end while

repeat

repeat 
 set n = n – 1 
until n = 0 
end repeat

for

Permits iteration over all results of a query.

For example:

declare n integer default 0;
for r as
  select budget from department
  where dept_name = 'Music'
do
  set n = n - r.budget
end for

• leave is used to exit the loop

• iterate starts from the next tuple

Conditional Constructs

SQL also supports conditional constructs such as if-then-else and case.

Exception Handling

Example:

declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin
...
.. signal out_of_classroom_seats
end

exit is the handler; it causes the block enclosed by begin..end to be exited.