Crear una función de Oracle que devuelva una tabla

Estoy tratando de crear una función en el paquete que devuelve una tabla. Espero llamar a la función una vez en el paquete, pero ser capaz de volver a utilizar sus múltiples veces de datos. Aunque sé que creo tablas temporales en Oracle, esperaba mantener las cosas SECAS.

Hasta ahora, esto es lo que tengo:

Encabezamiento:

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; 

Cuerpo:

 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; 

El encabezado comstack, el cuerpo no. Un mensaje de error es “valores insuficientes”, lo que probablemente significa que debería seleccionar MEASURE_RECORD, en lugar de MEASURE_TABLE.

¿Qué me estoy perdiendo?

Creo que quieres una función de tabla canalizada .

Algo como esto:

 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; 

Para simplificar, eliminé tus parámetros y no implementé un bucle en la función, pero puedes ver el principio.

Uso:

 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. 

Para devolver toda la tabla de una vez, puede cambiar el SELECCIONAR a:

 SELECT ... BULK COLLECT INTO T FROM ... 

Esto solo es recomendable para resultados que no sean excesivamente grandes, ya que todos deben acumularse en la memoria antes de ser devueltos; de lo contrario, considere la función canalizada como lo sugiere Charles, o devuelva un CURSOR DE REF.

  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 MEASURE_RECORD(L4_ID , L6_ID ,L8_ID ,YEAR , PERIOD,VALUE ) BULK COLLECT INTO T FROM ...** ; RETURN T; END GET_UPS; END TEST;