我试图在包中创建一个返回一个表的函数.我希望在包中调用该函数一次,但是可以多次重用数据.虽然我知道我在Oracle中创建了临时表,但我希望能够将事情保持在干燥状态.
到目前为止,这是我所拥有的：
标题：
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',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.
我失踪了什么
我想你想要一个
pipelined table function.
这样的事情
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;
为了简单起见,我删除了你的参数,并没有在函数中实现循环,但是你可以看到原理.
用法：
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.
