Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



Conditional Selection Statements in PL/SQL

The conditional selection statements, IF and CASE, run different statements for different data values. The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms:

  • IF
  • IF THEN ELSE
  • IF THEN ELSIF

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. The CASE statement has these forms:
Simple, which evaluates a single expression and compares it to several potential values.
Searched, which evaluates multiple conditions and chooses the first one that is true.

IF THEN Statement

The IF THEN statement has this structure:

IF condition THEN
statements
END IF;
DECLARE
PROCEDURE p (
sales NUMBER,
quota NUMBER,
emp_id NUMBER
)
IS
bonus NUMBER := 0;
updated VARCHAR2(3) := 'No';
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;

UPDATE employees
SET salary = salary + bonus
WHERE employee_id = emp_id;

updated := 'Yes';
END IF;

DBMS_OUTPUT.PUT_LINE E (
'Table updated? ' || updated || ', ' ||
'bonus = ' || bonus || '.'
);
END p;
BEGIN
p(10100, 10000, 120);
p(10500, 10000, 121);
END;
/
Table updated? No, bonus = 0.
Table updated? Yes, bonus = 125.
IF THEN ELSE Statement

The IF THEN ELSE statement has this structure:

IF condition THEN
statements
ELSE
else_statements
END IF;
DECLARE
a number(3) := 100;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
DBMS_OUTPUT.PUT_LINE E('a is less than 20 ' );
ELSE
DBMS_OUTPUT.PUT_LINE E('a is not less than 20 ' );
END IF;
DBMS_OUTPUT.PUT_LINE E('value of a is : ' || a);
END;
/

OutPut

a is not less than 20
value of a is : 100
IF THEN ELSIF Statement

The IF THEN ELSIF statement has this structure:

IF condition_1 THEN
statements_1
ELSIF condition_2 THEN
statements_2
[ ELSIF condition_3 THEN
statements_3
]...
[ ELSE
else_statements
]
END IF;
DECLARE
PROCEDURE p (sales NUMBER)
IS
bonus NUMBER := 0;
BEGIN
IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF;

DBMS_OUTPUT.PUT_LINE (
'Sales = ' || sales || ', bonus = ' || bonus || '.'
);
END p;
BEGIN
p(55000);
p(40000);
p(30000);
END;
/

OutPut

Sales = 55000, bonus = 1500.
Sales = 40000, bonus = 500.
Sales = 30000, bonus = 100.


Related Videos