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'
0 Comments