因为遍历查询是个动态过程,所以需要建立个临时表,来存放遍历后查询结果,然后再select distinct 出所需要的内容。
-- 创建用于存储查询结果的表
CREATE TABLE all_tables_results (
username VARCHAR2(30),
tablename VARCHAR2(30),
aa VARCHAR2(30),
bb date,
cc date
);
-- 遍历所有用户的A表,并将查询结果插入到all_tables_results表中
BEGIN
FOR cur IN (
SELECT owner, table_name
FROM all_tables
WHERE owner IN (SELECT username FROM all_users)
AND table_name = 'A'
) LOOP
EXECUTE IMMEDIATE 'INSERT INTO all_tables_results (username, tablename, aa,bb,cc) SELECT ''' || cur.owner || ''', ''' || cur.table_name || ''', aa,bb,cc FROM ' || cur.owner || '.' || cur.table_name;
END LOOP;
END;
/
--将所需的字段内容查询出来
SELECT DISTINCT aa,bb,cc FROM all_tables_results ORDER BY aa;