Oracle PL/SQL - IF THEN ELSE Statement

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

Introduction

IF THEN ELSE statement has this structure: 

IF condition THEN 
  statements 
ELSE 
  else_statements 
END IF;

If the value of condition is true, the statements run; otherwise, the else_statements run.

In the following code, the statement between THEN and ELSE runs if and only if the value of sales is greater than quota+200; otherwise, the statement between ELSE and END IF runs.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w ww .j  a v  a 2  s  .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 (
  3     sales  NUMBER,
  4     quota  NUMBER,
  5     emp_id NUMBER
  6   )
  7   IS
  8     bonus  NUMBER := 0;
  9   BEGIN
 10     IF sales > (quota + 200) THEN
 11       bonus := (sales - quota)/4;
 12      ELSE
 13        bonus := 50;
 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  END;
 25  /
bonus = 50

PL/SQL procedure successfully completed.

SQL>

Related Topic