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