oracle 动态选择要在 SELECT 语句中使用的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7041553/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically select the columns to be used in a SELECT statement
提问by craig
I would love to be able to use the system tables (Oracle in this case) to drive which fields are used in a SELECT statement. Something like:
我希望能够使用系统表（在本例中为 Oracle）来驱动在 SELECT 语句中使用哪些字段。就像是：
SELECT
(
select column_name
from all_tab_cols
where table_Name='CLARITY_SER'
AND OWNER='CLARITY'
AND data_type='DATE'
)
FROM CLARITY_SER
This syntax doesn't work, as the subquery returns multiple rows, instead of one row with multiple columns.
此语法不起作用，因为子查询返回多行，而不是具有多列的一行。
Is it possible to generate a SQL statement dynamically by querying the table schema information in order to select only certain columns?
是否可以通过查询表模式信息以仅选择某些列来动态生成 SQL 语句？
** edit ** Do this without using a function or procedure, if possible.
** 编辑 ** 如果可能，请在不使用函数或过程的情况下执行此操作。
回答by Tony Andrews
You can do this:
你可以这样做：
declare
l_sql varchar2(32767);
rc sys_refcursor;
begin
l_sql := 'select ';
for r in
( select column_name
from all_tab_cols
where table_Name='CLARITY_SER'
AND OWNER='CLARITY'
AND data_type='DATE'
)
loop
l_sql := l_sql || r.column_name || ',';
end loop;
l_sql := rtrim(l_sql,',') || ' from clarity_ser';
open rc for l_sql;
...
end;
回答by Allan
No, it's not possible to specify a column list dynamically in SQL. You'll need to use a procedural language to run the first query, use that to construct a second query, then run the second query.
不，不可能在 SQL 中动态指定列列表。您需要使用过程语言来运行第一个查询，使用它来构造第二个查询，然后运行第二个查询。
回答by Igby Largeman
You could use dynamic SQL. Create a function that takes the table name, owner, data type, executes the inner query and returns a comma-separated list of column names, or an array table if you prefer. Then construct the outer query and execute it with execute immediate.
您可以使用动态 SQL。创建一个函数，该函数接受表名、所有者、数据类型，执行内部查询并返回以逗号分隔的列名列表，或者如果您愿意，可以返回一个数组表。然后构造外部查询并使用execute immediate.
CREATE FUNCTION get_column_list(
table_name IN varchar2,
owner_name IN varchar2,
data_type IN varchar2)
RETURN varchar2
IS
BEGIN
...... (get columns and return comma-separated list)
END;
/
If your function returns a comma-separated list you can inline it:
如果您的函数返回一个逗号分隔的列表，您可以将其内联：
execute immediate 'select ' || get_column_list(table_name, owner_name, datatype) || ' from ' || table_name
Admittedly it's a long time since I played with oracle so I may be a bit off but I'm pretty sure this is quite doable.
诚然，我已经很长时间没有使用 oracle 了，所以我可能有点偏离，但我很确定这是非常可行的。
回答by Dave Costa
In SQLPlus you could do this:
在 SQLPlus 中，您可以这样做：
COLUMN cols NEW_VALUE cols
SELECT max( ltrim( sys_connect_by_path( column_name, ',' ), ',' ) ) cols
FROM
(
select rownum rn, column_name
from all_tab_cols
where table_Name='CLARITY_SER'
and OWNER='CLARITY'
AND data_type='DATE'
)
start with rn = 1 connect by rn = prior rn +1
;
select &cols from clarity.clarity_ser;
