PL SQL Operators And Control Statements Tutorial

Learn about different PL SQL Operators and Control Statements like if-then-else, for loop, while loop, etc. with sample code examples:

In the PL/SQL Commands tutorial of the PL/SQL series, we learned about PL SQL INSERT, UPDATE, DELETE and SELECT commands with programming examples.

In this article, we will discuss the different operators that PL/SQL supports like relational, logical, arithmetic, and comparison operators. We shall also investigate the control statements that are used extensively in PL/SQL.

PL/SQL condition and iterative structures have been explained in detail with examples here in this tutorial.

PL SQL Operators

A PL SQL operator directs the compiler to do arithmetic and logical operations with the help of a symbol.

By default, PL/SQL has the following operator types:

• Arithmetic
• Comparison
• Logical
• String
• Relational

#1) Arithmetic Operators

Code implementation with arithmetic operators:

declare 
    a number(5):= 2; 
    b number(5):= 1;
    res number(10); 
    sub number(10);
    mul number(10);
    div number(10);
    exp number(10);

begin 

    -- adding a and b and storing in res 
    res:= a+b;

    -- subtracting b from a and storing in sub
    sub:= a-b;

     -- multiplying a and b and storing in mul
     mul:= a*b;

     -- dividing a and b and storing in div
     div:= a/b;

     -- exponential operation and storing in exp
     exp:= a**b;

     dbms_output.put_line('Sum value is '||res); 
     dbms_output.put_line('Subtraction value is '||sub); 
     dbms_output.put_line('Multiplication value is '||mul); 
     dbms_output.put_line('Division value is '||div);
     dbms_output.put_line('Exponential value is '||exp); 
end; 
/

The output of the above code should be:

#2) Relational Operators

These operators perform the comparison and return values in Boolean.

Code implementation with relational operators:

DECLARE
     a NUMBER := 5;
     b NUMBER := 12;
BEGIN 
     IF a != b THEN 
        DBMS_OUTPUT.PUT_LINE('a is not equal to b'); 
     ELSIF a = b THEN 
         DBMS_OUTPUT.PUT_LINE('a is equal to b');
     ELSE
          DBMS_OUTPUT.PUT_LINE

              ('Not sure of the input');
    END IF;
END;
/

The output of the above code should be:

#3) Comparison Operators

This operator gives the output as either true, false, or null value based on the result of comparing one statement with the other.

Code implementation with comparison operator:

DECLARE
     i INTEGER; -- NULL value is set by default
BEGIN
     i := i*5; -- i is still NULL since NULL * 5 is NULL 
    IF i IS NULL THEN
         DBMS_OUTPUT.PUT_LINE('i is NULL.');
    END IF;
END;
/

The output of the above code should be:

#4) Logical Operators

PL/SQL supports more than one logical operator. They act on operands which are Boolean in nature and yield Boolean results as well.

Code implementation with logical operators:

 
declare
    n1 number := 5;
    n2 number := 10;
    n3 number := 15;
begin 
    if (n1 < n2 and n3 > n2 or n1 = 5)
        then dbms_output.put_line ('The condition is true');
     else dbms_output.put_line ('The condition is false');
    end if;
end;
/

The output of the above code should be:

Operator Precedence

PL SQL operator precedence is set to define how an operation involving a single or multiple operators or more than one operand shall be done. The operators with higher precedence are calculated first than the others while the operators with the same precedence are manipulated in no particular sequence.

The below table lists down the operators from the high to low precedence.

Control Statements In PL/SQL

The control structures in PL/SQL consists of the program structures like the iteration, sequence, and selection. The form of selection verifies a condition, then based on the results of either true or false, processes the next block of code in order.

The form of iteration runs a block of code or statements repeatedly until the condition is valid. Finally, the form of sequence processes the statements one by one in the order they are present.

Condition Statement In PL SQL

In decision-making scenarios, the condition statements like IF-THEN, IF-THEN-ELSE, IF-THEN-ELSEIF, and CASE are used.

#1) IF-THEN: This is one of the basic forms of IF statements. It contains a block of code with keywords ENDIF and THEN. If the result of a condition is true, the next block of code gets executed else not.

Structure of IF-THEN:

IF condition THEN
     Block of code
END IF;

We can also keep a short IF –THEN block on a single line like this.

IF big > small THEN temp: = big; END IF;

Code implementation with IF-THEN:

DECLARE 
     i number(10,2) := 15; 
BEGIN 
    -- checking the condition
     IF( i > 2 ) THEN 
          -- true condition 
          dbms_output.put_line('i is greater than 1 ' ); 
     END IF; 
     dbms_output.put_line('the numeric value of i is '|| i); 
END; 
/

The output of the above code should be:

#2) IF-THEN-ELSE: This is the next form of IF statement which has the ELSE keyword then the next block of code. This block of ELSE statements is optional and it executes when the condition is FALSE.

Structure of IF-THEN-ELSE:

IF condition THEN
     Block of code 1
ELSE
     Block of code 2
END IF;

Code implementation with IF-THEN-ELSE:

DECLARE 
     num number(10,2) := 150; 
BEGIN 
     -- checking the condition 
     IF( num > 100 ) THEN
           -- true condition 
          dbms_output.put_line('num is greater than 100 '); 
     ELSE 
          dbms_output.put_line('num is lesser than 100 '); 
     END IF; 
         dbms_output.put_line('value of number is : ' || num); 
END; 
/

The output of the above code should be:

#3) IF-THEN-ELSIF: This conditional statement gives the option of selecting among the alternatives. ELSE…ELSE block of code can optionally come after the IF-THEN statement. Thus it is used while dealing with multiple mutually exclusive choices.

An IF-THEN block of code can have none or multiple ELSIFs provided that they appear before the ELSE statement. Once we encounter a true condition in one ELSIF block, other ELSE or ELSIFs will be ignored.

Structure of IF-THEN-ELSIF:

IF condition 1 THEN
      Block of code 1
ELSIF condition 2 THEN
      Block of code 2
ELSE
      Block of code 3
END IF;

Code implementation with IF-THEN-ELSIF:

DECLARE 
      num number(8,2) := 20; 
BEGIN 
     IF ( num < 15 ) THEN 
          dbms_output.put_line('The num is greater than 15' ); 
     ELSIF ( num = 15 ) THEN 
          dbms_output.put_line('The num is 15' ); 
     ELSIF ( num <= 20 ) THEN 
          dbms_output.put_line('The num is less than equal to 20');
     ELSE 
          dbms_output.put_line('The num is invalid '); 
     END IF; 
          dbms_output.put_line('The num is : '|| num); 
END; 
/

The output of the above code is:

#4) CASE STATEMENT: This conditional statement will select one block of code from the choices. However like IF statements, CASE doesn’t use Boolean input, but an expression that helps to select one of the options.

The last choice of the CASE statement is the default one. CASE STATEMENT is more efficient and less lengthy compared to IF statements.

Structure of CASE STATEMENT:

CASE selector
    WHEN 'condition 1' THEN block of code 1;
    WHEN 'condition2' THEN block of code 2;
    WHEN 'condition 3' THEN block of code 3;
    ...
ELSE default case
END CASE;

Code implementation with CASE STATEMENT:

DECLARE 
      age number(5) := 15; 
BEGIN 
     CASE age 
      when '1' then dbms_output.put_line('Infant'); 
      when '5' then dbms_output.put_line('In school'); 
      when '15' then dbms_output.put_line('High school'); 
     else dbms_output.put_line('Qualified for class 10 '); 
   END CASE; 
END; 
/

The output of the above code is:

#5) SEARCHED CASE Statement: This conditional statement is devoid of any selector and the WHEN block of code only gives Boolean results and not any value of other data types.

Structure of SEARCHED CASE Statement:

CASE
WHEN 'condition 1' THEN block of code 1; 
WHEN 'condition 2' THEN block of code 2;
WHEN 'condition 3' THEN block of code 3;
... 
ELSE default case 
END CASE;

Code implementation with SEARCHED CASE Statement:

DECLARE 
     model varchar(10) := '4'; 
BEGIN 
     case 
        when model = '1' then dbms_output.put_line('FIAT'); 
        when model = '2' then dbms_output.put_line('TOYOTA'); 
        when model = '3' then dbms_output.put_line('MARUTI'); 
        when model = '4' then dbms_output.put_line('HYUNDAI'); 
        else dbms_output.put_line('Premium MODEL'); 
     end case; 
END; 
/

The output of the above code should be:

#6) NESTED IF-THEN-ELSE: This conditional statement gives the provision of using ELSE IF or IF block within another IF or ELSE IF.

Structure of NESTED IF-THEN-ELSE:

IF condition 1 THEN
     Block of code 1
IF condition 2 THEN
     Block of code 2
END IF; 
ELSE
     Block of code 3 when condition 1 is false
END IF;

Code implementation with NESTED IF-THEN-ELSE:

DECLARE 
    i number(10,2) := 65; 
    j number(5) := 10; 
BEGIN 
    -- checking the condition 
    IF( i > 50 ) THEN 
        -- if true move to the next IF statement 
        IF( j < 15 ) THEN 
             dbms_output.put_line('In the nested if block' ); 
        END IF; 
     END IF; 
dbms_output.put_line('The value of first number is : ' || i ); 
dbms_output.put_line('The value of second number is : ' || j ); 
END; 
/

The output of the above code should be:

Iterative Statement In PL SQL

While implementing logic in PL/SQL, we may require to process a block of code repeatedly several times. This is achieved with the help of LOOP statements. These are of three types, FOR-LOOP, WHILE-LOOP, and LOOP.

#1) PL SQL LOOPS

A LOOP has a basic structure as below:

LOOP
   Block of code
END LOOP;

After each iteration, the program flow points back to the top of the loop until a certain condition is satisfied. In case, we want to come out of the loop because of a situation, in that case, an EXIT statement can be used.

#2) PL SQL WHILE LOOP

This loop will run a block of code multiple times until the condition is valid. At the beginning of each iteration, the loop condition is checked. If it is satisfied, the block of code is executed.

If the condition is null or not satisfied, the loop is avoided and the next block of code is executed.

Structure of WHILE LOOP:

WHILE condition LOOP 
      Block of code
END LOOP; 

Code implementation with WHILE LOOP:

DECLARE 
     num number(10,1) := 3; 
BEGIN 
WHILE num < 4 LOOP 
     dbms_output.put_line('The value of number: ' || num); 
     num := num + 1; 
END LOOP; 
END; 
/

The output of the above code should be:

#3) PL SQL FOR LOOP

This type of loop allows the execution of a block of code to a particular number of times. The beginning step is executed once. This is primarily used for initialization and declaration of the loop variables. Then the boundary values (starting and ending range) are checked. If the condition is satisfied, the block of code inside the LOOP will get executed. If not, the program flow moves to the following block of code after the loop.

After one iteration, the loop counter increases or decreases by one. The whole process continues until the loop condition is satisfied. As the condition becomes FALSE, the loop ends.

In PL/SQL, the starting and ending range of the loop can be changed at the run time. The counter for the loop should be an integer, number, and so on (which can be numerically increased or decreased). The starting value of the counter variable can be any number.

Structure of FOR LOOP:

FOR counter IN start_value .. end_value LOOP 
    Block of code 1
END LOOP;

Code implementation of FOR LOOP:

DECLARE 
    num number(10,1);
BEGIN 
    FOR num in 1.. 5 LOOP
         dbms_output.put_line('The num is:' || num); 
    END LOOP; 
END; 
/

The output of the above code should be:

#4) NESTED LOOP

This type of statement allows one loop over the other. The external and internal loop structure can be of the same or different types. After each traversal, both the loops need to run successfully.

Structure of Nested Loop:

LOOP (outer)
     Block of code 1 
     LOOP (inner)
         Block of code 2
     END LOOP; 
END LOOP;

Code implementation with NESTED LOOP:

BEGIN
    FOR i IN 1..2 LOOP

       FOR j IN 1..2 LOOP
         DBMS_OUTPUT.PUT_LINE('The value of i is ' || i);
         DBMS_OUTPUT.PUT_LINE('The value of j is ' || j); 
       END LOOP;
     END LOOP;
END;
/

The output of the above code should be:

Loop Labels In PL/SQL

In PL/SQL, we have the provision of labeling the loops which increases the readability and meaning of the code. The label is represented by double angle brackets (<< and >>) and are mentioned at the top of the LOOP statement. The label can be placed at the end of the LOOP as well.

The label can also be described for EXIT statements while coming out of the loop.

Structure of LABELS:

<<Label name>>
LOOP
     Block of Code
END LOOP;

Code implementation with LOOP LABEL:

 
BEGIN
   <<OUTSIDE LOOP>>
   FOR i IN 1..2 LOOP 
        <<INSIDE LOOP>> 
        FOR j IN 1..2 LOOP
            DBMS_OUTPUT.PUT_LINE('The value of i is ' || i);
            DBMS_OUTPUT.PUT_LINE('The value of j is ' || j); 
        END LOOP;
    END LOOP; 
END;
/

Sequence Control Statements

The control statement is used to modify the flow of execution of the program.

#1) EXIT

An EXIT statement breaks a loop explicitly. When EXIT is found in the loop, the loop breaks and program flow moves to the next block of code. In conditions where we are using nested loops, the EXIT statement will first terminate the inner loop and then execute the block of code that appears immediately after it.

Code implementation with EXIT:

DECLARE 
     num number(10,1) := 15; 

BEGIN 
     -- loop condition 
     WHILE num < 20 LOOP 
         dbms_output.put_line ('The num is: ' || num); 
         num := num + 1; 
         IF mod(num, 2) = 0 THEN 
           -- exit from loop
           EXIT; 
         END IF; 
      END LOOP; 
END; 
/

The output of the above code should be:

#2) CONTINUE

This control statement allows the loop to come out and move to the next iteration to take place.

Code implementation with CONTINUE:

DECLARE 
     num number(10,1) := 5; 
BEGIN 
     -- while loop execution 
     WHILE num < 9 LOOP 
         dbms_output.put_line ('The number is : ' || num); 
         num := num + 1; 
         IF mod(num,2) = 0 THEN 
             num := num + 1; 
             CONTINUE; 
         END IF; 
     END LOOP; 
END;
/

The output of the above code should be:

#3) GOTO

The GOTO statement moves the control to a unique labeled block of code unconditionally. It is not a good programming technique to use GOTO statements because it reduces readability and traceability of code.

Structure of GOTO:

GOTO 60;
..
..
<<60>>
Block of code 1;

Coding implementation with the GOTO statement:

BEGIN
   GOTO message_two;
      <<message_one>>
      DBMS_OUTPUT.PUT_LINE( 'PLSQL Tutorial' );
   GOTO message_third;
     <<message_two>>
     DBMS_OUTPUT.PUT_LINE( 'Software Test Help!' );
   GOTO message_one;
     <<message_third>>
     DBMS_OUTPUT.PUT_LINE( 'PLSQL Control Statement' );
END;

The output of the above code should be:

Frequently Asked Questions

Q #1) What is the control statement in PL/SQL?

Answer: The control statements in PL/SQL are of the three types – loop statements, sequential statements, and control statements. The loop statement consists of WHILE LOOP, FOR LOOP, and basic LOOP.

Q #2) How do you check for equality in PL/SQL?

Answer: In PL/SQL, we have the equality operator which checks for equality.

For example,

SELECT NAME FROM EMPLOYEE WHERE AGE = 50;

Q #3) How do you stop an infinite loop in PL/SQL?

Answer: We can stop an infinite loop in PL/SQL with the help of EXIT statements. We need to keep the EXIT statements within the loop and not outside the loop.

Q #4) What is the purpose of the conditional control structure in PL/SQL?

Answer: The conditional control structure gives the option to control the flow of the program depending on some criteria. Thus the code in the program does not run in sequence. A block of code gets executed based on the condition.

Q #5) What are the 6 relational operators?

Answer: The relational operators of PL/SQL are <=, >=, <, >, !=, ==.

Q #6) What are the different types of operators?

Answer: The different types of operators in PL/SQL are listed below:

• Bitwise Operators
• Assignment Operators
• Arithmetic Operators
• Relational Operators
• Logical Operators

Conclusion

We have covered a large portion of PL/SQL in this tutorial dealing with topics like the multiple operators and their importance with code examples.

Also, we have explored the PL SQL control statements in detail. The control statements consist of the program structures like the iteration, condition, and sequence.

In the next tutorial, we shall discuss the PL SQL procedures, functions, cursors, and many more related topics.

<<PREV Tutorial | NEXT Tutorial>>