Ottenere un recordset da una Function PL/SQL

 This article is available in english language too.

Tempo fa sono incappato in un problema con Oracle. Come ottenere un recordset da una query parametrica in PL/SQL in un solo comando?

Le prime risposte cui si potrebbe pensare sono:

  • Uso una vista a cui passo un paramtero: non si possono passare parametri ad una vista; una vista potrebbe al massimo leggere un parametro dal SYS_CONTEXT o da una tabella di appoggio, ma in questo modo non si riuscirebbe in un solo comando ad ottenere il risultato
  • Uso una procedure; una procedure non può restituire un recordset; l’unico oggetto simile che può restituire è un cursore, che più che essere “visualizzato” dovrebbe essere poi utilizzato da una qualche altra procedura (PL/SQL o client che sia)
La tipologia di oggetto che risolve questo specifico problema è l’uso degli OBJECT TYPES e delle FUNCTION PIPELINED.
Una funzione pipelined può restituire una collezione di oggetti ognuno dei quali verrà restituito come un record nel recordset risultante; per definire un nuovo oggetto e la relativa collezione in PL/SQL avremo:
    CREATE OR REPLACE
    TYPE O_MY_OBJECT AS OBJECT (
      MY_DATE                       DATE
    , MY_NUMBER                     NUMBER
    , MY_VARCHAR                    VARCHAR2(20)
    );

    CREATE OR REPLACE
    TYPE T_MY_OBJECT AS TABLE OF O_MY_OBJECT;
Quindi, una volta definiti questi due tipi, si può dichiarare una funzione PIPELINED che restituisca il tipo collezione e nel suo body creare gli oggetti (i record) e mandarli in output con l’istruzione PIPE ROW():
    FUNCTION MY_FUNCTION(V_MY_PARAM IN VARCHAR2)
      RETURN  T_MY_OBJECT
      PIPELINED
    AS
    V_MY_OBJ_VAR O_MY_OBJECT := O_MY_OBJECT(null, null, null);
    BEGIN
          SELECT
                SYSDATE
              , 10
              , 'CIAO MONDO! -> ' || V_MY_PARAM
          INTO
              V_MY_OBJ_VAR.MY_DATE
            , V_MY_OBJ_VAR.MY_NUMBER
            , V_MY_OBJ_VAR.MY_VARCHAR
          FROM
              DUAL;

          PIPE ROW(V_MY_OBJ_VAR);

      RETURN;
    END MY_FUNCTION;
Vediamo i particolari della funzione:
  • V_MY_OBJ_VAR O_MY_OBJECT := O_MY_OBJECT(null, null, null); => Definisco una variabile e la inizializzo con il costruttore dell’oggetto
  • Parte INTO => inserisco i valori desiderati (in questo caso delle costanti) nelle proprietà del mio oggetto
  • PIPE ROW(V_MY_OBJ_VAR) => mando in output al recordset il mio oggetto
Per avere righe che vengono da una query parametrica, è sufficiente costruire un cursore e in un LOOP costruire le istanze di oggetti di cui si farà il PIPE ROW.
Infine per richiamare la funzione così costruita ed avere il risultato finale, è necessario utilizzare la funzione TABLE di PL/SQL:
     SELECT * FROM TABLE(MY_FUNCTION('SAMPLE VALUE'));
Il risultato, sarà una griglia in tutto e per tutto utilizzabile come una tabella, o una vista.

2 pensieri su “Ottenere un recordset da una Function PL/SQL

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...