从表函数返回N列 [英] Return N columns from a table function

问题描述

我需要实现一个表功能，我将提交一个未知列数的请求.看起来像:

I need to implement a table function, which I will submit a request with an unknown number of columns. It looks like:

SELECT * from TABLE (function())

where函数，例如'SELECT x, y FROM z.我不知道该怎么做，所以我想听听某种解决方法，只是一个想法.

where function, for example'SELECT x, y FROM z. I don't know how do this, so I'd like to hear some sort of way to solve, just as an idea.

推荐答案

下面是一个完全动态SQL的示例，您可以插入任何SELECT语句，并打印出相应的HTML:

Here is an example for a fully dynamic SQL, you can insert any SELECT statement and it prints out a corresponding HTML:

CREATE OR REPLACE PROCEDURE HtmlTable(sqlStr IN VARCHAR2) IS

    cur INTEGER := DBMS_SQL.OPEN_CURSOR;
    columnCount INTEGER;
    describeColumns DBMS_SQL.DESC_TAB;
    res INTEGER;
    c INTEGER;
    aCell  VARCHAR2(4000);

BEGIN

    DBMS_OUTPUT.PUT_LINE('<table>');

    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(cur, columnCount, describeColumns);

    DBMS_OUTPUT.PUT_LINE('<thead><tr>');
    FOR i IN 1..columnCount LOOP
        DBMS_OUTPUT.PUT_LINE('  <td>'||describeColumns(i).COL_NAME||'</td>');
        DBMS_SQL.DEFINE_COLUMN(cur, i, aCell, 4000);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('</tr></thead>');

    res := DBMS_SQL.EXECUTE(cur);

    DBMS_OUTPUT.PUT_LINE('<tbody>');
    WHILE (DBMS_SQL.FETCH_ROWS(cur) > 0) LOOP
        DBMS_OUTPUT.PUT_LINE('<tr>');
        c := 1;
        WHILE (c <= columnCount) LOOP
            DBMS_SQL.COLUMN_VALUE(cur, c, aCell);
            DBMS_OUTPUT.PUT_LINE('  <td>'||aCell||'</td>');
            c := c + 1;
        END LOOP;       
        DBMS_OUTPUT.PUT_LINE('</tr>');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('</tbody>');
    DBMS_OUTPUT.PUT_LINE('</table>');
    DBMS_SQL.CLOSE_CURSOR(cur);

END HtmlTable;

将此用作您的应用程序的基础.然后，您可以像这样执行它:

Use this as a base for your application. Then you can execute it like this:

BEGIN
    HtmlTable('SELECT x, y FROM z');
END;

这篇关于从表函数返回N列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！