Procedure
Assignments:
SET A:
Consider the following entities and their relationship.
Newspaper (name,language , publisher , cost )
Cities (pincode , city, state)
Relationship between Newspaper and Cities is many-to-many with
descriptive attribute daily required
Constraints: name and pincode primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to calculate city wise total cost of each newspaper
ANS
-- Table Creation
create table newspaper (
name varchar(50) primary key,
language varchar(50),
publisher varchar(50),
cost decimal(10,2)
);
insert into newspaper values ('Jay Baba', 'Marathi', 'Baba group', 3);
insert into newspaper values ('Time of India', 'English', 'Times group', 2);
insert into newspaper values ('Sakal', 'Marathi', 'S Group', 5);
select * from newspaper;
create table cities (
pincode int primary key,
city varchar(50),
state varchar(50)
);
insert into cities values (400001, 'Mumbai', 'Maharashtra');
insert into cities values (700001, 'Kolkata', 'West Bengal');
insert into cities values (600001, 'Chennai', 'Tamil Nadu');
select * from cities;
create table newspaper_cities (
name varchar(50),
foreign key (name) references newspaper (name),
pincode int,
foreign key (pincode) references cities (pincode),
daily_required int
);
insert into newspaper_cities values ('Jay Baba', 400001, 500);
insert into newspaper_cities values ('Jay Baba', 700001, 700);
insert into newspaper_cities values ('Jay Baba', 600001, 70);
insert into newspaper_cities values ('Time of India', 600001, 1000);
insert into newspaper_cities values ('Time of India', 400001, 300);
insert into newspaper_cities values ('Time of India', 700001, 350);
insert into newspaper_cities values ('Sakal', 700001, 1150);
select * from newspaper_cities;
--Create Procedure
create or replace procedure total_cost
is
v_cost newspaper.cost%type;
v_name newspaper_cities.name%type;
cursor c1 is select sum(cost * daily_required) as city_cost, newspaper_cities.name
from newspaper, cities, newspaper_cities
where newspaper_cities.name = newspaper.name and
newspaper_cities.pincode = cities.pincode group by newspaper_cities.name;
Begin
dbms_output.put_line('Total Cost | Newspapers');
dbms_output.put_line('---------------------------');
for rec IN c1 loop
dbms_output.put_line(rec.city_cost|| ' | '|| rec.name);
end loop;
exception
When Too_Many_Rows then
dbms_output.put_line('Too_Many_Rows');
End total_cost;
-- execution section
begin
total_cost();
end;
Output
-------------------------------------------------------------------------------------
2) Write a procedure which display details of news papers having cost greater
than 2 Rs
ANS
-- Create Procedure
create or replace Procedure display
is
cursor c_news is select * from newspaper where cost > 2;
Begin
dbms_output.put_line('Name | Language | Publisher | Cost');
dbms_output.put_line('-------------------------------------');
for rec IN c_news loop
dbms_output.put_line(rec.name||' | '||rec.language||' | '||rec.publisher||' | '||rec.cost);
end loop;
End display;
-- Execution section
begin
display();
end;
Output
---------------------------------------------------------------------------------------------
3) Write a procedure which take display details of cities where English news
paper is supplied.
ANS
--Create Procedure
create or replace Procedure details_cities(lang in newspaper.language%type) is
cursor display is select city, state, language from newspaper, cities, newspaper_cities where
newspaper_cities.name = newspaper.name and
newspaper_cities.pincode = cities.pincode and
language like lang;
Begin
dbms_output.put_line('Cities | State | Language');
dbms_output.put_line('----------------------------');
for rec IN display loop
dbms_output.put_line(rec.city||' | '||rec.state||' | '||rec.language);
end loop;
End details_cities;
--Execution Section
Begin
details_cities('English');
end;
/
----------------------------------------------------------------------------------------
SET B:
Consider the following entities and their relationships.
Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)
Relation between Library and Book is one to many.
Constraint: Primary key, Price should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:
1) Write a procedure which will accept publication name from user and
display details of books published by it.
ANS
--Create Tables
create table Library (
lno int primary key,
lname varchar(50),
location varchar(50),
librarian varchar(50),
no_of_books int
);
insert into Library values (1, 'C D J', 'Shrirampur', 'Prof. More sir', 5000);
insert into Library values (2, 'R B N B', 'Shrirampur', 'Prof. Hire sir', 4000);
select * from library;
create table book (
bid int primary key,
bname varchar(50),
author_name varchar(50),
price int NOT NULL,
publication varchar(50),
lno int,
foreign key (lno) references library (lno)
);
insert into book values (101, 'Math', 'Mr.Govind', 200, 'Sidd', 1);
insert into book values (102, 'Business', 'Mr.Ram', 500, 'Rahul', 1);
insert into book values (103, 'Goal', 'Mr.Ramesh', 400, 'Navnit', 2);
insert into book values (104, 'Communication', 'Mr.Lacy', 1100, 'Eng', 2);
-- Create Procedure
create or replace Procedure book_details(pname in book.publication%type)
is
cursor c1 is select bname, author_name, price, publication
from book, library
where book.lno = library.lno and publication like pname;
Begin
dbms_output.put_line('Book Name | Author Name | Price | Publication Name');
dbms_output.put_line('----------------------------------------------------');
for rec IN c1 loop
dbms_output.put_line(rec.bname||' | '||rec.author_name||' | '||rec.price||' | '||rec.publication);
end loop;
End book_details;
-- Calling Program
Begin
book_details('Sidd');
End;
------------------------------------------------
2) Write a procedure which will accept Library number from user and
display Book name and their price.
ANS
-- Create Procedure
create or replace Procedure display_books (plno IN book.lno%type)
is
cursor c5 is select bname, price from book, library
where book.lno = library.lno and
book.lno like plno;
Begin
dbms_output.put_line('Book Name | Price');
dbms_output.put_line('------------------');
for rec IN c5 loop
dbms_output.put_line(rec.bname||' | '||rec.price);
end loop;
End display_books;
-- Calling Program
Begin
display_books(1);
End;
/
----------------------------------------------------------------------------------
3) Write a procedure to display names of Libraries having books written by
“Mr. Patil”.
ANS
-- Create Procedure
create or replace Procedure librarian_name
is
cursor c6 is select librarian, author_name from book, library
where book.lno = library.lno and
author_name like 'Mr.Patil';
Begin
dbms_output.put_line('Librarian | Written By');
dbms_output.put_line('------------------------');
for rec IN c6 loop
dbms_output.put_line(rec.librarian||' | '||rec.author_name );
end loop;
End librarian_name;
-- Calling Program
Begin
librarian_name();
End;
/
0 Comments