Slip 20
Consider the following entities and their
relationships.
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with
date and time as descriptive attribute.
Constraint: Primary key, driver_name should not be
null.
SQL> create table Driver9(D_id int primary
key,Dname varchar(15),Addr varchar(15));
SQL> insert into Driver9
values(1,'Dinesh','Daund');
SQL>create table car9(Lno int primary key ,model
varchar(15),year int);
SQL> insert into car9 values(1,'Swift',2001);
SQL>create table dc9(D_id int, Lno int, day varchar(15));
SQL> insert into dc9 values(1,1,'Monday');
1)Write a procedure to display car details used on
specific day.
SQL> set serveroutput on
SQL> create or replace procedure p1(n in
varchar)as cursor c1 is select Dname,day,model,year from D
river9,car9,dc9 where Driver9.D_id=dc9.D_id and
car9.Lno=dc9.Lno;
2 c c1%rowtype;
3 begin
4 open c1;
5 dbms_output.put_line('Dname'||''||'day'||''||'model'||''||'year');
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 if (c.day = n) then
10
dbms_output.put_line(c.Dname||''||c.day||''||c.model||''||c.year);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('Monday');
3 end;
4 /
Dnamedaymodelyear
DineshMondaySwift2001
PL/SQL procedure successfully completed.
2)Write a cursor which will display driver wise car
details in the year 2018.
SQL> set
serveroutput on
SQL>
declare
2 cursor c1 is select dname,model,year from Driver9,car9,dc9 where
Driver9.d_id=dc9.d_id and
car9.lno=dc9.lno and year ='2001' ;
3 c c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 dbms_output.put_line(c.dname||''||c.model||''||c.year);
10 end
loop;
11 close c1;
12 end;
13 /
DineshSwift2001
RajAudi2001
PL/SQL procedure successfully completed.
