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--

Read More

Oracle PL/SQL Introduction

Oracle PL/SQL Introduction

*Procedural Language extension with SQL with design features of Programming language.
*Data Manipulation & query Statement of SQL are included with procedural Unit of
Code.
*It mainly used for reduce the network traffic.
*It group the set of logical statement Into single block & send the block
to the server for execution.

CONCEPTS IN PL/SQL::

1.Anonymous Block
2.Conditional Statements
3.Cursors
4.Exception
5.Stored Procedure
6.Stored Functions
7.Packages
8.Triggers

1.Unnamed block or Anonymous Block Structure::

DECLARE
           variable;
           cursor;
           Exception;
           SubProgram(Like Procedure);
BEGIN

           SQL Statements (*Mandatory)
           Business logics
EXCEPTION (*Optional)
Error Handling;
END;
/


EXAMPLE FOR ANONYMOUS BLOCK::
SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
2 dbms_Output.put_Line('This is My First Program');
3 END;
4 /

This is My First Program
PL/SQL procedure successfully completed.

VARIABLES::

*Temporary Storage of Data
*Manipulation of Stored Value
*Reusability
PL/SQL VARIABLES::
------------------
1.Scalar
2.Composite
3.Reference
4.Large Object

NON PL/SQL VARIABLES::

1.Bind Variables

Standard Rules to be follow to Declare PL/SQL Variable::

*Follow Naming rule
(v_Name...(Start with v_)
1-30 Character size
*Initialize NOT NULL/Constant/Default
*Declare One identified Per line
*Initialize identifiers by using assignment Operator(:=)


EXAMPLE-1

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_a NUMBER(3):=4;
3 BEGIN
4 dbms_Output.put_Line('The Value Of a Is :' || v_a);
5 END;
6 /
The Value Of a Is :4
PL/SQL procedure successfully completed.
------------------------------------------------------------------

Example-2

DECLARE
v_a NUMBER:=&a;
v_b NUMBER:=&b;
v_c NUMBER;
BEGIN
v_c := v_a + v_b;
dbms_output.put_Line('The Values Of a+b Is :' || v_c);
END;
/
Enter value for a: 4
Enter value for b: 5
The Values Of a+b Is :9
-----------------------------------------------------------------

Example-3


DECLARE
   v_a             NUMBER := 100;
   v_name          VARCHAR2 (100) := INITCAP ('Arul Xavier');
   v_dt            DATE := SYSDATE;
   v_dt_dif_fmt    VARCHAR2 (30) := TO_CHAR (SYSDATE, 'DD Mon, YYYY');
   v_dept          VARCHAR2 (10) NOT NULL := 'CSE';
   v_depid         NUMBER DEFAULT 10;
   v_pi   CONSTANT NUMBER := 3.14;
BEGIN
   v_a := 200;
   DBMS_OUTPUT.put_line ('Number : ' || v_a);
   DBMS_OUTPUT.put_line ('Name : ' || v_name);
   DBMS_OUTPUT.put_line ('Date : ' || v_dt);
   DBMS_OUTPUT.put_line ('Char Chk : ' || v_dt_dif_fmt);
   DBMS_OUTPUT.put_line ('Dname : ' || v_dept);
   DBMS_OUTPUT.put_line ('did : ' || v_depid);
   DBMS_OUTPUT.put_line ('PI : ' || v_pi);
END;
/
------------------------------------------------------------------

NESTED BLOCK or SCOPE OF VARIABLE::


<<Outer>>
DECLARE
   v_a   NUMBER := 20;
   v_c   NUMBER := 100;
   v_d   NUMBER;
   v_e   BOOLEAN := FALSE;
   v_f   NUMBER (200) := 39000;
BEGIN
   DBMS_OUTPUT.put_Line ('-------->' || v_e);
   DBMS_OUTPUT.put_Line ('-------->' || v_f);

   IF v_e
   THEN
      DBMS_OUTPUT.put_Line ('Boolean Check :' || 1 || '<------------');
   ELSE
      DBMS_OUTPUT.put_Line ('Boolean Check :' || 0 || '<------------');
   END IF;

  <<inner>>
   DECLARE
      v_a   NUMBER := 30;
   BEGIN
      DBMS_OUTPUT.put_Line ('Value Of a is:' || Outer.v_a);
      DBMS_OUTPUT.put_Line ('Value Of a is:' || v_c);
   END;

   DBMS_OUTPUT.put_Line ('Value Of a is:' || v_a);
END;
/
--------------------------------------------------------------------
DROP TABLE TEST_INSERT
/
CREATE TABLE TEST_INSERT(id NUMBER)
/
BEGIN
INSERT INTO TEST_INSERT VALUES(1000);
INSERT INTO TEST_INSERT VALUES(1001);
INSERT INTO TEST_INSERT VALUES(1002);
INSERT INTO TEST_INSERT VALUES(1003);
INSERT INTO TEST_INSERT VALUES(1004);
INSERT INTO TEST_INSERT VALUES(1005);
COMMIT;
END;
/
-->--
DECLARE
   v_salary   NUMBER (8);
BEGIN
   SELECT MAX (salary) INTO v_salary FROM employees;

   DBMS_OUTPUT.put_line ('Maximum Salary : ' || v_salary);
END;
/
-------------------------------------------------------------------------------
DECLARE
   v_empid    employees.employee_id%TYPE := 100;
   v_fname    employees.first_name%TYPE;
   v_salary   employees.salary%TYPE;
   v_hdate    employees.hire_date%TYPE;
BEGIN
   SELECT first_name, salary, hire_date
     INTO v_fname, v_salary, v_hdate
     FROM employees
    WHERE employee_id = v_empid;

   DBMS_OUTPUT.put_line (' First Name : ' || v_fname);
   DBMS_OUTPUT.put_line (' Salary : ' || v_salary);
   DBMS_OUTPUT.put_line (' Hire Date : ' || v_hdate);
END;
/
-------------------------------------------------------------------------------
DECLARE
   v_empid     employees.employee_id%TYPE := 100;
   v_alldate   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO v_alldate
     FROM employees
    WHERE employee_id = v_empid;

   DBMS_OUTPUT.put_line (' First Name : ' || v_alldate.first_name);
   DBMS_OUTPUT.put_line (' Salary : ' || v_alldate.salary);
   DBMS_OUTPUT.put_line (' Hire Date : ' || v_alldate.hire_date);
END;
/
----------------------------------------------------------------------------------
DECLARE
   v_department_id   NUMBER := 80;
BEGIN
   DELETE FROM employees
         WHERE department_id = v_department_id;
END;
/


BIND VARIABLES::

By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.

SQL> VARIABLE a NUMBER;
SQL>
SQL>
SQL> BEGIN
2 SELECT MAX(salary) INTO :a FROM employees;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> PRINT a;
A
----------
24000
SQL> SELECT :a FROM dual;
:A
----------
24000

                                               --END PL/SQL INTRO--

Read More