Introduction of PL/SQL language Elements

PL/SQL offers various languageconstructs such as variable assignments, control structures (loops, if-then-else), procedureand function calls, etc. However, PL/SQL does not allow commands of the SQL data definition language such as the create table statement. For this, PL/SQL provides special packages.

PL/SQL uses a modified select statement that requires each selected tuple to be assigned to a record (or a list of variables). There are several alternatives in PL/SQL to a assign a value to a variable. The most simple way to assign a value to a variable is

declare
counter integer := 0;
. . .
begin
counter := counter + 1;

Values to assign to a variable can also be retrieved from the database using a select statement
select into from where ;

It is important to ensure that the select statement retrieves at most one tuple ! Otherwise
it is not possible to assign the attribute values to the specified list of variables and a runtime
error occurs. If the select statement retrieves more than one tuple, a cursor must be used instead. Furthermore, the data types of the specified variables must match those of the retrieved attribute values. For most data types, PL/SQL performs an automatic type conversion (e.g., from integer to real).

Instead of a list of single variables, a record can be given after the keyword into. Also in this case, the select statement must retrieve at most one tuple !

declare
employee rec EMP%ROWTYPE;
max sal EMP.SAL%TYPE;
begin
select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO
into employee rec
from EMP where EMPNO = 5698;
select max(SAL) into max sal from EMP;
. . .
end;

PL/SQL provides while-loops, two types of for-loops, and continuous loops. Latter ones are used in combination with cursors. All types of loops are used to execute a sequence of statements multiple times. The specification of loops occurs in the same way as known from imperative programming languages such as C or Pascal.

A while-loop has the pattern
[<< >>]
while loop
;
end loop [] ;

A loop can be named. Naming a loop is useful whenever loops are nested and inner loops are
completed unconditionally using the exit ; statement.

Whereas the number of iterations through a while loop is unknown until the loop completes,
the number of iterations through the for loop can be specified using two integers.

[<< >>]
for in [reverse] .. loop

end loop [] ;

The loop counter is declared implicitly. The scope of the loop counter is only the
for loop. It overrides the scope of any variable having the same name outside the loop. Inside
the for loop, can be referenced like a constant. may appear in expressions,
but one cannot assign a value to . Using the keyword reverse causes the iteration to
proceed downwards from the higher bound to the lower bound.
Processing Cursors: Before a cursor can be used, it must be opened using the open statement
open [()] ;

The associated select statement then is processed and the cursor references the first selected
tuple. Selected tuples then can be processed one tuple at a time using the fetch command
fetch into ;

The fetch command assigns the selected attribute values of the current tuple to the list of
variables. After the fetch command, the cursor advances to the next tuple in the result set.
Note that the variables in the list must have the same data types as the selected values. After
all tuples have been processed, the close command is used to disable the cursor.

close ;

The example below illustrates how a cursor is used together with a continuous loop:

declare
cursor emp cur is select from EMP;
emp rec EMP%ROWTYPE;
emp sal EMP.SAL%TYPE;
begin
open emp cur;
loop
fetch emp cur into emp rec;
exit when emp cur%NOTFOUND;
emp sal := emp rec.sal;

end loop;
close emp cur;
. . .
end;

Each loop can be completed unconditionally using the exit clause:
exit [] [when ]

Using exit without a block label causes the completion of the loop that contains the exit statement.

A condition can be a simple comparison of values. In most cases, however, the condition
refers to a cursor. In the example above, %NOTFOUND is a predicate that evaluates to false if the
most recent fetch command has read a tuple. The value of %NOTFOUND is null
before the first tuple is fetched. The predicate evaluates to true if the most recent fetch failed
to return a tuple, and false otherwise. %FOUND is the logical opposite of %NOTFOUND.
Cursor for loops can be used to simplify the usage of a cursor:

[<< >>]

for in [()] loop

end loop [];

A record suitable to store a tuple fetched by the cursor is implicitly declared. Furthermore,
this loop implicitly performs a fetch at each iteration as well as an open before the loop is
entered and a close after the loop is left. If at an iteration no tuple has been fetched, the loop
is automatically terminated without an exit.

It is even possible to specify a query instead of in a for loop:

for in () loop

end loop;

That is, a cursor needs not be specified before the loop is entered, but is defined in the select
statement.
Example:

for sal rec in (select SAL + COMM total from EMP) loop
. . . ;
end loop;

total is an alias for the expression computed in the select statement. Thus, at each iteration
only one tuple is fetched. The record sal rec, which is implicitly defined, then contains only
one entry which can be accessed using sal rec.total. Aliases, of course, are not necessary if
only attributes are selected, that is, if the select statement contains no arithmetic operators
or aggregate functions.

For conditional control, PL/SQL offers if-then-else constructs of the pattern
if then
[elsif ] then
. . .
[else] end if ;

Starting with the first condition, if a condition yields true, its corresponding sequence of statements
is executed, otherwise control is passed to the next condition. Thus the behavior of this type of PL/SQL statement is analogous to if-then-else statements in imperative programming languages.

Except data definition language commands such as create table, all types of SQL statements
can be used in PL/SQL blocks, in particular delete, insert, update, and commit. Note that in PL/SQL only select statements of the type select into are allowed, i.e., selected attribute values can only be assigned to variables (unless the select statement is used in a subquery). The usage of select statements as in SQL leads to a syntax error. If update or delete statements are used in combination with a cursor, these commands can be restricted to currently fetched tuple. In these cases the clause where current of is added as shown in the following example.

Example: The following PL/SQL block performs the following modifications: All employees having ’KING’ as their manager get a 5% salary increase.

declare
manager EMP.MGR%TYPE;
cursor emp cur (mgr no number) is
select SAL from EMP
where MGR = mgr no
for update of SAL;
begin
select EMPNO into manager from EMP
where ENAME = ’KING’;
for emp rec in emp cur(manager) loop
update EMP set SAL = emp rec.sal 1.05
where current of emp cur;
end loop;
commit;
end;