Slip 7 - C) Consider the following entities and their relationships. Bill (billno, day, tableno, total) Menu (dish_no, dish_desc, price) The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute. Constraint: Primary key, price should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure to display menu details having price between 200 to 500 which were order on ‘Saturday’ . 2) Write a trigger which will fire before insert or update on Menu having price less than or equal to zero. (Raise user defined exception and give appropriate message)

Solution:

SQL>create table bill(bill_no int primary key,day varchar(10),table_no int,total int);

SQL>create table menu(dish_no int primary key,dish_description varchar(10),price int);

SQL>create table bm(bill_no int,dish_no int);

SQL>insert into bill values( 1,'monday','101','240');

SQL>insert into menu values(1,'paneer','200');

SQL>insert into bm values(1,1);

1) Write a procedure to display menu details having price between 200 to 500 which were order on ‘Saturday’ .

SQL> set serveroutput on;

SQL> create or replace procedure p1(a in varchar) as cursor c1 is select menu.dish_no,menu.price,bil

l.day from bill,menu,bm where bill.bill_no=bm.bill_no and menu.dish_no=bm.dish_no and price between

200 and 500 and day='saturday';

  2   c c1 %rowtype;

  3    begin

  4   open c1;

  5   dbms_output.put_line('dish_no'||''||'price'||''||'day');

  6   loop

  7   fetch c1 into c;

  8   exit when c1 %notfound;

  9   if(c.day=a)then

 10  dbms_output.put_line(c.dish_no||''||c.price||''||c.day);

 11   end if;

 12   end loop;

 13   close c1;

 14   end;

 15  /

Procedure created.

SQL> begin

  2  p1('saturday');

  3  end;

  4  /

dish_nopriceday

3400saturday

2) Write a trigger which will fire before insert or update on Menu having price less than or equal to zero. (Raise user defined exception and give appropriate message)

SQL> set serveroutput on;

SQL> create or replace trigger t1 before insert or update on menu

  2  for each row

  3  begin

  4  if(:new.price<=0)then

  5  raise_application_error(-20001,'price>0');

  6  end if;

  7  end;

  8  /

Trigger created

SQL>  insert into menu values(6,'rice',0);

 insert into menu values(6,'rice',0)

             *

ERROR at line 1:

ORA-20001: price>0

ORA-06512: at "SCOTT.T1", line 3

ORA-04088: error during execution of trigger 'SCOTT.T1'