List of Oracle Procedure
Prepared By : Uday Shah –
HOD (IT)
Contact : 7600044051
---------------------------------------------------------------------------------
1. Procedure to Print Name
create or replace procedure P1 as
begin
dbms_output.put_line('Ruparel Education Pvt.
Ltd.-Junagadh');
end p1;
pl/sql Code ::
declare
a
number(3);
begin
for a in
1..5
loop
P1;
end loop;
end;
2. Procedure to insert
Record in Student Table
create or replace procedure P2(xno stud.xno%type,xname stud.xname%type,xcity
stud.xcity%type)as
begin
insert into
stud(no,name,city)values(xno,xname,xcity);
end P2;
pl/sql Code ::
declare
xno
number(3);
xname
varchar2(20);
xcity
varchar2(10);
begin
p2(&xno,'&xname','&xcity');
end;
3. PL/SQL To Display
Record With Exception Handling.
declare
xno
number(3);
xname
varchar2(10);
xcity
varchar2(10);
no
number(3);
begin
xno:=&no;
select
xno,xname,xcity into xno,xname,xcity from stud where no=xno;
dbms_output.put_line('Name
is ::'||xname);
dbms_output.put_line('City
is ::'||xcity);
exception
when
no_data_found then
dbms_output.put_line('Record
Not Found');
end;
4. Procedure to Calculate
Area of Circle.
create or replace procedure area(pi in number,r in number,a
out number)is
begin
a:=pi*power(r,2);
end;
pl/sql Code ::
declare
pi
number:=3.14;
r
number(5);
a
number(8,2);
begin
r:=&r;
area(pi,r,a);
dbms_output.put_line('Answer
is::'||a);
end;
5. Procedure to Calculate
Simple Intrest
create or replace procedure sim(p in number,r in number,n in
number,si out number)is
begin
si:=p*r*n/100;
end;
pl/sql Code ::
declare
p
number(10);
r
number(10);
n
number(10);
si
number(10);
begin
p:=&p;
r:=&r;
n:=&n;
sim(p,r,n);
dbms_output.put_line('Simple
Interest is::'||si);
end;
6. Function To Check Roll
No and Generate New Roll No
create or replace function chek(st varchar2)return
varchar2 is
fnm
varchar2(10);
str
varchar2(10);
i number(10);
roll
number(10);
p
number(10);
c
number(10);
r
number(10);
status
number(10);
begin
p:=1;
roll:=1;
str:=st;
i:=1;
select
count(rollno) into r from stud;
while
i<=r
loop
select
firstnm into fnm from stud where rollno=roll;
if
fnm=str then
p:=0;
/*
return status:=1; */
return
true;
else
p:=1;
roll:=roll+1;
i:=i+1;
end
if;
end loop;
if p=1 then
/* return
status:=0; */
return
false;
end if;
end;
7. PL/SQL to Print Number
in Reverse Number using For Loop
declare
given_no
varchar2(5);
len
number(2);
rever_no
varchar2(5);
begin
given_no :=
'&Enter_no';
len :=
length(given_no);
for cntr in
reverse 1.. len
loop
rever_no
:= rever_no || substr(given_no,cntr,1);
end loop;
dbms_output.put_line('The
given No is ' || given_no);
dbms_output.put_line('The
Reverse No is ' || rever_no);
end;
8. PL/SQL to Print Number
in Reverse Number using While Loop
declare
no
number(8);
m
number(8);
r
number(8);
tot
number(8);
begin
no :=
&Input_no;
tot := 0;
while no
> 0
loop
r
:= mod(no,10);
tot
:= tot *10 + r;
no
:= floor(no /10);
end loop;
dbms_output.put_line('Total
is ' || tot);
end;
9. PL/SQL to Update
Employee Salary with Rs. 10 in inputted Department No.
declare
no
number(3);
begin
update emp
set sal=sal+10 where deptno=&deptno;
if
no:=sql%rowcount > 0 then
dbms_output.put_line('number of row is' || no);
else
dbms_output.put_line(' no row
count');
end if;
end;
10. Example Of Save point
and Commit
declare
t_sal
number(10);
begin
savepoint
s;
update emp
set sal=sal+2000 where ename='&name';
select
sum(sal) into t_sal from emp;
if
t_sal<10000 then
Rollback
to savepoint s;
dbms_output.put_line('Total
salary of all Employee is less then 20000');
end if;
Commit;
end;
11. Code To Create
Snapshot
create snapshot nw_emp pctfree 10 pctused 70 tablespace
system
storage (initial 50k next 50k pctincrease 0)
refresh start with (sysdate+0)+2/24
as select empno,ename,deptno from emp
12. Function to Return
Square of Given Number
create or replace function sqr(n in number) return number is
square number(10);
begin
square:=n*n;
end;
13. Function To Return Sum
of Digit of Inputted Number
create or replace function sod(n in number)
return number is
r number(2);
sm number(3);
no number;
begin
sm:=0;
no:=n;
while no=0
loop
r:=mod(no,10);
sm:=sm+R;
no:=floor(no/10);
end loop;
return sm;
end;
14. PL/SQL to Display and
Update Employee Information
declare
e2
emp%rowtype;
eno
emp.empno%type;
salary
emp.sal%type;
enm
emp.ename%type;
begin
eno:=&eno;
select *
into e2 from emp where empno=eno;
select sal
into salary from emp where empno=eno;
dbms_output.put_line('Employee
no is '||e2.eno);
dbms_output.put_line('Employee
salary is '||e2.salary);
dbms_output.put_line('Employee
name is '||e2.enm);
if
salary<2000 then
update emp
set sal=sal+2000 where empno=eno;
else
update emp
set sal=sal-2000 where empno=eno;
end if;
end;
15. PL/SQL to input 3 number and print Total and
Percentage of it
declare
m1 number(3);
m2 number(3);
m3 number(3);
total number(5);
per number(5);
begin
m1:=&m1;
m2:=&m2;
m3:=&m3;
total:=m1+m2+m3;
per:=total/3;
dbms_output.put_line('Total
of three subjects are='||total);
dbms_output.put_line('Persantage
of three subjects are='||per);
end;
16. Function to Calculate
Multiplication
create or replace function multi(n1 number, n2 number)
return number is
m number;
begin
m:=n1*n2;
return m;
end;
pl/sql Code ::
declare
a number;
b number;
m number;
begin
a:=&a;
b:=&b;
m:=multi(a,b);
dbms_output.put_line('multiplication
is' ||m);
end;
17. Function To Calculate
Maximum Value
create or replace function max1(n1 number, n2 number)
return number is
m number;
begin
if n2>n1
then
m:=n2;
else
m:=n1;
end if;
return m;
end;
pl/sql Code ::
declare
a number;
b number;
ans number;
begin
a:=&a;
b:=&b;
ans:=max1(a,b);
dbms_output.put_line('maximum
is '||ans);
end;
18. Example of While Loop
declare
i
number(3);
begin
i:=1
while
i<=10
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
19. PL/SQL Code to check
input name and if exist then Update it.
declare
no emp.empno%type;
ans
boolean;
dt date;
nm
emp.ename%type;
begin
nm:='&name';
select
empno into no from emp where ename=nm;
if nm=nm then
ans:=true;
else
ans:=false;
end if;
if ans=true
then
goto
status;
else
dbms_output.put_line('empno:'||no||'
inActive'||nm);
end if;
<<status>>
update emp set sal=sal+100 where empno=no;
select hiredate into dt from emp where empno=no;
insert into inact_mstr(acnt_no,opndt)
values(no,dt);
dbms_output.put_line('empno: '||no||' is
marked as Active');
exception
when
no_data_found then
dbms_output.put_line('Invalid Employee
name......'||nm);
end;
20. Cursor to Insert
Update and Audit Employee Records
declare
cursor
c_sal is select empno,ename,sal from emp where deptno=20;
xno
emp.empno%type;
xnm
emp.ename %type;
xsal
emp.sal%type;
no number;
begin
open c_sal;
if c_sal
%isopen then
loop
fetch
c_sal into xno,xnm,xsal;
no:=c_sal
%rowcount;
exit
when no=10;
insert
into new_emp values(xno,xnm,xsal,sysdate);
update
emp set sal=xsal+(xsal*0.10) where no=xno;
end loop;
close
c_sal;
else
dbms_output.put_line('unable
to open cursor');
end if;
end;
21. Cursor To Display
Employee Records
declare
cursor
c_emp is select e.ename,e.deptno,e.sal from emp e,dept d;
enm
emp.ename%type;
dno
emp.deptno%type;
salary
emp.sal%type;
no
number(3);
begin
open c_emp;
dbms_output.put_line('Name
Department Salary');
dbms_output.put_line('----------
---------- ----------');
loop
fetch
c_emp into enm,dno,salary;
no:=c_emp
%rowcount;
dbms_output.put_line(enm||' '||dno||':: ::'||salary);
exit
when no=5;
end loop;
dbms_output.put_line('Number
of Row Is ::'||no);
end;
22. Cursor To display
Employee Name , Salary and Job
declare
cursor
cur1_loop is select ename,sal,job from emp where deptno=&no;
xnm
emp.ename%type;
xsal
emp.sal%type;
xjob
emp.job%type;
begin
open
cur1_loop;
loop
fetch
cur1_loop into xnm,xsal,xjob;
exit
when cur1_loop %notfound;
dbms_output.put_line(xnm||'
Having Salary '||xsal||' Whuch job is '||xjob);
end
loop;
close
cur1_loop;
end;
23. Cursor to Display
Employee Information with %ISOPEN Attribute
declare
cursor
cur_loop is select empno,ename,sal from emp where deptno=&no;
xno
emp.empno%type;
xnm
emp.ename%type;
xsal
emp.sal%type;
begin
open
cur_loop;
if cur_loop
%isopen then
loop
fetch
cur_loop into xno,xnm,xsal;
exit
when cur_loop%notfound;
insert
into new_emp values(xno,xnm,xsal,sysdate);
end loop;
close
cur_loop;
else
dbms_output.put_line('Update
to Open Cursor');
end if;
end;
