Relational Operators

DECLARE
a number (2) := 21;
b number (2) := 10;
BEGIN
IF (a = b) then
dbms_output.put_line('Line 1 - a is equal to b');
ELSE
dbms_output.put_line('Line 1 - a is not equal to b');
END IF;
IF (a < b) then
dbms_output.put_line('Line 2 - a is less than b');
ELSE
dbms_output.put_line('Line 2 - a is not less than b');
END IF;

IF ( a > b ) THEN
dbms_output.put_line('Line 3 - a is greater than b');
ELSE
dbms_output.put_line('Line 3 - a is not greater than b');
END IF;
-- Lets change value of a and b
a := 5;
b := 20;
IF ( a <= b ) THEN
dbms_output.put_line('Line 4 - a is either equal or less
than b');
END IF;
IF ( b >= a ) THEN
dbms_output.put_line('Line 5 - b is either equal or greater
than a');
END IF;
IF ( a <> b ) THEN
dbms_output.put_line('Line 6 - a is not equal to b');
ELSE
dbms_output.put_line('Line 6 - a is equal to b');
END IF;
END;
/

Line 1 - a is not equal to b

Line 2 - a is not less than b
Line 3 - a is greater than b
Line 4 - a is either equal or less than b
Line 5 - b is either equal or greater than a
Line 6 - a is not equal to b

PL/SQL procedure successfully completed

LIKE Operator
This program tests the LIKE operator. Here, we will use a small procedure() to
show the functionality of the LIKE operator −
DECLARE
PROCEDURE compare (value varchar2, pattern varchar2 ) is
BEGIN
IF value LIKE pattern THEN
dbms_output.put_line ('True');
ELSE
dbms_output.put_line ('False');
END IF;
END;
BEGIN
compare('Zara Ali', 'Z%A_i');
compare('Nuha Ali', 'Z%A_i');
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
True
False

PL/SQL procedure successfully completed.

BETWEEN Operator
The following program shows the usage of the BETWEEN operator −
DECLARE
x number(2) := 10;
BEGIN
IF (x between 5 and 20) THEN
dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;

IF (x BETWEEN 5 AND 10) THEN

dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;

IF (x BETWEEN 11 AND 20) THEN

dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;
END;
/
When the above code is executed at the SQL prompt, it produces the following
result −
True
True
False

PL/SQL procedure successfully completed.

IN and IS NULL Operators
The following program shows the usage of IN and IS NULL operators −
DECLARE
letter varchar2(1) := '';
BEGIN
IF (letter in ('a', 'b', 'c')) THEN
dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;

IF (letter in ('m', 'n', 'o')) THEN

dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;

IF (letter is null) THEN

dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;
END;
/

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

PL/SQL procedure successfully completed.

Logical Operators Examples

Example
DECLARE
a boolean := true;
b boolean := false;
BEGIN
IF (a AND b) THEN
dbms_output.put_line('Line 1 - Condition is true');
END IF;
IF (a OR b) THEN
dbms_output.put_line('Line 2 - Condition is true');
END IF;
IF (NOT a) THEN
dbms_output.put_line('Line 3 - a is not true');
ELSE
dbms_output.put_line('Line 3 - a is true');
END IF;
IF (NOT b) THEN
dbms_output.put_line('Line 4 - b is not true');
ELSE
dbms_output.put_line('Line 4 - b is true');
END IF;
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
Line 2 - Condition is true
Line 3 - a is true
Line 4 - b is not true

PL/SQL procedure successfully completed.

Conditional Statements

It is the simplest form of the IF control statement, frequently used in decision-

making and changing the control flow of the program execution.
The IF statement associates a condition with a sequence of statements enclosed
by the keywords THEN and END IF. If the condition is TRUE, the statements get
executed, and if the condition is FALSE or NULL, then the IF statement does
nothing.

Syntax
Syntax for IF-THEN statement is −
IF condition THEN
S;
END IF;
Where condition is a Boolean or relational condition and S is a simple or compound
statement. Following is an example of the IF-THEN statement −
IF (a <= 20) THEN
c:= c+1;
END IF;

If the Boolean expression condition evaluates to true, then the block of code inside
the if statementwill be executed. If the Boolean expression evaluates to false, then
the first set of code after the end of the if statement (after the closing end if) will be
executed.

Flow Diagram

Example 1
Let us try an example that will help you understand the concept −
DECLARE
a number(2) := 10;
BEGIN
a:= 10;
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/
When the above code is executed at the SQL prompt, it produces the following
result −
a is less than 20
value of a is : 10

PL/SQL procedure successfully completed.

Example 2
Consider we have a table and few records in the table as we had created in PL/SQL
Variable Types
DECLARE
c_id [Link]%type := 1;
c_sal [Link]%type;
BEGIN
SELECT salary
INTO c_sal
FROM customers
WHERE id = c_id;
IF (c_sal <= 2000) THEN
UPDATE customers
SET salary = salary + 1000
WHERE id = c_id;
dbms_output.put_line ('Salary updated');
END IF;
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
Salary updated

PL/SQL procedure successfully completed.

Select * From Customer;

2. A sequence of IF-THEN statements can be followed by an optional sequence

of ELSE statements, which execute when the condition is FALSE.

Syntax
Syntax for the IF-THEN-ELSE statement is −
IF condition THEN
S1;
ELSE
 S2;
END IF;
Where, S1 and S2 are different sequence of statements. In the IF-THEN-ELSE
statements, when the test condition is TRUE, the statement S1 is executed
and S2 is skipped; when the test condition is FALSE, then S1 is bypassed and
statement S2 is executed. For example −
IF color = red THEN
dbms_output.put_line('You have chosen a red car')
ELSE
dbms_output.put_line('Please choose a color for your car');
END IF;

If the Boolean expression condition evaluates to true, then the if-then block of
code will be executed otherwise the else block of code will be executed.

Flow Diagram

Example
Let us try an example that will help you understand the concept −
DECLARE
a number(3) := 100;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
a is not less than 20
value of a is : 100

PL/SQL procedure successfully completed.

3. The IF-THEN-ELSIF statement allows you to choose between several

alternatives. An IF-THENstatement can be followed by an
optional ELSIF...ELSE statement. The ELSIF clause lets you add additional
conditions.
When using IF-THEN-ELSIF statements there are a few points to keep in mind.
 It's ELSIF, not ELSEIF.
 An IF-THEN statement can have zero or one ELSE's and it must come after
any ELSIF's.
 An IF-THEN statement can have zero to many ELSIF's and they must come
before the ELSE.
 Once an ELSIF succeeds, none of the remaining ELSIF's or ELSE's will be
tested.

Syntax
The syntax of an IF-THEN-ELSIF Statement in PL/SQL programming language is −
IF(boolean_expression 1)THEN
S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN
S2; -- Executes when the boolean expression 2 is true
ELSIF( boolean_expression 3) THEN
S3; -- Executes when the boolean expression 3 is true
ELSE
S4; -- executes when the none of the above condition is true
END IF;
Example
DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
dbms_output.put_line('Value of a is 20' );
ELSIF ( a = 30 ) THEN
dbms_output.put_line('Value of a is 30' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
 dbms_output.put_line('Exact value of a is: '|| a );
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
None of the values is matching
Exact value of a is: 100

PL/SQL procedure successfully completed.

4. 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. A selector is an expression, the value of
which is used to select one of several alternatives.

Syntax
The syntax for the case statement in PL/SQL is −
CASE selector
WHEN 'value1' THEN S1;
WHEN 'value2' THEN S2;
WHEN 'value3' THEN S3;
...
ELSE Sn; -- default case
END CASE;
Flow Diagram

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 the SQL prompt, it produces the following
result −
Excellent

PL/SQL procedure successfully completed.

Loop Condition
Basic loop structure encloses sequence of statements in between
the LOOP and END LOOP statements. With each iteration, the sequence of
statements is executed and then control resumes at the top of the loop.

Syntax
The syntax of a basic loop in PL/SQL programming language is −
LOOP
Sequence of statements;
END LOOP;
Here, the sequence of statement(s) may be a single statement or a block of
statements. An EXIT statement or an EXIT WHEN statement is required to break
the loop.

Example
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
10
20
30
40
50
After Exit x is: 60

PL/SQL procedure successfully completed.

You can use the EXIT WHEN statement instead of the EXIT statement −
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
exit WHEN x > 50;
END LOOP;
-- after exit, control resumes here
 dbms_output.put_line('After Exit x is: ' || x);
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
10
20
30
40
50
After Exit x is: 60

PL/SQL procedure successfully completed.

2. A WHILE LOOP statement in PL/SQL programming language repeatedly

executes a target statement as long as a given condition is true.

Syntax
WHILE condition LOOP
sequence_of_statements
END LOOP;
Example
DECLARE
a number(2) := 10;
BEGIN
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
value of a: 10
value of a: 11
value of a: 12
value of a: 13
value of a: 14
value of a: 15
value of a: 16
value of a: 17
value of a: 18
value of a: 19

PL/SQL procedure successfully completed.

3. A FOR LOOP is a repetition control structure that allows you to efficiently write a
loop that needs to execute a specific number of times.

Syntax
FOR counter IN initial_value .. final_value LOOP
sequence_of_statements;
END LOOP;
Following is the flow of control in a For Loop −
 The initial step is executed first, and only once. This step allows you to
declare and initialize any loop control variables.
 Next, the condition, i.e., initial_value .. final_value is evaluated. If it is TRUE,
the body of the loop is executed. If it is FALSE, the body of the loop does not
execute and the flow of control jumps to the next statement just after the for
loop.
 After the body of the for loop executes, the value of the counter variable is
increased or decreased.
 The condition is now evaluated again. If it is TRUE, the loop executes and the
process repeats itself (body of loop, then increment step, and then again
condition). After the condition becomes FALSE, the FOR-LOOP terminates.
Following are some special characteristics of PL/SQL for loop −
 The initial_value and final_value of the loop variable or counter can be
literals, variables, or expressions but must evaluate to numbers. Otherwise,
PL/SQL raises the predefined exception VALUE_ERROR.
 The initial_value need not be 1; however, the loop counter increment (or
decrement) must be 1.
 PL/SQL allows the determination of the loop range dynamically at run time.

Example
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
value of a: 10
value of a: 11
value of a: 12
value of a: 13
value of a: 14
value of a: 15
value of a: 16
value of a: 17
value of a: 18
value of a: 19
value of a: 20

PL/SQL procedure successfully completed.

Reverse FOR LOOP Statement
By default, iteration proceeds from the initial value to the final value, generally
upward from the lower bound to the higher bound. You can reverse this order by
using the REVERSE keyword. In such case, iteration proceeds the other way. After
each iteration, the loop counter is decremented.
However, you must write the range bounds in ascending (not descending) order.
The following program illustrates this −
DECLARE
a number(2) ;
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
value of a: 20
value of a: 19
value of a: 18
value of a: 17
value of a: 16
value of a: 15
value of a: 14
value of a: 13
value of a: 12
value of a: 11
value of a: 10

PL/SQL procedure successfully completed.

4. PL/SQL allows using one loop inside another loop. Following section shows a few
examples to illustrate the concept.
The syntax for a nested basic LOOP statement in PL/SQL is as follows −
LOOP
Sequence of statements1
LOOP
Sequence of statements2
END LOOP;
END LOOP;
The syntax for a nested FOR LOOP statement in PL/SQL is as follows −
FOR counter1 IN initial_value1 .. final_value1 LOOP
sequence_of_statements1
 FOR counter2 IN initial_value2 .. final_value2 LOOP
sequence_of_statements2
END LOOP;
END LOOP;
The syntax for a nested WHILE LOOP statement in Pascal is as follows −
WHILE condition1 LOOP
sequence_of_statements1
WHILE condition2 LOOP
sequence_of_statements2
END LOOP;
END LOOP;
Example
The following program uses a nested basic loop to find the prime numbers from 2 to
100 −
DECLARE
i number(3);
j number(3);
BEGIN
i := 2;
LOOP
j:= 2;
LOOP
exit WHEN ((mod(i, j) = 0) or (j = i));
j := j +1;
END LOOP;
IF (j = i ) THEN
dbms_output.put_line(i || ' is prime');
END IF;
i := i + 1;
exit WHEN i = 50;
END LOOP;
END;
/

When the above code is executed at the SQL prompt, it produces the following
result −
2 is prime
3 is prime
5 is prime
7 is prime
11 is prime
13 is prime
17 is prime
19 is prime
23 is prime
29 is prime
31 is prime
37 is prime
41 is prime
43 is prime
47 is prime

PL/SQL procedure successfully completed.