Raccolte Oracle PL / SQL: Varrays, nidificato e amp; Indice per tabelle

Sommario:

Anonim

Cos'è la raccolta?

Una raccolta è un gruppo ordinato di elementi di particolari tipi di dati. Può essere una raccolta di tipi di dati semplici o complessi (come tipi di dati definiti dall'utente o record).

Nella raccolta, 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 le cose più utili quando è necessario elaborare o manipolare dati di grandi dimensioni dello stesso tipo. Le raccolte possono essere popolate e manipolate come intere utilizzando l'opzione "BULK" in Oracle.

In questo tutorial imparerai-

  • Cos'è la raccolta?
  • Varrays
  • Tabelle annidate
  • Indice per tabella
  • Costruttore e concetto di inizializzazione nelle raccolte
  • Metodi di raccolta

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 annidate
  • Varrays

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 ulteriormente nella sezione sottostante.

Varrays

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

  • La dimensione del limite superiore è fissa
  • Popolato sequenzialmente a partire dal pedice "1"
  • Questo tipo di raccolta è sempre denso, ovvero non è possibile eliminare alcun elemento dell'array. Varray può essere cancellato nel suo insieme o può essere tagliato dalla fine.
  • Poiché è sempre denso in natura, ha una flessibilità molto inferiore.
  • È più appropriato da utilizzare quando la dimensione dell'array è nota e per 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 (eccetto l'operazione EXISTS) su una raccolta non inizializzata genererà un errore.
  • Può essere creato come oggetto 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 (denso).

Pedice 1 2 3 4 5 6 7
Valore Xyz Dfv Sde Cxs Vbc Nhu Qwe

Sintassi per VARRAY:

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

Tabelle annidate

Una tabella annidata è una raccolta in cui la dimensione della matrice 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 dimensioni superiori.
  • Poiché il limite di dimensione superiore non è fisso, la raccolta, la memoria deve essere estesa ogni volta prima di utilizzarla. Possiamo estendere la raccolta utilizzando la parola chiave "ESTENDI".
  • Popolato in modo sequenziale a partire dal pedice "1".
  • Questo tipo di raccolta può essere sia denso che scarso , cioè possiamo creare la raccolta come un denso e possiamo anche eliminare il singolo elemento dell'array in modo casuale, il che lo rende sparse.
  • 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 (eccetto l'operazione EXISTS) sulla raccolta non inizializzata genererà un errore.
  • Può essere creato come oggetto 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 della tabella annidata (densa e sparsa). Lo spazio dell'elemento di colore nero denota l'elemento vuoto in una raccolta, ovvero sparse.

Pedice 1 2 3 4 5 6 7
Valore (denso) Xyz Dfv Sde Cxs Vbc Nhu Qwe
Valore (sparse) Qwe Asd Afg Asd Wer

Sintassi per tabella annidata:

TYPE  IS TABLE OF ;
  • Nella sintassi precedente, type_name è dichiarato come raccolta di tabelle nidificate del tipo "DATA_TYPE". Il tipo di dati può essere di tipo semplice o complesso.

Indice per tabella

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

  • Il pedice può essere intero o stringhe. Al momento della creazione della raccolta, dovrebbe essere menzionato il tipo di pedice.
  • Queste raccolte non vengono archiviate in sequenza.
  • Sono sempre rari in natura.
  • La dimensione dell'array non è fissa.
  • Non possono essere memorizzati nella colonna del database. Devono essere creati e utilizzati in qualsiasi programma in quella particolare sessione.
  • Offrono maggiore flessibilità in termini di mantenimento del pedice.
  • I pedici possono essere anche di sequenza pedice negativa.
  • 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 ogni record nella raccolta.

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

Pedice (varchar) PRIMO SECONDO TERZO IL QUARTO QUINTO SESTO SETTIMO
Valore (sparse) Qwe Asd Afg Asd Wer

Sintassi per Indice per tabella

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

Costruttore e concetto di inizializzazione nelle raccolte

I costruttori sono la funzione incorporata 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 indirizzati 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.
  • Entrambe le tabelle Varray e annidate devono essere inizializzate tramite questi costruttori prima di essere indirizzate al 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 raccolte. Queste funzioni sono molto utili nel programma per determinare e modificare i diversi attributi delle collezioni. La tabella seguente fornirà le diverse funzioni e la loro descrizione.

Metodo Descrizione SINTASSI
ESISTE (n) Questo metodo restituirà risultati booleani. Restituirà 'TRUE' se l' ennesimo elemento esiste in quella raccolta, altrimenti restituirà FALSE. Solo le funzioni EXISTS possono essere utilizzate nella raccolta non inizializzata .EXISTS (posizione_elemento)
CONTARE Fornisce il conteggio totale degli elementi presenti in una raccolta .COUNT
LIMITE Restituisce la dimensione massima della collezione. Per Varray, restituirà la dimensione fissa che è stata definita. Per la tabella annidata e l'indice per tabella, restituisce NULL .LIMIT
PRIMO Restituisce il valore della prima variabile indice (pedice) delle raccolte . PRIMO
SCORSO Restituisce il valore dell'ultima variabile di indice (pedice) delle raccolte .LAST
PRIOR (n) Restituisce precede la variabile indice in una raccolta dell'elemento n- esimo . Se non è presente alcun valore di indice precedente, viene restituito NULL .PRIOR (n)
SUCCESSIVO (n) Restituisce la variabile indice in una raccolta dell'elemento n- esimo . In assenza di esito positivo, viene restituito il valore di indice NULL .NEXT (n)
ESTENDERE Estende un elemento in una raccolta alla fine .EXTEND
ESTENDI (n) Estende n elementi alla fine di una raccolta .EXTEND (n)
ESTENDI (n, i) Estende n copie dell'i- esimo elemento alla fine della raccolta .EXTEND (n, i)
TRIM Rimuove un elemento dalla fine della raccolta .TRIM
TRIM (n) Rimuove n elementi dalla fine della raccolta .TRIM (n)
ELIMINA Elimina tutti gli elementi dalla raccolta. Rende vuota la raccolta .DELETE
CANCELLA (n) Elimina l'ennesimo elemento dalla raccolta. Se l' elemento n- esimo è NULL, questo non farà nulla .DELETE (n)
CANCELLA (m, n) Elimina l'elemento nella gamma m ° al n ° nella collezione .DELETE (m, n)

Esempio 1: tipo di record a livello di sottoprogramma

In questo esempio, vedremo come popolare la raccolta utilizzando "BULK COLLECT" e come fare riferimento ai dati della raccolta.

DECLARETYPE 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();BEGININSERT 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_recFROM emp;dbms_output.put_line (‘Employee Detail');FOR i IN guru99_emp_rec.FIRST… guru99_emp_rec.LASTLOOPdbms_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 : il tipo di record "emp_det" è dichiarato con le colonne emp_no, emp_name, stipendio e gestore del tipo di 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: dichiarazione della variabile "guru99_emp_rec" come tipo "emp_det_tbl" e inizializzata con un costruttore nullo.
  • Riga di codice 12-15: inserimento dei dati di esempio nella tabella "emp".
  • Riga di codice 16: commit della transazione di inserimento.
  • Riga di codice 17: Recupero dei record dalla tabella "emp" e popolamento della 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 ciclo.

Output : come puoi vedere nello screenshot qui sopra, quando viene eseguito il codice sopra, otterrai il seguente output

Employee DetailEmployee Number: 1000Employee Name: AAAEmployee Salary: 25000Employee Manager Number: 1000----------------------------------------------Employee Number: 1001Employee Name: XXXEmployee Salary: 10000Employee Manager Number: 1000----------------------------------------------Employee Number: 1002Employee Name: YYYEmployee Salary: 15000Employee Manager Number: 1000----------------------------------------------Employee Number: 1003Employee Name: ZZZEmployee Salary: 7500Employee Manager Number: 1000----------------------------------------------