orcal数据库

    练练游标吧，触发器不太好弄。

存储过程,触发器,包,游标的使用
create or replace procedure Stat_Order
as
--声明变量OrderID_，amount_
--分别用来暂存定单号和总金额
OrderID_ zwd_orders.id%type;
amount_ zwd_orders.amount%type;
--创建游标detail_cur
cursor detail_cur is
select id from zwd_orders;
begin
     --打开游标
     open detail_cur;
     --提取游标
     --FETCH <游标名> INTO <变量列表>
     fetch detail_cur into OrderID_;
     --循环进行
     loop
     --如果游标到结尾则结束
     exit when not detail_cur%found;
          --查询相关结果并将其保存到amount_中
          --其中round(sum(num*price),0)为对sum(num*price)
          --所求值进行四舍五入到小数点第0位
          select sum(num*price) into amount_
                 from zwd_orderdetails
                 where orderid=orderid_;
          select round(amount_,0) into amount_ from dual;
          --更新结果
          update zwd_orders set Amount=round(amount_,0)
                 where id=orderid_;
          --再次提取游标（不可少，否则出现死循环）
          fetch detail_cur into OrderID_;
     end loop;
     CLOSE detail_cur;
end Stat_Order;

--创建计算订单总金额和状态的存储过程
--参数Order_id为订单的ID号
create or replace procedure statisticsOrders
( Order_id zwd_Orders.id%type
)
as
MinStatus zwd_Orders.status%type; --订单状态
a number(16,2); --总金额
begin
  select min(status),sum(num*price) into MinStatus,a
     from zwd_orderdetails where OrderID=Order_ID;
  Update zwd_Orders set Status=MinStatus,Amount=a  
     where id=Order_id;
end statisticsOrders;

--创建程序包,设置下面两个触发器用的全局变量
CREATE OR REPLACE Package Pkg_ORDERDETAILS_Update
as
  GV_NEWID ZWD_ORDERS.ID%TYPE;
  GV_OLDID ZWD_ORDERS.ID%TYPE;
End Pkg_ORDERDETAILS_Update;

--创建行方式触发器
create or replace trigger TRG_ORDERDETAILS_ROW
  after insert or update or delete  on zwd_orderdetails  
  for each row
declare
begin
  Pkg_ORDERDETAILS_Update.GV_OLDID:=:Old.ORDERID;
  Pkg_ORDERDETAILS_Update.GV_NEWID:=:NEW.ORDERID;
end TRG_ORDERDETAILS;

--创建表方式触发器
create or replace trigger TRG_ORDERDETAILS_TABLE
  after insert or update or delete on zwd_orderdetails  
declare
begin
  IF Pkg_ORDERDETAILS_Update.GV_NEWID IS NOT NULL THEN
    statisticsOrders(Pkg_ORDERDETAILS_Update.GV_NEWID);
  END IF;
  
  IF Pkg_ORDERDETAILS_Update.GV_OLDID  IS NOT NULL
   AND 
   (
    Pkg_ORDERDETAILS_Update.GV_NEWID IS NULL OR 
    Pkg_ORDERDETAILS_Update.GV_NEWID<>Pkg_ORDERDETAILS_Update.GV_OLDID
    )
  THEN
    statisticsOrders(Pkg_ORDERDETAILS_Update.GV_OLDID); 
  END IF; 
end TRG_ORDERDETAILS;