Procedure


1)Create or Replace a PL/SQL Procedure to display details of a given employee.

CREATE TABLE DEPARTMENT (DEPTNO NUMBER PRIMARY KEY NOT NULL, DEPTNAME VARCHAR2(20),LOCATION VARCHAR2(20))

insert into department values (1,’Computer’,’pune’)
insert into department values (2,’Marketing’,’pune’)
insert into department values (3,’Production’,’pune’)
insert into department values (4,’Quality’,’pune’)

CREATE TABLE EMPLOYEE(EMPNO NUMBER PRIMARY KEY NOT NULL, EMPNAME VARCHAR2(20),SALARY NUMBER,COMMISSION NUMBER, DESIGNATION VARCHAR2(20),DEPTNO NUMBER REFERENCES DEPARTMENT(DNO))

insert into employee values (1,’Ajay’,10000,500,’Software Enginieer’,1)
insert into employee values (2,’Vijay’,12000,700,’Software Enginieer’,1)
insert into employee values (3,’Sanjay’,10000,500,’Supervisior’,2)
insert into employee values (4,’Jay’,15000,100,’Manager’,3)

Sol.
create or replace procedure empdetails(eno in number)
as
cursor c is select empno,empname,salary,deptno from employee where empno=eno;
c1 c%rowtype;
begin
dbms_output.put_line(‘The Details of Given Employee As Follows’);
dbms_output.put_line(‘Employee NO. Employee Name Employee Salary Employee DeptNO’);
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.empno||’ ‘||c1.empname||’ ‘||c1.salary||’ ‘||c1.deptno);
end loop;
close c;
end;
Calling Program :-

Declare
eno number:=1;
Begin
empdetails(eno );
End;

2)Create or replace procesure Details of all movies of actor Amitabh.
Sol.

CREATE TABLE MOVIE (MVNO NUMBER PRIMARY KEY NOT NULL, MVNAME VARCHAR(20),RELEASEYEAR NUMBER )

insert into Movie values (1,’Lawaris’,1990)
insert into Movie values (2,’Gajani’,2008)
insert into Movie values (3,’Don’,1988)
insert into Movie values (4,’Taal’,2002)
insert into Movie values (5,’Devdas’,2002)

CREATE TABLE ACTOR (ACTNO NUMBER PRIMARY KEY NOT NULL, ACTNAME VARCHAR2(20))

insert into Actor values (1,’Amitabh’)
insert into Actor values (2,’Amir’)
insert into Actor values (3,’Aishwarya’)
insert into Actor values (4,’Anil’)

CREATE TABLE MOVIE_ACTOR (MVNO NUMBER REFERENCES MOVIE(MVNO),ACTNO NUMBER REFERENCES ACTOR(ACTNO),RATE NUMBER)

insert into Movie_Actor values (1,1,200000)
insert into Movie_Actor values (2,2,2000000)
insert into Movie_Actor values (2,4,1000000)
insert into Movie_Actor values (3,1,300000)
insert into Movie_Actor values (4,3,1000000)
insert into Movie_Actor values (5,3,1000000)

Create or Replace Procedure actordetails (actorname1 in varchar2)
AS
Cursor c IS select movie.mvno,movie.mvname,movie.releaseyear from movie,actor, movie_actor where
movie_actor.actno=(select actno from actor where actname=actorname1 ) and movie.mvno= movie_actor .mvno
and actor.actno= movie_actor .actno;
c1 c%rowtype;
Begin
dbms_output.put_line(‘Details of all movies of actor Amitabh Are As Follows :- ‘) ;
dbms_output.put_line(‘Movieno Moviename Releaseyear’);
Open c;
Loop
Fetch c into c1;
Exit when c%notfound;
dbms_output.put_line(c1.mvno||’ ’||c1.mvname||c1.releaseyear);
End loop;
Close c;
End;

Declare
aname varchar2(20):=’Amitabh’;
Begin
actordetails (aname);
End;

3)Create or Replace a PL/SQL Procedure to display details of all books written by ‘Kanetkar’.

Sol.

CREATE TABLE BOOK (BNO NUMBER PRIMARY KEY NOT NULL, BNAME VARCHAR2(20), PUBNAME VARCHAR2(20), PRICE NUMBER);

insert into book values(1,’C’,’BPB’,170)
insert into book values(2,’C++’,’BPB’,270)
insert into book values(3,’Mathematics’,’BPB’,140)
insert into book values(4,’Economics’,’Vision’,170)
insert into book values(5,’Accounting’,’Nirali’,240)

CREATE TABLE AUTHOR(ANO NUMBER PRIMARY KEY NOT NULL, ANAME VARCHAR2(20))

insert into author values(1,’Kanetkar’)
insert into author values(2,’Balgurusamy’)
insert into author values(3,’Wrox’)
insert into author values(4,’Fonde’)

CREATE TABLE BOOK_AUTHOR(BNO NUMBER REFERENCES BOOK(BNO),ANO NUMBER REFERENCES AUTHOR(ANO))
insert into book_author values(1,1)
insert into book_author values(2,1)
insert into book_author values(3,2)
insert into book_author values(4,3)

create or replace procedure detailofkanetkar(aname1 in varchar2)
as
cursor c is select book.bno,book.bname,book.pubname,book.price from book,author,book_author where
book.bno=book_author .bno and author.ano=book_author .ano and author.ano=(select ano from author where aname=aname1);
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.bno||’ ‘||c1.bname||’ ‘||c1.pubname||’ ‘||c1.price);
end loop;
close c;
end;

calling program
Declare
aname varchar2(20):=’Kanetkar’;
Begin
detailofkanetkar(aname);
end;

4)procedure to find total balance of all customers of ‘Pune’ city.
Sol.

CREATE TABLE CUSTOMER (CNO NUMBER PRIMARY KEY NOT NULL, CNAME VARCHAR2(20),CITY VARCHAR2(20))

insert into customer values (1,’Rajesh’,’pune’)
insert into customer values (2,’Girish’,’pune’)
insert into customer values (3,’Nilesh’,’Mumbai’)
insert into customer values (4,’Sandip’,’Mumbai’)
insert into customer values (5,’Raj’,’Nashik’)

CREATE TABLE ACCOUNT (ANO NUMBER PRIMARY KEY NOT NULL,ACC_TYPE VARCHAR2(20),BALANCE NUMBER,CNO NUMBER REFERENCES CUSTOMER(CNO))

insert into account values (1,’Saving’,8000,1)
insert into account values (2,’Current’,12000,2)
insert into account values (3,’Saving’,9000,3)
insert into account values (4,’Current’,11000,4)
insert into account values (5,’Saving’,14000,5)

create or replace procedure totalbalance (ccity in varchar2)
as
cursor d is select balance from account, customer where customer.cno =account.cno
and customer.city= ccity;
d1 d%rowtype;
result number:=0;
begin
open d;
loop
fetch d into d1;
exit when d%notfound;
result:= result+d1.balance;
end loop;
close d;
dbms_output.put_line(result);
end;
Calling Program
declare
ccity varchar2(20):=’pune’;
begin
totalbalance (ccity);
end;

5)PL/SQL Procedure to display details of all hospitals in ‘Pune’.

CREATE TABLE DOCTOR(DNO NUMBER PRIMARY KEY NOT NULL,DNAME VARCHAR2(20),DCITY VARCHAR2(20))

insert into doctor values (1,’Ganesh’,’Pune’);
insert into doctor values (2,’Rajesh’,’Pune’);
insert into doctor values (3,’Sachin’,’Nashik’);
insert into doctor values (4,’Swapnil’,’Mumbai’);
insert into doctor values (5,’Nilesh’,’Karad’);
CREATE TABLE HOSPITAL(HNO NUMBER PRIMARY KEY NOT NULL,HNAME VARCHAR2(20),HCITY VARCHAR2(20))

insert into HOSPITAL values (1,’KEM’,’Pune’);
insert into HOSPITAL values (2,’Sasun’,’Pune’);
insert into HOSPITAL values (3,’Krishna’,’Karad’);
insert into HOSPITAL values (4,’Lilavati’,’Mumbai’);

CREATE TABLE DOCTOR_HOSPITAL(DNO NUMBER REFERENCES DOCTOR (DNO),HNO NUMBER REFERENCES HOSPITAL (HNO))

insert into DOCTOR_HOSPITAL values (1,1);
insert into DOCTOR_HOSPITAL values (1,2);
insert into DOCTOR_HOSPITAL values (2,1);
insert into DOCTOR_HOSPITAL values (3,3);

create or replace procedure alldet(city1 in varchar2)
as
cursor c is select hno,hname,hcity from hospital where hcity=city1;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.hno||c1.hname||c1.hcity);
end loop;
close c;
end ;

declare

city varchar2(20):=’Pune’;

begin
alldet(city);

end;

6) PL/SQL Procedure to display details of all students of class ‘FY’.
sol.

CREATE TABLE STUDENT(ROLLNO NUMBER PRIMARY KEY NOT NULL,NAME VARCHAR2(20),TOTALMARKS NUMBER,CLASS VARCHAR2(20))

insert into STUDENT values(1,’Jay’,380,’TY’)
insert into STUDENT values(2,’Ajay’,480,’TY’)
insert into STUDENT values(3,’Vijay’,390,’FY’)
insert into STUDENT values(4,’Sanjay’,420,’TY’)
insert into STUDENT values(5,’Manjit’,380,’SY’)

CREATE TABLE TEACHER(TNO NUMBER PRIMARY KEY NOT NULL,TNAME VARCHAR2(20))

insert into TEACHER values(1,’Nilesh’)
insert into TEACHER values(2,’Rajesh’)
insert into TEACHER values(3,’Sachin’)
insert into TEACHER values(4,’Ranjeet’)

CREATE TABLE STUDENT_TEACHER(ROLLNO NUMBER REFERENCES STUDENT(ROLLNO),TNO NUMBER REFERENCES TEACHER(TNO),SUBJECT VARCHAR2(20))

insert into STUDENT_TEACHER values(1,1,’Data Structure’)
insert into STUDENT_TEACHER values(2,2,’Data Structure’)
insert into STUDENT_TEACHER values(3,3,’Account’)
insert into STUDENT_TEACHER values(1,4,’C’)
insert into STUDENT_TEACHER values(4,1,’Data Structure’)

create or replace procedure stu1(class1 in varchar2)
as
cursor c is select rollno,name,totalmarks,class from student where
class=class1;
c1 c%rowtype;
begin
open c ;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(C1.rollno||c1.name||c1.totalmarks||c1.class);
end loop;
close c;
end;

calling program

declare
class varchar2(20):=’FY’;
begin
stu1(class);
end;

7)procedure to increment number_of_days by 2 of all employees working on project having status progressive.
sol.

CREATE TABLE EMPLOYEE(ENO NUMBER PRIMARY KEY NOT NULL ENABLE,ENAME VARCHAR2(20),CITY VARCHAR2(20),DEPTNAME VARCHAR2(20))

insert into employee values (1,’Ajay’,’Pune’,’Computer’)
insert into employee values (2,’Vijay’,’Pune’,’Computer’)
insert into employee values (3,’Sanjay’,’Pune’,’Marketing’)
insert into employee values (4,’Jay’,’Pune’,’Production’)

CREATE TABLE PROJECT(PNO NUMBER PRIMARY KEY NOT NULL,PNAME VARCHAR2(20),STATUS VARCHAR2(20))

insert into PROJECT values (1,’Shree’,’C’)
insert into PROJECT values (2,’Jai’,’P’)
insert into PROJECT values (3,’Om’,’P’)
insert into PROJECT values (4,’Sai’,’I’)
CREATE TABLE EMPLOYEE_PROJECT(ENO NUMBER REFERENCES EMPLOYEE (ENO),PNO NUMBER REFERENCES PROJECT (PNO),NUMBEROFDAY NUMBER)

insert into EMPLOYEE_PROJECT values (1,1,45)
insert into EMPLOYEE_PROJECT values (2,1,65)
insert into EMPLOYEE_PROJECT values (3,2,25)
insert into EMPLOYEE_PROJECT values (2,3,35)
insert into EMPLOYEE_PROJECT values (4,3,75)
insert into EMPLOYEE_PROJECT values (2,4,35)
insert into EMPLOYEE_PROJECT values (4,4,75)

create or replace procedure increment1
as
cursor c is select employee.eno,project.pno,NUMBEROFDAY from employee_project,employee,project where
employee_project.eno=employee.eno and employee_project.pno=project.pno
and project.status=’P’;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
update employee_project set NUMBEROFDAY =c1.NUMBEROFDAY +2 where pno=c1.pno and eno=c1.eno ;
dbms_output.put_line(‘Record Updated’);
end loop;
close c;
end;
calling program
declare
begin
increment1();
end;

8)PL/SQL Procedure to display details of all teachers who are teaching subject ‘Data Structure’.

Sol.

CREATE TABLE STUDENT(ROLLNO NUMBER PRIMARY KEY NOT NULL,NAME VARCHAR2(20),TOTALMARKS NUMBER,CLASS VARCHAR2(20))

insert into STUDENT values(1,’Jay’,380,’TY’)
insert into STUDENT values(2,’Ajay’,480,’TY’)
insert into STUDENT values(3,’Vijay’,390,’FY’)
insert into STUDENT values(4,’Sanjay’,420,’TY’)
insert into STUDENT values(5,’Manjit’,380,’SY’)

CREATE TABLE TEACHER(TNO NUMBER PRIMARY KEY NOT NULL,TNAME VARCHAR2(20))

insert into TEACHER values(1,’Nilesh’)
insert into TEACHER values(2,’Rajesh’)
insert into TEACHER values(3,’Sachin’)
insert into TEACHER values(4,’Ranjeet’)

CREATE TABLE STUDENT_TEACHER(ROLLNO NUMBER REFERENCES STUDENT(ROLLNO),TNO NUMBER REFERENCES TEACHER(TNO),SUBJECT VARCHAR2(20))

insert into STUDENT_TEACHER values(1,1,’Data Structure’)
insert into STUDENT_TEACHER values(2,2,’Data Structure’)
insert into STUDENT_TEACHER values(3,3,’Account’)
insert into STUDENT_TEACHER values(1,4,’C’)
insert into STUDENT_TEACHER values(4,1,’Data Structure’)

create or replace procedure notc (sub1 in varchar2)
as
cursor c is select distinct teacher.tno,teacher.tname from student,teacher,student_teacher
where student.rollno=student_teacher.rollno and
teacher.tno=student_teacher.tno and student_teacher.subject=sub1
c1 c%rowtype;

begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.tno||’ ‘||c1.tname);
end loop;
close c;
end;

calling program

declare
sub varchar2(20):=’Data Structure’;
begin
notc(sub);
end;

9)PL/SQL procedure to display customer details who have taken maximum loan from ‘Pune’ city

Sol.

CREATE TABLE CUSTOMER (CNO NUMBER PRIMARY KEY NOT NULL, CNAME VARCHAR2(20),CITY VARCHAR2(20))

insert into customer values (1,’Rajesh’,’pune’)
insert into customer values (2,’Girish’,’pune’)
insert into customer values (3,’Nilesh’,’Mumbai’)
insert into customer values (4,’Sandip’,’Mumbai’)
insert into customer values (5,’Raj’,’Nashik’)

CREATE TABLE LOAN (LNO NUMBER PRIMARY KEY NOT NULL , LOAN_AMT NUMBER, NO_OF_YEARS NUMBER , CNO NUMBER REFERENCES CUSTOMER(CNO))

insert into Loan values (1,20000,4,1)
insert into Loan values (2,10000,2,2)
insert into Loan values (3,30000,5,3)
insert into Loan values (4,50000,7,4)

create or replace procedure maxloan(ccity in varchar2)
as
cursor d is select customer.cno,cname,city from loan, customer where customer.cno =loan.cno
and customer.city= ‘pune’ and loan_amt=(select max(loan_amt) from loan, customer where customer.cno =loan.cno
and customer.city= ‘pune’ );
d1 d%rowtype;
result number:=0;
begin
open d;
loop
fetch d into d1;
exit when d%notfound;
dbms_output.put_line(d1.cno||’ ‘||d1.cname||’ ‘||d1.city );
end loop;
close d;
end;

calling program

declare
city varchar2(20):=’pune’;
begin
maxloan(city);
end;

10)SQL Procedure to display details of all actors acted in movie ‘Race’.
Sol.

CREATE TABLE MOVIE (MVNO NUMBER PRIMARY KEY NOT NULL, MVNAME VARCHAR(20),RELEASEYEAR NUMBER )

insert into Movie values (1,’Race’,2008)
insert into Movie values (2,’Gajani’,2008)
insert into Movie values (3,’Don’,1988)
insert into Movie values (4,’Taal’,2002)
insert into Movie values (5,’Devdas’,2002)

CREATE TABLE ACTOR (ACTNO NUMBER PRIMARY KEY NOT NULL, ACTNAME VARCHAR2(20))

insert into Actor values (1,’Saif’)
insert into Actor values (2,’Amir’)
insert into Actor values (3,’Akshay’)
insert into Actor values (4,’Anil’)

CREATE TABLE MOVIE_ACTOR (MVNO NUMBER REFERENCES MOVIE(MVNO),ACTNO NUMBER REFERENCES ACTOR(ACTNO),RATE NUMBER)

insert into Movie_Actor values (1,1,4000000)
insert into Movie_Actor values (2,4,1000000)
insert into Movie_Actor values (1,3,300000)
insert into Movie_Actor values (1,4,3000000)
create or replace procedure noofactor1(moviename1 in varchar2)
as
cursor c is select actor.actno,actname,rate,mvname from actor,movie,movie_actor
where actor.actno=movie_actor.actno and movie.mvno=movie_actor.mvno
and mvname=moviename1 ;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.actno||’ ‘||c1.actname||’ ‘||c1.rate||’ ‘||c1.mvname );
end loop;
close c;
end;

Declare
moviename varchar2(20):=’Race’;
begin
noofactor1(moviename);
end;

11)procedure that will accept any two account number and transfer withdrawal amount from first account to second account.
Sol

CREATE TABLE CUSTOMER (CNO NUMBER PRIMARY KEY NOT NULL, CNAME VARCHAR2(20),CITY VARCHAR2(20))

insert into customer values (1,’Rajesh’,’pune’)
insert into customer values (2,’Girish’,’pune’)
insert into customer values (3,’Nilesh’,’Mumbai’)
insert into customer values (4,’Sandip’,’Mumbai’)
insert into customer values (5,’Raj’,’Nashik’)

CREATE TABLE ACCOUNT (ANO NUMBER PRIMARY KEY NOT NULL,ACC_TYPE VARCHAR2(20),BALANCE NUMBER,CNO NUMBER REFERENCES CUSTOMER(CNO))

insert into account values (1,’Saving’,8000,1)
insert into account values (2,’Current’,12000,2)
insert into account values (3,’Saving’,9000,3)
insert into account values (4,’Current’,11000,4)
insert into account values (5,’Saving’,14000,5)

create or replace procedure upbalance(acc1 in number,acc2 in number, wbal in number)
as
cursor c is select balance from account where ano=acc1;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
if c1.balance>wbal then
update account set BALANCE=(balance-wbal) where ano=acc1;
update account set BALANCE=(balance+wbal) where ano=acc2;
dbms_output.put_line(‘Updated’);
else
dbms_output.put_line(‘No sufficient balance in account :’||acc1);
end if;
end loop;
close c;
end;

procedure calling prog.
declare
c varchar2(20):=’vision’;
begin
upbalance(1,2,1000);
end;

or another answer

create or replace procedure ChangeBal(accno1 in number,accno2 in number,deductbal in number)
as
cursor c is select ano, balance from account where ano=accno1 ;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
update account set BALANCE=(c1.BALANCE-deductbal) where ano=accno1 ;
update account set BALANCE=BALANCE+deductbal where ano=accno2 ;
dbms_output.put_line(‘Balance is Updated’);
end loop;
close c;
end;

declare
a1 number :=2;
a2 number :=5;
a3 number :=2000;
begin
Changebal(a1,a2,a3);
end;

12)Procedure to find number of employees of a given department working on project having status ‘Incomplete’.
Sol.

CREATE TABLE EMPLOYEE(ENO NUMBER PRIMARY KEY NOT NULL ENABLE,ENAME VARCHAR2(20),CITY VARCHAR2(20),DEPTNAME VARCHAR2(20))
insert into employee values (1,’Ajay’,’Pune’,’Computer’)
insert into employee values (2,’Vijay’,’Pune’,’Computer’)
insert into employee values (3,’Sanjay’,’Pune’,’Marketing’)
insert into employee values (4,’Jay’,’Pune’,’Production’)

CREATE TABLE PROJECT(PNO NUMBER PRIMARY KEY NOT NULL,PNAME VARCHAR2(20),STATUS VARCHAR2(20))

insert into PROJECT values (1,’Shree’,’C’)
insert into PROJECT values (2,’Jai’,’P’)
insert into PROJECT values (3,’Om’,’P’)
insert into PROJECT values (4,’Sai’,’I’)
CREATE TABLE EMPLOYEE_PROJECT(ENO NUMBER REFERENCES EMPLOYEE (ENO),PNO NUMBER REFERENCES PROJECT (PNO),NUMBEROFDAY NUMBER)

insert into EMPLOYEE_PROJECT values (1,1,45)
insert into EMPLOYEE_PROJECT values (2,1,65)
insert into EMPLOYEE_PROJECT values (3,2,25)
insert into EMPLOYEE_PROJECT values (2,3,35)
insert into EMPLOYEE_PROJECT values (4,3,75)
insert into EMPLOYEE_PROJECT values (2,4,35)
insert into EMPLOYEE_PROJECT values (4,4,75)

create or replace procedure noofemp1(dname1 in varchar2)

as
cursor c is select employee.eno from employee,project,employee_project where
employee.eno= employee_project.eno and project.pno=employee_project.pno and
employee.DEPTNAME=dname1 and project.status=’I’;
c1 c%rowtype;
result number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
result :=result +1;
end loop;
close c;
dbms_output.put_line(result );
end;
calling program
declare
dname varchar2(20):=’Testing’;
begin
noofemp1(dname );
end;

13)procedure to display name of department spending maximum amount on books.

Sol.

CREATE TABLE DEPARTMENT (DNO NUMBER PRIMARY KEY NOT NULL, DNAME VARCHAR2(20));

insert into Department values(1,’Computer’)
insert into Department values(2,’Science’)
insert into Department values(3,’Arts’)
insert into Department values(4,’Commerce’)

CREATE TABLE BOOK (BNO NUMBER PRIMARY KEY NOT NULL, BNAME VARCHAR2(20), PUBNAME VARCHAR2(20), PRICE NUMBER, DNO NUMBER REFERENCES DEPARTMENT(DNO));

insert into book values(1,’C’,’BPB’,170,1)
insert into book values(2,’C++’,’BPB’,270,1)
insert into book values(3,’Mathematics’,’BPB’,140,2)
insert into book values(4,’Economics’,’Vision’,170,3)
insert into book values(5,’Accounting’,’Nirali’,240,4)

create or replace procedure maxamount
as
cursor c is select department.dname,sum(book.price)as price1 from department,book
where book.dno=department.dno group by department.dname;
c1 c%rowtype;
result number:=0;
departmentname varchar2(20);
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
if (result<c1.price1) then
result :=c1.price1;
departmentname :=c1.dname;
end if;
end loop;
close c;
dbms_output.put_line(departmentname);
end;

calling program

declare
begin
maxamount();
end;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s