Создайте функцию Oracle, которая возвращает таблицу
Я пытаюсь создать функцию в пакете, который возвращает таблицу. Я надеюсь вызвать функцию однажды в пакете, но смочь снова использовать его данные mulitple времена. В то время как я знаю, что составляю временные таблицы в Oracle, я надеялся сохранить вещи DRY.
До сих пор это - то, что я имею:
Заголовок:
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.
Что я пропускаю?
2 ответа
Я думаю, вам нужна конвейерная табличная функция.
Что-то вроде этого:
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;
Для простоты я убрал ваши параметры и не реализовал цикл в функции, но принцип вы можете увидеть.
Usage:
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.
Чтобы вернуть сразу всю таблицу, вы можете изменить SELECT на:
SELECT ...
BULK COLLECT INTO T
FROM ...
Это рекомендуется только для не слишком больших результатов, поскольку они все должно быть накоплено в памяти перед возвратом; в противном случае рассмотрите конвейерную функцию, предложенную Чарльзом, или возврат REF CURSOR.