基于列值的Oracle获取表名

Question

我有这样的桌子：

Table-1
Table-2
Table-3
Table-4
Table-5

每个表都有许多列，其中一个列名是employee_id。

现在，我想编写一个查询，它将

1)返回具有以下列的所有表

2)如果列具有值或空值，则结果应该通过传递employee_id来显示表。

例如显示表名，列名称从表-1，表-2，表-3，.其中employee_id='1234‘。

如果其中一个表没有此列，则不需要显示。

我已经用链接进行了验证，但它只显示表名和列名，而不是通过向其传递某些列值。

也验证了这，但这里验证的是我不想做的整个模式。

更新：

找到了一个解决方案，但是通过使用已弃用的but序列，

1)如何使这段代码成为xmltable？

2)如果表中没有值，则输出应该为空/空。或默认为“是”值。

WITH  char_cols AS
  (SELECT /*+materialize */ table_name, column_name
   FROM   cols
  WHERE  data_type IN ('CHAR', 'VARCHAR2') and table_name in ('Table-1','Table-2','Table-3','Table-4','Table-5')) 
SELECT DISTINCT SUBSTR (:val, 1, 11) "Employee_ID",
       SUBSTR (table_name, 1, 14) "Table",
       SUBSTR (column_name, 1, 14) "Column"
FROM   char_cols,
       TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
       || column_name
       || '" from "'
       || table_name
       || '" where upper("'
       || column_name
       || '") like upper(''%'
       || :val
       || '%'')' ).extract ('ROWSET/ROW/*') ) ) t ORDER  BY "Table" 
/ 

Answer 1

这个查询可以使用(非推荐的) XMLTABLE一步完成。

样例模式

--Table-1 and Table-2 match the criteria.
--Table-3 has the right column but not the right value.
--Table-4 does not have the right column.
create table "Table-1" as select '1234' employee_id from dual;
create table "Table-2" as select '1234' employee_id from dual;
create table "Table-3" as select '4321' employee_id from dual;
create table "Table-4" as select 1          id from dual;

查询

--All tables with the column EMPLOYEE_ID, and the number of rows where EMPLOYEE_ID = '1234'.
select table_name, total
from
(
    --Get XML results of dynamic query on relevant tables and columns.
    select
        dbms_xmlgen.getXMLType(
            (
                --Create a SELECT statement on each table, UNION ALL'ed together.
                select listagg(
                    'select '''||table_name||''' table_name, count(*) total
                     from "'||table_name||'" where employee_id = ''1234'''
                    ,' union all'||chr(10)) within group (order by table_name) v_sql
                from user_tab_columns
                where column_name = 'EMPLOYEE_ID'
            )
        ) xml
    from dual
) x
cross join
--Convert the XML data to relational.
xmltable('/ROWSET/ROW'
    passing x.xml
    columns
        table_name varchar2(128) path 'TABLE_NAME',
        total      number        path 'TOTAL'
);

结果

TABLE_NAME   TOTAL
----------   -----
Table-1      1
Table-2      1
Table-3      0

Answer 2

试着使用下面的代码。请注意，这可能是议会，澄清方案名称在循环。此代码适用于本地数据库。启动服务器输出；

DECLARE
  ex_query VARCHAR(300);
  num      NUMBER;
  emp_id number;
BEGIN
  emp_id := <put your value>;
  FOR rec IN
  (SELECT table_name
  FROM all_tab_columns
  WHERE column_name LIKE upper('employee_id')
  )
  LOOP
    num      :=0;
    ex_query := 'select count(*) from ' || rec.table_name || ' where employee_id = ' || emp_id;
    EXECUTE IMMEDIATE ex_query into num;
    if (num>0) then
      DBMS_OUTPUT.PUT_LINE(rec.table_name);
    end if;
  END LOOP;
END;

Answer 3

我尝试使用xml，但是我得到了一个无法解决的错误。大概是零尺寸的结果。要解决这个问题，而不是提出例外，有多难？！去问甲骨文。

不管怎么说。您可以做的是使用COLS表来知道哪个表有employee_id列。

1)表TABLE_LIKE_THIS中的哪个表(假设表名为C)中有该列？

 select * 
   from COLS, TABLE_LIKE_THIS t
  where cols.table_name = t
    and cols.column_name = 'EMPLOYEE_ID' 
 -- think Oracle metadata/ think upper case

2)您要寻找的值是哪一个:用动态PL/SQL编写一小块EXECUTE IMMEDIATE来计算符合上述条件的表

declare
  v_id varchar2(10) := 'JP1829';       -- value you are looking for
  v_col varchar2(20) := 'EMPLOYEE_ID'; -- column
  n_c  number := 0;
begin
  for x in (
   select table_name
     from all_tab_columns cols
        , TABLE_LIKE_THIS t
    where cols.table_name = t.c
      and cols.column_name = v_col
  ) loop
    EXECUTE IMMEDIATE 
    'select count(1) from '||x.table_name
  ||' where Nvl('||v_col||', ''@#'') = ''' ||v_id||''''  -- adding quotes around string is a little specific
    INTO n_c;
    if n_c > 0 then
      dbms_output.put_line(n_C|| ' in ' ||x.table_name||' has '||v_col||'='||v_id);
    end if;
  -- idem for null values
  -- ... ||' where '||v_col||' is null '
  -- or
  -- ... ||' where Nvl('||v_col||', ''@#'') = ''@#'' '
  end loop;
  dbms_output.put_line('done.');
end;
/

希望这能有所帮助