我可以在Oracle包中創建“表值函式”嗎？

我對甲骨文世界比較陌生。我的大部分經驗是使用 SQL Server。

我正在撰寫將受益于“引數化視圖”的代碼，也就是 SQL Server 中的“表值函式”(tvf)。

我在這里找到了一個很好的例子，我正在嘗試遵循：Oracle: Return a ?table? from a function

但我需要把我的放在一個包裹里，而我正和它玩得很開心。

這是我正在嘗試的示例：

CREATE OR REPLACE PACKAGE pkg_test_oracle_tvfs IS

    TYPE t_tvf_row IS RECORD(
        i NUMBER,
        n VARCHAR2(30));

    TYPE t_tvf_tbl IS TABLE OF t_tvf_row INDEX BY BINARY_INTEGER;

    FUNCTION fn_get_tvf(p_max_num_rows INTEGER) RETURN t_tvf_tbl;

END pkg_test_oracle_tvfs;

CREATE OR REPLACE PACKAGE BODY pkg_test_oracle_tvfs IS

    FUNCTION fn_get_tvf(p_max_num_rows INTEGER) RETURN t_tvf_tbl IS
        v_tvf_tbl t_tvf_tbl;
    BEGIN
    
        SELECT pkg_test_oracle_tvfs.t_tvf_row(rownum,
                                              uo.object_name)
          BULK COLLECT
          INTO v_tvf_tbl
          FROM user_objects uo
         WHERE rownum <= p_max_num_rows;
    
        RETURN v_tvf_tbl;
    
    END;
END pkg_test_oracle_tvfs;

意圖是我可以做類似的事情：

SELECT * FROM pkg_test_oracle_tvfs.fn_get_tvf(5);

或者

SELECT * FROM TABLE(pkg_test_oracle_tvfs.fn_get_tvf(5));

（我不清楚是否需要 TABLE() 。）

但是當我編譯包時，我得到：

Compilation errors for PACKAGE BODY XXX.PKG_TEST_ORACLE_TVFS

Error: PL/SQL: ORA-00913: too many values
Line: 11
Text: FROM user_objects uo

Error: PL/SQL: SQL Statement ignored
Line: 7
Text: SELECT pkg_test_oracle_tvfs.t_tvf_row(rownum,

我在這里做錯了什么？為什么這種語法在包外似乎可以正常作業，但在包內卻不行？

我是否需要使用Oracle 中描述的“管道”樣式來構建表：流水線 PL/SQL 函式如果是這樣，為什么這個示例與我一直試圖遵循的示例不同？

謝謝！

uj5u.com熱心網友回復：

您最初的錯誤是因為您選擇的是記錄型別，而不是物件型別，所以您不需要建構式：

        SELECT rownum, uo.object_name
          BULK COLLECT
          INTO v_tvf_tbl

fiddle，這表明它現在可以編譯，但是您不能從 SQL 呼叫它，因為已經解釋了原因的 MTO。

作為創建物件型別的替代方法，如果您修改集合型別，您可以按照您的建議使用流水線函式：

CREATE OR REPLACE PACKAGE pkg_test_oracle_tvfs IS

    TYPE t_tvf_row IS RECORD(
        i NUMBER,
        n VARCHAR2(30));

    TYPE t_tvf_tbl IS TABLE OF t_tvf_row;

    FUNCTION fn_get_tvf(p_max_num_rows INTEGER) RETURN t_tvf_tbl PIPELINED;

END pkg_test_oracle_tvfs;
/

CREATE OR REPLACE PACKAGE BODY pkg_test_oracle_tvfs IS

    FUNCTION fn_get_tvf(p_max_num_rows INTEGER) RETURN t_tvf_tbl PIPELINED IS
        v_tvf_tbl t_tvf_tbl;
    BEGIN
    
        SELECT rownum, uo.object_name
          BULK COLLECT
          INTO v_tvf_tbl
          FROM user_objects uo
         WHERE rownum <= p_max_num_rows;

        FOR i IN 1..v_tvf_tbl.COUNT LOOP
          PIPE ROW (v_tvf_tbl(i));
        END LOOP;

        RETURN;
    
    END;
END pkg_test_oracle_tvfs;
/

SELECT * FROM pkg_test_oracle_tvfs.fn_get_tvf(5);

SELECT * FROM TABLE(pkg_test_oracle_tvfs.fn_get_tvf(5));

小提琴

uj5u.com熱心網友回復：

有一個根本性的缺陷；RECORDs 和關聯陣列 ( )都是TABLE OF ... INDEX BY ...僅限 PL/SQL 的資料型別，不能在 SQL 陳述句中使用。

如果您想在 SQL 陳述句中使用類似記錄和類似陣列的資料結構，那么您需要在 SQL 范圍內定義它，這意味著您不能在包中定義它，并且需要使用OBJECT型別和嵌套- 表集合型別：

CREATE TYPE t_tvf_row IS OBJECT(
  i NUMBER,
  n VARCHAR2(30)
);

CREATE TYPE t_tvf_tbl IS TABLE OF t_tvf_row;

然后：

CREATE OR REPLACE PACKAGE pkg_test_oracle_tvfs IS
  FUNCTION fn_get_tvf(
    p_max_num_rows INTEGER
  ) RETURN t_tvf_tbl;
END pkg_test_oracle_tvfs;
/

CREATE OR REPLACE PACKAGE BODY pkg_test_oracle_tvfs IS
  FUNCTION fn_get_tvf(
    p_max_num_rows INTEGER
  ) RETURN t_tvf_tbl
  IS
    v_tvf_tbl t_tvf_tbl;
  BEGIN
    SELECT t_tvf_row(
             rownum,
             object_name
           )
    BULK COLLECT INTO v_tvf_tbl
    FROM   (
      SELECT object_name
      FROM   user_objects
      ORDER BY object_name
    )
    WHERE rownum <= p_max_num_rows;

    RETURN v_tvf_tbl;
  END;
END pkg_test_oracle_tvfs;
/

小提琴

為什么這個例子與我一直試圖遵循的例子不同？

因為您在 PL/SQL 范圍（一個包）中定義只能在 PL/SQL 中使用的資料型別（因為記錄和關聯陣列是 PL/SQL-only 資料型別），然后嘗試在 SQL 中使用它們范圍（SELECT宣告）。您下面的示例將資料型別定義OBJECT為非關聯陣列，并在 SQL 范圍內（包外）定義它們，然后在 SQL 陳述句中使用它們是允許的。

標籤：sql甲骨文功能包裹

上一篇：如何列印函式的回傳值？

下一篇：onClick={function},onClick={()=>function},onClick={function()}&onClick={()=>function()}有什么區別？[