PL/SQL CONTROL STRUCTURES

PL/SQL has a variety of control structures that allow you to control the behavior
of the block as it runs. These structures include conditional statements and
loops.
1 If-then-else
2 Case
Case with no else
Labeled case
Searched case
3 Simple loop
4 While loop
5 For loop
6 Goto and Labels
IF-THEN-ELSE
Syntax: If <condition1> then
Sequence of statements;
Elsif <condition1> then
Sequence of statements;
……
Else
Sequence of statements;
End if;
Ex: DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname =
'ACCOUNTING';
if dno = 10 then
dbms_output.put_line('Location is NEW YORK');
elsif dno = 20 then
dbms_output.put_line('Location is DALLAS');
elsif dno = 30 then
dbms_output.put_line('Location is CHICAGO');
else
dbms_output.put_line('Location is BOSTON');
end if;
END;
Output: Location is NEW YORK

CASE
Syntax: Case test-variable
When value1 then sequence of statements;
When value2 then sequence of statements;
……
When valuen then sequence of statements;
Else sequence of statements;
End case;
Page 1 of 6
Ex: DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname =
'ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location is NEW YORK');
when 20 then
dbms_output.put_line('Location is DALLAS');
when 30 then
dbms_output.put_line('Location is
CHICAGO');
else
dbms_output.put_line('Location is BOSTON');
end case;
END;
Output: Location is NEW YORK

CASE WITHOUT ELSE

Syntax: Case test-variable
When value1 then sequence of statements;
When value2 then sequence of statements;
……
When valuen then sequence of statements;
End case;
Ex: DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname =
'ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location is NEW
YORK');
when 20 then
dbms_output.put_line('Location is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
when 40 then
dbms_output.put_line('Location is BOSTON');
end case;
END;
Output: Location is NEW YORK

Page 2 of 6
 LABELED CASE
Syntax: <<label>>
Case test-variable
When value1 then sequence of statements;
When value2 then sequence of statements;
……
When valuen then sequence of statements;
End case;
Ex: DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname = ACCOUNTING';
<<my_case>>
case dno
when 10 then
dbms_output.put_line('Location is NEW YORK');
when 20 then
dbms_output.put_line('Location is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
when 40 then
dbms_output.put_line('Location is BOSTON');
end case my_case;
END;
Output: Location is NEW YORK

SEARCHED CASE
Syntax: Case
When <condition1> then sequence of statements;
When <condition2> then sequence of statements;
……
When <conditionn> then sequence of statements;
End case;
Ex: DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname = 'ACCOUNTING';
case dno
when dno = 10 then
dbms_output.put_line('Location is NEW YORK');
when dno = 20 then
dbms_output.put_line('Location is DALLAS');
when dno = 30 then
dbms_output.put_line('Location is CHICAGO');
when dno = 40 then
dbms_output.put_line('Location is BOSTON');
end case;
END;
Output Location is NEW YORK
Page 3 of 6
 SIMPLE LOOP
Syntax Loop
Sequence of statements;
Exit when <condition>;
End loop;
In the syntax exit when <condition> is equivalent to
If <condition> then
Exit;
End if;
Ex DECLARE
i number := 1;
BEGIN
loop
dbms_output.put_line('i = ' || i);
i := i + 1;
exit when i > 5;
end loop;
END;
Output i=1
i=2
i=3
i=4
i=5

WHILE LOOP
Syntax While <condition> loop
Sequence of statements;
End loop;
Ex DECLARE
i number := 1;
BEGIN
While i <= 5 loop
dbms_output.put_line('i = ' || i);
i := i + 1;
end loop;
END;
Output i=1
i=2
i=3
i=4
i=5

Page 4 of 6
 FOR LOOP
Syntax For <loop_counter_variable> in low_bound..high_bound loop
Sequence of statements;
End loop;
Ex BEGIN BEGIN
For i in 1..5 loop For i in reverse 1..5 loop
dbms_output.put_line('i = ' dbms_output.put_line('i
|| i); = ' || i);
end loop; end loop;
END; END;
Output i=1 i=5
i=2 i=4
i=3 i=3
i=4 i=2
i=5 i=1

NULL STATEMENT
Usually when you write a statement in a program, you want it to do something.
There are cases, however, when you want to tell PL/SQL to do absolutely
nothing, and that is where the NULL comes.
The NULL statement deos nothing except pass control to the next executable
statement.
You can use NULL statement in the following situations.
1 Improving program readability.
Sometimes, it is helpful to avoid any ambiguity inherent in an IF statement
that doesn’t cover all possible cases. For example, when you write an IF
statement, you do not have to include an ELSE clause.
2 Nullifying a raised exception.
When you don’t want to write any special code to handle an exception, you
can use the NULL statement to make sure that a raised exception halts
execution of the current PL/SQL block but does not propagate any exceptions
to enclosing blocks.
3 Using null after a label.
In some cases, you can pair NULL with GOTO to avoid having to execute
additional statements. For example, I use a GOTO statement to quickly move
to the end of my program if the state of my data indicates that no further
processing is required. Because I do not have to do anything at the
termination of the program, I place a NULL statement after the label because
at least one executable statement is required there. Even though NULL deos
nothing, it is still an executable statement.
GOTO AND LABELS
Syntax:
Goto label;
Where label is a label defined in the PL/SQL block. Labels are enclosed in double
angle brackets. When a goto statement is evaluated, control immediately
passes to the statement identified by the label.
Page 5 of 6
 Ex Output
BEGIN i=1
For i in 1..5 loop i=2
dbms_output.put_line('i = ' || i=3
i); i=4
if i = 4 then
goto exit_loop;
end if;
end loop;
<<exit_loop>>
Null;
END;
RESTRICTIONS ON GOTO

1 It is illegal to branch into an inner block, loop.

2 At least one executable statement must follow.
3 It is illegal to branch into an if statement.
4 It is illegal to branch from one if statement to another if statement.
5 It is illegal to branch from exception block to the current block.

PRAGMAS

Pragmas are compiler directives. They serve as instructions to the PL/SQL

compiler. The compiler will act on the pragma during the compilation of the
block.

Syntax:
PRGAMA instruction_to_compiler.

PL/SQL offers several pragmas:

1 AUTONOMOUS_TRANSACTION
2 EXCEPTION_INIT
3 RESTRICT_REFERENCES
4 SERIALLY_REUSABLE

Page 6 of 6