PL-SQL control statements and loops

Control Statements and Loops

Used to control PL/SQL logic with the conditional structure, with loops and with unconditional branching.

A Loop is nothing but executing the same block of code more than one time. PL/SQL supports the Loops, various types of loops in PL/SQL are as shown in this page.

IF..THEN Statement

is used to check certain condition, if the condition is TRUE then execute the THEN set of statements, otherwise execute ELSE set of statements.

IF-THEN ELSE
DECLARE
   var_num1 number(4);
   var_num2 number(4);
BEGIN
   var_num1 := 10;
   var_num2 := 20;
   IF var_num1 > var_num2 THEN
       dbms_output.put_line('The largest number is ' || to_char(var_num1));
   ELSE
        dbms_output.put_line('The largest number is ' || to_char(var_num2));
    END IF;
END;

DECLARE
    var_checkno number(4);
BEGIN
    var_checkno := &tocheck;
    IF mod(var_checkno,2) = 0 THEN
        dbms_output.put_line('Even number');
    ELSE
        dbms_output.put_line('Odd number');
    END IF;
END;

In the following PL/SQL block lets you enter 3 numbers and finds the largest one. In this you can see IF..THEN with in another IF..THEN.

DECLARE
     first_num number(3);
     sec_num number(3);
     third_num number(3);
BEGIN
     first_num := &number1;
     sec_num := &number2;
     third_num := &number3;
     IF first_num > sec_num THEN
         IF first_num > third_num THEN
             dbms_output.put_line('First Number ' || to_char(first_num) || ' is greater of all entered numbers');
         ELSE
             dbms_output.put_line('Third Number ' || to_char(third_num) || ' is greater of all entered numbers');
         END IF;
     ELSE
         IF sec_num > third_num THEN
             dbms_output.put_line('Second Number ' || to_char(sec_num) || ' is greater of all entered numbers');
         ELSE
             dbms_output.put_line('Third Number ' || to_char(third_num) || ' is greater of all entered numbers');
         END IF;
     END IF;
END;

IF-THEN-ELSIF

Enter customer number if the total number of orders < 1000 then OK, between 1000 and 2000 then GOOD other wise TOP CUSTOMER.

DECLARE
   var_custno CUTOMER.CUSTNO%TYPE;
   var_orders number(10);
BEGIN
   var_custno := &CustomerNo;
   Select count(order_id) into var_orders From orders where customer_no = var_custno;
   IF var_orders < 1000 THEN
       dbms_output.put_line(to_char(var_custno) || ' is a OK customer');
   ELSIF var_orders between 1000 and 2000 THEN
       dbms_output.put_line(to_char(var_custno) || ' is a GOOD customer');
   ELSE
       dbms_output.put_line(to_char(var_custno) || ' is a TOP customer');
   END IF;
END;

Unconditional Loop

What's an unconditional loop? which enters into the loop first then check the condition to get out of the loop, where as conditional loop checks the condition, based on the result it will decide whether to go into the loop or bypass the whole loop and continue to the next statement in the block.

DECLARE
      var_running number(4);
BEGIN
      var_running := 1;
      Loop
              var_running := var_running + 1;
             dbms_output.put_line(' The current number is ' || to_char( var_running ) );
              If var_running > 101 then
                   Exit;
              End If;
      End Loop
END;

While Loop

The syntax for While loop is

WHILE condition LOOP
        --pl/sql statements
END LOOP

For Loop
If you the know the number of times you are going to execute the code then we can use For loop in PL/SQL.
The syntax for For Loop is

For var in starting_no..ending_no Loop
      -- write the code to execute so many times
End Loop

Example
To Display the even numbers between 1 and 200 using For Loop in a PL/SQL block.

DECLARE
       var_runningvalue number(3);
BEGIN
       dbms_output.put_line('Even numbers between 1 and 200');
       dbms_output.put_line('-----------------------------------');
        For var_runningvalue in 1..200 Loop
              If mod(var_running,2) = 0 then
                       dbms_output.put_line(var_running)
              End If;
        End Loop;
END;

GOTO Statement

In a block we can skip some of the statements and jump to a execute position using GOTO statement. Declaration of GOTO statement is GOTO Lable_Name.

Example

DECLARE
        var_empno employee.employeeno%type;
        var_empname employee.employeename%type;
        var_empstate employee.state_code%type;
        var_salary number(12,2);
BEGIN
        Select stat_code,salary into var_empstate, var_salary 
        From employee Where employeeno = var_empno;
        IF var_empstate = 'TX' THEN
              GOTO <<texas>>
        END IF;
        Select state_tax into :var_statetax From state 
        where state = var_empstate;
        var_salary = var_salary - (var_salary * var_statetax/100);
        <<texas>>
        var_salary = var_salary + 0; -- just add 0 to the var_salary if its texas
END;