ORACLE IF Statement

ORACLE IF Statement

               If statement is used to conditional execution of particular block of code.

IF THEN Synatx:

                 if condition then

                     block of statement;

                end if;

Ex1: Check number is Even or not;

declare
 n number;
begin
n:=10;
if (mod(n,2)=0) then
          dbms_output.put_line('Even Number');
end if;
end;

OUTPUT

Even Number

PL/SQL procedure successfully completed.

Ex2: Update employees commission who is working as Clerk and earning salary more than 1000. and comm is null.

declare
 cursor c1 is select * from emp where job='CLERK';
 begin
 for e in c1
 loop
 if e.sal >1000 and e.comm is null then
 update emp set comm = 500.50 where empno=e.empno;
 end if;
 end loop;
 commit;
 end;

OUTPUT: select * from emp where job='CLERK';

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
--------- ---------- --------- ---------- --------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800
     7876 ADAMS      CLERK           7788 23-MAY-87       1100      500.5
     7900 JAMES      CLERK           7698 03-DEC-81        950
     7934 MILLER     CLERK           7782 23-JAN-82       1300      500.5

Ex: Display EVEN Number of Dates having Saturday.

declare
  lastday  number(2);
  day char(3);
begin
select to_number(to_char(last_day(sysdate),'DD'),'99') into lastday from dual;
dbms_output.put_line(lastday);
for i in 1..lastday
loop
select to_char(to_date(i,'DD'),'DY') into day from dual;
if mod(i,2)=0  and day='SAT' then
dbms_output.put_line('Even Number Date:'||' '||i||'Day is'||day);
end if;
end loop;
exception
when others then
       dbms_output.put_line('exception occurred');
end;
/

OUTPUT:

SQL> /
31
Even Number Date: 2Day isSAT
Even Number Date: 16Day isSAT
Even Number Date: 30Day isSAT

PL/SQL procedure successfully completed.

Note:for odd number of Saturdays  

if condition would be

if mod(i,2)<>0  and day='SAT' then

Tags:ORACLE IF Statement,ORACLE IF THEN STATEMENT, using oracle IF Statement in PL/SQL,using IF in PL/SQL, Checking condition using IF statement.