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:=#
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:=#
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