Oggi parliamo di una tipologia di tabelle particolari:
Le Table Function di Oracle sono funzioni PL/SQL progettate per restituire una collezione di dati (come un array o un set di record) che può essere interrogata all'interno di una clausola FROM di una query SQL, proprio come se fosse una tabella fisica o una vista.
Ecco le caratteristiche principali :
1. Funzionamento e Utilizzo
Per utilizzare una Table Function, è necessario definire un tipo di dato:
Si crea solitamente un TYPE di tipo oggetto (che rappresenta la riga) e un TYPE di tipo tabella (collezione di tali oggetti).
Sintassi SQL: Viene richiamata nella query utilizzando l'operatore TABLE().
Ecco le caratteristiche principali :
1. Funzionamento e Utilizzo
Per utilizzare una Table Function, è necessario definire un tipo di dato:
Si crea solitamente un TYPE di tipo oggetto (che rappresenta la riga) e un TYPE di tipo tabella (collezione di tali oggetti).
Sintassi SQL: Viene richiamata nella query utilizzando l'operatore TABLE().
Ad esempio:
SELECT * FROM TABLE(nome_funzione(parametri));
SELECT * FROM TABLE(nome_funzione(parametri));
2. Tipologie di Table Function
Esistono due varianti principali basate sulla modalità di restituzione dei dati:
Esistono due varianti principali basate sulla modalità di restituzione dei dati:
Table Function Standard (Non-Pipelined):
Eseguono tutta l'elaborazione internamente. Popolano una collezione completa in memoria (PGA).
Restituiscono l'intero set di dati al chiamante solo al termine dell'esecuzione.
Limite: Possono consumare molta memoria se il volume di dati è elevato.
Pipelined Table Functions:
Restituiscono i dati "riga per riga" (o a piccoli gruppi) man mano che vengono generati, utilizzando l'istruzione PIPE ROW. Non attendono la fine dell'elaborazione per mostrare i primi risultati, migliorando il tempo di risposta percepito.
Riducono drasticamente l'occupazione di memoria PGA poiché non devono memorizzare l'intera tabella prima di restituirla.
Restituiscono i dati "riga per riga" (o a piccoli gruppi) man mano che vengono generati, utilizzando l'istruzione PIPE ROW. Non attendono la fine dell'elaborazione per mostrare i primi risultati, migliorando il tempo di risposta percepito.
Riducono drasticamente l'occupazione di memoria PGA poiché non devono memorizzare l'intera tabella prima di restituirla.
3. Vantaggi Principali
Modularità: Permettono di incapsulare logiche di business complesse o trasformazioni di dati difficili da realizzare in semplice SQL.
Performance: Le versioni pipelined possono essere eseguite in parallelo e permettono lo streaming diretto dei dati verso i processi successivi senza passaggi intermedi su disco.
Flessibilità: In Oracle Database 23ai e versioni successive, queste funzioni integrano capacità avanzate per gestire set di dati dinamici e polimorfici.
In sintesi, le Table Function agiscono come un "ponte" tra il mondo procedurale (PL/SQL) e quello dichiarativo (SQL), rendendo dinamica la generazione di dati tabulari.
- Ma se vi sono dei type nel package come lo richiamo?
1. La Regola Fondamentale: Visibilità Public
Per poter richiamare la funzione in una query SQL (clausola FROM), i tipi devono essere definiti nella Specifica (Specification) del package, non nel Body. Questo li rende "pubblici" e visibili al motore SQL.
2. Sintassi di Chiamata
La sintassi corretta prevede l'uso del nome del package sia per la funzione che per il riferimento ai tipi (se necessario).
-- Chiamata SQL
-- Sintassi standard aggiornata al 2026
SELECT * FROM TABLE(nome_package.nome_funzione(parametri));
3. Differenze tra Pipelined e Standard (Oracle 12c e successivi)
Pipelined Table Functions:
È la modalità più comune per i package. Oracle permette di usare tipi definiti nel package fin dalle versioni precedenti, purché siano tipi collezione (come TABLE OF ...) dichiarati nella specifica.
Table Function Standard:
Fino alle vecchie versioni (es. 11g), Oracle richiedeva spesso che i tipi fossero definiti a livello di schema (CREATE TYPE ...). Tuttavia, a partire da Oracle 12c e confermato nelle versioni più recenti come 23ai, è possibile utilizzare tipi definiti nel package anche per le funzioni non-pipelined direttamente in SQL.
Esempio Pratico
-- Chiamata SQL
CREATE OR REPLACE PACKAGE mio_pkg AS
-- Definizione dei tipi nella specifica (obbligatorio)
TYPE t_record IS RECORD (id NUMBER, descr VARCHAR2(100));
TYPE t_tabella IS TABLE OF t_record;
-- Definizione della funzione
FUNCTION get_dati RETURN t_tabella PIPELINED;
END mio_pkg;
/
-- Chiamata SQL
SELECT * FROM TABLE(mio_pkg.get_dati());
Usa il codice con cautela.
Note Importanti per il 2026
Qualificazione:
Se richiami la funzione da un altro schema, devi usare schema.package.funzione.
Tipi Privati: Se i tipi sono nel Body del package, la funzione fallirà quando chiamata da SQL con l'errore ORA-06553 (o simili), perché il motore SQL non può accedere a oggetti privati del PL/SQL.
Tipi ad Oggetti: Mentre i tipi collezione possono stare nel package, i tipi di riga complessi (se definiti come OBJECT) devono spesso essere creati a livello di schema se si desidera una compatibilità totale con tutte le funzionalità SQL avanzate.
Ecco un esempio completo del Package Body basato sulla specifica discussa in precedenza.
In questo esempio, implementiamo una Pipelined Table Function, che è la modalità più efficiente per restituire dati da un package nel 2026.
Codice del Package Body
-- Chiamata SQL
CREATE OR REPLACE PACKAGE BODY mio_pkg AS
FUNCTION get_dati RETURN t_tabella PIPELINED IS
-- Variabile di appoggio basata sul tipo record definito nella specifica
v_riga t_record;
BEGIN
-- Esempio 1: Generazione manuale di righe
v_riga.id := 1;
v_riga.descr := 'Prima riga di esempio';
PIPE ROW(v_riga); -- Invia la riga immediatamente al chiamante
v_riga.id := 2;
v_riga.descr := 'Seconda riga di esempio';
PIPE ROW(v_riga);
-- Esempio 2: Popolamento tramite un cursore (scenario reale)
FOR r IN (SELECT employee_id, last_name FROM employees WHERE rownum <= 5)
LOOP
v_riga.id := r.employee_id;
v_riga.descr := r.last_name;
PIPE ROW(v_riga);
END LOOP;
-- La funzione pipelined termina con un RETURN vuoto
RETURN;
END get_dati;
END mio_pkg;
/
Spiegazione dei passaggi chiave:
v_riga t_record: Utilizziamo il tipo definito nella Specifica del package. È fondamentale che il tipo sia pubblico affinché il motore SQL possa "capire" la struttura dei dati in uscita.
PIPE ROW(v_riga): Questa è l'istruzione cruciale. Invece di riempire una collezione in memoria e restituirla alla fine, PIPE ROW "spara" la riga fuori dalla funzione non appena è pronta. Il destinatario (la query SQL) può iniziare a elaborarla subito.
RETURN finale: Nelle funzioni pipelined, il comando RETURN non deve restituire alcun valore (non scriverai RETURN v_tabella), poiché i dati sono già stati inviati riga per riga.
Gestione Memoria: Questo approccio è ideale per grandi volumi di dati perché l'occupazione di memoria (PGA) rimane costante indipendentemente dal numero di righe restituite.
Come interrogarlo:
Una volta compilato il body, lo richiami così:
-- Chiamata SQL
SELECT * FROM TABLE(mio_pkg.get_dati());
Usa il codice con cautela.
Se stai usando Oracle 23ai, puoi anche omettere l'operatore TABLE() e scrivere semplicemente:
-- Chiamata SQL
SELECT * FROM mio_pkg.get_dati();
Una table function può richiamare una procedura come tabella?
Sì, una Table Function può richiamare una procedura interna per ottenere dati, ma con alcune limitazioni tecniche importanti da considerare per il 2026:
1. La Procedura non può essere "la tabella" direttamente
In Oracle, una procedura non restituisce mai un set di dati che il motore SQL può leggere come una tabella. Per trasformare l'output di una procedura in un formato tabellare interrogabile via SQL, la Table Function deve fungere da "wrapper" (contenitore).
2. Come implementarlo (Il meccanismo)
La Table Function deve chiamare la procedura, riceverne i dati tramite parametri di tipo OUT (solitamente collezioni o sys_refcursor) e poi "emetterli" verso l'esterno.
Scenario A: La procedura restituisce una collezione (OUT collection)
Questa è la via più semplice. La procedura riempie una lista di record, la funzione la riceve e la distribuisce.
-- Chiamata SQL
-- La procedura che estrae i dati
PROCEDURE estrai_dati_proc(p_risultato OUT t_tabella) IS
BEGIN
SELECT id, descr BULK COLLECT INTO p_risultato FROM tabella_sorgente;
END;
-- La Table Function che la espone come tabella
FUNCTION get_tabella_da_proc RETURN t_tabella PIPELINED IS
v_collezione t_tabella;
BEGIN
estrai_dati_proc(v_collezione); -- Chiama la procedura
FOR i IN 1 .. v_collezione.COUNT LOOP
PIPE ROW(v_collezione(i)); -- Espone i dati riga per riga
END LOOP;
RETURN;
END;
Scenario B:
La procedura restituisce un Cursore (OUT sys_refcursor)
Se la procedura restituisce un cursore aperto, la funzione può scorrere il cursore e inviare i dati con PIPE ROW.
3. Limitazioni Cruciali
Niente DML:
Se chiami la Table Function all'interno di una query SELECT, la procedura richiamata non può eseguire operazioni di INSERT, UPDATE o DELETE. Se lo facesse, riceveresti l'errore ORA-14551: cannot perform a DML operation inside a query.
Effetti Collaterali: La procedura deve essere "read-only" rispetto al database se viene usata in contesti SQL puri.
Prestazioni:
Chiamare una procedura che popola una collezione intera prima di passarla alla funzione annulla parte dei vantaggi del "pipelining", poiché i dati devono essere comunque caricati tutti in memoria prima di essere mostrati.
In sintesi
Puoi usare una Table Function per "mascherare" una procedura e farla apparire come una tabella, a patto che la procedura si limiti a leggere dati e che la funzione si occupi di trasformare l'output (collezione o cursore) in righe SQL.
Ecco un esempio completo e pronto all'uso.
In questo scenario, creiamo un package dove una procedura estrae i dati e una Table Function la richiama per esporre i risultati in una query SQL.
1. Definizione della Specifica del Package
In questa fase definiamo i tipi di dato necessari. Devono essere nella specifica per essere visibili al motore SQL.
-- Chiamata SQL
CREATE OR REPLACE PACKAGE pkg_esempio_tf AS
-- Tipo che rappresenta la singola riga
TYPE t_riga IS RECORD (
codice NUMBER,
testo VARCHAR2(100)
);
-- Tipo che rappresenta l'intera collezione
TYPE t_tabella IS TABLE OF t_riga;
-- Procedura che esegue la logica (es. estrazione dati)
PROCEDURE estrai_dati_procedurale(p_risultato OUT t_tabella);
-- Table Function che richiama la procedura
FUNCTION get_dati_da_procedura RETURN t_tabella PIPELINED;
END pkg_esempio_tf;
/
2. Definizione del Body del Package
Qui implementiamo la logica: la procedura popola una collezione e la funzione la trasforma in righe "interrogabili".
-- Chiamata SQL
CREATE OR REPLACE PACKAGE BODY pkg_esempio_tf AS
-- Implementazione della procedura
PROCEDURE estrai_dati_procedurale(p_risultato OUT t_tabella) IS
BEGIN
-- In un caso reale, qui potresti avere logica complessa,
-- calcoli o cursori dinamici.
p_risultato := t_tabella();
p_risultato.EXTEND;
p_risultato(1).codice := 10;
p_risultato(1).testo := 'Dati da Procedura - A';
p_risultato.EXTEND;
p_risultato(2).codice := 20;
p_risultato(2).testo := 'Dati da Procedura - B';
END estrai_dati_procedurale;
-- Implementazione della Table Function
FUNCTION get_dati_da_procedura RETURN t_tabella PIPELINED IS
v_dati t_tabella;
BEGIN
-- 1. Chiamiamo la procedura interna per ottenere la collezione
estrai_dati_procedurale(v_dati);
-- 2. Cicliamo sulla collezione restituita dalla procedura
IF v_dati IS NOT NULL THEN
FOR i IN 1 .. v_dati.COUNT LOOP
-- 3. Inviamo ogni singola riga al motore SQL
PIPE ROW(v_dati(i));
END LOOP;
END IF;
RETURN;
END get_dati_da_procedura;
END pkg_esempio_tf;
/
3. Come richiamare la funzione in SQL
Ora puoi interrogare la procedura come se fosse una tabella fisica:
-- Chiamata SQL
-- Sintassi standard
SELECT * FROM TABLE(pkg_esempio_tf.get_dati_da_procedura());
-- Se usi Oracle 23ai (o versioni successive del 2026), puoi anche omettere TABLE():
SELECT * FROM pkg_esempio_tf.get_dati_da_procedura();
--Perché si fa così?
Reusability:
Se hai già una procedura complessa che calcola dati, non devi riscriverla; la Table Function fa solo da "ponte" (wrapper).
Filtri SQL:
Anche se i dati nascono in una procedura PL/SQL, una volta passati dalla Table Function puoi usare WHERE, ORDER BY e JOIN come su una tabella normale.
Astrazione:
L'utente finale vede una tabella, ma dietro le quinte c'è un'elaborazione procedurale complessa.
Nota importante:
Assicurati che la procedura estrai_dati_procedurale non esegua COMMIT o ROLLBACK se prevedi di usare questa funzione all'interno di una SELECT, altrimenti riceverai un errore di violazione di purezza.
--- Se mi va in errore in ORA-06531: Riferimento a collection non inizializzata che succede?
L'errore ORA-06531 si verifica perché si sta tentando di aggiungere elementi a una collezione (il tuo TYPE ... IS TABLE OF) o di leggerne la proprietà .COUNT senza averla prima inizializzata con un costruttore.
In PL/SQL, le collezioni di tipo "Nested Table" (come t_tabella) nascono atomicamente nulle (null). Prima di usarle, devi "accendere" l'oggetto.
Ecco come correggere il codice nel Package Body:
La Soluzione: Inizializzazione
Devi aggiungere v_dati := t_tabella(); (il costruttore) prima di passare la variabile alla procedura o prima di usarla.
1. Correzione nella Procedura (se crei i dati lì)
Se la procedura deve riempire la tabella da zero, deve inizializzarla:
-- Chiamata SQL
PROCEDURE estrai_dati_procedurale(p_risultato OUT t_tabella) IS
BEGIN
-- SOLUZIONE: Inizializzo la collezione vuota
p_risultato := t_tabella();
-- Ora posso usare EXTEND e assegnare valori
p_risultato.EXTEND;
p_risultato(1).codice := 10;
-- ...
END;
2. Correzione nella Table Function (se usi BULK COLLECT)
Se invece usi una query con BULK COLLECT, Oracle inizializza la collezione automaticamente per te. Se però la collezione viene passata come parametro senza che la query trovi dati, potrebbe rimanere non inizializzata.
Per sicurezza, nella Table Function, scrivi così:
-- Chiamata SQL
FUNCTION get_dati_da_procedura RETURN t_tabella PIPELINED IS
v_dati t_tabella := t_tabella(); -- Inizializzazione immediata
BEGIN
estrai_dati_procedurale(v_dati);
-- Controllo di sicurezza aggiuntivo
IF v_dati IS NOT NULL AND v_dati.COUNT > 0 THEN
FOR i IN 1 .. v_dati.COUNT LOOP
PIPE ROW(v_dati(i));
END LOOP;
END IF;
RETURN;
END;
Perché succede?
Le Table Function e le procedure gestiscono i tipi TABLE OF come oggetti complessi:
Dichiarazione: v_dati t_tabella;
- La variabile esiste ma punta a NULL.
Inizializzazione: v_dati := t_tabella();
- La variabile ora punta a una collezione vuota (0 elementi), ma "viva".
Popolamento: v_dati.EXTEND;
- Crea lo spazio per il primo elemento.
Consiglio per il 2026: Se i dati provengono da una SELECT, usa sempre BULK COLLECT INTO v_dati all'interno della procedura; è più veloce e gestisce l'inizializzazione della collezione automaticamente, evitandoti l'errore ORA-06531.
-- Al posto di for i posso usare qualcos'altro
Sì, nel 2026 esistono diverse alternative al ciclo FOR i IN 1 .. COUNT per scorrere una collezione e inviare i dati con PIPE ROW.
Ecco le opzioni principali, dalla più moderna alla più classica:
1. Il ciclo FOR ALL (Indici Sparsi)
Se la tua collezione non è densa (ovvero se hai eliminato degli elementi e gli indici non sono sequenziali), il classico FOR i fallirebbe. Il modo più robusto per scorrere una collezione è usare la sintassi FIRST..LAST con la funzione NEXT:
-- Chiamata SQL
DECLARE
i PLS_INTEGER;
BEGIN
i := v_dati.FIRST; -- Parte dal primo indice disponibile
WHILE i IS NOT NULL LOOP
PIPE ROW(v_dati(i));
i := v_dati.NEXT(i); -- Salta direttamente all'indice successivo esistente
END LOOP;
END;
Usa il codice con cautela.
2. La sintassi "Cursor FOR Loop" su Collezione (Dalla 12c)
Oracle permette di trattare una collezione come se fosse un set di dati SQL all'interno del PL/SQL. Questo rende il codice molto pulito:
-- Chiamata SQL
FUNCTION get_dati_da_procedura RETURN t_tabella PIPELINED IS
v_dati t_tabella;
BEGIN
estrai_dati_procedurale(v_dati);
-- Usi un ciclo FOR che tratta la collezione come una sorgente dati
FOR r IN (SELECT * FROM TABLE(v_dati)) LOOP
PIPE ROW(r);
END LOOP;
RETURN;
END;
Usa il codice con cautela.
3. Utilizzo di un Ref Cursor (Migliore per Performance)
Invece di caricare tutto in una collezione (che occupa memoria RAM/PGA) e poi ciclarla, puoi far restituire alla procedura un Sys_Refcursor. È l'approccio più professionale perché non richiede l'inizializzazione della tabella e non causa l'errore ORA-06531.
Nel Body del Package:
-- Chiamata SQL
FUNCTION get_dati_da_procedura RETURN t_tabella PIPELINED IS
v_rc SYS_REFCURSOR;
v_riga t_riga; -- Il tipo record singolo
BEGIN
-- La procedura apre il cursore invece di riempire una tabella
apri_cursore_procedurale(v_rc);
LOOP
FETCH v_rc INTO v_riga;
EXIT WHEN v_rc%NOTFOUND;
PIPE ROW(v_riga);
END LOOP;
CLOSE v_rc;
RETURN;
END;
Quale scegliere?
Usa il punto 2 (SELECT FROM TABLE) se vuoi un codice elegante e facile da leggere.
Usa il punto 3 (Ref Cursor) se la procedura deve gestire migliaia o milioni di righe, perché è l'unico modo per non saturare la memoria del server (evita il caricamento massivo in v_dati).
Usa il punto 1 (WHILE NEXT) se la tua collezione viene manipolata (es. cancellazione di righe) prima di essere restituita.
-estrai_dati_procedurale(v_dati);
estrai_dati_procedurale(v_dati);
è la chiamata alla procedura che abbiamo definito in precedenza all'interno del package.
Nello schema logico che stiamo seguendo, rappresenta il momento in cui la Table Function "chiede" i dati alla procedura per poi poterli restituire al database.
Ecco il dettaglio di cosa succede in quella riga:
1. Il ruolo di "v_dati"
v_dati è una variabile (una collezione/array) definita dentro la tua funzione. In questa chiamata funge da contenitore.
Prima della chiamata: v_dati è vuota (o appena inizializzata).
Durante la chiamata: Il controllo passa alla procedura, che riempie v_dati con i record (i dati degli impiegati, fatture, ecc.).
Dopo la chiamata: La Table Function riprende il controllo e v_dati è ora "piena" di dati pronti per essere inviati con PIPE ROW.
2. Analogie per capire meglio
Immagina questa scena:
La Table Function è un cameriere che deve portare i piatti al tavolo (il database SQL).
La Procedura (estrai_dati_procedurale) è lo chef in cucina.
v_dati è il vassoio.
Quando scrivi estrai_dati_procedurale(v_dati);, il cameriere sta porgendo il vassoio vuoto allo chef e sta aspettando che lui ci appoggi sopra tutti i piatti pronti. Una volta riavuto il vassoio pieno, il cameriere inizierà a distribuirli uno per uno (PIPE ROW).
3. Perché è necessaria?
Si usa questo approccio quando la logica per estrarre i dati è troppo complessa per una singola query SQL.
Ad esempio, se per ottenere una riga devi:
Leggere dalla tabella A.
Fare un calcolo matematico.
Controllare una condizione sulla tabella B.
Inserire il risultato finale nella collezione.
Esempio di come deve apparire nel codice (per evitare errori):
-- Chiamata SQL
-- Nella funzione
FUNCTION get_dati RETURN t_tabella PIPELINED IS
v_dati t_tabella; -- Dichiarazione del "vassoio"
BEGIN
-- Chiamata: passo il vassoio alla procedura
-- La procedura deve avere un parametro OUT di tipo t_tabella
estrai_dati_procedurale(v_dati);
-- Ora il vassoio è pieno, posso servirlo
FOR i IN 1 .. v_dati.COUNT LOOP
PIPE ROW(v_dati(i));
END LOOP;
RETURN;
END;
Ricorda: Se la procedura non è nello stesso package, dovrai chiamarla come nome_altro_package.estrai_dati_procedurale(v_dati);.
Nessun commento:
Posta un commento