case statement in PL/SQL
A CASE statement is similar to IF-THEN-ELSIF statement.
That selects one alternative based on the condition from the available options.
CASE statement uses “selector” rather than a Boolean expression to choose the sequence.
Execute a sequence of statements based on a selector.
How many type is case statement in pl/sql?
Two types:
(1)Simple CASE statement
(2)Searched CASE statement.
PL/SQL Case Structure :
Simple CASE statement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Declare f_rank varchar2(10); b_grade char(1); begin b_grade :='b'; case b_grade when 'a' then f_rank :='very good'; when 'b' then f_rank :='good'; else f_rank :='no such grade'; end case; dbms_output.put_line(f_rank); end; SQL> / good • PL/SQL procedure successfully completed. |
Searched CASE statement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
declare d_sales NUMBER; c_commission NUMBER; Begin d_sales :=150000; case when d_sales >200000 then c_commission := 0.1; when d_sales >10000 then c_commission := 0.2; else c_commission := 0; end case; dbms_output.put_line('commission is' || c_commission *100 ||'%'); end; SQL> / commission is20% • PL/SQL procedure successfully completed. |
