Oracle PL/SQL - Nested IF THEN ELSE Statements

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. IF THEN Statement

Introduction

IF statements can be nested.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  ww w.ja va2s . c o m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL>
SQL> DECLARE
  2    PROCEDURE p(sales  NUMBER,quota  NUMBER,emp_id NUMBER)
  3    IS
  4      bonus  NUMBER := 0;
  5    BEGIN
  6      IF sales > (quota + 200) THEN
  7        bonus := (sales - quota)/4;
  8      ELSE
  9        IF sales > quota THEN
 10          bonus := 50;
 11        ELSE
 12          bonus := 0;
 13        END IF;
 14      END IF;
 15
 16      DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
 17
 18      UPDATE emp
 19      SET salary = salary + bonus
 20      WHERE empid = emp_id;
 21    END p;
 22  BEGIN
 23    p(10100, 10000, 100);
 24
 25  END;
 26  /
bonus = 50

PL/SQL procedure successfully completed.

SQL>

Related Topic