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.
Ø If-then-else
Ø Case
ü Case with no else
ü Labeled case
ü Searched case
Ø Simple loop
Ø While loop
Ø For loop
Ø 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;
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
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
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
FOR LOOP
Syntax:
For <loop_counter_variable>
in low_bound..high_bound loop
Sequence of
statements;
End loop;
Ex1:
BEGIN
For i in 1..5 loop
dbms_output.put_line('i = ' || i);
end loop;
END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5
Ex2:
BEGIN
For i in reverse 1..5 loop
dbms_output.put_line('i = ' || i);
end loop;
END;
Output:
i = 5
i = 4
i = 3
i = 2
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.
Ø 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.
Ø 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.
Ø 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.
Ex:
BEGIN
For i in 1..5 loop
dbms_output.put_line('i = ' || i);
if i = 4 then
goto exit_loop;
end if;
end loop;
<<exit_loop>>
Null;
END;
Output:
i = 1
i = 2
i = 3
i = 4
RESTRICTIONS ON GOTO
Ø It is illegal to branch
into an inner block, loop.
Ø At least one executable
statement must follow.
Ø It is illegal to branch
into an if statement.
Ø It is illegal to branch
from one if statement to another if statement.
Ø It is illegal to branch
from exception block to the current block.
No comments:
Post a Comment