--orderbook2--有書直接插入預(yù)定表booked，用它的trigger update book表
create or replace procedure orderbook2 ( p_isbn in char , p_number in number)
is 
  v_name book.name%TYPE;--用type方法聲明變量
  v_price number;
  v_book number:=0; --判斷有幾條紀(jì)錄
begin
  select count(*) into v_book from book where isbn=p_isbn; --有無此書
  if v_book =0 then 
    dbms_output.put_line('sorry 。沒有此書號(hào)的圖書.您的定購未成功');
  else
    --有書直接插入預(yù)定表booked，用它的trigger update book表
    --update book set shuliang=shuliang - p_number where isbn=p_isbn;
    --更新圖書表
    insert into booked values( p_isbn, p_number,user);
    --更新預(yù)定信息
    commit;
    dbms_output.put_line('您的圖書訂購成功 ' );
    select name,price into v_name,v_price from book
    where isbn = p_isbn;
    dbms_output.put_line('ISBN : ' || p_isbn);
    dbms_output.put_line('書名 : ' || v_name);
    dbms_output.put_line('售價(jià) : ' || v_price);
    dbms_output.put_line('您訂購了 : ' || p_number || '本');
  end if;
end;

--直接插入預(yù)定表booked，用它的trigger update book表
create or replace trigger updatebook after insert on booked for each row
begin
  update book set shuliang=shuliang - :new.benshu where isbn=:new.isbn;
end;

call orderbook2('7-5053-6548-2',30);

--注意：此時(shí)不管任何方式插入 booked表都會(huì)觸發(fā)此trigger.所以，調(diào)用orderbook本身
--將調(diào)用兩次update book.

--目前已經(jīng)缺貨的圖書
create table quehuo(
isbn char(13),
shuliang number(4,0));

--update book之后 trigger 觸發(fā)，更新quehuo
create or replace trigger set_quehuo after update on book
 for each row
begin
  if :new.shuliang <0 then 
   update quehuo set shuliang=:new.shuliang where isbn=:new.isbn ;
   if sql%notfound then 
	--如果原來不缺貨,則插入新紀(jì)錄。使用sql%notfound判斷update是否更新了紀(jì)錄
     insert into quehuo values(:new.isbn,:new.shuliang);
   end if;
  end if;
end;

--測(cè)試
 call orderbook2('7-5053-6548-2',999);
  select * from book;
 call orderbook2('7-5053-6548-7',2);
 select * from book;