open cur_ply for
select ply_list.* from cust_renew_ply_list ply_list
left join
(
select level4.ply_no as level4_ply_no
from cust_ply_data_new data_new
inner join cust_temp_ply_base base
on data_new.ply_no = base.c_ply_no
left join cust_temp_ply_base parents
on base.c_ply_no = parents.c_ply_no
left join cust_ply_data_new level2
on data_new.last_ply_no = level2.ply_no
left join cust_ply_data_new level3
on level2.last_ply_no = level3.ply_no
left join cust_ply_data_new level4
on level3.last_ply_no = level4.ply_no
where base.c_orig_flg = 1
and sysdate >= base.t_insrnc_bgn_tm
and sysdate <= add_months(base.t_insrnc_end_tm,3)
and level4.ply_no is not null
) vx
on ply_list.renew_ply_no = vx.level4_ply_no;
loop
-- 每次处理指定行数的记录
fetch cur_ply bulk collect
into table_renew limit v_rows_count;
exit when table_renew.count = 0; for i in 1 .. table_renew.count loop
item_renew := table_renew(i);
--处理单个客户逻辑
v_level_4_ply := item_renew.renew_ply_no;
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
while nvl(v_level_4_ply,'error') <> 'error'
loop
insert into cust_renew_ply_list
(
start_ply_no,
renew_ply_no,
prod_params,
user_id
)
values
(
item_renew.start_ply_no,
v_level_4_ply,
item_renew.prod_params,
item_renew.user_id
);
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
end loop;
commit;
end loop;
end loop;
close cur_ply;
求高人指点 此段 代码 什么含义必有重谢!!!
select ply_list.* from cust_renew_ply_list ply_list
left join
(
select level4.ply_no as level4_ply_no
from cust_ply_data_new data_new
inner join cust_temp_ply_base base
on data_new.ply_no = base.c_ply_no
left join cust_temp_ply_base parents
on base.c_ply_no = parents.c_ply_no
left join cust_ply_data_new level2
on data_new.last_ply_no = level2.ply_no
left join cust_ply_data_new level3
on level2.last_ply_no = level3.ply_no
left join cust_ply_data_new level4
on level3.last_ply_no = level4.ply_no
where base.c_orig_flg = 1
and sysdate >= base.t_insrnc_bgn_tm
and sysdate <= add_months(base.t_insrnc_end_tm,3)
and level4.ply_no is not null
) vx
on ply_list.renew_ply_no = vx.level4_ply_no;
loop
-- 每次处理指定行数的记录
fetch cur_ply bulk collect
into table_renew limit v_rows_count;
exit when table_renew.count = 0; for i in 1 .. table_renew.count loop
item_renew := table_renew(i);
--处理单个客户逻辑
v_level_4_ply := item_renew.renew_ply_no;
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
while nvl(v_level_4_ply,'error') <> 'error'
loop
insert into cust_renew_ply_list
(
start_ply_no,
renew_ply_no,
prod_params,
user_id
)
values
(
item_renew.start_ply_no,
v_level_4_ply,
item_renew.prod_params,
item_renew.user_id
);
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
end loop;
commit;
end loop;
end loop;
close cur_ply;
求高人指点 此段 代码 什么含义必有重谢!!!
--打开动态游标cur_ply
OPEN cur_ply FOR
SELECT ply_list.*
FROM cust_renew_ply_list ply_list
LEFT JOIN (SELECT level4.ply_no AS level4_ply_no
FROM cust_ply_data_new data_new
INNER JOIN cust_temp_ply_base base
ON data_new.ply_no = base.c_ply_no
LEFT JOIN cust_temp_ply_base parents
ON base.c_ply_no = parents.c_ply_no
LEFT JOIN cust_ply_data_new level2
ON data_new.last_ply_no = level2.ply_no
LEFT JOIN cust_ply_data_new level3
ON level2.last_ply_no = level3.ply_no
LEFT JOIN cust_ply_data_new level4
ON level3.last_ply_no = level4.ply_no
WHERE base.c_orig_flg = 1
AND SYSDATE >= base.t_insrnc_bgn_tm
AND SYSDATE <= add_months(base.t_insrnc_end_tm, 3)
AND level4.ply_no IS NOT NULL) vx
ON ply_list.renew_ply_no = vx.level4_ply_no; LOOP
--将游标查询到的数据集存入嵌套表table_renew,取行数v_rows_count
FETCH cur_ply BULK COLLECT
INTO table_renew LIMIT v_rows_count;
EXIT WHEN table_renew.count = 0;
--循环处理嵌套表的中的记录
FOR i IN 1 .. table_renew.count LOOP
item_renew := table_renew(i);
--处理单个客户逻辑
v_level_4_ply := item_renew.renew_ply_no;
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
WHILE nvl(v_level_4_ply, 'error') <> 'error' LOOP
INSERT INTO cust_renew_ply_list
(start_ply_no, renew_ply_no, prod_params, user_id)
VALUES
(item_renew.start_ply_no, v_level_4_ply, item_renew.prod_params,
item_renew.user_id);
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
END LOOP;
COMMIT;
END LOOP; --结束嵌套表循环
END LOOP; --结束游标循环
CLOSE cur_ply; --关闭游标
END;
精通Oracle 10g PL/SQL编程(http://download.csdn.net/source/552111)
--打开动态游标cur_ply
OPEN cur_ply FOR
SELECT ply_list.*
FROM cust_renew_ply_list ply_list
LEFT JOIN (SELECT level4.ply_no AS level4_ply_no
FROM cust_ply_data_new data_new
INNER JOIN cust_temp_ply_base base
ON data_new.ply_no = base.c_ply_no
LEFT JOIN cust_temp_ply_base parents
ON base.c_ply_no = parents.c_ply_no
LEFT JOIN cust_ply_data_new level2
ON data_new.last_ply_no = level2.ply_no
LEFT JOIN cust_ply_data_new level3
ON level2.last_ply_no = level3.ply_no
LEFT JOIN cust_ply_data_new level4
ON level3.last_ply_no = level4.ply_no
WHERE base.c_orig_flg = 1
AND SYSDATE >= base.t_insrnc_bgn_tm
AND SYSDATE <= add_months(base.t_insrnc_end_tm, 3)
AND level4.ply_no IS NOT NULL) vx
ON ply_list.renew_ply_no = vx.level4_ply_no; LOOP
--将游标查询到的数据集存入嵌套表table_renew,取行数v_rows_count
FETCH cur_ply BULK COLLECT
INTO table_renew LIMIT v_rows_count;
EXIT WHEN table_renew.count = 0;
--循环处理嵌套表的中的记录
FOR i IN 1 .. table_renew.count LOOP
item_renew := table_renew(i);
--处理单个客户逻辑
v_level_4_ply := item_renew.renew_ply_no;
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
WHILE nvl(v_level_4_ply, 'error') <> 'error' LOOP
INSERT INTO cust_renew_ply_list
(start_ply_no, renew_ply_no, prod_params, user_id)
VALUES
(item_renew.start_ply_no, v_level_4_ply, item_renew.prod_params,
item_renew.user_id);
v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
END LOOP;
COMMIT;
END LOOP; --结束嵌套表循环
END LOOP; --结束游标循环
CLOSE cur_ply; --关闭游标
END;