Rdbms Practical Silps

RDBMS 9 Slips

Q.1) Consider the following entities and their relationship. [Marks 40]
Patient (p_no, p_name, p_addr)
Doctor (d_no, d_name, d_addr, city)

Relationship between Patient and Doctor is many-to-many with descriptive
attribute disease and no_of_visits.
Constraints: primary key, foreign key,
primary key for third table(p_no, d_no, disease),
p_name and d_name 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 display patient detail who has visited more than 3 times
to the given doctor for ‘Diabetes’.

2) Write a trigger which will restrict insertion or updation of doctor_patient details
having no_of_visits less than zero. (Raise user defined exception and give appropriate
message)

3) Write a function which will count total number of patients visiting to given doctor for
‘Asthma’.

4) Write a cursor which will display doctor wise details of patients visited to them. (Use
parameterized cursor)


create table pat
(
pno number(10) primary key,
pname varchar2(20) NOT NULL,
p_add varchar2(20)
);

create table doc
(
dno number(10)primary key,
dname varchar2(20) NOT NULL,
d_add varchar2(10),
city varchar2(10)
);

create table p_doc
(
pno number(10)references pat(pno),
dno number(10)references doc(dno),
disease varchar2(20),
no_visit number(10)
);


insert into pat values(11,'mr.sharma','pune');
insert into pat values(12,'mr.nair','thane');
insert into pat values(13,'mr.joshi','nagar');
insert into pat values(14,'mr.khan','pune');
insert into pat values(15,'mr.yadav','mumbai');

insert into doc values(123,'dr.kadu','katraj','pune');
insert into doc values(124,'dr.kapoor','swargate','pune');
insert into doc values(125,'dr.chilal','ring road','nasik');
insert into doc values(126,'dr.sinha','sp road','thane');
insert into doc values(127,'dr.kadam','kondhwa','pune');

insert into p_doc values(11,123,'diabetes',5);
insert into p_doc values(12,124,'asthma',2);
insert into p_doc values(13,125,'diabetes',4);
insert into p_doc values(14,126,'cancer',5);
insert into p_doc values(15,127,'thyroid',3);

Q.1) Write a procedure which will display patient detail who has visited more than 3 times
to the given doctor for ‘Diabetes’.

PROCEDURE
create or replace procedure p9
(pname in varchar2)
as
cursor c4 is select pat.* from pat,doc,p_doc
where pat.pno=p_doc.pno and
doc.dno=p_doc.dno and
no_visit>3 and
p_doc.disease='diabetes'and
doc.dname='dr.kadu';
x c4%rowtype;
begin
for x in c4
loop
dbms_output.put_line(x.pno||'   '||x.pname||'   '||x.p_add);
end loop;
end p9;
/
OUTPUT-

SQL> execute p9('dr.kadu');
11   mr.sharma   pune

PL/SQL procedure successfully completed.

Q.2) Write a trigger which will restrict insertion or updation of doctor_patient details
having no_of_visits less than zero. (Raise user defined exception and give appropriate
message)

TRIGGER
create or replace trigger t15
before insert or update on p_doc
for each row
begin
if inserting then
if:new.no_visit<0 then
raise_application_error('-20010','No of visit is less than 0');
end if;
end if;
if updating then
if:new.no_visit<0 then
raise_application_error('-20010','No of visit is less than 0');
end if;
end if;
end t11;
/
OUTPUT-

insert into p_doc values(15,127,'thyroid',-3);


Trigger created.

SQL> insert into p_doc values(15,127,'thyroid',-3);
insert into p_doc values(15,127,'thyroid',-3)
            *
ERROR at line 1:
ORA-20010: No of visit is less than 0
ORA-06512: at "SYSTEM.T15", line 4
ORA-04088: error during execution of trigger 'SYSTEM.T15'

update p_doc set no_visit=-3
where disease='thyroid';


SQL> update p_doc set no_visit=-3
  2  where disease='thyroid';
update p_doc set no_visit=-3
       *
ERROR at line 1:
ORA-20010: No of visit is less than 0
ORA-06512: at "SYSTEM.T15", line 9
ORA-04088: error during execution of trigger 'SYSTEM.T15'


Q.3) Write a function which will count total number of patients visiting to given doctor for
‘Asthma’.

FUNCTION
create or replace function f9
(fname in varchar2)
return number
as
cnt number;
begin
select count(p_doc.no_visit)into cnt from pat,doc,p_doc
where pat.pno=p_doc.pno and
doc.dno=p_doc.dno and
disease='asthma';
if SQL%found then
return cnt;
else
return 0;
end if;
end f9;
/
OUTPUT-

Function created.


SQL> select f9('dr.kadam') from dual;

F9('DR.KADAM')
--------------
             1

//CALLING//

declare
a varchar2(10);
b number(10);
begin
a:=&a;
b:=f9(a);
if(b>0) then
dbms_output.put_line('total no of patient is'|| b);
else
dbms_output.put_line('no data found');
end if;
end;
/

OUTPUT-

Enter value for a: 'dr.kadam'
old   5: a:=&a;
new   5: a:='dr.kadam';
total no of patient is1

PL/SQL procedure successfully completed.


Q.4) Write a cursor which will display doctor wise details of patients visited to them. (Use
parameterized cursor)


CURSOR
declare
cursor c5(vname in varchar2)is select dname,pat.*from pat,doc,p_doc
where pat.pno=p_doc.pno and
doc.dno=p_doc.dno order by dname;
x c5%rowtype;
cname doc.dname%type;
begin
cname:=&cname;
for x in c5(cname)
loop
dbms_output.put_line(x.dname||'   '||x.pno||'   '||x.pname||'   '||x.p_add);
end loop;
end;
/
OUTPUT-
Enter value for cname: 'dr.kapoor'
old   8: cname:=&cname;
new   8: cname:='dr.kapoor';
dr.chilal   13   mr.joshi   nagar
dr.kadam   15   mr.yadav   mumbai
dr.kadu   11   mr.sharma   pune
dr.kapoor   12   mr.nair   thane
dr.sinha   14   mr.khan   pune

PL/SQL procedure successfully completed.