PLSQL LEARNING CLASS2

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:

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.

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