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;
ifi>10then
exit;
endif;
endloop;
end;
/
3) Example for WHILE structure which generates a number series.
declare
i number:=1;
begin
whilei<=10
loop
dbms_output.put_line(i);
i:=i+1;
endloop;
end;
/
4) Example for FOR structure which generates a number series.
begin
fori in 1..10
loop
dbms_output.put_line(i);
endloop;
end;
5) PL/SQL block to count no. of vowels and consonants in a given string
declare
namevarchar2(20):='&name';
ccnt number:=0;
vcnt number:=0;
begin
fori in 1..length(name)
loop
ifupper(substr(name,i,1)) in ('A','E','I','O','U') then
vcnt:=vcnt+1;
else
ccnt:=ccnt+1;
endif;
endloop;
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
fori in reverse 1..length(str)
loop
strn:=strn||substr(str,i,1);
endloop;
dbms_output.put_line(strn);
end;
/
7) PL/SQL block to find the FACTORIAL of a given number.
declare
i number:=#
k number:=1;
begin
forj in 1..i
loop
k:=K*J;
endloop;
dbms_output.put_line(k);
end;
/
8) PL/SQL block to print the STARS in a triangular form.
declare
c varchar(10):=' ';
begin
fori in 1..5
loop
c:=' ';
forj in 1..i
loop
c:=c||'*';
endloop;
dbms_output.put_line(c);
endloop;
end;
/
9) PL/SQL block to convert initial of the string to CAPITAL without using INITCAP
declare
namevarchar2(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
updateemp 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
selectsal into nsal from emp where empno=eno;
ifnsal<3000then
updateemp set sal=sal+500where empno=eno;
else
updateemp set sal=sal+1000where empno=eno;
endif;
commit;
end;
/
12) PL/SQL block to that deletes records from master and child table
declare
dno number(2):=&dno;
begin
deletefrom emp where deptno=dno;
deletefrom 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
insertinto 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
selectsal into nsal from emp where empno=eno;
dbms_output.put_line(nsal);
exception
whenno_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
selectcomm into ncomm from emp where empno=eno;
dbms_output.put_line(ncomm);
ifncomm is null then
raiseno_comm;
endif;
exception
whenno_comm then
dbms_output.put_line('No commision');
end;
/
Cursors
17) PL/SQL block using CURSOR to display records from emp table
declare
cursoremp_cur is select * from emp;
emp_rec emp%rowtype;
begin
openemp_cur;
loop
fetchemp_cur into emp_rec;
exitwhen emp_cur%notfound;
dbms_output.put_line(emp_rec.empno||emp_rec.ename||emp_rec.sal);
endloop;
closeemp_cur;
end;
/
18) PL/SQL block using CURSOR attributes to display employee records.
Declare
cursorc is select * from emp;
e emp%rowtype;
begin
ifc%IsOpen then
dbms_output.put_line('Cursor is Already Opened');
else
openc;
endif;
loop
fetchc into e;
dbms_output.put_line('Mr.'||e.ename||' who is an '||e.job||' draws Rs.'||e.sal);
exitwhen c%NOTFOUND;
endloop;
dbms_output.put_line(c%ROWCOUNT||' Rows were displayed');
ifc%IsOpen then
closec;
else
dbms_output.put_line('Cursor is already closed');
endif;
end;
/
19) PL/SQL block using CURSOR to display the required highest salary from emp.
declare
cursorecur is select distinct(sal) from emp order by sal desc;
nsal emp.sal%type;
i number:=#
ctr number:=1;
begin
ifecur%isopen then
closeecur;
endif;
openecur;
loop
fetchecur into nsal;
ifi=ctr then
dbms_output.put_line(nsal);
exit;
endif;
ctr:=ctr+1;
exitwhen ecur%notfound;
endloop;
closeecur;
end;
/
20) PL/SQL block using CURSOR FOR loop to display employee details
declare
cursorc1 is select * from emp;
begin
fori in c1
loop
dbms_output.put_line(i.ename||i.sal);
endloop;
end;
/
21) PL/SQL block using CURSOR FOR loop to display dept and emp details.
declare
cursord is select * from dept;
cursore(dno number) isselect * from emp where deptno=dno;
begin
fordepartment in d
loop
dbms_output.put_line('DEPARTMENT NUMBER : ' || department.deptno);
dbms_output.put_line('--------------------------------');
foremployee in e(department.deptno)
loop
dbms_output.put_line('Mr.'||employee.ename||' is working in department '||department.dname||
' at '||department.loc||' as '||employee.job);
endloop;
endloop;
end;
22) PL/SQL block using CURSOR FOR loop to display the table name and constraints of it.
declare
cursort is select * from tab;
cursorc(tname varchar2) isselect * from user_constraints where table_name likeupper(tname);
begin
fortables in t
loop
dbms_output.put_line('Constraints in Table : ' || tables.tname);
dbms_output.put_line('------------------------------------------');
forcons in c(tables.tname)
loop
dbms_output.put_line(cons.constraint_name);
endloop;
endloop;
end;
/
Triggers
23) PL/SQL block using TRIGGER to not to update a table before or after office hours
createor replace trigger trg_secure before insert on emp
declare
x number;
begin
ifto_char(sysdate,'hh24') not between 1 and 20 then
raise_application_error(-20000,'cannot manipulate data in un official hrs.');
elsifto_char(sysdate,'hh24:30') between12: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.');
endif;
selectcount(*) into x from holidays where h_date=to_date(sysdate,'dd-mon-yy');
ifx>0then
raise_application_error(-20003,'cannot manipulate data on a public holiday.');
endif;
end;
/
24) PL/SQL block using TRIGGER to check for salary tobe more than 5000
createor replace trigger sal_chk before insert on emp for each row
begin
if:new.sal>5000then
raise_application_error(-20004,'Salary should be above 5000.');
endif;
end;
/
25) PL/SQL block using TRIGGER to salary with more than old salary
createor replace trigger salUpdate
beforeupdate on emp for each row
begin
if:new.sal < :old.sal then
raise_application_error(-20005,'New salary is lesser than Old');
endif;
end;
/
26) PL/SQL block using TRIGGER not to accept the existing empno (Unique Empno)
createor relace trigger dupEmpno
beforeinsert or update on emp
foreach row
declare
cursorc is select * from emp;
begin
fori in c
loop
ifi.empno=:new.empno then
raise_application_error(-20006,'Empno already exists');
endif;
endloop;
end;
/
27) PL/SQL block using TRIGGER to generate auto employee numbers
createor replace trigger AutoEmpno
beforeinsert on emp for each row
declare
n number;
begin
selectnvl(max(empno),7000)+1into 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
createor replace trigger empTrans
beforeinsert or update or delete on emp
foreach row
declare
wEnd exception;
usr exception;
begin
ifupper(rtrim(to_char(sysdate,'day')))='SAT'or
upper(rtrim(to_char(sysdate,'day')))='SUN' then
raisewEnd;
endif;
ifupper(user)<>'SCOTT'then
raiseusr;
endif;
exception
whenwEnd then
raise_application_error(-20007,'Transactions are not allowed at week ends');
whenusr 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
createor replace trigger restrictDML
beforeinsert or update or delete on emp
foreach row
declare
v varchar2(20);
begin
selectupper(rtrim(to_char(sysdate,'day'))) into v from dual;
ifv='FRIDAY'then
raise_application_error(-20009,'Transactions are not allowed on FRIDAY');
endif;
end;
30) An example of a row trigger follows:
createor replace trigger mytrig2
afterdelete or insert or update on emp
foreach row
begin
ifdeleting then
insertinto emp1 (empno, ename, job, sal, deldate) values
(:old.empno, :old.ename, :old.job,:old.sal, sysdate);
elsifinserting then
insertinto emp2 (empno, ename, job, sal, deldate) values
(:new.empno, :new.ename, :new.job,:new.sal, sysdate);
elsifupdating then
insertinto emp3 (empno, ename, old_sal, new_sal, upddate)
values(:old.empno, :old.ename, :old.sal, :new.sal, sysdate);
endif
end;
/
Procedures
31) PL/SQL block using PROCEDURE that works on dept and emp
createor replace procedure DeptEmp(dno number) is
d dept%rowtype;
e emp%rowtype;
cursorc(d number) isselect * 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('---------------------------');
fore in c(d.deptno)
loop
dbms_output.put_line(e.ename||' - '||e.job);
endloop;
end;
/
32) PL/SQL block using PROCEDURE to find the factorial of given number
createor replace procedure Fact(a in number,b outnumber) is
f number(4):=1;
begin
fori in 1..a
loop
f:=f*i;
endloop;
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
createor replace procedure Arith(a number,b number,c char) is
d number(4);
ex exception;
begin
ifc='+'then
d:=a+b;
elsifc='-'then
d:=a-b;
elsifc='*'then
d:=a*b;
elsifc='/'then
d:=a/b;
elsifc='%'then
d:=mod(a,b);
else
raiseex;
endif;
dbms_output.put_line(a||' '||c||' '||b||' = '||d);
exception
whenex then
dbms_output.put_line(' Not a Valied Operator ');
whenzero_divide then
dbms_output.put_line(' Denominator shouldnot be zero ');
whenothers then
dbms_output.put_line('SQLERROR');
end;
/
Functions
35) PL/SQL block using FUNCTION to find the factorial of given number
createor replace function FunFact(a number) returnnumber is
f number(4):=1;
begin
fori in 1..a
loop
f:=f*i;
endloop;
returnf;
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
createor replace package bank_pack is
minbal exception;
cursorc1(no number) isselect * from transact where accno=no;
procedurenew_acc(name varchar2,oamt number);
functiondeposit(no number,amt number) return number;
functionwithdraw(no number,amt number) return number;
proceduretrans(no number,tty char,amt number);
procedureclose_acc(no number);
proceduredetails(no number);
end;
/
38) PL/SQL block for creating Package Body
createor replace package body bank_pack is
procedurenew_acc(name varchar2,oamt number) is
n number;
begin
selectnvl(max(accno),0)+1into n from bankmast;
ifoamt<1000then
raiseminbal;
endif;
insertinto bankmast values(n,name,oamt);
dbms_output.put_line('New Account for '||name||' is successfully created');
exception
whenminbal then
dbms_output.put_line('Minimum balance should be Rs.1000');
end;
functiondeposit(no number,amt number) return number is
pb number;
begin
selectopamt into pb from bankmast where accno=no;
returnpb+amt;
exception
whenno_data_found then
dbms_output.put_line('Invalied Account Number');
end;
functionwithdraw(no number,amt number) return number is
pb number;
begin
selectopamt into pb from bankmast where accno=no;
ifpb-amt<250then
raiseminbal;
endif;
returnpb-amt;
exception
whenno_data_found then
dbms_output.put_line('Invalied Account Number');
whenminbal then
dbms_output.put_line('Insufficient Balance');
end;
proceduretrans(no number,tty char,amt number) is
bal number;
begin
iftty in ('D','d') then
bal:=deposit(no,amt);
insertinto transact values(no,tty,amt,bal);
updatebankmast 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
insertinto transact values(no,tty,amt,bal);
updatebankmast set opamt=bal where accno=no;
dbms_output.put_line('Withdrawn Rs.'||amt||' from Account No : '||no);
else
raiseminbal;
endif;
endif;
exception
whenminbal then
dbms_output.put_line('Insufficient Balance');
dbms_output.put_line('U need to maintain Min Balance');
end;
procedureclose_acc(no number) is
n bankmast.name%type;
begin
selectname into n from bankmast where accno=no;
deletefrom transact where accno=no;
deletefrom bankmast where accno=no;
dbms_output.put_line('Account of Mr.'||n||' is Closed');
end;
proceduredetails(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('--------------------------------');
fori in c1(no)
loop
dbms_output.put_line(i.accno||' '||i.ttype||' '||i.tamt||' '||i.cbal);
endloop;
end;
end;
/
