Creare una funzione Oracle che restituisce una tabella

Sto cercando di creare una funzione nel pacchetto che restituisce una tabella. Spero di chiamare la funzione una sola volta nel pacchetto, ma essere in grado di riutilizzare i suoi dati più volte. Mentre so di creare tabelle temporanee in Oracle, speravo di mantenere le cose ASCIUTTE.

Finora, questo è quello che ho:

Intestazione:

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; 

Corpo:

 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; 

L’intestazione si compila, il corpo no. Un messaggio di errore è “non abbastanza valori”, il che probabilmente significa che dovrei selezionare in MEASURE_RECORD, anziché MEASURE_TABLE.

Cosa mi manca?

Penso che tu voglia una funzione di tavolo pipeline .

Qualcosa come questo:

 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; 

Per semplicità ho rimosso i tuoi parametri e non ho implementato un ciclo nella funzione, ma puoi vedere il 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. 

Per restituire l’intera tabella in una sola volta è ansible modificare SELECT in:

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

Questo è consigliabile solo per risultati che non sono eccessivamente grandi, poiché devono essere tutti accumulati in memoria prima di essere restituiti; altrimenti considera la funzione pipeline come suggerito da Charles, o restituendo un CURSORE 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;