PL/SQL Control Structures

PL/SQL Control Structures define as follow two ways

Ø Conditional statements

Ø Loops

Conditional Statements

      There are three forms of IF statements

IF-THEN-END IF;

IF-THEN-ELSE-END IF;

IF-THEN-ELSIF-END IF;

Syntax:

IF condition THEN

   Statements;

ELSIF condition THEN

   Statements;

ELSE

   Statements;

END IF;

Note:- You can also have a IF within a IF called as Nested IF.

 Example: IF..ELSE..END IF

-- Block to demonstrate IF...ELSE...END IF

DECLARE

   vname   emp.ename%TYPE;

   veno    emp.empno%TYPE   := &emp_num;

   vsal    emp.sal%TYPE;

BEGIN

   SELECT ename, sal

     INTO vname, vsal

     FROM emp

    WHERE empno = veno;

   -- Displays an appropriate message if salary is greater than 1500

   IF vsal > 1500

   THEN

      DBMS_OUTPUT.put_line(vname || ' earns a salary greater than 1500');

   --Else it shows the employee name whose sal is <1500

   ELSE

      DBMS_OUTPUT.put_line(vname || ' earns a salary not greater than 1500');

   END IF;

   DBMS_OUTPUT.put_line('This line executes irrespective of the condition');

END;

Example: Nested IF

DECLARE

   firstno    NUMBER (5) := &firstno;

   secondno   NUMBER (5) := &secondno;

BEGIN

   IF firstno IS NULL OR secondno IS NULL

   THEN

      DBMS_OUTPUT.put_line('Improper Input');

   ELSE

      IF firstno = secondno

      THEN

         DBMS_OUTPUT.put_line('The numbers are equal');

      ELSE

         IF firstno > secondno

         THEN

            DBMS_OUTPUT.put_line('The first no. is greater');

         ELSE

            DBMS_OUTPUT.put_line('The second no. is greater');

         END IF;

      END IF;

   END IF;

END;

/

LOOP Statements

Simple Loops

WHILE Loops

FOR Loops

Simple Loops

          LOOP

                  Sequence_of_statements;

      END LOOP;

         Add EXIT statement to exit from the loop

WHILE Loops

         WHILE  condition

         LOOP

                   Statements;

         END LOOP;

         Condition is evaluated before each iteration of the loop

Eg. # 1

DECLARE

   v_count        NUMBER (5) := &count;

                            -- If you enter count as 10 program will give all

   -- even and odd numbers from 1 to 10.

   v_even_count   NUMBER (5) := 0;

   v_odd_count    NUMBER (5) := 0;

BEGIN

   WHILE v_count > 0

   LOOP

      IF MOD (v_count, 2) = 0

      THEN

         v_even_count := v_even_count + 1;

      ELSE

         v_odd_count := v_odd_count + 1;

      END IF;

      v_count := v_count - 1;

   END LOOP;

   DBMS_OUTPUT.put_line('Even Nos are :' || v_even_count);

   DBMS_OUTPUT.put_line('Odd Nos are  :' || v_odd_count);

END;

LOOP Statement: Example

DECLARE

   v_i   NUMBER (2) := 1;

BEGIN

   LOOP

      DBMS_OUTPUT.put_line('Value : ' || v_i);

      EXIT WHEN v_i = 10;

      v_i := v_i + 1;

   END LOOP;

          END;

WHILE Loop: Example

           

DECLARE

   v_i   NUMBER (2) := 1;

BEGIN

   WHILE (v_i <= 10)

   LOOP

      DBMS_OUTPUT.put_line('Value : ' || v_i);

      v_i := v_i + 1;

   END LOOP;

           END;

FOR Loops

The number of iterations for simple loops and WHILE loops is not known in advance, it depends on the loop condition. Numeric FOR loops, on the other hand, have defined number of iterations.   

FOR  counter  IN  [REVERSE]  low_bound  ..   high_bound

 LOOP

                 Statements;

 END LOOP;

   Where:

counter: is an implicitly declared integer whose value automatically  increases or decreases by 1 on each iteration

          REVERSE: causes the counter to decrement from upper bound to lower bound

low_bound: specifies the lower bound for the range of counter values

high_bound: specifies the upper bound for the range of counter values

The following loop will print the number series from 1 to 10.

BEGIN

   FOR v_count IN 1 .. 10

   LOOP

      DBMS_OUTPUT.put_line('Count is :' || v_count);

   END LOOP;

         END;

The following loop will print the number series from 10 to 1.

BEGIN

   FOR v_count IN REVERSE 1 .. 10

   LOOP

      DBMS_OUTPUT.put_line('Count is :' || v_count);

   END LOOP;

END;

FOR Loop: Example

 BEGIN

   FOR v_i IN 1 .. 10

   /* The LOOP VARIABLE v_i of type BINARY_INTEGER is    declared automatically */

   LOOP

      DBMS_OUTPUT.put_line('Value : ' || v_i);

   END LOOP;

END;

For Loop with EXIT condition

DECLARE

   myno      NUMBER (5) := &myno;

   counter   NUMBER (5) := 1;

BEGIN

   FOR i IN 2 .. myno - 1

   LOOP

      counter := counter + 1;

      EXIT WHEN myno MOD i = 0;

   END LOOP;

   IF counter = myno - 1

   THEN

      DBMS_OUTPUT.put_line

                   ('The given

                             number is prime');

   ELSE

      DBMS_OUTPUT.put_line

                     ('The given number is not

              a prime number');

   END IF;

END;

/