oracle Pl/SQL- 从查询中获取列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18020060/
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
Pl/SQL- Get column names from a query
提问by bobroxsox
I'm using Pl/SQL with Oracle Database 11g.
我在 Oracle 数据库 11g 中使用 Pl/SQL。
I'm writing a function that takes in a select statement as a parameter (varchar2). The function uses a for loop to go over the rows and apply formatting to specific columns, and output the whole thing. Basically, I need some way to get the column names so that I can display them at the top. I know there are various ways to do this for tables, but since this query is passed in, all columns may not have been selected, aliases may have been used, etc.
我正在编写一个将 select 语句作为参数 (varchar2) 的函数。该函数使用 for 循环遍历行并将格式应用于特定列，并输出整个内容。基本上，我需要某种方法来获取列名，以便我可以将它们显示在顶部。我知道有多种方法可以对表执行此操作，但是由于传入了此查询，因此可能尚未选择所有列，可能已使用别名等。
Is there a way I can select out the column names from this query?
Ideally something like: select column_names from (subquery)
有没有办法可以从此查询中选择列名？
理想情况下是这样的：select column_names from (subquery)
回答by dseibert
I believe you can use DESCRIBE_COLUMNS to do this. Just pass in the cursor and the other required parameters.
我相信你可以使用 DESCRIBE_COLUMNS 来做到这一点。只需传入游标和其他必需的参数。
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1026120
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1026120
declare
v_sql varchar2(32767) := 'select 1 column1, 2 column2 from dual';
v_cursor_id integer;
v_col_cnt integer;
v_columns dbms_sql.desc_tab;
begin
v_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor_id, v_sql, dbms_sql.native);
dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns);
for i in 1 .. v_columns.count loop
dbms_output.put_line(v_columns(i).col_name);
end loop;
dbms_sql.close_cursor(v_cursor_id);
exception when others then
dbms_sql.close_cursor(v_cursor_id);
raise;
end;
/
Output:
COLUMN1
COLUMN2
回答by Phi
Based on dseibert's answer, I created function for usage:
根据dseibert的回答，我创建了使用函数：
create type cols_name
as table of varchar2(32767)
/
CREATE OR REPLACE FUNCTION GET_COLUMNS_NAME(p_selectQuery IN VARCHAR2) RETURN cols_name PIPELINED IS
v_cursor_id integer;
v_col_cnt integer;
v_columns dbms_sql.desc_tab;
begin
v_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor_id, p_selectQuery, dbms_sql.native);
dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns);
for i in 1 .. v_columns.count loop
pipe row(v_columns(i).col_name);
end loop;
dbms_sql.close_cursor(v_cursor_id);
return;
exception when others then
dbms_sql.close_cursor(v_cursor_id);
raise;
end;
/
Using it:
使用它：
select * from TABLE(get_columns_name('select 1 column1, 2 column2 from dual'));
Results:
结果：
**COLUMN_VALUE**
COLUMN1
COLUMN2
