比如有几个表,几个不知道...aaa1,aaa2,aaa3...结构一样,表名前面是一样的。如字段为字段A,字段B,是一样的,
根据一个条件:如 字段A=ccc,来查询所有符合字段A=ccc的字段B的值:select 字段B from ... where 字段A=ccc。但是知道符合 字段A=ccc 这个条件的数据都在一个表中,但不知道在哪个表中。这个查询SQL怎么写?
根据一个条件:如 字段A=ccc,来查询所有符合字段A=ccc的字段B的值:select 字段B from ... where 字段A=ccc。但是知道符合 字段A=ccc 这个条件的数据都在一个表中,但不知道在哪个表中。这个查询SQL怎么写?
union all
select 'aaa2' as tblName,A,B from aaa2 where A='ccc'
union all
select 'aaa3' as tblName,A,B from aaa3 where A='ccc'.....select 'aaaN' as tblName,A,B from aaaN where A='ccc'
如果你想要重复数据,就用union allselect A,B from aaa1 where A='ccc'
union all
select A,B from aaa2 where A='ccc'
union all
select A,B from aaa3 where A='ccc'
...
select A,B from aaan where A='ccc'
根据一个条件:如 字段A=ccc,来查询所有符合字段A=ccc的字段B的值:select 字段B from ... where 字段A=ccc。 但是知道符合 字段A=ccc 这个条件的数据都在一个表中,但不知道在哪个表中。 这个查询SQL怎么写?
declare
tsql varchar2(2000);
begin
for AA in(
select table_name as tablename from all_tables where table_name like 'aaa%'
) loop
tsql:='insert into temptable select * from '||AA.tablename '|| where A='''ccc'''';
execute immediate tsql;
end loop;
end;最后再查询temptable表就行了
请先试了再问,否则浪费所有人时间!!!
select 'aaa1' as tblName,A,B from aaa1 where A='ccc'
union all
select 'aaa2' as tblName,A,B from aaa2 where A='ccc'
union all
select 'aaa3' as tblName,A,B from aaa3 where A='ccc'.....select 'aaaN' as tblName,A,B from aaaN where A='ccc'
比如,你只知道有aaa1,aaa2,aaa3,但是不是有aaa4,aaa5就不知道了
(
A varchar(10),
B int
)
create table aaa2
(
A varchar(10),
B int
)
create table aaa3
(
A varchar(10),
B int
)
insert into aaa3 values('ccc',1);
SQL> declare
2 v_count int;
3 v_tablename varchar(20);
4 v_sql varchar(1000);
5 cursor c is select table_name from all_tables where table_name like upper('aaa%') ;
6 begin
7 open c;
8 loop
9 fetch c into v_tablename;
10 if c%found then
11
12 EXECUTE IMMEDIATE'select count(*) from '||v_tablename||' where A=''ccc''' into v_count;
13 if v_count>0 then
14 dbms_output.put_line('我所找到的表是:'||v_tablename);
15 dbms_output.put_line('我的查询语句是:'||'select B from '||v_tablename);
16 end if;
17 else
18 exit;
19 end if;
20 end loop;
21 close c;
22 end;
23 /
我所找到的表是:AAA3
我的查询语句是:select B from AAA3PL/SQL 过程已成功完成。
SQL> create or replace procedure v_prc(mycursor out sys_refcursor)
2 is
3 v_count int;
4 v_tablename varchar(20);
5 v_sql varchar(1000);
6 cursor c is select table_name from all_tables where table_name like upper('aaa%') ;
7 begin
8 open c;
9 loop
10 fetch c into v_tablename;
11 if c%found then
12
13 EXECUTE IMMEDIATE'select count(*) from '||v_tablename||' where A=''ccc''' into v_count;
14 if v_count>0 then
15 dbms_output.put_line('我所找到的表是:'||v_tablename);
16 dbms_output.put_line('我的查询语句是:'||'select B from '||v_tablename ||' where A=''ccc''');
17 v_sql :='select B from '||v_tablename ||' where A=''ccc''';
18 open mycursor for v_sql;
19 end if;
20 else
21 exit;
22 end if;
23 end loop;
24
25 close c;
26
27 end;
28 /过程已创建。SQL> var c1 sys_refcursor;
用法: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> var c1 refcursor;
SQL> exec v_prc(:c1);
我所找到的表是:AAA3
我的查询语句是:select B from AAA3 where A='ccc'PL/SQL 过程已成功完成。SQL> print c1 B
----------
1