Oracle PL/SQL - IF THEN Statement

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

Introduction

The IF THEN statement has this structure: 

IF condition THEN 
  statements 
END IF;

If the condition is true, the statements run; otherwise, the IF statement does nothing.

In the following code, the statements between THEN and END IF run if and only if the value of sales is greater than quota+200.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w w  w.j  a  v a  2  s.co 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> DECLARE
  2    PROCEDURE p (sales  NUMBER,quota  NUMBER,emp_id NUMBER)
  3    IS
  4      bonus    NUMBER := 0;
  5      updated  VARCHAR2(3) := 'No';
  6    BEGIN
  7      IF sales > (quota + 200) THEN
  8        bonus := (sales - quota)/4;
  9        UPDATE emp
 10        SET salary = salary + bonus
 11        WHERE empid = emp_id;
 12        updated := 'Yes';
 13      END IF;
 14
 15      DBMS_OUTPUT.PUT_LINE('Table updated?' || updated || ', ' ||'bonus = ' || bonus || '.');
 16    END p;
 17  BEGIN
 18    p(10100, 10000, 100);
 19  END;
 20  /
Table updated?No, bonus = 0.

PL/SQL procedure successfully completed.

SQL>
SQL>

Related Topics

Exercise