ddl1_sm_ok_new.txt

orale培训教材包括了所有的sql说明和实例

--求一次交易的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');