DBMS Control Structures

Control Structures

PL/SQL Control Structures are used to control flow of execution.  PL/SQL provides different kinds of statements to provide such type of procedural capabilities.These statements are almost same as that of provided by other languages.
 The flow of control statements can be classified into the following categories: 

  • Conditional Control
  • Iterative Control
  • Sequential Control

Conditional Control :

PL/SQL allows the use of an IF statement to control the execution of a block of code. 
 In PL/SQL, the IF -THEN - ELSIF - ELSE - END IF construct in code blocks allow specifying certain conditions under which a specific block of code should be executed. 

Syntax:

IF < Condition > THEN 
     < Action > 
ELSIF <Condition> THEN
     < Action >
ELSE < Action > 
END IF; 

Example:

create file named "condi.sql"
DECLARE 
        a Number := 30;       b Number; 
BEGIN 
        IF a > 40 THEN 
          b := a - 40; 
          DBMS_OUTPUT.PUT_LINE('b=' || b); 
       elseif a = 30 then 
          b := a + 40; 
          DBMS_OUTPUT.PUT_LINE('b=' || b); 
       ELSE 
          b := 0; 
          DBMS_OUTPUT.PUT_LINE('b=' || b); 
       END IF; 
END; 
/

Output:

Run SQL Command Line
SQL>set serveroutput on

SQL>start d://condi.sql
b=70

PL/SQL successfully completed.

Iterative Control :

 Iterative control indicates the ability to repeat or skip sections of a code block. 
 A loop marks a sequence of statements that has to be repeated. The keyword loop has to be placed before the first statement in the sequence of statements to be repeated, while the keyword end loop is placed immediately after the last statement in the sequence. 
Once a loop begins to execute, it will go on forever. Hence a conditional statement that controls the number of times a loop is executed always accompanies loops.
 PL/SQL supports the following structures for iterative control:  

Simple loop :

In simple loop, the key word loop should be placed before the first statement in the sequence and the keyword end loop should be written at the end of the sequence to end the loop.

Syntax:

Loop 
   < Sequence of statements >
 End loop; 

Example:

create file named it.sql
DECLARE 
    i number := 0; 
BEGIN
     LOOP 
    dbms_output.put_line ('i = '||i);
    i:=i+1; 
               EXIT WHEN i>=11; 
     END LOOP; 
      
END; 
/

Output:

Run SQL Command Line
SQL>set serveroutput on

SQL>start d://it.sql
i = 0
i = 1
i = 2
i = 3
i = 4
i = 5
i = 6
i = 7
i = 8
i = 9
i = 10
PL/SQL successfully completed.
WHILE loop

The while loop executes commands in its body as long as the condtion remains true

Syntax :


WHILE < condition >
LOOP
      < Action >
END LOOP

Example :

find reverse of given number using while loop

DECLARE
    num Number(3) :=123;
    ans Number(3) :=0;
    i Number(3) :=0;
 BEGIN
    WHILE num != 0
     LOOP
           i:=mod(num,10);
           ans:=(ans * 10 ) + i;
           num:=floor(num/10);
     END LOOP;
    dbms_output.put_line('reverse of given number is: ' || ans);
END;
/

Output :

Run SQL Command Line
SQL>set serveroutput on

SQL>start d://rev.sql

reverse of given number is: 321
PL/SQL successfully completed.
The FOR Loop

The  FOR  loop can be used when the number of iterations to be executed are known.

Syntax :

FOR variable IN [REVERSE] start..end 
 LOOP 
   < Action > 
 END LOOP; 

The variable in the For Loop need not be declared. Also the increment value cannot be specified. The For Loop variable is always incremented by 1.

Example :

DECLARE
     i number ;
 BEGIN
    FOR i IN 1 .. 10
     LOOP
         dbms_output.put_line ('i = '||i);   
     END LOOP;
 END; 
 /

Output :

Run SQL Command Line
SQL>set serveroutput on

SQL>start d://it.sql
i = 1
i = 2
i = 3
i = 4
i = 5
i = 6
i = 7
i = 8
i = 9
i = 10
PL/SQL successfully completed.

Sequential Control :

The GOTO Statement

The GOTO statement changes the flow of control within a PL/SQL block. This statement allows execution of a section of code, which is not in the normal flow of control. The entry point into such a block of code is marked using the tags «userdefined name». The GOTO statement can then make use of this user-defined name to jump into that block of code for execution.

Syntax :

GOTO jump;
....
<<jump>>

Example :

DECLARE
   
 BEGIN
     dbms_output.put_line ('code starts');
     dbms_output.put_line ('before GOTO statement');
    GOTO down;
     dbms_output.put_line ('statement will not get executed..');
     <<down>>
     dbms_output.put_line ('flow of execution jumped here.');   
 END; 
 /

Output :

Run SQL Command Line
SQL>set serveroutput on

SQL>start d://a.sql
code starts
before gotostatements
flow of execution jumped here.

PL/SQL successfully completed.

Share This Page to Download:

Share to Download

Share This Page on:


Ask Question