如何从其所在的所有表中选择一个列？

Question

我有很多表具有相同的列“ customer_number”。我可以通过查询获取所有这些表的列表：

SELECT table_name FROM ALL_TAB_COLUMNS 
WHERE COLUMN_NAME = 'customer_number';

问题是如何从所有这些表中获取具有特定客户编号的所有记录，而不对每个表运行相同的查询。

Answer 1

我假设您要自动执行此操作。两种方法。

1. SQL生成SQL脚本

。

spool run_rep.sql 
set head off pages 0 lines 200 trimspool on feedback off

SELECT 'prompt ' || table_name || chr(10) ||

       'select ''' || table_name ||
         ''' tname, CUSTOMER_NUMBER from ' || table_name || ';' cmd
FROM all_tab_columns
WHERE column_name = 'CUSTOMER_NUMBER';

spool off

@ run_rep.sql

1. PLSQL

使用动态sql的类似想法：

DECLARE
   TYPE rcType IS REF CURSOR;
   rc   rcType;
   CURSOR c1 IS SELECT table_name FROM all_table_columns WHERE column_name = 'CUST_NUM';
   cmd   VARCHAR2(4000);
   cNum  NUMBER;
BEGIN
   FOR r1 IN c1 LOOP
      cmd := 'SELECT cust_num FROM ' || r1.table_name ;
      OPEN rc FOR cmd;
      LOOP
         FETCH rc INTO cNum;
         EXIT WHEN rc%NOTFOUND;
         -- Prob best to INSERT this into a temp table and then
         -- select * that to avoind DBMS_OUTPUT buffer full issues
         DBMS_OUTPUT.PUT_LINE ( 'T:' || r1.table_name || ' C: ' || rc.cust_num );
      END LOOP;
      CLOSE rc;
   END LOOP;
END;