RDBMS 5 Slips
Q.1) Consider the following entities and their relationship. [Marks 40]
Drug(d_no, d_name, company, price)
Medical _store(m_no, m_name, m_city, ph_no)
Relationship between Drug and Medical_Store is many-to-many with descriptive
attribute quantity.
Constraints: primary key, foreign key,
m_name and d_name should not be null,
m_city can be pune or pimpri.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a package, which consists of one procedure and one function. Pass drug
number as a parameter to procedure and display details of that drug. Pass city as a
parameter to a function and return total number of medical_store in given city.
2) Write a trigger that restricts insertion and updation of drug having price less than
zero. (Raise user defined exception and give appropriate message)
3) Write a function which will count total number of drugs available in given medical
store.
4) Write a cursor which will display medical store wise drug details with available
quantity.
Drug(d_no, d_name, company, price)
Medical _store(m_no, m_name, m_city, ph_no)
attribute quantity.
Constraints: primary key, foreign key,
m_name and d_name should not be null,
m_city can be pune or pimpri.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a package, which consists of one procedure and one function. Pass drug
number as a parameter to procedure and display details of that drug. Pass city as a
parameter to a function and return total number of medical_store in given city.
2) Write a trigger that restricts insertion and updation of drug having price less than
zero. (Raise user defined exception and give appropriate message)
3) Write a function which will count total number of drugs available in given medical
store.
4) Write a cursor which will display medical store wise drug details with available
quantity.
create table dru
(
dno number(10) primary key,
dname varchar2(20) NOT NULL,
comp varchar2(20),
price number(10)
);
create table med
(
mno number(10) primary key,
mname varchar2(20) NOT NULL,
city varchar2(10)check(city in('pune','pimpari')),
phno number(10)
);
create table d_medi
(
dno number(10) references dru(dno),
mno number(10) references med(mno),
quantity number(20)
);
insert into dru values(111,'crocin','cipla',60);
insert into dru values(112,'combiflame','nit',100);
insert into dru values(113,'sinarest','cipla',150);
insert into dru values(114,'av-10','ayur',120);
insert into dru values(115,'zintac','cipla',90);
insert into med values(1,'sai','pune',9890339055);
insert into med values(2,'dhara','pimpari',9845673255);
insert into med values(3,'adarsh','pune',9567824536);
insert into med values(4,'ravi','pimpari',9890672819);
insert into med values(5,'diya','pune',9860256786);
insert into d_medi values(111,1,20);
insert into d_medi values(112,2,10);
insert into d_medi values(113,3,15);
insert into d_medi values(114,4,22);
insert into d_medi values(115,5,15);
Q.1) Write a package, which consists of one procedure and one function. Pass drug
number as a parameter to procedure and display details of that drug. Pass city as a
parameter to a function and return total number of medical_store in given city.
PACKAGE
create or replace package medi_pack as
procedure pmedi(pno in number);
function fmedi(vcity in varchar2)
return number;
end medi_pack;
/
OUTPUT-
Package created.
create or replace package body medi_pack as
procedure pmedi(pno in number)
as
cursor c2 is select dru.* from dru, med,d_medi
where dru.dno=d_medi.dno and
med.mno=d_medi.mno;
x c2%rowtype;
begin
for x in c2
loop
dbms_output.put_line(x.dno||' '||x.dname ||' '||x.comp||' '||x.price);
end loop;
end pmedi;
function fmedi(vcity in varchar2)
return number
as
cnt number;
begin
select count(med.mno) into cnt from dru,med,d_medi
where dru.dno=d_medi.dno and
med.mno=d_medi.mno;
if SQL%found then
return cnt;
else
return 0;
end if;
end fmedi;
end medi_pack;
/
OUTPUT-
Package body created.
SQL> execute medi_pack.pmedi(115);
111 crocin cipla 60
112 combiflame nit 100
113 sinarest cipla 150
114 av-10 ayur 120
115 zintac cipla 90
PL/SQL procedure successfully completed.
SQL> select medi_pack.fmedi('pune')from dual;
MEDI_PACK.FMEDI('PUNE')
-----------------------
5
Q.2) Write a trigger that restricts insertion and updation of drug having price less than
zero. (Raise user defined exception and give appropriate message)
TRIGGER
zero. (Raise user defined exception and give appropriate message)
TRIGGER
create or replace trigger t8
before insert or update on dru
for each row
begin
if inserting then
if:new.price<0 then
raise_application_error('-20010','cannot insert! price should be above 0');
end if;
end if;
if updating then
if:new.price<0 then
raise_application_error('-20010','cannot insert! price should be above 0');
end if;
end if;
end t8;
/
OUTPUT-
insert into dru values(115,'zintac','cipla',-100);
SQL> insert into dru values(115,'zintac','cipla',-100);
insert into dru values(115,'zintac','cipla',-100)
*
ERROR at line 1:
ORA-20010: cannot insert! price should be above 0
ORA-06512: at "HR.T8", line 4
ORA-04088: error during execution of trigger 'HR.T8'
update dru set price=-100
where dno=115;
SQL> update dru set price=-100
2 where dno=115;
update dru set price=-100
*
ERROR at line 1:
ORA-20010: cannot insert! price should be above 0
ORA-06512: at "HR.T8", line 9
ORA-04088: error during execution of trigger 'HR.T8'
Q.3) Write a function which will count total number of drugs available in given medical
store.
FUNCTION
store.
FUNCTION
create or replace function f4
(vname in varchar2)
return number
as
cnt number;
begin
select count(d_medi.quantity) into cnt from dru,med,d_medi
where dru.dno=d_medi.dno and
med.mno=d_medi.mno and med.mname='sai';
if SQL%found then
return cnt;
else
return 0;
end if;
end f4;
/
OUTPUT-
Function created.
SQL> select f4('sai')from dual;
F4('SAI')
----------
1
CALLING
declare
a varchar2(20);
b number(10);
begin
a:=&a;
b:=f4(a);
if(b>0) then
dbms_output.put_line('total no.of drug is'||b);
else
dbms_output.put_line('no data found');
end if;
end;
/
OUTPUT-
13 /
Enter value for a: 'sai'
old 5: a:=&a;
new 5: a:='sai';
total no.of drug is1
PL/SQL procedure successfully completed.
Q.4) Write a cursor which will display medical store wise drug details with available
quantity.
CURSOR
quantity.
CURSOR
declare
cursor c5 is select mname,dru.*,d_medi.quantity from dru,med,d_medi
where dru.dno=d_medi.dno and
med.mno=d_medi.mno;
x c5%rowtype;
begin
for x in c5
loop
dbms_output.put_line(x.mname||' '||x.dno|| ' '||x.dname||' '|| x.comp||' '||x.price||' '||x.quantity);
end loop;
end;
/
OUTPUT-
sai 111 crocin cipla 60 20
dhara 112 combiflame nit 100 10
adarsh 113 sinarest cipla 150 15
ravi 114 av-10 ayur 120 22
diya 115 zintac cipla 90 15
PL/SQL procedure successfully completed.
Tags:
RDBMS