以table.column格式返回Oracle列名吗? [英] Return Oracle column names in table.column format?

问题描述

我可以使用任何设置或方法来使Oracle以<table>.<column>格式返回结果吗?例如:

Is there any setting or method I can use to get Oracle to return results in <table>.<column> format? For example:

查询:

SELECT     *
FROM       foo f
INNER JOIN bar b
ON         b.foo_id = f.id

所需结果:

F.ID  F.BLAH  B.ID  B.FOO_ID  B.BLAH
--------------------------------------------------------
1     blah    7     1         blah
2     blah    8     2         blah
3     blah    9     2         blah

显而易见的解决方案是分别为每个列SELECT f.id AS F_ID, ...别名；但是，我需要导出一些非常大的旧表(超过300列)，因此使用此方法将导致查询量巨大且不切实际.

The obvious solution is to individually alias each column SELECT f.id AS F_ID, ...; however, I'm needing to export some very large legacy tables (300+ columns), so using this method would cause the queries to be enormous and impractical.

推荐答案

Oracle中没有做到这一点的选项"；您 可以找到允许您这样做的客户端，因为这是通常在客户端中完成的工作；我不知道一个.

There is no "option" in Oracle to do this; you may be able to find a client that allows you to do so as this is a job that would normally be done in the client; I don't know of one.

要扩展丁骨的答案，您将必须动态地执行此操作.这不是意味着您必须列出每列.您可以使用数据字典，尤其是 all_tab_columns 或user_tab_columns创建您的询问.创建具有所需确切定义的视图会更容易，以便您可以根据需要重新使用它.

To expand upon tbone's answer you're going to have to do this dynamically. This does not mean that you have to list every column. You would use the data dictionary, specifically all_tab_columns or user_tab_columns to create your query. It would be easier to create a view with the exact definition you want so that you can re-use it if you want.

目的是利用以下事实:存在的列作为字符串存储在表中，以便创建使用该列的查询.由于列名和表名存储为字符串，因此您可以使用字符串聚合技术轻松创建查询或DDL语句，然后可以手动或动态执行该语句.

The aim is to use the fact that the columns existence is stored in a table as a string in order to create a query to use that column. As the column names and table names are stored as strings you can use string aggregation techniques to easily create a query or DDL statement that you can then manually, or dynamically, execute.

如果您使用的是Oracle 11g第2版，则 listagg 功能可为您提供帮助:

If you're using Oracle 11g Release 2 the listagg function is available to help you:

select 'create or replace view my_view as 
        select '
      || listagg( table_name || '.' || column_name 
               || ' as ' 
               || substr(table_name,1,1) || '_' 
               || column_name, ', ')
        within group 
         ( order by case when table_name = 'FOO' then 0 else 1 end
                  , column_id
          )
       || ' from foo f
            join bar b
              on f.id = b.foo_id'
  from user_tab_columns
 where table_name in ('FOO','BAR')
        ;

假定此表结构为:

create table foo ( id number, a number, b number, c number);
create table bar ( foo_id number, a number, b number, c number);

此单个查询产生以下内容:

This single query produces the following:

create or replace view my_view as 
 select FOO.ID as F_ID, FOO.A as F_A, FOO.B as F_B, FOO.C as F_C
      , BAR.FOO_ID as B_FOO_ID, BAR.A as B_A, BAR.B as B_B, BAR.C as B_C 
   from foo f 
   join bar b on f.id = b.foo_id

这是 SQL提琴进行证明.

在不使用11.2的情况下，您可以使用未记录的函数 wm_concat 或用户定义的函数 stragg ，由汤姆·凯特(Tom Kyte)创建. Oracle Base在字符串聚合技术和关于堆栈溢出有很多帖子.

In you're not using 11.2 you can achieve exactly the same results using the undocumented function wm_concat or the user-defined function stragg, which was created by Tom Kyte. Oracle Base has an article on string aggregation techniques and there are many posts on Stack Overflow.

作为一点补充，您实际上可以通过对上述查询稍作更改来精确地创建所需的内容.您可以使用引用的标识符来创建列TABLE_NAME.COLUMN_NAME格式.您必须引用它，因为.在Oracle中不是对象名称的有效字符.这样做的好处是您可以完全获得所需的东西.不利之处在于，如果不使用select * from ...，查询创建的视图将非常麻烦.选择命名列将要求将其引用.

As a little addendum you can actually create exactly what you're looking for with a small change to the above query. You can use a quoted identifier to create a column in the TABLE_NAME.COLUMN_NAME format. You have to quote it as . is not a valid character for an object name in Oracle. The benefit of this is that you gain exactly what you want. The downside is that querying the created view is a huge pain if you don't use select * from ...; selecting named columns will require them to be quoted.

select 'create or replace view my_view as
        select '
      || listagg( table_name || '.' || column_name 
               || ' as ' 
               || '"' || table_name || '.'
               || column_name || '"', ', ')
        within group 
         ( order by case when table_name = 'FOO' then 0 else 1 end
                  , column_id
          )
       || ' from foo f
            join bar b
              on f.id = b.foo_id'
  from user_tab_columns
 where table_name in ('FOO','BAR')
        ;

此查询返回:

create or replace view my_view as 
 select FOO.ID as "FOO.ID", FOO.A as "FOO.A", FOO.B as "FOO.B", FOO.C as "FOO.C"
      , BAR.FOO_ID as "BAR.FOO_ID", BAR.A as "BAR.A"
      , BAR.B as "BAR.B", BAR.C as "BAR.C"
   from foo f 
   join bar b on f.id = b.foo_id

这篇关于以table.column格式返回Oracle列名吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！