从Oracle函数返回一个表 [英] Returning a table from an Oracle function

问题描述

我在这里看到了很多解决方案，试图解决这个问题，他们已经非常成功，但现在我处于一些错误的杂草中，而这些错误似乎无法通过。

我在Oracle 11g上。我需要一个函数来返回记录集（表）。这里是我使用的代码：

$ p $ CREATE TYPE T_TABLE is OBJECT
（
Field1 int
，Field2 int
）;

CREATE TYPE T_TABLE_COLL IS TABLE T_TABLE;

CREATE OR REPLACE FUNCTION FN_MyFunction
RETURN T_TABLE_COLL
IS
BEGIN
FOR IN（选择Field1，Field2 FROM Table1）LOOP
IF I.Field1 = 1 THEN
BEGIN
INSERT INTO T_TABLE
SELECT Field1，Field2
FROM Table2
WHERE Field2 = I.Field2;
END;
ELSIF I.Field1 = 2 THEN
BEGIN
INSERT INTO T_TABLE
SELECT Field1，Field2
FROM Table2
WHERE Field2 = I.Field2;
END;
END IF;
END LOOP;
RETURN T_SMRYACCT_TABLE_COLL;
END;


我从这里收到的错误是：

1. 在FUNCTION FN_MyFunction行和PL / SQL上忽略语句：ORA-04044：每行不允许使用过程，函数，包或类型INSERT INTO T_TABLE_COLL行




2. PLS-00330：无效使用RETURN行上的类型名称或子类型名称

我在做什么错误的表类型？

  CREATE OR REPLACE FUNCTION FN_MyFunction 
 RETURN T_TABLE_COLL 
 IS 
 l_res_coll T_TABLE_COLL; 
 l_index number; 
 BEGIN 
 l_res_coll：= T_TABLE_COLL（）; 
 FOR I IN（SELECT col1，col2 FROM Table1）
 LOOP 
 IF I.col1 = 1 THEN 
 l_res_coll.extend; 
 l_index：= l_res_coll.count; 
 l_res_coll（l_index）：= T_TABLE（i.col1，i.col2）; 
 END IF; 
 END LOOP; 
返回l_res_coll; 
 END; 
  

行动中的功能

  select * 
 from table（FN_MyFunction（））
  

有关收藏夹是什么以及如何使用它们的更多信息阅读此

I've looked at many solutions here to try to solve this and they have gotten pretty far but now I'm in the weeds on some errors that I can#t seem to get past.

I am on Oracle 11g. I need a function to return a record set (table). Here is the code I'm using:

CREATE TYPE T_TABLE IS OBJECT
(
    Field1 int
    , Field2 int
);

CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;

CREATE OR REPLACE FUNCTION FN_MyFunction
RETURN T_TABLE_COLL 
IS
BEGIN
  FOR I IN (SELECT Field1, Field2 FROM Table1) LOOP
    IF I.Field1 = 1 THEN
        BEGIN           
            INSERT INTO T_TABLE     
            SELECT Field1, Field2
            FROM Table2
            WHERE Field2 = I.Field2;
        END;
    ELSIF I.Field1 = 2 THEN
        BEGIN           
            INSERT INTO T_TABLE     
            SELECT Field1, Field2
            FROM Table2
            WHERE Field2 = I.Field2;
        END;  
  END IF;
  END LOOP;
  RETURN T_SMRYACCT_TABLE_COLL;
END;

The errors I receive from this are:

1. Statement Ignored on the FUNCTION FN_MyFunction line and PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here on each line INSERT INTO T_TABLE_COLL line

2. PLS-00330: invalid use of type name or subtype name on the RETURN line

What am I doing wrong with the table types?

解决方案

T_TABLE_COLL is a collection. You cannot use insert on collections.

CREATE OR REPLACE FUNCTION FN_MyFunction
RETURN T_TABLE_COLL
IS
  l_res_coll T_TABLE_COLL;
  l_index number;
BEGIN
  l_res_coll := T_TABLE_COLL();
  FOR I IN (SELECT col1, col2 FROM Table1)
  LOOP
    IF I.col1 = 1 THEN
      l_res_coll.extend;
      l_index := l_res_coll.count;  
      l_res_coll(l_index):= T_TABLE(i.col1, i.col2);
    END IF;
  END LOOP;
  return l_res_coll;
END;

Function in action

select *
  from table(FN_MyFunction())

To get more information about what collections are and how to use them read this

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