oracle从select语句中读取列名 [英] oracle read column names from select statement

问题描述

我想知道如何在oracle中读取列名.好的，我知道有一个名为USER_TAB_COLUMNS的表，它提供有关它的信息，但是如果我有2或3级嵌套查询，并且我不知道列名.或者我只是使用join语句进行简单查询，而我想获取列名.怎么做?有什么想法吗?

I wonder how read column names in oracle. Ok, I know that there is table named USER_TAB_COLUMNS which gives info about it, but if I have 2 or 3 level nested query and I don't know column names. Or I just have simple query with join statement and i want to get column names. How to do that? any idey?

select * from person a
join person_details b where a.person_id = b.person_id

谢谢

推荐答案

我认为您正在寻找:

DECLARE

    sqlStr VARCHAR2(1000);
    cur INTEGER;
    columnCount INTEGER;
    describeColumns DBMS_SQL.DESC_TAB2;

BEGIN
    sqlStr := 'SELECT a.*, b.*, SYSDATE as "Customized column name" 
              FROM person a JOIN person_details b 
              WHERE a.person_id = b.person_id';

    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS2(cur, columnCount, describeColumns);      
    FOR i IN 1..columnCount LOOP
        DBMS_OUTPUT.PUT_LINE ( describeColumns(i).COL_NAME );
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cur);

END;    

这篇关于oracle从select语句中读取列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！