Pl/SQL- 从查询中获取列名

2021-12-24 00:00:00 sql oracle plsql

我在 Oracle 数据库 11g 中使用 Pl/SQL.

I'm using Pl/SQL with Oracle Database 11g.

我正在编写一个函数，它将 select 语句作为参数 (varchar2).该函数使用 for 循环遍历行并将格式应用于特定列，并输出整个内容.基本上，我需要某种方法来获取列名，以便我可以将它们显示在顶部.我知道有多种方法可以对表执行此操作，但是由于传入了此查询，因此可能未选择所有列、可能已使用别名等.

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 column_names from (subquery)

Is there a way I can select out the column names from this query?
Ideally something like:
select column_names from (subquery)

推荐答案

我相信你可以使用 DESCRIBE_COLUMNS 来做到这一点.只需传入游标和其他必需的参数即可.

I believe you can use DESCRIBE_COLUMNS to do this. Just pass in the cursor and the other required parameters.

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

相关文章