oralce 數組的妙用

DECLARE
  v_num pls_integer := 0;
  v_mod_num pls_integer:=0;
  v_addr_num PLS_INTEGER:=0;
  V_SALES_ID      VARCHAR2(32);
  v_count_memberId PLS_INTEGER :=0;
  v_ids_count PLS_INTEGER :=0;
  v_mod_num_t PLS_INTEGER:=0;
  id_start PLS_INTEGER:=0;
   v_sfa_maturity  VARCHAR2(8);
    v_sale_owner_id   VARCHAR2(32);
    v_datetime VARCHAR2(32);
    v_sale_count PLS_INTEGER:=0;
  type yy_assign_type is table of yy_assign_tbd20091001%rowtype index by binary_integer;
  type customer_list_type is table of custmer_list_tbd20091001%rowtype index by binary_integer;
  TYPE  t_number is table of number;
  yy_assign_table     yy_assign_type;
  customer_list_table customer_list_type;
   id_addr_table t_number := t_number();
   id_table t_number := t_number();
BEGIN
  select yy.* bulk collect
    into yy_assign_table
    from yy_assign_tbd20091001 yy,cs_v_tp_member t
   where yy.customer_type = 'salesType'
   AND   yy.member_id =t.member_id
   order by decode(yy.res_type,
                   'idsType',
                   1,
                   'specialResourceType',
                   2,
                   'personalType',
                   3,
                   'agentType',
                   4,
                   'generalResourceType',
                   5),
            yy.serial_num,
            to_date(yy.gmt_expired, 'yyyy-MM-dd'),
            yy.province,
            yy.mature_degree;

  select * bulk collect
    into customer_list_table
    from custmer_list_tbd20091001
   where customer_type = 'N' ORDER BY sales_id;

   select  rownum  ,serial_num   bulk collect
    into id_addr_table,id_table from ( select distinct(yy.serial_num)
    from yy_assign_tbd20091001 yy, cs_v_tp_member t
   where yy.res_type='idsType' AND yy.customer_type = 'salesType' AND yy.member_id=t.member_id order by yy.serial_num )t1;

     SELECT Count(*) INTO v_ids_count   from yy_assign_tbd20091001  yy,cs_v_tp_member t
      where yy.res_type='idsType' AND yy.member_id=t.member_id  AND yy.customer_type = 'salesType';
      select   to_char(sysdate,'YYYYMMDDHH24MISSmmss') INTO   v_datetime from   dual;
  for i in 1 .. yy_assign_table.count loop
      IF(yy_assign_table(i).SERIAL_NUM>0) THEN
            IF(customer_list_table.Count <> 1)   THEN
              FOR j IN 1 ..id_addr_table.Count LOOP
                IF(yy_assign_table(i).SERIAL_NUM = id_table(j) ) THEN
                  v_addr_num  :=id_addr_table(j);
                END IF;
              END LOOP;
              --如果分完了 再從第一個開始分起
             v_mod_num :=mod(v_addr_num,customer_list_table.count);
             IF(v_mod_num=0) THEN
                    v_mod_num:=v_addr_num;
              END IF ;
             V_SALES_ID :=customer_list_table(v_addr_num).SALES_ID;
            ELSE
                V_SALES_ID :=customer_list_table(customer_list_table.count).SALES_ID;
            END IF;
             Dbms_Output.put_line('v_mod_num is :'||v_mod_num||'V_SALES_ID is : '||V_SALES_ID);
      ELSE
          --算出其他資源類型的 下標
            id_start:= i - v_ids_count +id_addr_table.Count;
            v_mod_num_t :=mod(id_start,customer_list_table.count);
            Dbms_Output.put_line('v_ids_count is :'||v_ids_count||'id_addr_table is : '||id_addr_table.Count ||'v_mod_num_t is :'||i ||'id_start is '||id_start);
             IF(v_mod_num_t=0) THEN
                    v_mod_num_t:=customer_list_table.count;
              END IF ;
             V_SALES_ID :=customer_list_table(v_mod_num_t).SALES_ID;
      END  IF ;
       SELECT Count(*), sales_owner_id ,sfa_maturity INTO v_count_memberId ,v_sale_owner_id,v_sfa_maturity FROM cs_member_ex cs  WHERE cs.member_id = yy_assign_table(i).MEMBER_ID
 GROUP BY sales_owner_id ,sfa_maturity;
    IF(v_count_memberId > 0) THEN
    
UPDATE yy_assign_tbd20091001 SET is_used='y'WHERE member_id=yy_assign_table(i).MEMBER_ID AND customer_type = 'salesType' ;

  UPDATE custmer_list_tbd20091001  SET IS_used='y'
   where customer_type = 'N' AND SALES_ID=V_SALES_ID;
   UPDATE yy_assign_tbd20091001 SET is_used='y' WHERE customer_type ='salesType' AND member_id=yy_assign_table(i).MEMBER_ID;
    END IF;
    v_num := v_num + 1;
    if mod(v_num ,100)=0  THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    Dbms_Output.put_line('error encounter');
    ROLLBACK;
END;
/
commit;