Question

求助用循环拼写查询语句动态SQL方法

Answer 1

declare
l_counter number:=0;
l_sql varchar2(4000):=null;
begin
select count(*)
into l_counter
from user_tables
where table_name like 'T%'; --你自己控制一下
if l_counter >=1 then
for i in 1..l_counter loop
if i = l_counter then
l_sql := l_sql||'select * from t'||i;
else
l_sql := l_sql||'select * from t'||i||chr(10)||'union all';
end if;
end loop;
end if;
dbms_output.putline(l_sql);
exception when others then
raise;
end;