如何从其所在的所有表中选择一个列? [英] How to select a column from all tables in which it resides?

问题描述

我有很多表具有相同的"customer_number"列. 我可以通过查询获取所有这些表的列表:

I have many tables that have the same column 'customer_number'. I can get a list of all these table by query:

SELECT table_name FROM ALL_TAB_COLUMNS 
WHERE COLUMN_NAME = 'customer_number';

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

The question is how do I get all the records that have a specific customer number from all these tables without running the same query against each of them.

推荐答案

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

I assume you want to automate this. Two approaches.

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的类似想法:

Similar idea to use dynamic 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;

这篇关于如何从其所在的所有表中选择一个列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！