Slip 20 - C) 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. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure to display car details used on specific day. 2) Write a cursor which will display driver wise car details in the year 2018.

Solution: 

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.