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

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;