Oracle 函数返回包规范中定义的记录类型的表

Question

我有一个函数，它获取字符串和分隔符作为输入参数，并将拆分的字符串作为表返回。当我运行这个函数时：

create or replace PACKAGE split_pkg AS
TYPE triplex_record IS RECORD (
                id      NUMBER,
                data    VARCHAR2(4000),
                data1   VARCHAR2(4000),
                data2   VARCHAR2(4000)
                );
TYPE triplex_tab IS TABLE OF triplex_record;
FUNCTION triplex (p_txt IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN triplex_tab ;
END split_pkg;
/
create or replace PACKAGE BODY split_pkg AS
FUNCTION triplex (p_txt IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN triplex_tab
AS
    triplex_tbl     triplex_tab := triplex_tab();
BEGIN
    FOR i IN 1..TRUNC((REGEXP_COUNT(p_txt , '[^' || p_delimiter || ']+'))/3)
    LOOP
        triplex_tbl.EXTEND;
        triplex_tbl(triplex_tbl.LAST).id := i;
        triplex_tbl(triplex_tbl.LAST).data := TRIM(REGEXP_SUBSTR(p_txt, '[^' || p_delimiter || ']+' , 1, 3 * i - 2));
        triplex_tbl(triplex_tbl.LAST).data1 := TRIM(REGEXP_SUBSTR(p_txt, '[^' || p_delimiter || ']+' , 1, 3 * i - 1));
        triplex_tbl(triplex_tbl.LAST).data2 := TRIM(REGEXP_SUBSTR(p_txt, '[^' || p_delimiter || ']+' , 1, 3 * i));
        --PIPE ROW(triplex_tbl);
    END LOOP;
    RETURN triplex_tbl;
END triplex;
END split_pkg;

我得到以下错误：

ORA-00902：数据类型无效

如果创建为流水线函数，我没有问题，我只想在 PACKAGE 的标头中使用 RECORD TYPE 定义我的 TYPE，而不是在架构级别使用 CREATE TYPE。怎么能做到呢？

Answer 1

没有看到软件包的问题：

SET SERVEROUTPUT ON;
DECLARE
  triplets SPLIT_PKG.triplex_tab;
BEGIN
  triplets := split_pkg.triplex( 'a1,a2,a3,b1,b2,b3,c1,c2,c3,d1', ',' );
  FOR i IN 1 .. triplets.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( triplets(i).data );
    DBMS_OUTPUT.PUT_LINE( triplets(i).data1 );
    DBMS_OUTPUT.PUT_LINE( triplets(i).data2 );
    DBMS_OUTPUT.PUT_LINE( '------' );
  END LOOP;
END;
/

输出：

anonymous block completed
a1
a2
a3
------
b1
b2
b3
------
c1
c2
c3
------

但是，如果要在SQL中使用结果，则需要在SQL中而不是在包中定义类型。

CREATE TYPE triplex_obj IS OBJECT (
  id      NUMBER,
  data    VARCHAR2(4000),
  data1   VARCHAR2(4000),
  data2   VARCHAR2(4000)
);
CREATE TYPE triplex_tab IS TABLE OF triplex_obj;
create or replace PACKAGE split_pkg AS
FUNCTION triplex (p_txt IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN triplex_tab ;
END split_pkg;
/
SHOW ERRORS;
create or replace PACKAGE BODY split_pkg AS
FUNCTION triplex (p_txt IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN triplex_tab
AS
    triplex_tbl     triplex_tab := triplex_tab();
BEGIN
    FOR i IN 1..TRUNC((REGEXP_COUNT(p_txt , '[^' || p_delimiter || ']+'))/3)
    LOOP
        triplex_tbl.EXTEND;
        triplex_tbl(triplex_tbl.LAST) := triplex_obj(
          i,
          TRIM(REGEXP_SUBSTR(p_txt, '[^' || p_delimiter || ']+' , 1, 3 * i - 2)),
          TRIM(REGEXP_SUBSTR(p_txt, '[^' || p_delimiter || ']+' , 1, 3 * i - 1)),
          TRIM(REGEXP_SUBSTR(p_txt, '[^' || p_delimiter || ']+' , 1, 3 * i - 0))
        );
    END LOOP;
    RETURN triplex_tbl;
END triplex;
END split_pkg;
/
SHOW ERRORS;

然后：

SET SERVEROUTPUT ON;
DECLARE
  triplets triplex_tab;
BEGIN
  triplets := split_pkg.triplex( 'a1,a2,a3,b1,b2,b3,c1,c2,c3,d1', ',' );
  FOR i IN 1 .. triplets.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( triplets(i).data );
    DBMS_OUTPUT.PUT_LINE( triplets(i).data1 );
    DBMS_OUTPUT.PUT_LINE( triplets(i).data2 );
    DBMS_OUTPUT.PUT_LINE( '------' );
  END LOOP;
END;
/

提供与上述相同的输出，但您也可以执行以下操作：

SELECT *
FROM   TABLE( split_pkg.triplex( 'a1,a2,a3,b1,b2,b3,c1,c2,c3,d1', ',' ) );

其中输出：

id      Data    Data1   Data2
------- ------- ------- -------
1       a1      a2      a3
2       b1      b2      b3
3       c1      c2      c3