在SQL中使用USER_TAB_COLLS从列中检索值

Question

我正在尝试获取包含特定列名的所有表的名称。现在，对于所有具有该列名的表，我希望检索该列中具有特定值的表。

用于获取表列表的查询为：

SELECT COLUMN_NAME, TABLE_NAME 
FROM USER_TAB_COLS 
WHERE COLUMN_NAME LIKE 'USER_ID';

请帮我完成这个查询。我是SQL的新手。示例：

WEB_APPLICATION (TABLE-1)
USER_ID NAME GENDER
abc     ABC  M

DESKTOP_APPLICATION (TABLE-2)
LOGIN_ID NAME GENDER
bcd      BCD  F

MOBILE_APPLICATION(TABLE-3)
USER_ID NAME GANDER
abc     ABC  F

EXPECTED OUTPUT:
TABLE_NAME          GENDER
WEB_APPLICATION     M
MOBILE_APPLICATION  F

现在，我想获取列为USER_ID、值为abc的表。

希望这一次我说得很清楚。

Answer 1

为此，您需要使用动态SQL，因此可以在PL/SQL中执行此操作。

declare
cursor TABS is
  SELECT TABLE_NAME 
  FROM USER_TAB_COLS 
  GROUP BY TABLE_NAME
  HAVING COUNT(
    CASE 
      WHEN COLUMN_NAME IN ('USER_ID','GENDER') 
    THEN 1 END
  ) = 2;
  type tab is table of varchar(100) index by pls_integer;
  v_tab tab;
begin
  for t in TABS loop
    execute immediate 'select gender from ' ||t.table_name bulk collect into v_tab;
    if v_tab.count > 0 then 
      for i in v_tab.first..v_tab.last loop
        dbms_output.put_line(t.table_name ||'    '|| v_tab(i));
      end loop;
    end if;
  end loop;
end;
/

Answer 2

此查询创建所需的SQL语句(每个表占一行)

select 
'select '''||table_name||''' table_name, gender from '|| table_name ||
' where USER_ID = ''abc''' || 
case when rn != 1 then ' UNION ALL ' end as sql_txt
from (
select table_name,
row_number() over (order by table_name desc) as rn
from (
select table_name from user_tab_columns where column_name = 'GENDER'
INTERSECT
select table_name from user_tab_columns where column_name = 'USER_ID'
))  order by table_name;

运行它会得到如下语句

select 'MOBILE_APPLICATION' table_name, gender from MOBILE_APPLICATION where USER_ID = 'abc' UNION ALL 
select 'WEB_APPLICATION' table_name, gender from WEB_APPLICATION where USER_ID = 'abc';

如果您运行它，您将获得所需的结果

TABLE_NAME         GENDER
------------------ ------
MOBILE_APPLICATION F      
WEB_APPLICATION    M 

样本数据

create table WEB_APPLICATION as
select 'abc' USER_ID, 'ABC' NAME, 'M' GENDER from dual;

create table DESKTOP_APPLICATION as
select 'bcd' LOGIN_ID, 'BCD' NAME, 'F' GENDER from dual;

create table MOBILE_APPLICATION as
select 'abc' USER_ID, 'ABC' NAME, 'F' GENDER from dual;