如何在oracle过程中将全局临时表转换为集合/类型 [英] how to convert global temp table to collection/type in oracle procedure

问题描述

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

I have a procedure that is using a temp table. I want to get rid of the temp table and use a collection to remove I/O. it has about 5000 records.

我想将数据插入到该集合中，然后要访问该集合，例如:

I want to insert data into this collection then I want to access the collection like:

select * from table(my_type_for_gtt)

我找不到这样的例子.我很困惑，我必须先创建一个记录类型，然后再创建为表吗?

I could not find an example of this. I'm confused, do I have to first create a record type and then create as table of?

有人可以举个简短的例子吗?

can someone please show a quick small example?

推荐答案

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

You are heading in the right direction - first create your types

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

CREATE TYPE myCollection AS TABLE OF myEntry;

接下来是一些示例函数，它们返回集合的行"

Next some example functions returning 'rows' of your collection

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.

Variation - this time we use pipelining, so that the results are returned to the query as they are created. Note that despite being a function, there is no end RETURN for a PIPELINED function.

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);

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

To replace your temp table with purely in-memory data, you will need something to store your collection in - i.e. a package with state.

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;

这篇关于如何在oracle过程中将全局临时表转换为集合/类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！