oracle利用循环批量检索对应的数据

按照单个字符查询匹配

begin 
declare
cursor myemp_cur
is select * from table_a a where a.type1='user';
type myemp_tab is table of table_a%rowtype;
myemp_rd myemp_tab;
begin
open myemp_cur;
loop --循环1
fetch myemp_cur bulk collect into myemp_rd limit 20;
for i in 1..myemp_rd.count loop
dbms_output.put_line('**********************************************************');
dbms_output.put_line('目标对象：'||myemp_rd(i).MKT_TRADE_TYPE_DESC);
dbms_output.put_line('结果对象：');
--循环2
declare i_num number;
begin
  select length(myemp_rd(i).MKT_TRADE_TYPE_DESC) into i_num from dual;
  for i1 in 1..i_num loop

dbms_output.put_line(i1||'--');

--循环3
    declare
    cursor myemp_cur1
    is select * from table_a a where a.DK_SYSTEM_OF_UPD！='FCMS' and 
    a.mkt_trade_type_desc like '%'||substr(myemp_rd(i).MKT_TRADE_TYPE_DESC,i1,1)||'%';
    type myemp_tab1 is table of table_a%rowtype;
    myemp_rd1 myemp_tab1;
    begin
    open myemp_cur1;
    loop
    fetch myemp_cur1 bulk collect into myemp_rd1 limit 20;
    for i3 in 1..myemp_rd1.count loop
    dbms_output.put_line(i3||':'||myemp_rd1(i3).MKT_TRADE_TYPE_DESC);
    end loop;
    exit when myemp_cur1%notfound;
    end loop;
    end;
--
end loop;
end;
--
end loop;
exit when myemp_cur%notfound;
end loop;
end;
end;

结果:
不太尽人意。换成两个字符匹配后：

看起来明了多了。

 

转载于:https://www.cnblogs.com/Babylon/p/8528271.html