plsql - 使用用户定义类型努力收集和返回 DBMS_SQL.COLUMN_VALUE

Question

这是我在 StackOverflow 上的第一个问题，我是自学的，所以请保持温和。

我的目标是能够通过生成的包中的动态查询/游标批量收集 header /值

    SELECT * 
    BULK COLLECT INTO l_cur_val 
    FROM TABLE (CUSTOM.GET_REF_VAL(l_cursor));

我在与 GET_REF_VAL 类似的代码块中成功地为列标题工作(在输入/* COLUMN VALUES */部分之前只需返回 l_col_head )。

当我尝试将 DBMS_SQL.COLUMN_VALUE 的返回值分配到我的 t_col_val UDT 时，错误就来了。 (类型定义在注释中)

类型

CREATE OR REPLACE TYPE CUSTOM.r_col_val IS OBJECT (l_col_val VARCHAR2(250 byte));
CREATE OR REPLACE TYPE CUSTOM.t_col_val IS TABLE OF CUSTOM.r_col_val;

错误

--l_val(n) := r_col_val(l_dum_val);    --returns: ORA-06533: Subscript beyond count
--l_val(n) := l_dum_val;               --returns: PLS-00382: expression is of wrong type

表返回函数 GET_REF_VAL

CREATE OR REPLACE FUNCTION 
CUSTOM.GET_REF_VAL
    (
    p_cursor IN SYS_REFCURSOR
    )
RETURN t_col_val
IS

l_val t_col_val := t_col_val();
l_col t_col_head := t_col_head();

n INTEGER := 0;

l_cursor        SYS_REFCURSOR := p_cursor;
l_cursor_id     INTEGER;
l_dummy         INTEGER;
l_col_cnt       INTEGER;
l_tab_rec       DBMS_SQL.DESC_TAB2;
l_dum_val       VARCHAR2(250);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(l_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS2(l_cursor_id, l_col_cnt, l_tab_rec);

/* COLUMN HEADERS */
FOR r IN 1..l_col_cnt
    LOOP

        l_col.extend;
        n := n + 1;
        l_col(n) := r_col_head(l_tab_rec(r).col_name);
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_dum_val, 4000); 

    END LOOP;

/* COLUMN VALUES */
LOOP 
    IF DBMS_SQL.FETCH_ROWS(l_cursor_id)> 0 THEN 

    FOR i IN 1 .. l_col_cnt
    LOOP

        l_val.extend;
        n := n + 1;
        DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_dum_val);        
        DBMS_OUTPUT.PUT_LINE(l_dum_val); -- This return l_dum_val with no issues

        --l_val(n) := r_col_val(l_dum_val); -- ORA-06533: Subscript beyond count
        --l_val(n) := l_dum_val; --PLS-00382: expression is of wrong type

    END LOOP;

    ELSE 
    EXIT; 
    END IF;
END LOOP;  


DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
RETURN l_val;

END;
/

执行 block

DECLARE

l_sql_stmt VARCHAR(10000) :=
q'!
    SELECT
        SYS_CONTEXT('USERENV','OS_USER') AS OS_USER ,
        SYS_CONTEXT('USERENV','SESSION_USER') AS SESSION_USER,
        SYS_CONTEXT('USERENV','ISDBA') AS ISDBA,
        SYS_CONTEXT('USERENV','SID') AS SID,
        SYS_CONTEXT('USERENV','CURRENT_SQL') AS CURRENT_SQL,
        SYS_CONTEXT('USERENV','DB_NAME') AS DB_NAME,
        SYS_CONTEXT('USERENV','HOST') AS HOST,
        SYS_CONTEXT('USERENV','IP_ADDRESS') AS IP_ADDRESS,
        SYS_CONTEXT('USERENV','SERVICE_NAME') AS SERVICE_NAME
    FROM
        DUAL
!';

l_cursor        SYS_REFCURSOR;
l_cursor_id     INTEGER;
l_dummy         VARCHAR2(50);

TYPE t_cur_head IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
l_cur_head t_cur_head;

TYPE t_cur_val IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
l_cur_val t_cur_val;    

BEGIN

l_cursor := CUSTOM.GET_REF_CUR(l_sql_stmt);

IF l_cursor%ISOPEN
THEN

    /* Header fetch works fine */

    /*
    SELECT * 
    BULK COLLECT INTO l_cur_head 
    FROM TABLE (CUSTOM.GET_REF_HEAD(l_cursor));

    FOR i IN 1 .. l_cur_head.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE(l_cur_head(i));
    END LOOP;
    */


    /* Values fetch fails */

    SELECT * 
    BULK COLLECT INTO l_cur_val 
    FROM TABLE (CUSTOM.GET_REF_VAL(l_cursor));

    FOR i IN 1 .. l_cur_val.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE(l_cur_val(i));
    END LOOP;

END IF;

END;

所以我想总之我想知道的是

a) 如何使用用户定义的类型处理 dbms_sql.column_value 的返回值

b) 如何将 VARCHAR2 值 (l_dum_val) 插入到具有 VARCHAR2 记录 (l_col_val) 的 UDT 对象中

c) 代码中还有其他明显的错误/不良做法吗？

感谢您的耐心等待。

Answer 1

您注释掉的第一行:

    --l_val(n) := r_col_val(l_dum_val); -- ORA-06533: Subscript beyond count

收到该错误是因为您没有重置 n在第二个循环之前为零。你根本不需要那个计数器变量，你可以使用 l_val.count相反(在两个循环中)。

您注释掉的第二行:

    --l_val(n) := l_dum_val; --PLS-00382: expression is of wrong type

收到该错误是因为 l_val(n)指向一个具有字符串属性的对象；它不是直接指向一个字符串。因此，您可以通过其构造函数分配一个新对象；这是第一个版本试图做的，但它应该是:

    l_val(l_val.count) := r_col_val(l_dum_val);

一旦该对象存在，您可以直接分配属性:

    l_val(some_index).l_col_val := r_col_val(l_dum_val);

但是您必须先创建一个对象，然后才能访问其属性，并且由于您只有一个默认构造函数，因此在这种情况下，这可能对您没有多大用处。

因此，通过这些更改(以及一些缩进，并稍微重构以摆脱 else )，现在可以使用:

CREATE OR REPLACE FUNCTION 
GET_REF_VAL
    (
    p_cursor IN SYS_REFCURSOR
    )
RETURN t_col_val
IS

    l_val t_col_val := t_col_val();
    l_col t_col_head := t_col_head();

    l_cursor        SYS_REFCURSOR := p_cursor;
    l_cursor_id     INTEGER;
    l_dummy         INTEGER;
    l_col_cnt       INTEGER;
    l_tab_rec       DBMS_SQL.DESC_TAB2;
    l_dum_val       VARCHAR2(250);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(l_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS2(l_cursor_id, l_col_cnt, l_tab_rec);

    /* COLUMN HEADERS */
    FOR r IN 1..l_col_cnt
    LOOP

        l_col.extend;
        l_col(l_col.count) := r_col_head(l_tab_rec(r).col_name);
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_dum_val, 4000); 

    END LOOP;

    /* COLUMN VALUES */
    LOOP 
        IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN 
            EXIT; 
        END IF;

        FOR i IN 1 .. l_col_cnt
        LOOP

            l_val.extend;
            DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_dum_val);        
            DBMS_OUTPUT.PUT_LINE(l_dum_val);
            l_val(l_val.count) := r_col_val(l_dum_val);

        END LOOP;
    END LOOP;  

    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
    RETURN l_val;

END;
/

db<>fiddle

您的代码建议您有一个单独的函数来获取标题，因此您正在复制代码。您可以使用两个输出变量简化为一个过程:

CREATE OR REPLACE PROCEDURE
GET_REF_HEAD_AND_VAL
    (
    p_cursor IN OUT SYS_REFCURSOR,
    p_col OUT SYS.odcivarchar2list,
    p_val OUT SYS.odcivarchar2list
    )
IS

    l_cursor_id     INTEGER;
    l_col_cnt       INTEGER;
    l_tab_rec       DBMS_SQL.DESC_TAB3;
    l_value         VARCHAR2(250 byte);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS3(l_cursor_id, l_col_cnt, l_tab_rec);

    /* COLUMN HEADERS */
    p_col := SYS.odcivarchar2list();
    FOR r IN 1..l_col_cnt
    LOOP

        p_col.extend;
        p_col(p_col.count) := l_tab_rec(r).col_name;
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_value, 250);

    END LOOP;

    /* COLUMN VALUES */
    p_val := SYS.odcivarchar2list();
    LOOP 
        IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN 
            EXIT; 
        END IF;

        FOR i IN 1 .. l_col_cnt
        LOOP

            p_val.extend;
            DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_value);        
            --DBMS_OUTPUT.PUT_LINE(l_dum_val);
            p_val(p_val.count) := l_value;

        END LOOP;
    END LOOP;  

    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);

END;
/

这是使用内置集合类型而不是创建您自己的对象/表类型(尽管您仍然可以创建自己的集合类型；尽管它不需要使用对象)。

db<>fiddle