Oracle 自定义类型的表函数

你好，

我有这个集合: 从自定义记录构建的嵌套表，该记录包含三个varchars字段和两个数字字段。

集合中填充了过程中的数据。

我需要对该集合进行排序，并且还需要根据其中的数据选择一些元素。
为了避免使用集合进行多次循环，我想使用DML语言进行循环，它将变得更加容易和优雅。

例如:

从MY_COLLECTION中选择 *，其中field_1 = x;

我发现有一种方法可以使用表函数。

所以我用脚本定义了模式级别 (m_lincs用户) 的类型:

--The record:
CREATE or REPLACE TYPE rCprefBalQty AS OBJECT(
     agg_chan varchar2(20)
    ,chan_id  varchar2(20)
    ,prod_cd  varchar2(30)
    ,cp_ref   number(10)
    ,nBalQty  NUMERIC);
    
/
--The nested table definition:

CREATE or replace TYPE tCpRefQty IS TABLE OF rCprefBalQty;

/

--and the function to use with the TABLE function: 

CREATE OR REPLACE FUNCTION fecth_collection(collec_in IN tCpRefQty) RETURN tCpRefQty AS
coll    tCpRefQty := tCpRefQty();
  
  BEGIN
    RETURN(coll);
  END;

但是，当我在包中使用表函数时，出现以下错误:

包M_LINCS.TESTING_TABLE的编译错误

错误: PL/SQL: ORA-00947: 值不足
行: 11
文本: 开始

错误: PL/SQL: SQL语句被忽略
行: 9
文本: temp_tshoragecoll tCpRefQty := tCpRefQty();

这是我写的包 (m_lincs也是):

CREATE OR REPLACE PACKAGE TESTING_TABLE IS

END TESTING_TABLE;
/
CREATE OR REPLACE PACKAGE BODY TESTING_TABLE IS

  PROCEDURE test_dml(tShortageColl_in IN tCpRefQty) IS
  
    temp_tShortageColl tCpRefQty := tCpRefQty();
  
  BEGIN
  
    SELECT * BULK COLLECT
    INTO   temp_tShortageColl
    FROM   TABLE(fecth_collection(tShortageColl_in))

    
    ORDER  BY agg_chan
             ,chan_id
             ,prod_cd;
  
  END test_dml;

END TESTING_TABLE;

谢谢康纳!

埃内斯托。

我不知道你的意思。

“我需要查询具有自定义类型的嵌套表，因此需要使用表函数。”

你是说你有一个物理数据库表，它有一些嵌套的表类型作为列？

如果是这样，您能给我们一些该表的DDL样本吗？

还是你的意思？

====================

附录: 我仍然迷路了-您实际上从未 * 提供 * 任何内容作为输入或输出到表。

所以我做了一些改变 -- 我希望他们能为你解释一些事情 ....

SQL> CREATE or REPLACE TYPE rCprefBalQty AS OBJECT(
  2       agg_chan varchar2(20)
  3      ,chan_id  varchar2(20)
  4      ,prod_cd  varchar2(30)
  5      ,cp_ref   number(10)
  6      ,nBalQty  NUMERIC);
  7
  8  /

Type created.

SQL>
SQL> CREATE or replace TYPE tCpRefQty IS TABLE OF rCprefBalQty;
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE FUNCTION fecth_collection RETURN tCpRefQty AS
  2    col    tCpRefQty := tCpRefQty();
  3    BEGIN
  4
  5      col.extend(3);
  6      col(1) := rCprefBalQty('agg1','chan1','prod1',1,10);
  7      col(2) := rCprefBalQty('agg2','chan2','prod2',2,20);
  8      col(3) := rCprefBalQty('agg3','chan3','prod3',3,30);
  9
 10      RETURN(col);
 11    END;
 12  /

Function created.

SQL>
SQL> select * from table(fecth_collection);

AGG_CHAN             CHAN_ID              PROD_CD                            CP_REF    NBALQTY
-------------------- -------------------- ------------------------------ ---------- ----------
agg1                 chan1                prod1                                   1         10
agg2                 chan2                prod2                                   2         20
agg3                 chan3                prod3                                   3         30

3 rows selected.

SQL>
SQL>
SQL> create table t (
  2       c1 varchar2(20)
  3      ,c2  varchar2(20)
  4      ,c3  varchar2(30)
  5      ,c4   number(10)
  6      ,c5  number);

Table created.

SQL>
SQL> insert into t values ('a','b','c',1,2);

1 row created.

SQL> insert into t values ('e','f','g',5,6);

1 row created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION fecth_collection(rc sys_refcursor) RETURN tCpRefQty AS
  2
  3      v1 varchar2(20);
  4      v2  varchar2(20);
  5      v3  varchar2(30);
  6      v4   number(10);
  7      v5  number;
  8
  9    col    tCpRefQty := tCpRefQty();
 10    BEGIN
 11      loop
 12        fetch rc into v1,v2,v3,v4,v5;
 13        exit when rc%notfound;
 14      col.extend;
 15      col(col.count) := rCprefBalQty(v1,v2,v3,v4,v5);
 16      end loop;
 17
 18      RETURN(col);
 19    END;
 20  /

Function created.

SQL>
SQL> select * from table(fecth_collection(cursor(select * from t)));

AGG_CHAN             CHAN_ID              PROD_CD                            CP_REF    NBALQTY
-------------------- -------------------- ------------------------------ ---------- ----------
a                    b                    c                                       1          2
e                    f                    g                                       5          6

2 rows selected.