select * from( select 'A1' AS TNAME,A1.* FROM A1 UNION ALL select 'A2' AS TNAME,A2.* FROM A2 …… )where TNAME='你的表名' and 其他条件select A1.* FROM A1 where '你获得的表名'<>'A1' UNION ALL select A2.* FROM A2 where '你获得的表名'<>'A2' ……
基本实现了,用的动态sql用union是可以实现,但A1,A2 是动态查询出来的,所以有点麻烦。。 declare type table_cur_type is ref cursor; emp_cur table_cur_type; a_record A%rowtype; sql_statement varchar2(200); v_tblName varchar2(200); v_num number; v_count number; v_1 varchar2(32); v_2 varchar2(100); begin v_1:='bj'; ----查询根据条件A表中的 表名称 sql_statement := 'select * from A where type = :1 '; open emp_cur for sql_statement
using v_1; -----循环取出A表中存储的表名 再进行查询 loop fetch emp_cur into a_record; exit when emp_cur%notfound; dbms_output.put_line('表名:' || a_record.tblname || '); ------ str:='select count(id) c1 from '||a_record.tblname||' where I_DEL<>3 '; execute immediate str into v_num ; v_count :=v_num+v_count; DBMS_OUTPUT.PUT_LINE(v_num);
---- dbms_output.put_line('----------------'); end loop; DBMS_OUTPUT.PUT_LINE('v_count:'+v_count); end;
select 'A1' AS TNAME,A1.* FROM A1
UNION ALL
select 'A2' AS TNAME,A2.* FROM A2
……
)where TNAME='你的表名' and 其他条件select A1.* FROM A1 where '你获得的表名'<>'A1'
UNION ALL
select A2.* FROM A2 where '你获得的表名'<>'A2'
……
declare
type table_cur_type is ref cursor;
emp_cur table_cur_type;
a_record A%rowtype;
sql_statement varchar2(200);
v_tblName varchar2(200);
v_num number;
v_count number;
v_1 varchar2(32);
v_2 varchar2(100);
begin
v_1:='bj';
----查询根据条件A表中的 表名称
sql_statement := 'select * from A where type = :1 ';
open emp_cur for sql_statement
using v_1;
-----循环取出A表中存储的表名 再进行查询
loop
fetch emp_cur into a_record;
exit when emp_cur%notfound;
dbms_output.put_line('表名:' || a_record.tblname || ');
------
str:='select count(id) c1 from '||a_record.tblname||' where I_DEL<>3 ';
execute immediate str into v_num ;
v_count :=v_num+v_count;
DBMS_OUTPUT.PUT_LINE(v_num);
----
dbms_output.put_line('----------------');
end loop;
DBMS_OUTPUT.PUT_LINE('v_count:'+v_count);
end;