Slip 28 - 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 function which accept a table number and display total amount of bill for a specific table 2) Write a cursor which will display table wise menu details.

Solution:

SQL>create table bill(bno int primary key,tableno int,total int);

SQL>insert into bill values(1,201,3);

SQL>create table menu(mno int primary key,dish_no int,discription varchar(15),price int);

SQL>insert into menu values(101,1,'xyz','2000');

SQL>create table bm(bno int,mno int,quantity varchar(20));

SQL>insert into bm values(1,101,'aaa');

1) Write a function which accept a table number and display total amount of bill for a specific table

SQL> set serveroutput on

SQL> create or replace function f1(abc in varchar) return number as xyz number;

  2  begin

  3  select sum(bill.total)into xyz from bill where tableno='203';

  4  if sql %found then

  5  return (xyz);

  6  else

  7  return null;

  8  end if;

  9  end f1;

 10  /

Function created.

SQL> begin

  2  dbms_output.put_line('amt-'||f1('203'));

  3  end;

  4  /

2) Write a cursor which will display table wise menu details

SQL>  set serveroutput on

SQL>  declare

  2   cursor c1 is select  discription,price  from bill,menu,bm where bill.bno=bm.bno and menu.mno=bm.mno order by tableno;

  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. discription ||''||c. price);

 10    end loop;

 11    close c1;

 12    end;

 13  /