create table Patient(p_no number primary key, p_name varchar2(20),p_addr varchar2(20));
create table Doctor(d_no number primary key, d_name varchar2(20), d_addr varchar2(20), city varchar2(20));
create table P_D(p_no number references Patient(p_no),d_no number references Doctor(d_no),disease varchar2(20),no_of_visits number,primary key(p_no,d_no,disease));
insert into Patient values(1,'raj','pune');
insert into Patient values(2,'rajesh','pune');
insert into Patient values(3,'mukesh','mumbai');
insert into Doctor values(101,'yogesh','pune','pune');
insert into Doctor values(102,'dipali','pune','pune');
insert into Doctor values(103,'amol','varsova','mumbai');
insert into P_D values(1,101,'swineflue',7);
insert into P_D values(1,101,'dehydrtion ',7);
insert into P_D values(1,102,'dehydrtion ',3);
insert into P_D values(2,102,'diabetes ',7);
insert into P_D values(3,103,'diabetes ',2);
insert into P_D values(1,101,'diabetes',4);
insert into P_D values(1,101,'asthma',4);
insert into P_D values(2,101,'asthma',4);
insert into P_D values(2,101,'diabetes',5);
insert into P_D values(3,101,'diabetes',6);
Q1)
create or replace procedure p1(s_name in varchar2)
as
cursor c1 is select p.* from Patient p,Doctor d,P_D pd
where p.p_no=pd.p_no and d.d_no=pd.d_no
and pd.disease='diabetes' and d.d_name=s_name and pd.no_of_visits>3;
begin
for abc in c1
loop
dbms_output.put_line('patient no:='||abc.p_no||'patient name:=' ||abc.p_name|| 'patient address:='||abc.p_addr);
end loop;
end;
declare
begin
p1('yogesh');
end;
Q2)
create or replace trigger trg
before insert or update on P_D
for each row
begin
if:new.no_of_visits<0 then
raise_application_error(-2010,'no_of_visits less than zero');
end if;
end;
Recent Posts
Posted on 2019-07-18
Posted on 2019-07-18
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-07-17
Posted on 2019-05-28
Posted on 2019-05-24
Posted on 2019-05-24
Posted on 2019-05-23
Posted on 2019-05-23
Posted on 2019-05-23
Posted on 2019-05-23
Posted on 2019-05-23
Posted on 2019-05-23
Posted on 2019-05-23
Posted on 2019-05-23
Posted on 2019-05-23