Saturday, June 29, 2013

PL-SQL Examples



1)    Example of a simple Pl/SQL block
declare
a number:=&a;
b number:=&b;
c number;
begin
c:=a+b;
dbms_output.put_line('the sum is'||c);
end;
/

2)    Example for LOOP structure which generates a number series.
declare
i number:=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
if i>10 then
exit;
end if;
end loop;
end;
/

3)    Example for WHILE structure which generates a number series.
declare
i number:=1;
begin
while i<=10
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
/

4)    Example for FOR structure which generates a number series.
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;

5)    PL/SQL block to count no. of vowels and consonants in a given string
declare
name varchar2(20):='&name';
ccnt number:=0;
vcnt number:=0;
begin
for i in 1.. length(name)
loop
if upper(substr(name,i,1)) in ('A','E','I','O','U') then
vcnt:=vcnt+1;
else
ccnt:=ccnt+1;
end if;
end loop;
dbms_output.put_line('vcount is'||vcnt);
dbms_output.put_line('ccount is'||ccnt);
end;
/

6)    PL/SQL block to print the string in REVERSE
declare
str varchar2(30):='&string';
strn varchar2(30);
begin
for i in reverse 1..length(str)
loop
strn:=strn||substr(str,i,1);
end loop;
dbms_output.put_line(strn);
end;
/

7)    PL/SQL block to find the FACTORIAL of a given number.
declare
i number:=&num;
k number:=1;
begin
for j in 1..i
loop
k:=K*J;
end loop;
dbms_output.put_line(k);
end;
/

8)    PL/SQL block to print the STARS in a triangular form.
declare
c varchar(10):=' ';
begin
for i in 1..5
loop
    c:=' ';
    for j in 1..i
    loop
    c:=c||'*';
    end loop;
dbms_output.put_line(c);
end loop;
end;
/

9)    PL/SQL block to convert initial of the string to CAPITAL without using INITCAP
declare
name varchar2(15):='&name';
xname varchar2(15);
begin
xname:=upper(substr(name,1,1))||lower(substr(name,2));
dbms_output.put_line(xname);
end;
/

10)    PL/SQL block to update the salary of the given employee number
declare
eno number(4):=&empno;
nsal number(7,2):=&sal;
begin
update emp set sal=sal+nsal where empno=eno;
commit;
dbms_output.put_line('salary is updated for empno: '||eno);
end;
/


11)    PL/SQL block to update the salary of the an employee on given condition
declare
eno number(4):=&eno;
nsal number(7,2);
begin
select sal into nsal from emp where empno=eno;
if nsal<3000 then
update emp set sal=sal+500 where empno=eno;
else
update emp set sal=sal+1000 where empno=eno;
end if;
commit;
end;
/

12)    PL/SQL block to that deletes records from master and child table
declare
dno number(2):=&dno;
begin
delete from emp where deptno=dno;
delete from dept where deptno=dno;
end;
/

13)    PL/SQL block to insert records into a database table
declare
x dept.deptno%type:=&dno;
y dept.dname%type:='&dname';
z dept.loc%type:='&loc';
begin
insert into dept values(x,y,z);
end;
/

14)    PL/SQL block to that represents the usage of % attributes
declare
x emp%rowtype;
y emp.empno%type:=&eno;
begin
select * into x from emp where empno=y;
dbms_output.put_line(x.empno||x.ename||x.sal);
end;
/

Exceptions
15)    PL/SQL block toto raise an EXCEPTION when the record to search is not found.
declare
eno emp.empno%type:=&eno;
nsal emp.sal%type;
begin
select sal into nsal from emp where empno=eno;
dbms_output.put_line(nsal);
exception
when no_data_found then
dbms_output.put_line('no such employee');
end;
/

16)    PL/SQL block to raise an user defined EXCEPTION when commission is null
declare
no_comm exception;
eno emp.empno%type:=&eno;
ncomm emp.comm%type;
begin
select comm into ncomm from emp where empno=eno;
dbms_output.put_line(ncomm);
if ncomm is null then
raise no_comm;
end if;
exception
when no_comm then
dbms_output.put_line('No commision');
end;
/

Cursors
17)    PL/SQL block using CURSOR to display records from emp table
declare
cursor emp_cur is select * from emp;
emp_rec emp%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_rec;
exit when emp_cur%notfound;
dbms_output.put_line(emp_rec.empno||emp_rec.ename||emp_rec.sal);
end loop;
close emp_cur;
end;
/

18)    PL/SQL block using CURSOR attributes to display employee records.
Declare
cursor c is select * from emp;
e emp%rowtype;
begin
if c%IsOpen then
 dbms_output.put_line('Cursor is Already Opened');
else
 open c;
end if;
loop
 fetch c into e;
 dbms_output.put_line('Mr.'||e.ename||' who is an '||e.job||' draws Rs.'||e.sal);
 exit when c%NOTFOUND;
end loop;
dbms_output.put_line(c%ROWCOUNT ||' Rows were displayed');
if c%IsOpen then
 close c;
else
 dbms_output.put_line('Cursor is already closed');
end if;
end;
/

19)    PL/SQL block using CURSOR to display the required highest salary from emp.
declare
cursor ecur is select distinct(sal) from emp order by sal desc;
nsal emp.sal%type;
i number:=&num;
ctr number:=1;
begin
if ecur%isopen then
close ecur;
end if;
open ecur;
loop
fetch ecur into nsal;
if i=ctr then
dbms_output.put_line(nsal);
exit;
end if;
ctr:=ctr+1;
exit when ecur%notfound;
end loop;
close ecur;
end;
/

20)    PL/SQL block using CURSOR FOR loop to display employee details
declare
cursor c1 is select * from emp;
begin
for i in c1
loop
dbms_output.put_line(i.ename||i.sal);
end loop;
end;
/

21)    PL/SQL block using CURSOR FOR loop to display dept and emp details.
declare
cursor d is select * from dept;
cursor e(dno number) is select * from emp where deptno=dno;
begin
for department in d
loop
    dbms_output.put_line('DEPARTMENT NUMBER : ' || department.deptno);
    dbms_output.put_line('--------------------------------');
    for employee in e(department.deptno)
    loop
        dbms_output.put_line('Mr.'||employee.ename||' is working in department '||department.dname||
' at '||department.loc||' as '||employee.job);
    end loop;
end loop;
end;


22)    PL/SQL block using CURSOR FOR loop to display the table name and constraints of it.
declare
cursor t is select * from tab;
cursor c(tname varchar2) is select * from user_constraints where table_name like upper(tname);
begin
for tables in t
loop
    dbms_output.put_line('Constraints in Table : ' || tables.tname);
    dbms_output.put_line('------------------------------------------');
    for cons in c(tables.tname)
    loop
        dbms_output.put_line(cons.constraint_name);
    end loop;
end loop;
end;
/


Triggers
23)    PL/SQL block using TRIGGER to not to update a table before or after office hours
create or replace trigger trg_secure before insert on emp
declare
x number;
begin
if to_char(sysdate,'hh24') not between 1 and 20 then
raise_application_error(-20000,'cannot manipulate data in un official hrs.');
elsif to_char(sysdate,'hh24:30')  between 12:30 and 13.30 then
raise_application_error(-20001,'cannot manipulate during lunch hrs.'); elsif upper(to_char(sysdate,'dy')) in ('SAT','SUN') then
raise_application_error(-20002,'cannot manipulate data on week ends.');
end if;
select count(*) into x from holidays where h_date=to_date(sysdate,'dd-mon-yy');
if x>0 then
raise_application_error(-20003,'cannot manipulate data on a public holiday.');
end if;
end;
/

24)    PL/SQL block using TRIGGER to check for salary tobe more than 5000
create or replace trigger sal_chk before insert on emp for each row
begin
if :new.sal>5000 then
raise_application_error(-20004,'Salary should be above 5000.');
end if;
end;
/

25)    PL/SQL block using TRIGGER to salary with more than old salary
create or replace trigger salUpdate
before update on emp for each row
begin
    if :new.sal < :old.sal then
        raise_application_error(-20005,'New salary is lesser than Old');
    end if;
end;
/

26)    PL/SQL block using TRIGGER not to accept the existing empno (Unique Empno)
create or relace trigger dupEmpno
before insert or update on emp
for each row
declare
cursor c is select * from emp;
begin
for i in c
loop
    if i.empno=:new.empno then
        raise_application_error(-20006,'Empno already exists');
    end if;
end loop;
end;
/

27)    PL/SQL block using TRIGGER to generate auto employee numbers
create or replace trigger AutoEmpno
before insert on emp for each row
declare
n number;
begin
select nvl(max(empno),7000)+1 into n from emp;
:new.empno:=n;
end;
/

28)    PL/SQL block using TRIGGER allow only the owner to work with table and only on working days
 create or replace trigger empTrans
before insert or update or delete on emp
for each row
declare
wEnd exception;
usr exception;
begin
if upper(rtrim(to_char(sysdate,'day')))='SAT' or
   upper(rtrim(to_char(sysdate,'day')))='SUN'     then
   raise wEnd;
end if;
if upper(user)<>'SCOTT' then
    raise usr;
end if;
exception
when wEnd then
raise_application_error(-20007,'Transactions are not allowed at week ends');
when usr then raise_application_error(-20008,'Transactions are allowed only by Scott');
end;
/


29)    PL/SQL block using TRIGGER that will not allow user to work on fridays
create or replace trigger restrictDML
before insert or update or delete on emp
for each row
declare
v varchar2(20);
begin
select upper(rtrim(to_char(sysdate,'day'))) into v from dual;
if v='FRIDAY' then
     raise_application_error(-20009,'Transactions are not allowed on FRIDAY');
end if;
end;

30)  An example of a row trigger follows:
create or replace trigger mytrig2
after delete or insert or update on emp
for each row
begin
   if deleting then
      insert into emp1 (empno, ename, job, sal, deldate) values
      (:old.empno, :old.ename, :old.job, :old.sal, sysdate);
   elsif inserting then
      insert into emp2 (empno, ename, job, sal, deldate) values     
      (:new.empno, :new.ename, :new.job, :new.sal, sysdate);
   elsif updating then
      insert into emp3 (empno, ename, old_sal, new_sal, upddate)
      values (:old.empno, :old.ename, :old.sal, :new.sal, sysdate);
   end if
end;
/

Procedures
31)  PL/SQL block using PROCEDURE that works on dept and emp
create or replace procedure DeptEmp(dno number) is
d dept%rowtype;
e emp%rowtype;
cursor c(d number) is select * from emp where deptno=d;
begin
     select * into d from dept where deptno=dno;
     dbms_output.put_line('Department : '||d.dname);
     dbms_output.put_line('---------------------------');
     for e in c(d.deptno)
     loop
           dbms_output.put_line(e.ename||' - '||e.job);
     end loop;
end;
/

32)  PL/SQL block using PROCEDURE to find the factorial of given number
create or replace procedure Fact(a in number,b out number) is
f number(4):=1;
begin
for i in 1..a
loop
    f:=f*i;
end loop;
b:=f;
end;
/

33)  PL/SQL block that calls the procedure to find factorial
declare
x number(4):=&x;
y number(4);
begin
Fact(x,y);
dbms_output.put_line('Factorial of ' || x || ' is ' || y);
end;
/


34)  PL/SQL block using PROCEDURE to work with arithmetic operations
create or replace procedure Arith(a number,b number,c char) is
     d number(4);
     ex exception;
begin
     if c='+' then
           d:=a+b;
     elsif c='-' then
           d:=a-b;
     elsif c='*' then
           d:=a*b;
     elsif c='/' then
           d:=a/b;
     elsif c='%' then
           d:=mod(a,b);
     else
           raise ex;
     end if;
     dbms_output.put_line(a||' '||c||' '||b||' = '||d);
exception
     when ex then
           dbms_output.put_line(' Not a Valied Operator ');
     when zero_divide then
           dbms_output.put_line(' Denominator shouldnot be zero ');
     when others then
           dbms_output.put_line('SQLERROR');
end;
/


Functions
35)  PL/SQL block using FUNCTION to find the factorial of given number
create or replace function FunFact(a number) return number is
f number(4):=1;
begin
for i in 1..a
loop
     f:=f*i;
end loop;
return f;
end;
/

36)  PL/SQL block that calls the function to find factorial
declare
n number(2):=&n;
r number(4);
begin
r:=FunFact(n);
dbms_output.put_line('Factorial of '||n||' is : '||r);
end;
/

Packages
37)  PL/SQL block for creating Package Specification
create or replace package bank_pack is
minbal exception;
cursor c1(no number) is select * from transact where accno=no;
procedure new_acc(name varchar2,oamt number);
function deposit(no number,amt number) return number;
function withdraw(no number,amt number) return number;
procedure trans(no number,tty char,amt number);
procedure close_acc(no number);
procedure details(no number);
end;
/

38)  PL/SQL block for creating Package Body
create or replace package body bank_pack is
procedure new_acc(name varchar2,oamt number) is
n number;
begin
     select nvl(max(accno),0)+1 into n from bankmast;
     if oamt<1000 then
           raise minbal;
     end if;
     insert into bankmast values(n,name,oamt);
     dbms_output.put_line('New Account for '||name||' is successfully created');
exception
     when minbal then
           dbms_output.put_line('Minimum balance should be Rs.1000');
end;

function deposit(no number,amt number) return number is
     pb number;
begin
     select opamt into pb from bankmast where accno=no;
     return pb+amt;
exception
     when no_data_found then
           dbms_output.put_line('Invalied Account Number');
end;

function withdraw(no number,amt number) return number is
     pb number;
begin
     select opamt into pb from bankmast where accno=no;
     if pb-amt<250 then
           raise minbal;
     end if;
     return pb-amt;
exception
     when no_data_found then
           dbms_output.put_line('Invalied Account Number');
     when minbal then
           dbms_output.put_line('Insufficient Balance');
end;

procedure trans(no number,tty char,amt number) is
     bal number;
begin
     if tty in ('D','d') then
           bal:=deposit(no,amt);
           insert into transact values(no,tty,amt,bal);
           update bankmast set opamt=bal where accno=no;
           dbms_output.put_line('Deposited Rs.'||amt||' into Account No : '||no);
     else
           bal:=withdraw(no,amt);
          if (bal>=1000) then
                insert into transact values(no,tty,amt,bal);
                update bankmast set opamt=bal where accno=no;       
                dbms_output.put_line('Withdrawn Rs.'||amt||' from Account No : '||no);
           else
                raise minbal;
           end if;
     end if;
exception
     when minbal then
           dbms_output.put_line('Insufficient Balance');
           dbms_output.put_line('U need to maintain Min Balance');
end;

procedure close_acc(no number) is
n bankmast.name%type;
begin
     select name into n from bankmast where accno=no;
     delete from transact where accno=no;
     delete from bankmast where accno=no;
     dbms_output.put_line('Account of Mr.'||n||' is Closed');
end;

procedure details(no number) is
n bankmast%rowtype;
begin
     select * into n from bankmast where accno=no;
     dbms_output.put_line('Account No : '||n.accno);
     dbms_output.put_line('Name : '||n.name);
     dbms_output.put_line('Opening Balance : '||n.opamt);
     dbms_output.put_line('--------------------------------');
     for i in c1(no)
     loop
           dbms_output.put_line(i.accno||' '||i.ttype||' '||i.tamt||' '||i.cbal);
     end loop;
end;
end;
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect