--测试: create table table001(type varchar2(10)); insert into table001 values('text'); insert into table001 values('text');create table table002(type varchar2(10)); insert into table002 values('tecwsxt'); insert into table002 values('text');create table table003(type varchar2(10)); insert into table003 values('texy');set serveroutput on; declare i int; s varchar2(1000); v1 int; begin for i in 1..3 loop s := 'table'||substr('000'||to_char(i),length(to_char(i))+1,3); execute immediate 'select count(*) cnt from '||s||' where type = ''text'' ' into v1; if nvl(v1,0) >= 1 then dbms_output.put_line(s); end if; end loop; end;/* 结果 table001 table002 */
先组合成sql
然后用油标.
create table table001(type varchar2(10));
insert into table001 values('text');
insert into table001 values('text');create table table002(type varchar2(10));
insert into table002 values('tecwsxt');
insert into table002 values('text');create table table003(type varchar2(10));
insert into table003 values('texy');set serveroutput on;
declare i int;
s varchar2(1000);
v1 int;
begin
for i in 1..3 loop
s := 'table'||substr('000'||to_char(i),length(to_char(i))+1,3);
execute immediate 'select count(*) cnt from '||s||' where type = ''text'' '
into v1;
if nvl(v1,0) >= 1 then
dbms_output.put_line(s);
end if;
end loop;
end;/* 结果
table001
table002
*/