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.
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;
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
No comments:
Post a Comment