02- Writing Control Structures




As the name implies, PL/SQL supports programming language features like conditional statements, iterative statements.

The programming constructs are similar to how you use in programming languages like Java and C++.




IF Statement

Conditional Statements in PL/SQL

General Syntax of the IF statement in PL/SQL is:


1)
IF condition 
THEN 
statement 1; 
ELSE 
statement 2; 
END IF;


2)
IF condition 1 
THEN 
statement 1; 
statement 2; 
ELSIF condtion2 THEN 
statement 3; 
ELSE 
statement 4; 
END IF



Example
The code below will have as an output:  You are a child

set serveroutput on;
DECLARE
age number(4):=11;
BEGIN
if(age>11) then
dbms_output.put_line('You are grown boy/girl');
else
dbms_output.put_line('you are a child');
end if;
END;


NULL Value in if statement

Why will the code below display "I am a child"?

set serveroutput on;
DECLARE
age number(4);
BEGIN
if(age>11) then
dbms_output.put_line('I am a grown boy/girl');
else
dbms_output.put_line('I am a child');
end if;
END;

The age variable is not initialized ( is null) and each expression that contains a null value is evaluated as null. Null is considered to be FALSE.
Herewith the condition of the if statement (age>11) will be FALSE.



CASE statement

Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions.

General syntax is:

CASE selector
    WHEN 'value1' THEN S1;
    WHEN 'value2' THEN S2;
    WHEN 'value3' THEN S3;
    ...
    ELSE Sn;  -- default case
END CASE;


Example




DECLARE
   grade char(1) := 'A';
BEGIN
   CASE grade
      when 'A' then dbms_output.put_line('Excellent');
      when 'B' then dbms_output.put_line('Very good');
      when 'C' then dbms_output.put_line('Well done');
      when 'D' then dbms_output.put_line('You passed');
      when 'F' then dbms_output.put_line('Better try again');
      else dbms_output.put_line('No such grade');
   END CASE;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Excellent




The code above is equivalent to:

DECLARE
   grade char(1) := 'A';
BEGIN   
CASE
   WHEN grade = 'A' THEN dbms_output.put_line('Excellent');
   WHEN grade = 'B' THEN dbms_output.put_line('Very Good');
   WHEN grade = 'C' THEN dbms_output.put_line('Good');
   WHEN grade = 'D' THEN dbms_output.put_line('Fair');
   WHEN grade = 'F' THEN dbms_output.put_line('Poor');
   ELSE dbms_output.put_line('No such grade');
END CASE;
END;
/



CASE Expression

A case expression is an expression which returns one value based on sequencial tests of a boolean expression.

Example

set serveroutput on;
DECLARE
   grade char(1) := UPPER('&give_grade');
   gr_result varchar2(20);
BEGIN
gr_result:=case
          when grade = 'A' then 'Excellent'
          when grade = 'B' then 'Good'
          else 'No such grade'
          end;
          dbms_output.put_line(gr_result);

end;


The syntax difference between a CASE Expression and a CASE Statement is:

Case Expression ends with "END;"
Case Statement ends with "END CASE;"



Iterative Statements in PL/SQL

Iterative control Statements are used when we want to repeat the execution of one or more statements for specified number of times.
There are three types of loops in PL/SQL:

  1. Simple Loop
  2. While Loop
  3. For Loop

LOOP statement

A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates.
 An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

General Syntax to write a Simple Loop is:


LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;

These are the important steps to be followed while using Simple Loop.

1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without WHEN condition, the statements in the loop is executed only once.



WHILE Loops


A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. 
The condition is evaluated at the beginning of each iteration. 
The iteration continues until the condition becomes false.

The General Syntax to write a WHILE LOOP is:

WHILE <condition>
LOOP statements;
END LOOP;

Important steps to follow when executing a while loop: 
1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) EXIT WHEN statement and EXIT statements can be used in while loops but it's not done oftenly.




FOR Loops


A FOR LOOP is used to execute a set of statements for a predetermined number of times. 
Iteration occurs between the start and end integer values given. 
The counter is always incremented by 1. 
The loop exits when the counter reachs the value of the end integer.

The General Syntax to write a FOR LOOP is: 

FOR counter IN [REVERSE] val1..val2
LOOP statements;
END LOOP;

val1 - Start integer value.
val2 - End integer value.

Important steps to follow when executing a while loop: 
1) The counter variable is implicitly declared in the declaration section, so it's not necessary to declare it explicity.
2) The counter variable is incremented by 1 and does not need to be incremented explicitly.
3) EXIT WHEN statement and EXIT statements can be used in FOR loops but it's not done oftenly.

REVERSE option loops in the reversve that would be from val2..val1

Example 1
The code below will list numbers from 1 to 10 and will calculate and display the respective sum.

 Code Output
 set serveroutput on;
DECLARE
   counter number ;
   s number :=0;
BEGIN
  for counter IN 1..10 LOOP
  s:=s+counter;
  dbms_output.put_line(counter);
  END LOOP;
  dbms_output.put_line(s);
end;




 anonymous block completed
1
2
3
4
5
6
7
8
9
10
55


Example 2 [ Reverse] Loop
The code below will list numbers from 10 to 11 and will calculate and display the respective sum.

 Code Output
set serveroutput on;
DECLARE
   counter number ;
   s number :=0;
BEGIN
  for counter IN REVERSE 1..10 LOOP
  s:=s+counter;
  dbms_output.put_line(counter);
  END LOOP;
  dbms_output.put_line(s);
end;



 anonymous block completed
10
9
8
7
6
5
4
3
2
1
55



Same scenarion with while loop

set serveroutput on;
DECLARE
   counter number ;
   s number :=0;
BEGIN
counter:=1;
  WHILE (counter<=10) LOOP
  s:=s+counter;
  dbms_output.put_line(counter);
  counter:=counter+1;
  END LOOP;
  dbms_output.put_line(s);
end;


Nested Loops

It is possible to nest loops within each other.

For example the code below:

set serveroutput on;
DECLARE
   i number ;
   j number;
  
BEGIN
  for i IN  1..3 LOOP
    for j IN 1..2 LOOP
      dbms_output.put_line('(i,j)='||'('||i||','||j||')');
    END LOOP;
  END LOOP;
  
end;

will display 

anonymous block completed
(i,j)=(1,1)
(i,j)=(1,2)
(i,j)=(2,1)
(i,j)=(2,2)
(i,j)=(3,1)
(i,j)=(3,2)