Oracle Colecții PL/SQL: Varrays, Imbricate și indexate pe tabele

Ce este Colectia?

O colecție este un grup ordonat de elemente de anumite tipuri de date. Poate fi o colecție de tip de date simple sau tip de date complexe (cum ar fi definite de utilizator sau tipuri de înregistrare).

În colecție, fiecare element este identificat printr-un termen numit „indice”. Fiecărui articol din colecție i se atribuie un indice unic. Datele din acea colecție pot fi manipulate sau preluate prin referire la acel indice unic.

Colecțiile sunt cele mai utile lucruri atunci când trebuie procesate sau manipulate date mari de același tip. Colecțiile pot fi populate și manipulate ca întreg folosind opțiunea „BULK” în Oracle.

Colecțiile sunt clasificate în funcție de structură, indice și stocare, după cum se arată mai jos.

• Index-by-tables (cunoscut și sub numele de Associative Array)
• Tabele imbricate
• Varrays

În orice moment, datele din colecție pot fi menționate prin trei termeni Nume colecție, Indice, Nume câmp/coloană ca „ ( ). ”. Veți afla mai multe despre aceste categorii de colecții menționate mai sus în secțiunea de mai jos.

Varrays

Varray este o metodă de colectare în care dimensiunea matricei este fixă. Dimensiunea matricei nu poate fi depășită decât valoarea sa fixă. Indicele Varray are o valoare numerică. Următoarele sunt atributele lui Varrays.

• Dimensiunea limită superioară este fixă
• Populat secvenţial începând cu indicele „1”
• Acest tip de colecție este întotdeauna dens, adică nu putem șterge niciun element de matrice. Varray poate fi șters ca un întreg sau poate fi tăiat de la sfârșit.
• Deoarece este întotdeauna dens în natură, are foarte puțină flexibilitate.
• Este mai potrivit să se folosească atunci când dimensiunea matricei este cunoscută și să se realizeze activități similare pe toate elementele matricei.
• Indicele și secvența rămân întotdeauna stabile, adică indicele și numărul colecției sunt întotdeauna aceleași.
• Ele trebuie inițializate înainte de a le utiliza în programe. Orice operație (cu excepția operației EXISTS) pe o colecție neinițializată va genera o eroare.
• Poate fi creat ca obiect de bază de date, care este vizibil în întreaga bază de date sau în interiorul subprogramului, care poate fi utilizat numai în acel subprogram.

Figura de mai jos va explica schematic alocarea memoriei lui Varray (dens).

Sintaxă pentru VARRAY:

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

• În sintaxa de mai sus, type_name este declarat ca VARRAY de tipul „DATA_TYPE” pentru limita de dimensiune dată. Tipul de date poate fi de tip simplu sau complex.

Mese cuibărite

Un tabel Imbricat este o colecție în care dimensiunea matricei nu este fixă. Are tipul de indice numeric. Mai jos sunt mai multe descrieri despre tipul de tabel imbricat.

• Tabelul Imbricat nu are o limită superioară de dimensiune.
• Deoarece limita superioară de dimensiune nu este fixă, colecția, memoria trebuie extinsă de fiecare dată înainte de a o folosi. Putem extinde colecția folosind cuvântul cheie „EXTEND”.
• Populat secvenţial începând cu indicele „1”.
• Acest tip de colecție poate fi din ambele dens și rar, adică putem crea colecția ca o densă și, de asemenea, putem șterge aleatoriu elementul de matrice individual, ceea ce îl face ca rară.
• Oferă mai multă flexibilitate în ceea ce privește ștergerea elementului de matrice.
• Este stocat în tabelul bazei de date generat de sistem și poate fi folosit în interogarea de selectare pentru a prelua valorile.
• Indicele și secvența nu sunt stabile, adică indicele și numărul elementului de matrice pot varia.
• Ele trebuie inițializate înainte de a le utiliza în programe. Orice operație (cu excepția operației EXISTS) pe colecția neinițializată va genera o eroare.
• Poate fi creat ca obiect de bază de date, care este vizibil în întreaga bază de date sau în interiorul subprogramului, care poate fi utilizat numai în acel subprogram.

Figura de mai jos va explica schematic alocarea memoriei tabelului imbricat (dens și rar). Spațiul elementului de culoare neagră denotă elementul gol dintr-o colecție, adică rar.

Sintaxa pentru tabelul imbricat:

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;

• În sintaxa de mai sus, type_name este declarată ca colecție de tabele imbricate de tipul „DATA_TYPE”. Tipul de date poate fi de tip simplu sau complex.

Index cu tabel

Index-by-table este o colecție în care dimensiunea matricei nu este fixă. Spre deosebire de celelalte tipuri de colecție, în colecția index cu tabel, indicele poate fi definit de utilizator. Următoarele sunt atributele index-cu-tabel.

• Indicele poate de întregi sau șiruri. În momentul creării colecției, trebuie menționat tipul de indice.
• Aceste colecții nu sunt stocate secvenţial.
• Ele sunt întotdeauna rare în natură.
• Dimensiunea matricei nu este fixă.
• Ele nu pot fi stocate în coloana bazei de date. Acestea vor fi create și utilizate în orice program din sesiunea respectivă.
• Ele oferă mai multă flexibilitate în ceea ce privește menținerea indicelui.
• Indicele pot fi, de asemenea, de secvență de indice negativ.
• Ele sunt mai potrivite de utilizat pentru valori colective relativ mai mici în care colecția poate fi inițializată și utilizată în cadrul acelorași subprograme.
• Nu trebuie să fie inițializate înainte de a începe să le utilizați.
• Nu poate fi creat ca obiect de bază de date. Acesta poate fi creat doar în interiorul subprogramului, care poate fi folosit doar în acel subprogram.
• BULK COLLECT nu poate fi utilizat în acest tip de colecție, deoarece indicele trebuie dat în mod explicit pentru fiecare înregistrare din colecție.

Figura de mai jos va explica schematic alocarea memoriei tabelului imbricat (spars). Spațiul element de culoare neagră denotă elementul gol dintr-o colecție, adică rar.

Sintaxă pentru index-cu-tabel

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

• În sintaxa de mai sus, type_name este declarată ca o colecție index cu tabel de tipul „DATA_TYPE”. Tipul de date poate fi de tip simplu sau complex. Variabila subsciprt/index este dată ca tip VARCHAR2 cu dimensiunea maximă de 10.

Conceptul de constructor și inițializare în colecții

Constructorii sunt funcția încorporată furnizată de oracol care are același nume ca și al obiectului sau colecțiilor. Ele sunt executate mai întâi ori de câte ori obiectul sau colecțiile sunt trimise pentru prima dată într-o sesiune. Mai jos sunt detaliile importante ale constructorului în contextul colecției:

• Pentru colecții, acești constructori ar trebui apelați în mod explicit pentru a-l inițializa.
• Atât tabelele Varray, cât și cele imbricate trebuie să fie inițializate prin acești constructori înainte de a fi trimise în program.
• Constructorul extinde implicit alocarea memoriei pentru o colecție (cu excepția Varray), prin urmare constructorul poate de asemenea să atribuie variabilele colecțiilor.
• Atribuirea de valori colecției prin constructori nu va face niciodată colecția rară.

Metode de colectare

Oracle oferă multe funcții pentru a manipula și a lucra cu colecțiile. Aceste funcții sunt foarte utile în program pentru a determina și modifica diferitele atribute ale colecțiilor. Tabelul următor va oferi diferitele funcții și descrierea acestora.

Exemplul 1: Tip de înregistrare la nivel de subprogram

În acest exemplu, vom vedea cum să populați colecția folosind „COLECTARE VRAC' și cum să trimiteți datele de colectare.

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

Explicația codului:

• Linia de cod 2-8: Tipul de înregistrare „emp_det” este declarat cu coloanele emp_no, emp_name, salariu și manager de tip de date NUMBER, VARCHAR2, NUMBER, NUMBER.
• Linia de cod 9: Crearea colecției „emp_det_tbl” a elementului de tip de înregistrare „emp_det”
• Linia de cod 10: Declararea variabilei „guru99_emp_rec” ca tip „emp_det_tbl” și inițializată cu constructor nul.
• Linia de cod 12-15: Inserarea datelor eșantionului în tabelul „emp”.
• Linia de cod 16: Efectuarea tranzacției de inserare.
• Linia de cod 17: Preluarea înregistrărilor din tabelul „emp” și popularea variabilei de colecție în bloc folosind comanda „BULK COLLECT”. Acum variabila „guru99_emp_rec” conține toate înregistrările care sunt prezente în tabelul „emp”.
• Linia de cod 19-26: Setarea buclei „FOR” folosind pentru a tipări toate înregistrările din colecție una câte una. Metoda de colectare FIRST și LAST este utilizată ca limită inferioară și superioară a buclă.

producție: După cum puteți vedea în captura de ecran de mai sus, când codul de mai sus este executat, veți obține următoarea ieșire

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