plsql 触发器 循环 数组

Question

create or replace trigger tab7005_tri
before update of OPPTIME,SQDJ,HYZCH,ZCSWSIDM,XM,XB,ZZMM,SFZHM,ZYNX,JRSWSSJ,XLXW,WYCD,ZYZK,HYZSBH,ZGZSBH,XYZCZG,XRZW,DRSWSZSJ,SFQYSZRDLC,SFSJFS,GRYJ,EMAIL,QH,LXDH,CELLPHONE,ZZJSQLY,SWSMC,SWSDJ,ZSSH,ZSMC,SWSSHYJ,OWNER,ORG,SUPERORG,SRZE,GRADE1INFOR,GRADE2INFOR,GRADE1STATE,GRADE2STATE,XRZW_ADDITION,INFOR1,INFOR2
on gszj.tab7005
for each row
declare
TYPE arr IS TABLE OF gszj.tab7005.storeid%TYPE;
varArr arr;
ind number := 1;
begin
  varArr := arr('OPPTIME','SQDJ','HYZCH','ZCSWSIDM','XM','XB','ZZMM','SFZHM','ZYNX','JRSWSSJ','XLXW','WYCD','ZYZK','HYZSBH','ZGZSBH','XYZCZG','XRZW','DRSWSZSJ','SFQYSZRDLC','SFSJFS','GRYJ','EMAIL','QH','LXDH','CELLPHONE','ZZJSQLY','SWSMC','SWSDJ','ZSSH','ZSMC','SWSSHYJ','OWNER','ORG','SUPERORG','SRZE','GRADE1INFOR','GRADE2INFOR','GRADE1STATE','GRADE2STATE','XRZW_ADDITION','INFOR1','INFOR2');
  loop 
    exit when ind > 42;
      if :NEW.(varArr(ind)) <> :old.(varArr(ind)) then        update gszj.tab7006 t6 set t6.varArr(ind) = :NEW.varArr(ind) where t6.relateddocstoreid = :NEW.storeid;
        update gszj.tab7007 t7 set t7.varArr(ind) = :NEW.varArr(ind) where t7.relateddocstoreid = :NEW.storeid;
        update gszj.tab7008 t8 set t8.varArr(ind) = :NEW.varArr(ind) where t8.relateddocstoreid = :NEW.storeid;
        update gszj.tab7009 t9 set t9.varArr(ind) = :NEW.varArr(ind) where t9.relateddocstoreid = :NEW.storeid;
      end if;
      
      dbms_output.put_line('123'||varArr(ind));
   end loop;
end;
现在要在if :NEW.(varArr(ind)) 处循环得到列名 但是报错   高手求救

Answer 1

:new.(vararr(ind))改成:new.varr(ind)行吗？

Answer 2

感觉你的ind 一直没有增加呢。

Answer 3

---col 改成相应的字段create or replace trigger tab7005_tri
before update of OPPTIME,SQDJ,HYZCH,ZCSWSIDM,XM,XB,ZZMM,SFZHM,ZYNX,JRSWSSJ,XLXW,WYCD,ZYZK,HYZSBH,ZGZSBH,XYZCZG,XRZW,DRSWSZSJ,SFQYSZRDLC,SFSJFS,GRYJ,EMAIL,QH,LXDH,CELLPHONE,ZZJSQLY,SWSMC,SWSDJ,ZSSH,ZSMC,SWSSHYJ,OWNER,ORG,SUPERORG,SRZE,GRADE1INFOR,GRADE2INFOR,GRADE1STATE,GRADE2STATE,XRZW_ADDITION,INFOR1,INFOR2
on gszj.tab7005
for each row
declare
TYPE arr IS TABLE OF gszj.tab7005.storeid%TYPE;
varArr arr:= arr('OPPTIME','SQDJ','HYZCH','ZCSWSIDM','XM','XB','ZZMM','SFZHM','ZYNX','JRSWSSJ','XLXW','WYCD','ZYZK','HYZSBH','ZGZSBH','XYZCZG','XRZW','DRSWSZSJ','SFQYSZRDLC','SFSJFS','GRYJ','EMAIL','QH','LXDH','CELLPHONE','ZZJSQLY','SWSMC','SWSDJ','ZSSH','ZSMC','SWSSHYJ','OWNER','ORG','SUPERORG','SRZE','GRADE1INFOR','GRADE2INFOR','GRADE1STATE','GRADE2STATE','XRZW_ADDITION','INFOR1','INFOR2');
begin
  for i in 1..varArr.count 
    loop      if :NEW.col <> :old.col then        update gszj.tab7006 t6 set t6.col2 = varArr(i) where t6.relateddocstoreid = :NEW.storeid;
        update gszj.tab7007 t7 set t7.col2 =varArr(i) where t7.relateddocstoreid = :NEW.storeid;
        update gszj.tab7008 t8 set t8.col2 =varArr(i) where t8.relateddocstoreid = :NEW.storeid;
        update gszj.tab7009 t9 set t9.col2 =varArr(i) where t9.relateddocstoreid = :NEW.storeid;
      end if;
end loop;
end;