Oracle Collezioni PL/SQL: Varray, annidati e indicizzati per tabelle

Che cos'è una raccolta?

Una collezione è un gruppo ordinato di elementi di un particolare tipo di dati. Può essere una collezione di un tipo di dati semplice o di un tipo di dati complesso, come ad esempio tipi definiti dall'utente o tipi record.

In una collezione, ogni elemento è identificato da un termine chiamato “pedice”. A ciascun elemento viene assegnato un indice univoco e i dati possono essere manipolati o recuperati facendo riferimento a tale indice.

Le raccolte sono più utili quando è necessario elaborare o manipolare una grande quantità di dati dello stesso tipo. Le raccolte possono essere popolate e manipolate nel loro complesso utilizzando l'opzione 'BULK' in Oracle.

Le collezioni sono classificate in base alla struttura, all'indice e alla modalità di conservazione, come illustrato di seguito:

• Tabelle indicizzate (note anche come array associativi)
• Tabelle nidificate
• Varray

In qualsiasi momento, i dati in una raccolta possono essere indicati tramite tre termini: nome della raccolta, indice e nome del campo o della colonna, come “ ). Nelle sezioni seguenti potrai approfondire queste categorie di collezioni.

Tipologie di collezioni in sintesi

Le tre tipologie di collezione comportano compromessi diversi. La tabella seguente le mette a confronto prima di analizzarle nel dettaglio.

Varray

Un Varray è una collezione in cui la dimensione dell'array è fissa e non può essere superata. L'indice di un Varray è un valore numerico. Gli attributi dei Varray sono:

• La dimensione limite superiore è fissa.
• I numeri vengono inseriti in sequenza a partire dal pedice '1'.
• Questo tipo di collezione è sempre denso; non è possibile eliminare singoli elementi dell'array. Un Varray può essere eliminato per intero o troncato alla fine.
• Essendo sempre denso, ha pochissima flessibilità.
• È più appropriato quando la dimensione dell'array è nota e si eseguono attività simili su tutti gli elementi.
• L'indice e il conteggio della collezione rimangono sempre stabili.
• Deve essere inizializzata prima dell'uso. Qualsiasi operazione diversa da EXISTS su una collezione non inizializzata genera un errore.
• Può essere creato come oggetto di database visibile in tutto il database, oppure all'interno di una sottoprogramma per essere utilizzato solo in quel contesto.

La figura seguente illustra l'allocazione della memoria di un Varray (denso).

Sintassi per VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• Nella sintassi sopra riportata, type_name viene dichiarato come un VARRAY di tipo 'DATA_TYPE' per il limite di dimensione specificato. Il tipo di dati può essere semplice o complesso.

Tabelle annidate

Una tabella nidificata è una collezione in cui la dimensione dell'array non è fissa. Ha un tipo di indice numerico. Ulteriori informazioni sul tipo di tabella nidificata:

• La tabella annidata non ha un limite massimo di dimensione.
• Poiché il limite superiore non è fisso, la memoria deve essere estesa ogni volta prima dell'uso, utilizzando la parola chiave 'EXTEND'.
• I numeri vengono inseriti in sequenza a partire dal pedice '1'.
• Questo tipo di collezione può essere sia denso e scarno; possiamo crearlo denso e anche eliminare singoli elementi in modo casuale, rendendolo sparso.
• Offre maggiore flessibilità per l'eliminazione degli elementi dell'array.
• Viene memorizzato in una tabella di database generata dal sistema e può essere utilizzato in una query SELECT per recuperare i valori.
• L'indice e il conteggio possono variare.
• Deve essere inizializzata prima dell'uso. Qualsiasi operazione diversa da EXISTS su una collezione non inizializzata genera un errore.
• Può essere creato come oggetto di database visibile in tutto il database, oppure all'interno di una sottoprogramma per essere utilizzato solo in quel contesto.

La figura seguente illustra l'allocazione della memoria di una tabella annidata (densa e sparsa). Uno spazio vuoto indica un elemento sparso.

Sintassi per la tabella nidificata:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• Nella sintassi sopra riportata, type_name viene dichiarato come una raccolta di tabelle nidificate di tipo 'DATA_TYPE'. Il tipo di dati può essere semplice o complesso.

Indice per tabella

Una tabella indicizzata è una collezione in cui la dimensione dell'array non è fissa. A differenza di altri tipi di collezione, l'indice di una tabella indicizzata può essere definito dall'utente. Gli attributi di una tabella indicizzata sono:

• L'indice può essere un numero intero o una stringa. Il tipo di indice deve essere specificato al momento della creazione della collezione.
• Queste raccolte non vengono archiviate in sequenza.
• Sono sempre sparsi in natura.
• La dimensione dell'array non è fissa.
• Non possono essere memorizzati in una colonna di un database. Vengono creati e utilizzati all'interno di una sessione specifica.
• Offrono maggiore flessibilità nel mantenimento del pedice.
• Gli indici possono essere in sequenza negativa.
• Sono più adatti per valori di raccolta relativamente piccoli utilizzati all'interno dello stesso sottoprogramma.
• Non è necessario inizializzarli prima dell'uso.
• Non possono essere creati come oggetti di database; vengono creati solo all'interno di un sottoprogramma.
• L'opzione BULK COLLECT non può essere utilizzata con questo tipo di raccolta, poiché l'indice deve essere specificato esplicitamente per ogni record.

La figura seguente illustra l'allocazione della memoria di una tabella indicizzata (sparsa). Uno spazio vuoto indica un elemento sparso.

Sintassi per Indice per tabella:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• Nella sintassi sopra riportata, type_name è dichiarato come una raccolta di tabelle indicizzate di tipo 'DATA_TYPE'. La variabile di indice è di tipo VARCHAR2 con una dimensione massima di 10.

Costruttore e concetto di inizializzazione nelle raccolte

I costruttori sono funzioni integrate fornite da Oracle che hanno lo stesso nome dell'oggetto o della collezione. Vengono eseguiti per primi ogni volta che un oggetto o una collezione viene menzionato per la prima volta in una sessione. Dettagli importanti di un costruttore nel contesto di una collezione:

• Nel caso delle collezioni, questi costruttori devono essere chiamati esplicitamente per inizializzare la collezione.
• Sia Varray che le tabelle annidate devono essere inizializzate tramite questi costruttori prima di poter essere richiamate nel programma.
• Un costruttore estende implicitamente l'allocazione di memoria per una collezione (ad eccezione di Varray), quindi può anche assegnare variabili alla collezione.
• L'assegnazione di valori tramite costruttori non rende mai la collezione sparsa.

Metodi di raccolta

Oracle Offre numerose funzioni per manipolare e lavorare con le collezioni. Queste funzioni determinano e modificano i diversi attributi di una collezione. La tabella seguente elenca le diverse funzioni e le relative descrizioni.

Esempio 1: Tipo di record a livello di sottoprogramma

In questo esempio, vediamo come popolare la collezione utilizzando 'RITIRO IN BLOCCOe come fare riferimento ai dati della raccolta.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det;
guru99_emp_rec emp_det_tbl:= emp_det_tbl();
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,'AAA',25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX',10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,'ZZZ',7500,1000);
COMMIT;
SELECT emp_no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line ('Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line ('Employee Number: '||guru99_emp_rec(i).emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec(i).emp_name);
dbms_output.put_line ('Employee Salary:'|| guru99_emp_rec(i).salary);
dbms_output.put_line('Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

Code Spiegazione

• Code righe 2-8: Tipo di registrazione La tabella 'emp_det' è dichiarata con le colonne emp_no, emp_name, manager e salary di tipo dati NUMBER, VARCHAR2, NUMBER e NUMBER.
• Code riga 9: Creazione della raccolta 'emp_det_tbl' di tipo record elemento 'emp_det'.
• Code riga 10: Dichiarazione della variabile 'guru99_emp_rec' di tipo 'emp_det_tbl' e inizializzazione con un costruttore nullo.
• Code righe 12-15: Inserimento dei dati di esempio nella tabella 'emp'.
• Code riga 16: Commiting della transazione di inserimento.
• Code riga 17: Recupero dei record dalla tabella 'emp' e popolamento in blocco della variabile di raccolta tramite "BULK COLLECT". La variabile 'guru99_emp_rec' ora contiene tutti i record presenti nella tabella 'emp'.
• Code righe 19-26: Impostazione del ciclo 'FOR' per stampare tutti i record della collezione uno per uno. I metodi di collezione FIRST e LAST vengono utilizzati come limiti inferiore e superiore della collezione. loop.

Produzione: Quando il codice sopra riportato viene eseguito, si ottiene il seguente output.

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

DOMANDE FREQUENTI