PL/SQL Control statements

PL/SQL supports the conditional statements and iterative statements like other programming languages such as C++, java etc.

PL/SQL IF Statements

1. IF-THEN Statement

Syntax

IF condition
THEN
Statement;
END IF;


This syntax is used when user needs to execute statements when condition is true.

2. IF-THEN-ELSE Statement

Syntax

IF condition
THEN
[Statements to execute when condition is TRUE]
ELSE
[Statements to execute when condition is FALSE]
END IF;


This syntax is used to execute one set of statements when condition is TRUE or different set of statements when condition is FALSE.

3. IF-THEN-ELSIF statement

Syntax

IF Condition1
THEN
Statements to execute when condition1 is TRUE
ELSIF condition2
THEN
Statements to execute when condition2 is TRUE
END IF;


This syntax is used to execute one set of statements when condition1 is TRUE or a different set of statements when condition is FALSE.

4. IF-THEN-ELS-IF-ELSE Statement

Syntax

IF condition1
THEN
Statements to execute when condition1 is TRUE
ELSIF condition2
THEN
Statements to execute when condition2 is TRUE
ELSE
Statements to execute when both condition1 and condition2 are FALSE
END IF;


This syntax is used to execute one set of statements if condition1 is TRUE, a different set of statements when condition2 is TRUE or a third set of statements when both condition1 and condition2 are false.

Lets take an example to understand the IF-THEN statement.

Example

DECLARE  
a number(3) := 200;  
BEGIN  
-- check the boolean condition using if statement   
IF( a < 10 ) THEN  
-- if condition is true then print the following    
dbms_output.put_line('a is less than 10 ' );  
ELSE  
dbms_output.put_line('a is not less than 10 ' );  
END IF;  
dbms_output.put_line('value of a is : ' || a);  
END;


Output:
a is not less than 10
value of a is : 200

PL/SQL Case statement

The PL/SQL CASE Statement provides facility to execute a sequence of statements based on a selector. A selector may be variable, function or an expression.

Syntax

CASE [expression]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…....................
WHEN condition_n THEN result_n
ELSE result
END;


Example

DECLARE  
   grade char(1) := 'C';  
BEGIN  
   CASE grade  
      when 'A' then dbms_output.put_line('Distinction');  
      when 'B' then dbms_output.put_line('First class');  
      when 'C' then dbms_output.put_line('Second class');  
      when 'D' then dbms_output.put_line('Pass class');    
      else dbms_output.put_line('Failed');  
   END CASE;  
END;  
/


Output:
Second class

PL/SQL Loop

  • Loops are iterative control statements.
  • They are used to repeat execution of one or more statements for defined number of times.

Syntax

LOOP
Sequence of statements;
END LOOP;


The four types of loops are:

1. PL/SQL Exit Loop

  • It is used a set of statements is executed at least once before termination of loop.
  • There should be an EXIT condition in the loop, otherwise the loop will get into an infinite number of iterations.

Syntax

LOOP   
Statements;   
EXIT;   
[or EXIT WHEN condition;]
END LOOP;


Lets take an example to understand Exit loop.

Example

DECLARE  
i NUMBER := 1;  
BEGIN  
LOOP  
EXIT WHEN i>5;  
dbms_output.put_line(i);  
i := i+1;  
END LOOP;  
END;


Output:
1
2
3
4
5

2. PL/SQL WHILE Loop

  • It is used when a set of statements should be executed as long as condition is true.
  • The condition is decided at the beginning of each iteration and continues until condition becomes false.

Syntax

WHILE [condition]
LOOP Statements;
END LOOP;


Example

DECLARE  
i INTEGER := 1;  
BEGIN  
WHILE i <= 5 LOOP  
dbms_output.put_line(i);  
i := i+1;  
END LOOP;  
END;


Output:
1
2
3
4
5

3. PL/SQL FOR Loop

  • It is used to execute a set of statements for a fixed number of times.
  • It is iterated between the start and end integer values.

Syntax

FOR counter IN initial_value .. final_value LOOP  
LOOP statements;   
END LOOP;


Example

BEGIN  
FOR k IN 1..5 LOOP  
dbms_output.put_line(k)   
END LOOP;  
END;


Output:
1
2
3
4
5

4. PL/SQL GOTO Statement

In PL/SQL, GOTO statement makes you able to get an unconditional jump from the GOTO to a specific executable statement label in the same subprogram of the PL/SQL block.

Syntax

I) GOTO label_name;

II) GOTO label_name;
   …....
   …....
   <<label_name>>
   Statement;


In the above syntax, the label declaration contains the label_name is encapsulated within the << >> symbol and it should be followed by at least one statement to execute.

Lets take an example to understand how to use GOTO statement.

Example

DECLARE  
   a number(2) := 50;  
BEGIN  
   <<loopstart>>  
   -- while loop execution   
   WHILE a < 60LOOP  
      dbms_output.put_line ('value of a: ' || a);  
      a := a + 1;  
      IF a = 55 THEN  
         a := a + 1;  
         GOTO loopstart;  
      END IF;  
   END LOOP;  
END;  
/


Output:
value of a: 50
value of a: 51
value of a: 52
value of a: 53
value of a: 54
value of a: 56
value of a: 57
value of a: 58
value of a: 59
▲