列出查询（Oracle）的所有列。

32 浏览
0 Comments

列出查询（Oracle）的所有列。

在Oracle中是否有一条SQL语句可以让我提取查询返回的所有列？

结合Java，我可以执行查询并处理元数据，但我寻找一种方便的方式。

admin 更改状态以发布 2023年5月21日
0

1 答案

0 Comments

尝试这个：\n

DECLARE 
    sqlstr VARCHAR2(1000);
    cur INTEGER;
    columnCount INTEGER;
    describeColumns DBMS_SQL.DESC_TAB;
BEGIN 
    cur := DBMS_SQL.OPEN_CURSOR;
    sqlstr := 'SELECT * FROM TBL_A A, TBL_B B WHERE A.ID = B.ID';
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(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;

\n更新：为了获得结果集，您可以这样做：\n

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(30);
CREATE OR REPLACE FUNCTION GetColumns(sqlstr IN VARCHAR2) 
   RETURN VARCHAR_TABLE_TYPE PIPELINED AS
    cur INTEGER;
    columnCount INTEGER;
    describeColumns DBMS_SQL.DESC_TAB;
BEGIN 
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(cur, columnCount, describeColumns);   
    FOR i IN 1..columnCount LOOP
        PIPE ROW(describeColumns(i).col_name);
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cur);
   RETURN;
END;
SELECT * FROM TABLE(GetColumns('SELECT * FROM TBL_A A, TBL_B B WHERE A.ID = B.ID'));

0