oracle 从查询返回列名的表中选择特定列的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11242953/
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
select data of specific column from table where column names are returned by query
提问by Rohan
I have written 2 separate queries
我写了 2 个单独的查询
1)
1)
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME =
(SELECT DISTINCT UT.TABLE_NAME
FROM USER_TABLES UT
WHERE UT.TABLE_NAME = 'MY_TABLE')
AND COLUMN_NAME NOT IN ('AVOID_COLUMN')
2)
2)
SELECT *
FROM MY_TABLE MT
WHERE MT.COL1 = '1'
The 1st query returns the names of all the columns except the one I want to avoid. The 2nd one returns data of all the columns from the table. Is there some way to merge these queries so that only those column's data is selected from the 2nd query, which are returned from the 1st query?
第一个查询返回除我想避免的列之外的所有列的名称。第二个返回表中所有列的数据。有什么方法可以合并这些查询，以便仅从第一个查询返回的第二个查询中选择那些列的数据？
Thanks in advance
提前致谢
回答by Frank Schmitt
You'll have to use dynamic SQL for this (BTW, I got rid of the subselect for the USER_TABLES query - it's unnecessary):
您必须为此使用动态 SQL（顺便说一句，我删除了 USER_TABLES 查询的子选择 - 这是不必要的）：
var cur refcursor
/
declare
v_stmt varchar2(4000);
begin
v_stmt := 'SELECT ';
for cur in (
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME =
'MY_TABLE'
AND COLUMN_NAME NOT IN ('AVOID_COLUMN')
)
loop
v_stmt := v_stmt || cur.column_name || ',';
end loop;
-- get rid of trailing ','
v_stmt := regexp_replace(v_stmt, ',$', '');
v_stmt := v_stmt || ' from my_table MT WHERE MT.COL1 = ''1''';
dbms_output.put_line(v_stmt);
open :cur for v_stmt;
end;
