Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 6 April 2016

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;

/
Share this article :

0 comments:

Post a Comment