各位大侠,我现在有多个表,表的名字结构相似,比如说为,biao1,biao2,biao3...biao12我想写一个语句,一次遍历全部表。但是1到12的参数如何写?也就是表的名称要每次动态变化,写成一条语句。谢谢各位啦!
select * from (select * from biao1
union all
select * from biao2
union all
select * from biao3
union all
select * from biao4
....
)where number = '1234'
select * from (select * from biao1
union all
select * from biao2
union all
select * from biao3
union all
select * from biao4
....
)where number = '1234'
你可以写一个function,在funtion里,动态拼出如上的sql,通过execute immediate执行动态sql并,把结果用游标,表记录,或者数组的形式返回。
参数可以是不带数字后缀的表名和condition的字符串根据表名参数,把sql拼出了,然后把condition加上,用execute immediate执行这个动态sql,返回表记录或者array或者游标。
TYPE mytable IS TABLE OF biao1%ROWTYPE;
l_d mytable;
sql varchar2(10000) :='select * from biao';
for i in 1..12 loop
sql:=sql||i;
if i > 1 then
sql:=sql||' union ' ;
end if;
end loop;OPEN l_c FOR sql;
FETCH l_c BULK COLLECT INTO l_d;
CLOSE l_c;
定义一个function 参数是 表名 tablename, 和condition
返回是一个游标实现上,根据你的sql样本,动态把tablename拼出
'select * from
( select * from '||tablename||'1
union all
select * from '||tablename||'2
union all
select * from '||tablename||'3
union all
select * from '||tablename||'4
....
) '||condition;通过open cursor把打开动态sql的游标,这里也可以用execute immediate把结果fetch到一个表记录或者是array里