如何在oracle过程中将全局临时表转换为集合/类型

Question

我有一个使用临时表的过程。我想摆脱临时表并使用集合来删除 I/O。它有大约 5000 条记录。

我想将数据插入到这个集合中，然后我想访问该集合，例如：

select * from table(my_type_for_gtt)

我找不到这样的示例。我很困惑，我是否必须首先创建记录类型，然后创建为表？

有人可以举一个简单的小例子吗？

Answer 1

您正朝着正确的方向前进 - 首先创建您的类型

CREATE TYPE myEntry
AS
OBJECT
  (attr1 NUMBER,
   attr2 VARCHAR2(20)
  );

CREATE TYPE myCollection AS TABLE OF myEntry;

接下来是一些返回集合“行”的示例函数

CREATE OR REPLACE FUNCTION ExampleMyCollection1
RETURN myCollection  
IS
   lCol myCollection := myCollection(); /* Must initialise empty collection */
BEGIN
    lCol.EXTEND(1000);
    /* Populate the collection entries with objects */
    FOR i IN 1..1000 LOOP
        lCol(i) := myEntry(i,'An entry for '||i);
    END LOOP;
    RETURN lCol;
END ExampleMyCollection1;

SELECT * FROM TABLE(ExampleMyCollection1);

变体 - 这次我们使用管道，以便结果在创建时返回到查询。请注意，尽管是一个函数，但 PIPELINED 函数没有结束 RETURN。

CREATE OR REPLACE FUNCTION ExampleMyCollection2
RETURN myCollection PIPELINED
IS
BEGIN
    FOR i IN 1..1000 LOOP
        PIPE ROW(myEntry(i,'An entry for '||i));
    END LOOP;
END ExampleMyCollection2;

SELECT * FROM TABLE(ExampleMyCollection2);

要将临时表替换为纯粹的内存数据，您将需要一些东西来存储您的集合 - 即带有状态的包。

CREATE OR REPLACE PACKAGE pMyCollection
AS
   PROCEDURE AddEntry(entry IN myEntry);

   FUNCTION fCurrentCollection RETURN myCollection;

   PROCEDURE ClearEntries;

END pMyCollection;

CREATE OR REPLACE PACKAGE BODY pMyCollection
AS
   /* Stateful variable to hold the collection */
   pCollection myCollection := myCollection();

   PROCEDURE AddEntry(entry IN myEntry)
   IS
   BEGIN
      pCollection.EXTEND;
      pCollection(pCollection.LAST) := entry;
   END;

   PROCEDURE ClearEntries 
   IS
   BEGIN
      pCollection.DELETE;
   END ClearEntries;

   FUNCTION fCurrentCollection
   RETURN myCollection
   IS
   BEGIN
      /* Return whole collection - we could use pipelining and parameters to return partial elements */
      RETURN pCollection;
   END fCurrentCollection;

END pMyCollection;