Conditional Statements

Conditional Statements

The following conditional statement are there in Oracle.Which is used inside the PL/SQL Block While Checking the Condition or Validating the Business logic's in our own Way.

1.IF
2.IF ELSE
3.Multiple IF ELSE

4.LOOP
5.FOR LOOP
6.WHILE

7.GO TO
8.EXIT
9.CONTINUE
10.BREAK

Example For IF::

DECLARE
   v_a   NUMBER := 7;
BEGIN
   IF v_a < 10
   THEN
      DBMS_OUTPUT.put_Line (v_a || ' ' || 'Is Less than 10');
   END IF;
END;
/

7 Is Less than 10

ELSE::

DECLARE
   v_a   NUMBER := 11;
BEGIN
   IF v_a < 10
   THEN
      DBMS_OUTPUT.put_Line (v_a || ' ' || 'is Less than 10');
   ELSE
      DBMS_OUTPUT.put_Line (v_a || ' ' || 'is Greater than 10');
   END IF;
END;
/

11 is Greater than 10
PL/SQL procedure successfully completed.


ELSE IF::

Multiple If Statements:

   v_a   NUMBER := 2;
BEGIN
   IF v_a = 1
   THEN
      DBMS_OUTPUT.put_Line ('The Value of a is One');
   ELSE IF v_a = 2
   THEN
      DBMS_OUTPUT.put_Line ('The Value of a is Two');
   ELSE IF v_a = 2
   THEN
      DBMS_OUTPUT.put_Line ('The Value of a is Three');
   ELSE
      DBMS_OUTPUT.put_Line ('The Value of a is One,Two,Three');
   END IF;
END;
/

The Value of a is Two
PL/SQL procedure successfully completed.

LOOP::

In Oracle, the LOOP statement is used when you are not sure how many times
you want the loop body to execute at least once.

Example::

DECLARE
   v_a   NUMBER := 1;
BEGIN
   LOOP
      DBMS_OUTPUT.put_Line (v_a);
      v_a := v_a + 1;
      EXIT WHEN v_a > 10;
   END LOOP;
END;
/

1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.


FOR LOOP::

In Oracle, the FOR LOOP allows you to execute code repeatedly for a fixed
number of times.
Example::

DECLARE
   v_a   NUMBER := 1;
BEGIN
   FOR i IN 1 .. 8
   LOOP
      DBMS_OUTPUT.put_Line (i);
   END LOOP;
END;
/

1
2
3
4
5
6
7
8
PL/SQL procedure successfully completed.

REVERSE KEYWORD in FOR LOOP::

    The loop counter will count in reverse.
Example:

DECLARE
   v_a   NUMBER := 1;
BEGIN
   FOR i IN REVERSE 1 .. 8
   LOOP
      DBMS_OUTPUT.put_Line (i);
   END LOOP;
END;
/

8
7
6
5
4
3
2
1
PL/SQL procedure successfully completed.

CONTINUE Keyword in LOOP::

The Continue statement exits the current iteration of the loop unconditionally and then transfers the control to the next iteration of the current loop.

Example

BEGIN
   FOR i IN 1 .. 50
   LOOP
      IF MOD (i, 5) = 0
      THEN
         CONTINUE;
      END IF;

      DBMS_OUTPUT.put_Line (i);
   END LOOP;
END;
/

EXIT IN LOOP::

The Oracle PL/SQL EXIT statement is used to skip the current block. It can be used to terminate loops or stop processing based on a condition you specify. When encountered, an EXIT statement forces a loop to complete immediately and unconditionally. Control is then passed to the next statement.

BEGIN
   FOR i IN 1 .. 50
   LOOP
      IF i = 3
      THEN
         EXIT;
      END IF;

      DBMS_OUTPUT.put_Line (i);
   END LOOP;

   DBMS_OUTPUT.put_Line ('Control Came out From Loop');
END;
/

1
2
Control Came out From Loop
PL/SQL procedure successfully completed.

Example::

DECLARE
   v_grade   CHAR (1);
BEGIN
   v_grade := 'B';

   CASE v_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 ('Good');
      WHEN 'D'
      THEN
         DBMS_OUTPUT.Put_Line ('Fair');
      WHEN 'E'
      THEN
         DBMS_OUTPUT.Put_Line ('Poor');
      ELSE
         DBMS_OUTPUT.Put_Line ('No Grade');
   END CASE;
END;
/

Very Good

PL/SQL procedure successfully completed.

--Conditional Statements End--