主题新回顾(发布时间:2010-12-11 11:09:53) |
---|
-- 作者:wangxinxin
-- Oracle PLSQL中用pro进行 BULK COLLECT 的高效率查询处理 过程样例:
create or replace procedure real_user as c_count number(10); begin DECLARE TYPE mobile_type IS TABLE OF mobile_dabenying.mobile%TYPE; --按表字段类型来定义变量类型 t_mobile mobile_type := mobile_type(); BEGIN select mobile BULK COLLECT --批量选择数据 INTO t_mobile from (select mobile from mobile_dabenying order by mobile); if (t_mobile.COUNT > 0) then FOR i IN t_mobile.FIRST .. t_mobile.LAST LOOP select count(*) --从号段中过滤 into c_count from mobile_region where t_mobile(i) between start_mobile and end_mobile; if (c_count > 0) then c_count := 0; insert into mobile_dabenying2 (mobile) values (t_mobile(i)); --真实用户 else dbms_output.put_line(t_mobile(i)); insert into mobile_dabenying3 (mobile) values (t_mobile(i)); --假用户 end if; END LOOP; end if; END; end; |