存储过程的数组方式

create or replace procedure cydao(V_num varchar2) as Type V_jtcylist is Record ( v_pcode cz_s_jtcy.pcode%type, v_rcode cz_s_jtcy.rcode%type, v_pname cz_s_jtcy.pname%type, v_hzgx cz_s_jtcy.hzgx%type, v_cardno cz_s_jtcy.cardno%type, v_bank cz_s_jtcy.bank%type, v_khname cz_s_jtcy.khname%type, v_bankno cz_s_jtcy.bankno%type, v_sex cz_s_jtcy.sex%type, v_nation cz_s_jtcy.nation%type, v_whcd cz_s_jtcy.whcd%type, v_hyzk cz_s_jtcy.hyzk%type, v_xzzw cz_s_jtcy.xzzw%type, v_chrq cz_s_jtcy.chrq%type, v_phone cz_s_jtcy.phone%type, v_memo cz_s_jtcy.memo%type, v_flag cz_s_jtcy.flag%type, v_grsf cz_s_jtcy.grsf%type, v_partitionid cz_s_map.partitionid%type ); type tablep is table of V_jtcylist index by binary_integer; mytable tablep; vN Number; begin vN:=1; for rnum in ( select cy.pcode,cy.rcode,cy.pname,cy.hzgx,cy.cardno,cy.bank,cy.khname,cy.bankno,cy.sex,cy.nation,cy.whcd,cy.hyzk,cy.xzzw,cy.chrq,cy.phone,cy.memo,cy.flag,cy.grsf, lk.partitionid from ( select k.shopid,x.shopid fk from (select shopid,parentid,shopname from s_shop where shoptype =1)x, (select shopid,parentid,shopname from s_shop where shoptype=2)y, (select shopid,parentid,shopname from s_shop where shoptype=3)z, (select shopid,parentid,shopname from s_shop where shoptype =4)k where x.shopid=y.parentid and y.shopid=z.parentid and z.shopid=k.parentid)l, (select a.areacode,a.pcode,a.address,a.hcode,a.hkxz,a.phone,a.zipcard,a.memo,a.flag from cz_s_jtxx a) m, (select t.areacodeid,t.partitionid from cz_s_map t) lk, (select areacode,shopid from cz_s_area )mk, (select cz_s_jtcy.pcode,cz_s_jtcy.rcode,cz_s_jtcy.pname,cz_s_jtcy.hzgx,cz_s_jtcy.cardno,cz_s_jtcy.bank,cz_s_jtcy.khname,cz_s_jtcy.bankno,cz_s_jtcy.sex,cz_s_jtcy.nation,cz_s_jtcy.whcd,cz_s_jtcy.hyzk,cz_s_jtcy.xzzw,cz_s_jtcy.chrq,cz_s_jtcy.phone,cz_s_jtcy.memo,cz_s_jtcy.flag,cz_s_jtcy.grsf from cz_s_jtcy)cy where l.shopid=m.areacode and l.fk=mk.shopid and mk.areacode=lk.areacodeid and lk.partitionid=V_num and cy.pcode=m.pcode ) loop mytable(vN).v_pcode:=rnum.pcode; mytable(vN).v_rcode:=rnum.rcode; mytable(vN).v_pname:=rnum.pname; mytable(vN).v_hzgx:=rnum.hzgx; mytable(vN).v_cardno:=rnum.cardno; mytable(vN).v_bank:=rnum.bank; mytable(vN).v_khname:=rnum.khname; mytable(vN).v_bankno:=rnum.bankno; mytable(vN).v_sex:=rnum.sex; mytable(vN).v_nation:=rnum.nation; mytable(vN).v_whcd:=rnum.whcd; mytable(vN).v_hyzk:=rnum.hyzk; mytable(vN).v_xzzw:=rnum.xzzw; mytable(vN).v_chrq:=rnum.chrq; mytable(vN).v_phone:=rnum.phone; mytable(vN).v_memo:=rnum.memo; mytable(vN).v_flag:=rnum.flag; mytable(vN).v_grsf:=rnum.grsf; mytable(vN).v_partitionid:=rnum.partitionid; vN:=vN+1; end loop; vN:=mytable.First; for rnums in vN..mytable.count loop insert into cz_s_jtcys values (jtcy.nextval,mytable(vN).v_pcode,mytable(vN).v_rcode, mytable(vN).v_pname,mytable(vN).v_hzgx,mytable(vN).v_cardno,mytable(vN).v_bank,mytable(vN).v_khname,mytable(vN).v_bankno,mytable(vN).v_sex,mytable(vN).v_nation, mytable(vN).v_whcd,mytable(vN).v_hyzk,mytable(vN).v_xzzw, mytable(vN).v_chrq, mytable(vN).v_phone,mytable(vN).v_memo, mytable(vN).v_flag,mytable(vN).v_grsf, mytable(vN).v_partitionid); end loop; commit; end cydao;