创建一个返回表的Oracle函数 [英] Create an Oracle function that returns a table

问题描述

我正在尝试在包中创建一个返回表的函数.我希望在包中一次调用该函数，但能够多次重复使用它的数据.当我知道我在Oracle中创建临时表时，我希望将其保持干燥.

I'm trying to create a function in package that returns a table. I hope to call the function once in the package, but be able to re-use its data mulitple times. While I know I create temp tables in Oracle, I was hoping to keep things DRY.

到目前为止，这就是我所拥有的:

So far, this is what I have:

标题:

CREATE OR REPLACE PACKAGE TEST AS 

    TYPE MEASURE_RECORD IS RECORD (
      L4_ID VARCHAR2(50),
      L6_ID VARCHAR2(50),
      L8_ID VARCHAR2(50),
      YEAR NUMBER,
      PERIOD NUMBER,
      VALUE NUMBER
    );

    TYPE MEASURE_TABLE IS TABLE OF MEASURE_RECORD;

    FUNCTION GET_UPS(
      TIMESPAN_IN IN VARCHAR2 DEFAULT 'MONTLHY',
      STARTING_DATE_IN DATE,
      ENDING_DATE_IN DATE  
    ) RETURN MEASURE_TABLE;

END TEST;

身体:

CREATE OR REPLACE PACKAGE BODY TEST AS 

  FUNCTION GET_UPS (
    TIMESPAN_IN IN VARCHAR2 DEFAULT 'MONTLHY',
    STARTING_DATE_IN DATE,
    ENDING_DATE_IN DATE
  ) RETURN MEASURE_TABLE IS

    T MEASURE_TABLE;

  BEGIN

        SELECT  ...
        INTO    T
        FROM    ...

      ;

  RETURN T;

  END GET_UPS;

END TEST;

头文件编译，主体不编译.错误消息之一是值不足"，这可能意味着我应该选择MEASURE_RECORD，而不是MEASURE_TABLE.

The header compiles, the body does not. One error message is 'not enough values', which probably means that I should be selecting into the MEASURE_RECORD, rather than the MEASURE_TABLE.

我想念什么?

推荐答案

我认为您需要流水线表功能.

类似这样的东西:

CREATE OR REPLACE PACKAGE test AS

    TYPE measure_record IS RECORD(
       l4_id VARCHAR2(50), 
       l6_id VARCHAR2(50), 
       l8_id VARCHAR2(50), 
       year NUMBER, 
       period NUMBER,
       VALUE NUMBER);

    TYPE measure_table IS TABLE OF measure_record;

    FUNCTION get_ups(foo NUMBER)
        RETURN measure_table
        PIPELINED;
END;

CREATE OR REPLACE PACKAGE BODY test AS

    FUNCTION get_ups(foo number)
        RETURN measure_table
        PIPELINED IS

        rec            measure_record;

    BEGIN
        SELECT 'foo', 'bar', 'baz', 2010, 5, 13
          INTO rec
          FROM DUAL;

        -- you would usually have a cursor and a loop here   
        PIPE ROW (rec);

        RETURN;
    END get_ups;
END;

为简单起见，我删除了您的参数，并且未在函数中实现循环，但是您可以看到该原理.

For simplicity I removed your parameters and didn't implement a loop in the function, but you can see the principle.

用法:

SELECT *
  FROM table(test.get_ups(0));



L4_ID L6_ID L8_ID       YEAR     PERIOD      VALUE
----- ----- ----- ---------- ---------- ----------
foo   bar   baz         2010          5         13
1 row selected.

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