RDBMS SLIP 7 GIVES YOU THE PERFECT KNOWLEDGE

slip 7

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

Table created.

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

Table created.
SQL>insert into bill values( 1,'monday','101','240');
SQL>insert into menu values(1,'paneer','200');
SQL>insert into bm values(1,1);

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 "SYSTEM.T1", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T1'




SQL> create or replace procedure p1(a in varchar) as cursor c1 is select
menu.dish_no,menu.price,bill.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');