开发中遇见一个问题,关于trigger,内有详细说明,望大家相助!

Question

小弟初学TRIGGER，遇见一些问题。
这个TRIGGER的功能是在，当wis_purchaseorder这个表中的status列被UPDATE为C时，从刚才那条数据里拿出PO_ID,在wis_purchaseorderdtl和wis_transaction和wis_deliverynotes 3个表中拿出Item_ID,Center_ID,Balance 3个数据(返回的可以为多行),然后根据Item_ID和Center_ID在wis_master寻找记录,如果wis_master表中有记录就update,无记录就insert现在遇见问题,不知如何把触发trigger时的PO_ID传到第一个游标getItemCenter中.望各位大虾霸道相助.create or replace trigger purchaseorder_status_trigger
    after update
      of status,PO_ID
      on HKBN.wis_purchaseorder
      referencing old as old_value
                  new as new_value
    for each row
        when( new_value.status <> 'C')
     declare
    ITEM char(32);
    CENTER char(32);
    BALANCE NUMBER;
    STOCKIN NUMBER;
 
    cursor getItemCenter IS select p.Item_ID,p.Center_ID,d.Balance from HKBN.wis_purchaseorderdtl p left join HKBN.wis_transaction t on p.POD_ID = t.POD_ID left join HKBN.wis_deliverynotes d on t.DN_ID = d.DN_ID where p.PO_ID = new_value.PO_ID;
    cursor todoMaster IS select stockin from HKBN.wis_master where item_id = ITEM and center_id = CENTER;       begin
      if getItemCenter%isopen =false
         THEN            open getItemCenter;
            fetch getItemCenter into ITEM,CENTER,BALANCE;
            while getItemCenter%found
            loop                        open todoMaster;                            fetch todoMaster into STOCKIN;
                            begin
                            if todoMaster%FOUND
                              then
                             --update statement                             update HKBN.wis_master set stockin = stockin + BALANCE where center_id = CENTER and item_id = ITEM;
                             else
                             --insert statement                             insert into HKBN.wis_master (MR_ID,ITEM_ID,CENTER_ID,STOCKIN) values(SYS_GUID(),ITEM,CENTER,BALANCE);
                             END IF;                             end;                       close todoMaster;
            fetch getItemCenter into ITEM,CENTER;
            end loop;
            close getItemCenter;
            END IF;
            END;

Answer 1

改用游标变量,再使用绑定变量吧!!
create   or   replace   trigger   purchaseorder_status_trigger 
        after   update 
            of   status,PO_ID 
            on   HKBN.wis_purchaseorder 
            referencing   old   as   old_value 
                                    new   as   new_value 
        for   each   row 
                when(   new_value.status   <>   'C') 
          declare 
        ITEM   char(32); 
        CENTER   char(32); 
        BALANCE   NUMBER; 
        STOCKIN   NUMBER; 
        cur is ref cursor;
        cur  getItemCenter;
        cursor   todoMaster   IS   select   stockin   from   HKBN.wis_master   where   item_id   =   ITEM   and   center_id   =   CENTER;               begin 
            open getItemCenter for 'select   p.Item_ID,p.Center_ID,d.Balance   from   HKBN.wis_purchaseorderdtl   p   left   join   HKBN.wis_transaction   t   on   p.POD_ID   =   t.POD_ID   left   join   HKBN.wis_deliverynotes   d   on   t.DN_ID   =   d.DN_ID   where   p.PO_ID   = '||new_value.PO_ID;             if   getItemCenter%isopen   =false 
                  THEN                         open   getItemCenter; 
                        fetch   getItemCenter   into   ITEM,CENTER,BALANCE; 
                        while   getItemCenter%found 
                        loop                                                 open   todoMaster;                                                         fetch   todoMaster   into   STOCKIN; 
                                                        begin 
                                                        if   todoMaster%FOUND 
                                                            then 
                                                          --update   statement                                                           update   HKBN.wis_master   set   stockin   =   stockin   +   BALANCE   where   center_id   =   CENTER   and   item_id   =   ITEM; 
                                                          else 
                                                          --insert   statement                                                           insert   into   HKBN.wis_master   (MR_ID,ITEM_ID,CENTER_ID,STOCKIN)   values(SYS_GUID(),ITEM,CENTER,BALANCE); 
                                                          END   IF;                                                           end;                                               close   todoMaster; 
                        fetch   getItemCenter   into   ITEM,CENTER; 
                        end   loop; 
                        close   getItemCenter; 
                        END   IF; 
                        END; 

Answer 2

给个例子给你看看,希望有帮助!!
 declare
 x int;
 y test%rowtype;
 type cur is ref cursor;
 cur_t cur;
 begin
 x:=5;
 open cur_t for 'select * from test where id>'||x;
 fetch cur_t into y;
 close cur_t;
 end;

Answer 3

把我上面写的语句中的
  open   getItemCenter   for   'select       p.Item_ID,p.Center_ID,d.Balance       from       HKBN.wis_purchaseorderdtl       p       left       join       HKBN.wis_transaction       t       on       p.POD_ID       =       t.POD_ID       left       join       HKBN.wis_deliverynotes       d       on       t.DN_ID       =       d.DN_ID       where       p.PO_ID       =   ' ¦ ¦new_value.PO_ID; 
  
也可以改成:
open   getItemCenter   for   'select       p.Item_ID,p.Center_ID,d.Balance       from       HKBN.wis_purchaseorderdtl       p       left       join       HKBN.wis_transaction       t       on       p.POD_ID       =       t.POD_ID       left       join       HKBN.wis_deliverynotes       d       on       t.DN_ID       =       d.DN_ID       where       p.PO_ID =:X' using new_value.PO_ID;你试试哪个性能更好些

Answer 4

 create or replace trigger purchaseorder_status_trigger   
                after update   
                        of status,PO_ID   
                        on HKBN.wis_purchaseorder   
                        referencing old as old_value   
                                    new as new_value   
                for each row   
                when(new_value.status<>'C')   
                    declare   
                ITEM       char(32);   
                CENTER       char(32);   
                BALANCE       NUMBER;   
                STOCKIN       NUMBER;   
                type cur is ref cursor; 
                getItemCenter cur; 
                cursor todoMaster IS select stockin from HKBN.wis_master where item_id  = ITEM and center_id =  CENTER;   
                  begin   
                    open getItemCenter FOR 'select p.Item_ID,p.Center_ID,d.Balance from HKBN.wis_purchaseorderdtl p left join HKBN.wis_transaction t on p.POD_ID = t.POD_ID left join HKBN.wis_deliverynotes d on t.DN_ID = d.DN_ID where p.PO_ID   =:X'using new_value.PO_ID;    
                        if getItemCenter%isopen =false   
                                    THEN   
                                       open getItemCenter;   
                                       fetch getItemCenter into ITEM,CENTER,BALANCE;   
                                                while  getItemCenter%found   
                                                loop   
                                                  open todoMaster;   
                                                  fetch todoMaster into STOCKIN;   
                                                        begin   
                                                        if todoMaster%FOUND   
                                                           then   
                                                           --update       statement   
                                                           update HKBN.wis_master set stockin = stockin + BALANCE where center_id = CENTER and item_id = ITEM;   
                                                        else   
                                                           --insert       statement   
                                                           insert       into       HKBN.wis_master       (MR_ID,ITEM_ID,CENTER_ID,STOCKIN)       values(SYS_GUID(),ITEM,CENTER,BALANCE);   
                                                        END IF;   
                                                        end;   
                                                        close todoMaster;   
                                                  fetch getItemCenter into ITEM,CENTER;   
                                                end loop;   
                                                close getItemCenter;   
                       END IF;   
                       END;   
报错说new_value.PO_ID没定义？