1) Conditional statement in PLSQL:
===============================
— IF Statement
— Case Statement
IF Statement:
=============
IF condition then
statement;
end IF;
—————————————————————-
IF condition1 THEN
[statements to execute when condition1 is TRUE…]
ELSE
[statements to execute when condition1 is FALSE]
END IF;
———————————————————————
IF condition1 THEN
[statements to execute when condition1 is TRUE…]
ELSIF condition2 THEN
[statements to execute when condition2 is TRUE…]
ELSE
[statements to execute when both condition1 and condition2 are FALSE]
END IF;
—————————————————-
—Example 1:
declare
a boolean:=False;
begin
if a then
dbms_output.put_line(‘The condition is true’);
else
dbms_output.put_line(‘The condition is false’);
end if;
end;
declare
a number:=10;
b number:=5;
begin
if a>b then
dbms_output.put_line(a ||’ is greater than ‘||b);
else
dbms_output.put_line(b ||’ is greater than ‘||a);
end if;
end;
————————————————————————-
—Example 2:
DECLARE
a boolean := true;
b boolean := false;
BEGIN
IF (a AND b) THEN
dbms_output.put_line(‘Line 1 – Condition is true’);
else
dbms_output.put_line(‘Line 2 – Condition is true’);
END IF;
END;
————————————————————————-
— Example 3:
DECLARE
a boolean := true;
b boolean := false;
BEGIN
IF (a AND b) THEN
dbms_output.put_line(‘Line 1’);
END IF;
IF (a OR b) THEN
dbms_output.put_line(‘Line 2’);
END IF;
IF (NOT a) THEN
dbms_output.put_line(‘Line 3’);
ELSE
dbms_output.put_line(‘Line 4’);
END IF;
IF (NOT b) THEN
dbms_output.put_line(‘Line 5’);
ELSE
dbms_output.put_line(‘Line 6’);
END IF;
END;
— Example 4:
declare
v_max_salary number(10);
begin
select max(salary) into v_max_salary
from employees;
dbms_output.put_line(‘The maximum salary’||v_max_salary);
IF v_max_salary>100000 then
delete from employees where salary=v_max_salary;
end if;
end;
===============================================================================
————————————————————-
CASE Statement
CASE selector
WHEN selector_value_1 THEN
statements_1
WHEN selector_value_1 THEN
statement_2
…
ELSE
else_statements
END CASE;
declare
vsalary number(10);
vlocal number(10):=&vemp_id;
begin
select salary into vsalary from employees where employee_id=vlocal;
case vsalary
when vsalary > 15000 then
dbms_output.put_line(‘Fair salary’);
when vsalary > 10000 and vsalary < 15000 then
dbms_output.put_line(‘Avg salary’);
else
dbms_output.put_line(vsalary||’ Low salary’);
end case;
end;
————————————————————-
–searched case:
The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE . Expression whose value is evaluated once and used to select one of several alternatives. selector can have any PL/SQL data type except BLOB , BFILE , or a user-defined type.
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
…
WHEN condition_n THEN statements_n
[ ELSE
else_statements ]
END CASE;]
Example:
declare
vsalary number(10);
begin
select salary into vsalary from employees where employee_id=101;
case
when vsalary > 15000 and vsalary < 20000 then
dbms_output.put_line(‘Fair salary’);
when vsalary > 5000 and vsalary < 10000 then
dbms_output.put_line(‘low salary’);
else
dbms_output.put_line(vsalary ||’high salary’);
end case;
end;
===================================================================
— case statement in SQL
select cust_id,cust_name,mobile_no,dob,city,country,
case
when initcap(country)=’India’ then ‘+91-‘||mobile_no
when country=’USA’ then ‘+1-‘||mobile_no
when country=’Singapore’ then ‘+65-‘||mobile_no
else mobile_no
end new_phone_no from customer;
====================================================================
Simple CASE or searched CASE statement:
As a rule of thumb, use a searched CASE statement when you want to execute a sequence of statements based on the results of multiple Boolean expressions and use a simple CASE statement when you want to execute a sequence of statements based on the result of a single expression.
2) LOOPING Statements in PLSQL
