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

By: Fiona Marrone Fiona Marrone
Hours aggiornato

Che cos'è la raccolta?

Una Collection è un gruppo ordinato di elementi di particolari tipi di dati. Può essere una collection di tipo di dati semplice o di tipo di dati complesso (come i tipi definiti dall'utente o di record).

Nella collezione ogni elemento è identificato da un termine chiamato “pedice”. A ogni elemento della raccolta viene assegnato un pedice univoco. I dati in quella raccolta possono essere manipolati o recuperati facendo riferimento a quel pedice univoco.

Le raccolte sono molto utili quando è necessario elaborare o manipolare dati di grandi dimensioni dello stesso tipo. Le raccolte possono essere popolate e manipolate nel loro insieme utilizzando l'opzione 'BULK' in Oracle.

Le raccolte sono classificate in base alla struttura, al pedice e all'archiviazione come mostrato di seguito.

  • Indice per tabelle (noto anche come array associativo)
  • Tabelle nidificate
  • Varray

In qualsiasi momento, i dati nella raccolta possono essere indicati con tre termini Nome raccolta, Pedice, Nome campo/colonna come " ( ). ”. Imparerai a conoscere queste categorie di raccolta sopra menzionate più avanti nella sezione seguente.

Varray

Varray è un metodo di raccolta in cui la dimensione dell'array è fissa. La dimensione dell'array non può essere superata rispetto al suo valore fisso. L'indice di Varray è di un valore numerico. Di seguito sono riportati gli attributi di Varray.

  • La dimensione del limite superiore è fissa
  • Compilato in sequenza iniziando con il pedice "1"
  • Questo tipo di raccolta è sempre densa, ovvero non è possibile eliminare alcun elemento dell'array. Varray può essere eliminato completamente oppure tagliato dalla fine.
  • Poiché è sempre di natura densa, ha una flessibilità molto minore.
  • È più appropriato da utilizzare quando la dimensione dell'array è nota ed eseguire attività simili su tutti gli elementi dell'array.
  • Il pedice e la sequenza rimangono sempre stabili, cioè il pedice e il conteggio della raccolta sono sempre gli stessi.
  • Devono essere inizializzati prima di utilizzarli nei programmi. Qualsiasi operazione (tranne l'operazione EXISTS) su una raccolta non inizializzata genererà un errore.
  • Può essere creato come oggetto di database, visibile in tutto il database o all'interno del sottoprogramma, che può essere utilizzato solo in quel sottoprogramma.

La figura seguente spiegherà schematicamente l'allocazione della memoria di Varray (densa).

deponente 1 2 3 4 5 6 7
Valore Xyz Dfv Qui Cx Vbc Morbido qwe

Sintassi per VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
  • Nella sintassi sopra, type_name è dichiarato come VARRAY del tipo 'DATA_TYPE' per il limite di dimensione specificato. Il tipo di dati può essere semplice o complesso.

Tabelle annidate

Una tabella nidificata è una raccolta in cui la dimensione dell'array non è fissa. Ha il tipo di pedice numerico. Di seguito sono riportate ulteriori descrizioni sul tipo di tabella nidificata.

  • La tabella nidificata non ha limiti di dimensione superiore.
  • Poiché il limite di dimensione superiore non è fisso, la memoria della raccolta deve essere estesa ogni volta prima di utilizzarla. Possiamo estendere la raccolta utilizzando la parola chiave "EXTEND".
  • Compilato in sequenza a partire dal pedice "1".
  • Questo tipo di raccolta può essere di entrambi denso e scarno, ovvero possiamo creare una raccolta densa e possiamo anche eliminare i singoli elementi dell'array in modo casuale, rendendolo sparso.
  • Offre maggiore flessibilità per quanto riguarda l'eliminazione dell'elemento dell'array.
  • Viene memorizzato nella tabella del database generata dal sistema e può essere utilizzato nella query di selezione per recuperare i valori.
  • Il pedice e la sequenza non sono stabili, ovvero il pedice e il conteggio dell'elemento dell'array possono variare.
  • Devono essere inizializzati prima di utilizzarli nei programmi. Qualsiasi operazione (tranne l'operazione EXISTS) sulla raccolta non inizializzata genererà un errore.
  • Può essere creato come oggetto di database, visibile in tutto il database o all'interno del sottoprogramma, che può essere utilizzato solo in quel sottoprogramma.

La figura seguente spiegherà schematicamente l'allocazione di memoria della tabella annidata (densa e sparsa). Lo spazio dell'elemento di colore nero denota l'elemento vuoto in una raccolta, cioè sparsa.

deponente 1 2 3 4 5 6 7
Valore (denso) Xyz Dfv Qui Cx Vbc Morbido qwe
Valore(sparso) qwe Asd afg Asd Chi

Sintassi per la tabella nidificata:

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
  • Nella sintassi sopra, type_name è dichiarato come Nested table collection del tipo 'DATA_TYPE'. Il tipo di dati può essere semplice o complesso.

ARTICOLI CORRELATI

Indice per tabella

Index-by-table è una raccolta in cui la dimensione dell'array non è fissa. A differenza degli altri tipi di raccolta, nella raccolta index-by-table l'indice può essere definito dall'utente. Di seguito sono riportati gli attributi di index-by-table.

  • Il pedice può contenere numeri interi o stringhe. Al momento della creazione della raccolta, è necessario menzionare il tipo di pedice.
  • Queste raccolte non vengono archiviate in sequenza.
  • Sono sempre sparsi in natura.
  • La dimensione dell'array non è fissa.
  • Non possono essere archiviati nella colonna del database. Dovranno essere creati e utilizzati in qualsiasi programma in quella particolare sessione.
  • Danno maggiore flessibilità in termini di mantenimento dell'indice.
  • I pedici possono anche avere una sequenza di pedici negativi.
  • Sono più appropriati da utilizzare per valori collettivi relativamente più piccoli in cui la raccolta può essere inizializzata e utilizzata all'interno degli stessi sottoprogrammi.
  • Non è necessario inizializzarli prima di iniziare a utilizzarli.
  • Non può essere creato come oggetto di database. Può essere creato solo all'interno del sottoprogramma, che può essere utilizzato solo in quel sottoprogramma.
  • BULK COLLECT non può essere utilizzato in questo tipo di raccolta poiché il pedice deve essere fornito esplicitamente per ciascun record nella raccolta.

La figura seguente spiegherà schematicamente l'allocazione di memoria della tabella annidata (sparsa). Lo spazio dell'elemento di colore nero denota l'elemento vuoto in una raccolta, cioè sparsa.

Pedice (varchar) PRIMO SECONDA TERZO IL QUARTO QUINTO SESTO SETTIMO
Valore(sparso) qwe Asd afg Asd Chi

Sintassi per indice per tabella

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
  • Nella sintassi sopra, type_name è dichiarato come una raccolta indice-per-tabella del tipo 'DATA_TYPE'. Il tipo di dati può essere semplice o complesso. La variabile subsciprt/index è data come tipo VARCHAR2 con dimensione massima pari a 10.

Costruttore e concetto di inizializzazione nelle raccolte

I costruttori sono la funzione integrata fornita dall'oracolo che ha lo stesso nome dell'oggetto o delle raccolte. Vengono eseguiti per primi ogni volta che l'oggetto o le raccolte vengono referenziati per la prima volta in una sessione. Di seguito sono riportati i dettagli importanti del costruttore nel contesto della raccolta:

  • Per le raccolte, questi costruttori dovrebbero essere chiamati esplicitamente per inizializzarli.
  • Sia le tabelle Varray che quelle Nested devono essere inizializzate tramite questi costruttori prima di essere inserite nel programma.
  • Il costruttore estende implicitamente l'allocazione di memoria per una raccolta (eccetto Varray), quindi il costruttore può anche assegnare le variabili alle raccolte.
  • L'assegnazione di valori alla raccolta tramite i costruttori non renderà mai la raccolta sparsa.

Metodi di raccolta

Oracle fornisce molte funzioni per manipolare e lavorare con le collezioni. Queste funzioni sono molto utili nel programma per determinare e modificare i diversi attributi delle collezioni. La seguente tabella fornirà le diverse funzioni e la loro descrizione.

Metodo Descrizione SINTASSI
ESISTE (n) Questo metodo restituirà risultati booleani. Restituirà 'TRUE' se il nth L'elemento esiste in quella raccolta, altrimenti restituirà FALSE. Solo le funzioni EXISTS possono essere utilizzate nella raccolta non inizializzata .EXISTS(posizione_elemento)
COUNT Fornisce il conteggio totale degli elementi presenti in una raccolta .CONTARE
LIMITE Restituisce la dimensione massima della raccolta. Per Varray, restituirà la dimensione fissa che è stata definita. Per la tabella nidificata e l'indice per tabella, restituisce NULL .LIMITE
PRIMO Restituisce il valore della prima variabile indice (pedice) delle raccolte .PRIMO
ULTIMO Restituisce il valore dell'ultima variabile indice (pedice) delle raccolte .SCORSO
PRECEDENTE (n) Returns precede la variabile indice in una raccolta di nth elemento. Se non è presente alcun valore di indice precedente, viene restituito NULL .PRIOR(n)
SUCCESSIVO (n) Restituisce la variabile indice successiva in una raccolta di nth elemento. Se non ci sono risultati positivi, viene restituito il valore di indice NULL .NEXT(n)
ESTENDERE Estende un elemento in una raccolta alla fine .ESTENDERE
ESTENDERE (n) Estende n elementi alla fine di una raccolta .ESTENDERE(n)
ESTENDERE (n,i) Estende n copie di ith elemento alla fine della raccolta .ESTENDERE(n,i)
TRIM Rimuove un elemento dalla fine della raccolta .ORDINARE
TRIM (n) Rimuove n elementi dalla fine della raccolta .TRIM (n)
DELETE Elimina tutti gli elementi dalla raccolta. Rende la raccolta vuota .ELIMINARE
ELIMINA (n) Elimina l'ennesimo elemento dalla raccolta. Se poith l'elemento è NULL, ciò non farà nulla .DELETE(n)
CANCELLA (m,n) Elimina l'elemento nell'intervallo mth tonnellatath nella collezione .DELETE(m,n)

Esempio 1: tipo di record a livello di sottoprogramma

In questo esempio vedremo come popolare la collection utilizzando 'RITIRO IN BLOCCO' e come fare riferimento ai dati di raccolta.

Tipo di record a livello di sottoprogramma

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;
/

Spiegazione del codice:

  • Riga di codice 2-8: Tipo di registrazione 'emp_det' viene dichiarato con le colonne emp_no, emp_name, stipendio e manager di tipo dati NUMBER, VARCHAR2, NUMBER, NUMBER.
  • Riga di codice 9: Creazione della raccolta 'emp_det_tbl' dell'elemento di tipo record 'emp_det'
  • Riga di codice 10: Dichiarando la variabile 'guru99_emp_rec' come tipo 'emp_det_tbl' e inizializzata con costruttore null.
  • Riga di codice 12-15: Inserimento dei dati di esempio nella tabella 'emp'.
  • Riga di codice 16: Commiting della transazione di inserimento.
  • Riga di codice 17: Recuperando i record dalla tabella 'emp' e popolando la variabile di raccolta in blocco utilizzando il comando "BULK COLLECT". Ora la variabile 'guru99_emp_rec' contiene tutti i record presenti nella tabella 'emp'.
  • Riga di codice 19-26: Impostazione del ciclo 'FOR' utilizzando per stampare tutti i record nella raccolta uno per uno. Il metodo di raccolta FIRST e LAST viene utilizzato come limite inferiore e superiore del loop.

Uscita: Come puoi vedere nello screenshot qui sopra, quando il codice sopra viene eseguito otterrai 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
----------------------------------------------

Riassumi questo post con: