--求一次交易的Totalprice，一次交易后商品的庫存量 
 
 
DROP TABLE SM_SaleOrderList CASCADE CONSTRAINT;
CREATE TABLE SM_SaleOrderList(
    TransactionID Number(10) NOT NULL UNIQUE,
    TotalPrice Number(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    SaleTime DATE NOT NULL,
    PRIMARY KEY(TransactionID));
 
 
DROP TABLE SM_TransactionList CASCADE CONSTRAINT;
CREATE TABLE SM_TransactionList(
    TransactionID Number(10) NOT NULL,
    ItemID CHAR(13) NOT NULL,
    Amount Number(10,0) NOT NULL,
    SerialID NUMBER(13) NOT NULL UNIQUE,
    PRIMARY KEY(SerialID));
 
 
DROP TABLE SM_ItemList CASCADE CONSTRAINT;
CREATE TABLE SM_ItemList(
    ItemID CHAR(13) NOT NULL UNIQUE,
    ItemName VARCHAR(50) NOT NULL,
    Price NUMBER(7,2) NOT NULL,
    Unit VARCHAR(10) NOT NULL,
    ItemAmount number(10,0) NOT NULL,
    PRIMARY KEY(ItemID));

alter table SM_TransactionList add constraint ItemList_fk 
foreign key (ItemID) references SM_ItemList (ItemID);

alter table SM_TransactionList add constraint SaleOrder_fk 
foreign key (TransactionID) references SM_SaleOrderList (TransactionID);

--插入商品表測試數據

insert into sm_itemlist (itemid,itemname,price,unit,itemamount)
values('1111111111111','BOOK',10.2,'本',100);
insert into sm_itemlist (itemid,itemname,price,unit,itemamount)
values('1111111111112','豆腐',10.2,'盒',100);
insert into sm_itemlist (itemid,itemname,price,unit,itemamount)
values('1111111111113','BEER',10.2,'瓶',100);

--注意當數據不完整，如一次交易有兩個serialid,但itemid卻一樣，會出錯。

--第一次交易兩種商品:1號交易  顧客A 賣了兩本書，兩盒豆腐
--必須首先插入父表(商品交易表，其中總價先設為0)
insert into sm_saleorderlist values(1,0,'1234567890',sysdate);
insert into sm_transactionlist (serialid,transactionid,itemid,amount)
values(1,1,'1111111111111',2);


insert into sm_transactionlist (serialid,transactionid,itemid,amount)
values(2,1,'1111111111112',2);

--第二次交易一種商品:2號交易  顧客B 賣了兩本書
--必須首先插入父表(商品交易表，其中總價先設為0)
insert into sm_saleorderlist values(2,0,'1234567891',sysdate);
insert into sm_transactionlist (serialid,transactionid,itemid,amount)
values(3,2,'1111111111111',2);

--第3次交易一種商品:3號交易  顧客C 賣了兩本書
--必須首先插入父表(商品交易表，其中總價先設為0)
insert into sm_saleorderlist values(3,0,'1234567891',sysdate);
insert into sm_transactionlist (serialid,transactionid,itemid,amount)
values(4,3,'1111111111111',2); 

-----------------------測試數據插入完畢------------------------------------------------


--建立一個view,存儲各次交易的總額
create or replace view v_total as 
select transactionid,sum(amount*price) total from sm_itemlist,sm_transactionlist 
where  sm_itemlist.itemid=sm_transactionlist.itemid
group by transactionid;
 
--更新交易紀錄表：sm_saleorderlist中的totalprice
update sm_saleorderlist set totalprice
=(select total from v_total where  
v_total.transactionid=sm_saleorderlist.transactionid);



--逐條更新更符合實際情況.一筆交易完成后，立刻求出其總價。
--可以建一個存儲過程，每次插入交易紀錄表sm_transactionlist后，call,每次只更新一條對應紀錄
--每完成一次交易，用交易號調用此存儲過程，得到本次交易總價
create or replace procedure set_total( p_tranid number) is  
	v_total number;
begin 
  --取出本次交易總價給v_total
        select sum(amount*price) into v_total 
        from sm_itemlist,sm_transactionlist 
        where  
          sm_itemlist.itemid=sm_transactionlist.itemid 
        and 
          sm_transactionlist.transactionid=p_tranid
        group by transactionid;
  --更新sm_saleorderlist相應紀錄
          update sm_saleorderlist set totalprice=v_total
          where transactionid=p_tranid;
  commit;
end;

--第4次交易一種商品：4號交易  顧客C 賣了兩本書
--必須首先插入父表(商品交易表，其中總價先設為0)
insert into sm_saleorderlist values(4,0,'1234567891',sysdate);
insert into sm_transactionlist (serialid,transactionid,itemid,amount)
values(5,4,'1111111111111',2); 
call  set_total(4);


--求出一次交易后，相應庫存為多少：傳入交易號，和商品號，更新商品表相應商品的庫存數量

create or replace procedure set_amount( v_trid number,v_ItemID CHAR) is
v_amount number;
begin
  select amount into v_amount from sm_transactionlist where
  itemid=v_ItemID and transactionid=v_trid;
  
  --求出交易后，庫存
  update sm_itemlist set 
  itemamount=itemamount-v_amount
  where itemid=v_ItemID;
end;

--每次交易后，調用此存儲過程，更改庫存。
call set_amount(1,'1111111111111');