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;
/








------------------------------------------------------------------------------------------------------------------