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

Ce este o colecție?

O colecție este un grup ordonat de elemente de un anumit tip de date. Poate fi o colecție de un tip de date simplu sau de un tip de date complex, cum ar fi tipurile definite de utilizator sau tipurile de înregistrare.

Într-o colecție, fiecare element este identificat printr-un termen numit „indice”. Fiecărui element i se atribuie un indice unic, iar datele pot fi manipulate sau preluate prin referire la acel indice unic.

Colecțiile sunt cele mai utile atunci când trebuie procesată sau manipulată o cantitate mare de date de același tip. Colecțiile pot fi populate și manipulate ca un întreg folosind opțiunea „BULK” din Oracle.

Colecțiile sunt clasificate în funcție de structură, indice și spațiu de stocare, așa cum se arată mai jos:

• Tabele indexate (cunoscute și sub denumirea de matrice asociative)
• Tabele imbricate
• Varrays

În orice moment, datele dintr-o colecție pot fi denumite prin trei termeni: numele colecției, indicele și numele câmpului sau coloanei, adică „ ( ). Veți afla despre aceste categorii de colecții în secțiunile de mai jos.

Tipuri de colecții dintr-o privire

Cele trei tipuri de colecții prezintă compromisuri diferite. Tabelul de mai jos le prezintă una lângă alta înainte de a fi prezentată în detaliu fiecare dintre ele.

Varrays

O Variabilă (Varray) este o colecție în care dimensiunea matricei este fixă ​​și nu poate fi depășită. Indicele unei Variabile (Varray) este o valoare numerică. Atributele Variabilelor (Varrays) sunt:

• Dimensiunea limită superioară este fixă.
• Populat secvențial începând cu indicele „1”.
• Acest tip de colecție este întotdeauna densă; nu putem șterge elemente individuale ale matricei. O Variabilă poate fi ștearsă în întregime sau decupată de la sfârșit.
• Deoarece este întotdeauna densă, are foarte puțină flexibilitate.
• Este mai potrivit atunci când dimensiunea matricei este cunoscută și activități similare sunt efectuate asupra tuturor elementelor.
• Indicele și numărătorul colecției rămân întotdeauna stabile.
• Trebuie inițializată înainte de utilizare. Orice operațiune, cu excepția EXISTS, asupra unei colecții neinițializate generează o eroare.
• Poate fi creat ca un obiect de bază de date vizibil în întreaga bază de date sau în interiorul unui subprogram pentru a fi utilizat doar acolo.

Figura de mai jos explică alocarea memoriei pentru o unitate Varray (densă).

Sintaxă pentru VARRAY:

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

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

Mese cuibărite

Un tabel imbricat este o colecție în care dimensiunea matricei nu este fixă. Are un tip de indice numeric. Mai multe despre tipul de tabel imbricat:

• Tabelul imbricat nu are limită superioară de dimensiune.
• Deoarece limita superioară nu este fixă, memoria trebuie extinsă de fiecare dată înainte de utilizare, folosind cuvântul cheie „EXTEND”.
• Populat secvențial începând cu indicele „1”.
• Acest tip de colecție poate fi ambele dens și rarîl putem crea ca fiind dens și, de asemenea, putem șterge elemente individuale aleatoriu, ceea ce îl face rar.
• Oferă mai multă flexibilitate pentru ștergerea elementelor unui tablou.
• Este stocat într-un tabel de bază de date generat de sistem și poate fi utilizat într-o interogare de selecție pentru a prelua valori.
• Indicele și numărătorul pot varia.
• Trebuie inițializată înainte de utilizare. Orice operațiune, cu excepția EXISTS, asupra unei colecții neinițializate generează o eroare.
• Poate fi creat ca un obiect de bază de date vizibil în întreaga bază de date sau în interiorul unui subprogram pentru a fi utilizat doar acolo.

Figura de mai jos explică alocarea memoriei unui tabel imbricat (dens și dispers). Un spațiu de element gol denotă un element dispers.

Sintaxa pentru tabelul imbricat:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

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

Indexare după tabel

Un tabel index-by este o colecție în care dimensiunea matricei nu este fixă. Spre deosebire de alte tipuri de colecții, indicele unui tabel index-by poate fi definit de utilizator. Atributele unui tabel index-by sunt:

• Indicele poate fi un număr întreg sau un șir de caractere. Tipul de indice trebuie menționat la crearea colecției.
• Aceste colecții nu sunt stocate secvenţial.
• Ele sunt întotdeauna rare în natură.
• Dimensiunea matricei nu este fixă.
• Acestea nu pot fi stocate într-o coloană a bazei de date. Sunt create și utilizate în cadrul unei anumite sesiuni.
• Acestea oferă mai multă flexibilitate în menținerea indicelui.
• Indicele pot fi o secvență negativă.
• Acestea sunt mai potrivite pentru valori de colecție relativ mai mici utilizate în cadrul aceluiași subprogram.
• Nu este nevoie să fie inițializate înainte de utilizare.
• Acestea nu pot fi create ca obiecte ale bazei de date; ele sunt create doar în interiorul unui subprogram.
• BULK COLLECT nu poate fi utilizat cu acest tip de colecție, deoarece indicele trebuie dat explicit pentru fiecare înregistrare.

Figura de mai jos explică alocarea memoriei pentru un tabel index-by (sparse). Un spațiu gol pentru elemente indică un element sparse.

Sintaxă pentru tabelul indexat după:

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 de tabele index-by de tipul „DATA_TYPE”. Variabila subscript este dată ca tip VARCHAR2 cu o dimensiune maximă de 10.

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

Constructorii sunt funcții încorporate furnizate de Oracle care au același nume ca obiectul sau colecția. Acestea sunt executate primele ori de câte ori se face referire la un obiect sau la o colecție pentru prima dată într-o sesiune. Detalii importante despre un constructor în contextul colecției:

• Pentru colecții, acești constructori trebuie apelați explicit pentru a inițializa colecția.
• Atât Varray, cât și tabelele imbricate trebuie inițializate prin intermediul acestor constructori înainte de a fi referite la ele în program.
• Un constructor extinde implicit alocarea de memorie pentru o colecție (cu excepția Varray), astfel încât poate atribui și variabile colecției.
• Atribuirea de valori prin constructori nu face niciodată ca colecția să fie dispersă.

Metode de colectare

Oracle oferă numeroase funcții pentru manipularea și lucrul cu colecții. Aceste funcții determină și modifică diferitele atribute ale unei colecții. Tabelul de mai jos prezintă diferitele funcții și descrierile acestora.

Exemplul 1: Tip de înregistrare la nivel de subprogram

În acest exemplu, vedem cum se populează colecția folosind „COLECTARE VRAC„și cum să se facă referire la datele colecției.”

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 Explicație

• Code liniile 2-8: Tipul de înregistrare „emp_det” este declarat cu coloanele emp_no, emp_name, manager și salary de tipuri de date NUMBER, VARCHAR2, NUMBER și NUMBER.
• Code linia 9: Se creează colecția „emp_det_tbl” a elementului de tip înregistrare „emp_det”.
• Code linia 10: Declararea variabilei „guru99_emp_rec” ca fiind de tipul „emp_det_tbl” și inițializarea acesteia cu un constructor nul.
• Code liniile 12-15: Inserarea datelor eșantionului în tabelul „emp”.
• Code linia 16: Efectuarea tranzacției de inserare.
• Code linia 17: Se preiau înregistrările din tabelul „emp” și se populează variabila de colecție în bloc folosind „BULK COLLECT”. Variabila „guru99_emp_rec” conține acum toate înregistrările prezente în tabelul „emp”.
• Code liniile 19-26: Setarea buclei „FOR” pentru a imprima toate înregistrările din colecție una câte una. Metodele de colectare FIRST și LAST sunt utilizate ca limite inferioare și superioare ale buclă.

ieșire: Când codul de mai sus este executat, obțineți următorul rezultat.

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

Întrebări frecvente