Oracle如何定义两个数组变量

DECLARE TYPE ARRAYLIST IS VARRAY(100) OF VARCHAR(50);             
MY_ARRAYLIST ARRAYLIST := ARRAYLIST('000015','000032');--这里填要依次更改的卡片编码
TYPE ARRAYLIST2 IS VARRAY(100) OF VARCHAR(50); 
MY_ARRAYLIST2 ARRAYLIST2 := ARRAYLIST2('111','222');--这里依次填正确的使用部门ID
BEGIN 
  for i in 1..MY_ARRAYLIST.count  
      loop  
        --dbms_output.put_line(MY_ARRAYLIST(i)); 
        for r in (SELECT B.FASSETNO FROM T_FA_CARD A INNER JOIN t_fa_allocation B ON A.FALTERID = B.FALTERID
WHERE A.FNUMBER = MY_ARRAYLIST(i)) loop
          --dbms_output.put_line(MY_ARRAYLIST2(i)); 
          execute immediate 'UPDATE T_FA_CHANGEALLOC SET FALLOCUSEDEPTID = '||MY_ARRAYLIST2(i)||' WHERE FPREALLOCASSETNO = '''||r.FASSETNO||'''';
         end loop;
      end loop;
END;
posted @ 2020-12-04 11:26  CH|heyi  阅读(406)  评论(0编辑  收藏  举报