PL/SQL CONTROL STRUCTURES
PL/SQL has a
variety of control structures that allow you to control the behaviour 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:
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.
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:
Ø AUTONOMOUS_TRANSACTION
Ø EXCEPTION_INIT
Ø RESTRICT_REFERENCES
Ø SERIALLY_REUSABLE
No comments:
Post a Comment