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;