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:

Example

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

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.

Example 2 [ Reverse] Loop

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

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)