oracle千万数据做递归查询,递归优化，请各位帮忙看看，40万的数据量

create or replace procedure SP_CRMS_AC_RG_RECURSION(p_organ_no in varchar2,p_group_no in varchar2)

is

v_rule Varchar2(100);

v_id crms_temp_recursion_customer.id%TYPE; --

v_organ_no crms_temp_recursion_customer.organ_no%TYPE; --

begin

--1.提取传入客户的关联与被关联客户，并放入临时表中

v_rule := '提取传入客户的关联与被关联客户';

insert into crms_temp_recursion_customer(id,organ_no)

(select carbr.id,carbr.re_organ_no

from crms_temp_base_relation carbr

where carbr.obj_organ_no = p_organ_no

and carbr.base_group_no is  null

union

select car.id,car.obj_organ_no

from crms_temp_base_relation car

where car.re_organ_no = p_organ_no

and car.base_group_no is null);

--2。设置查询客户其所属的基础集群编号

v_rule := '设置查询客户其所属的基础集群编号';

update crms_temp_ac_rg_memberinfo ctarm

set ctarm.base_group_no = p_group_no

where ctarm.base_group_no is null

and ctarm.organ_no=p_organ_no;

commit;

--3。遍历关联和被关联客户，并递归查询

v_rule := '遍历关联和被关联客户，并递归查询';

begin

Select ctrc.id,ctrc.organ_no into v_id,v_organ_no

from crms_temp_recursion_customer ctrc

where rownum=1;

exception

when no_data_found then

v_id := 0;

end;

while (v_id <> 0) loop

delete from crms_temp_recursion_customer ctrc where ctrc.organ_no = v_organ_no;

update crms_temp_base_relation ca

set ca.base_group_no = p_group_no

where ca.id= v_id;

sp_crms_ac_rg_recursion(v_organ_no,p_group_no);

begin

Select ctrc.id,ctrc.organ_no into v_id,v_organ_no

from crms_temp_recursion_customer ctrc

where rownum=1;

exception

when no_data_found then

v_id := 0;

end;

commit;

end loop;

EXCEPTION

--意外

WHEN OTHERS Then

Begin

ROLLBACK;

Common_Tools.Add_ErrorLog('SP_CRMS_AC_RG_RECURSION',

v_rule,

SQLERRM,

'');

Common_Tools.Add_ExceRecode('SP_CRMS_AC_RG_RECURSION',

v_rule,

'程序：……');

End;

end SP_CRMS_AC_RG_RECURSION;

[本帖最后由 Hot_mobile 于 2009-3-2 19:30 编辑]