Oracle PL/SQL-collecties: Varrays, genest en geïndexeerd op tabellen
Fiona Bruin
Wat is verzamelen?
Een verzameling is een geordende groep elementen van bepaalde gegevenstypen. Het kan een verzameling zijn van een eenvoudig gegevenstype of een complex gegevenstype (zoals door de gebruiker gedefinieerde of recordtypen).
In de verzameling wordt elk element geïdentificeerd door een term die wordt genoemd “subscript.” Elk item in de collectie krijgt een uniek subscript toegewezen. De gegevens in die verzameling kunnen worden gemanipuleerd of opgehaald door naar dat unieke subscript te verwijzen.
Verzamelingen zijn het nuttigst wanneer grote gegevens van hetzelfde type moeten worden verwerkt of gemanipuleerd. Collecties kunnen als geheel worden gevuld en gemanipuleerd met behulp van de 'BULK'-optie in Oracle.
Collecties worden geclassificeerd op basis van de structuur, het subscript en de opslag, zoals hieronder weergegeven.
- Index per tabel (ook bekend als Associatieve Array)
- Geneste tabellen
- Varrays
Op elk moment kan naar gegevens in de verzameling worden verwezen met drie termen. Verzamelnaam, subscript, veld-/kolomnaam als “ ( ). ”. In het onderstaande gedeelte leest u meer over deze bovengenoemde collectiecategorieën.
Varrays
Varray is een verzamelmethode waarbij de grootte van de array vastligt. De arraygrootte kan niet groter zijn dan de vaste waarde. Het subscript van de Varray is een numerieke waarde. Hieronder volgen de kenmerken van Varrays.
- Bovengrensgrootte is vast
- Opeenvolgend ingevuld, beginnend met het subscript '1'
- Dit verzamelingstype is altijd compact, dat wil zeggen dat we geen array-elementen kunnen verwijderen. Varray kan in zijn geheel worden verwijderd, of kan vanaf het einde worden bijgesneden.
- Omdat het altijd compact van aard is, heeft het heel weinig flexibiliteit.
- Het is geschikter om dit te gebruiken als de arraygrootte bekend is en om vergelijkbare activiteiten uit te voeren op alle array-elementen.
- Het subscript en de volgorde blijven altijd stabiel, dat wil zeggen dat het subscript en het aantal van de verzameling altijd hetzelfde zijn.
- Ze moeten worden geïnitialiseerd voordat ze in programma's worden gebruikt. Elke bewerking (behalve EXISTS-bewerking) op een niet-geïnitialiseerde verzameling zal een fout genereren.
- Het kan worden aangemaakt als een databaseobject, dat zichtbaar is in de hele database of binnen het subprogramma, en dat alleen in dat subprogramma kan worden gebruikt.
In de onderstaande afbeelding wordt de geheugentoewijzing van Varray (dicht) schematisch uitgelegd.
| subscript | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Waarde | xyz | Dfv | sde | Cx's | Vbc | Zacht | Qwe |
Syntaxis voor VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- In de bovenstaande syntaxis wordt type_name gedeclareerd als VARRAY van het type 'DATA_TYPE' voor de gegeven groottelimiet. Het gegevenstype kan een eenvoudig of complex type zijn.
Geneste tabellen
Een geneste tabel is een verzameling waarin de grootte van de array niet vaststaat. Het heeft het numerieke subscripttype. Hieronder vindt u meer beschrijvingen van het geneste tabeltype.
- Voor de geneste tabel geldt geen maximumgrootte.
- Omdat de maximale grootte niet vastligt, moet het geheugen van de verzameling elke keer worden uitgebreid voordat we het kunnen gebruiken. We kunnen de collectie uitbreiden met het trefwoord 'EXTEND'.
- Opeenvolgend ingevuld, beginnend met het subscript '1'.
- Dit verzamelingstype kan van beide zijn dicht en schaars, dwz we kunnen de collectie compact maken, en we kunnen ook het individuele array-element willekeurig verwijderen, waardoor het zo schaars wordt.
- Het geeft meer flexibiliteit met betrekking tot het verwijderen van het array-element.
- Het wordt opgeslagen in de door het systeem gegenereerde databasetabel en kan worden gebruikt in de selectiequery om de waarden op te halen.
- Het subscript en de volgorde zijn niet stabiel, dat wil zeggen dat het subscript en het aantal array-elementen kunnen variëren.
- Ze moeten worden geïnitialiseerd voordat ze in programma's worden gebruikt. Elke bewerking (behalve EXISTS-bewerking) op de niet-geïnitialiseerde verzameling zal een fout opleveren.
- Het kan worden aangemaakt als een databaseobject, dat zichtbaar is in de hele database of binnen het subprogramma, en dat alleen in dat subprogramma kan worden gebruikt.
In de onderstaande afbeelding wordt de geheugentoewijzing van de geneste tabel (dicht en schaars) schematisch uitgelegd. De zwart gekleurde elementruimte geeft het lege element in een verzameling aan, dwz schaars.
| subscript | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Waarde (dicht) | xyz | Dfv | sde | Cx's | Vbc | Zacht | Qwe |
| Waarde(schaars) | Qwe | asd | afg | asd | Wer |
Syntaxis voor geneste tabel:
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
- In de bovenstaande syntaxis wordt type_name gedeclareerd als Nested table collection van het type 'DATA_TYPE'. Het datatype kan een eenvoudig of complex type zijn.
Index per tabel
Index-by-table is een verzameling waarin de arraygrootte niet vastligt. In tegenstelling tot de andere verzamelingstypen kan in de index-by-table-verzameling het subscript door de gebruiker worden gedefinieerd. Hieronder volgen de kenmerken van index-by-table.
- Het subscript kan bestaan uit een geheel getal of tekenreeksen. Bij het aanmaken van de collectie moet het subscripttype worden vermeld.
- Deze collecties worden niet opeenvolgend opgeslagen.
- Ze zijn altijd schaars van aard.
- De arraygrootte staat niet vast.
- Ze kunnen niet worden opgeslagen in de databasekolom. Ze moeten worden gemaakt en gebruikt in elk programma in die specifieke sessie.
- Ze bieden meer flexibiliteit bij het onderhouden van het subscript.
- De subscripts kunnen ook een negatieve subscriptvolgorde hebben.
- Ze zijn geschikter om te gebruiken voor relatief kleinere collectieve waarden waarin de verzameling binnen dezelfde subprogramma's kan worden geïnitialiseerd en gebruikt.
- Ze hoeven niet te worden geïnitialiseerd voordat u ze gaat gebruiken.
- Het kan niet als databaseobject worden gemaakt. Het kan alleen binnen het subprogramma worden aangemaakt en kan alleen in dat subprogramma worden gebruikt.
- BULK COLLECT kan niet worden gebruikt in dit collectietype, omdat het subscript expliciet moet worden opgegeven voor elk record in de collectie.
In de onderstaande afbeelding wordt de geheugentoewijzing van de geneste tabel (sparse) schematisch uitgelegd. De zwart gekleurde elementruimte geeft het lege element in een verzameling aan, dwz schaars.
| Subscript (varchar) | EERST | TWEEDE | DERDE | VIERDE | VIJFDE | ZESDE | ZEVENDE |
| Waarde(schaars) | Qwe | asd | afg | asd | Wer |
Syntaxis voor index per tabel
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
- In de bovenstaande syntaxis wordt type_name gedeclareerd als een index-by-table collectie van het type 'DATA_TYPE'. Het datatype kan een eenvoudig of complex type zijn. De subsciprt/index variabele wordt gegeven als VARCHAR2 type met een maximale grootte van 10.
Constructor- en initialisatieconcept in collecties
Constructors zijn de ingebouwde functies die door het oracle worden geleverd en die dezelfde naam hebben als het object of de collecties. Ze worden als eerste uitgevoerd wanneer het object of de collecties voor het eerst in een sessie worden verwezen. Hieronder staan de belangrijke details van de constructor in de collectiecontext:
- Voor verzamelingen moeten deze constructors expliciet worden aangeroepen om deze te initialiseren.
- Zowel Varray- als geneste tabellen moeten via deze constructors worden geïnitialiseerd voordat ze naar het programma worden verwezen.
- De constructor breidt impliciet de geheugentoewijzing voor een verzameling uit (behalve Varray), waardoor de constructor de variabelen ook aan de verzamelingen kan toewijzen.
- Het toewijzen van waarden aan de collectie via constructors zal de collectie nooit schaars maken.
Collectiemethoden
Oracle biedt veel functies om de collecties te manipuleren en ermee te werken. Deze functies zijn erg handig in het programma om de verschillende kenmerken van de collecties te bepalen en te wijzigen. De volgende tabel geeft de verschillende functies en hun beschrijving.
| Methode | Beschrijving | SYNTAXIS |
|---|---|---|
| BESTAAT (n) | Deze methode retourneert Booleaanse resultaten. Het zal 'TRUE' retourneren als de nth element bestaat in die verzameling, anders retourneert het FALSE. Alleen EXISTS-functies kunnen worden gebruikt in een niet-geïnitialiseerde verzameling | .EXISTS(element_positie) |
| COUNT | Geeft het totale aantal elementen in een verzameling weer | .GRAAF |
| LIMIT | Het retourneert de maximale grootte van de verzameling. Voor Varray wordt de vaste grootte geretourneerd die is gedefinieerd. Voor geneste tabellen en index-per-tabel levert dit NULL op | .BEGRENZING |
| EERST | Retourneert de waarde van de eerste indexvariabele (subscript) van de collecties | .EERST |
| LAATSTE | Retourneert de waarde van de laatste indexvariabele (subscript) van de collecties | .LAATST |
| VOORAFGAAND (n) | Het resultaat gaat vooraf aan de indexvariabele in een verzameling van de nth element. Als er geen voorafgaande indexwaarde is, wordt NULL geretourneerd | .VORIGE(n) |
| VOLGENDE (n) | Retourneert volgt de indexvariabele op in een verzameling van de nth element. Als er geen succesvolle indexwaarde is, wordt NULL geretourneerd | .VOLGENDE(n) |
| UITBREIDEN | Breidt één element uit in een verzameling aan het einde | .VERLENGEN |
| VERLENGEN (n) | Breidt n elementen uit aan het einde van een verzameling | .VERLENGEN(n) |
| VERLENGEN (n,i) | Verlengt n exemplaren van de ith element aan het einde van de verzameling | .VERLENGEN(n,i) |
| TRIM | Verwijdert één element van het einde van de verzameling | .TRIM |
| TRIM (n) | Verwijdert n elementen van het einde van de verzameling | .TRIM (n) |
| VERWIJDEREN | Verwijdert alle elementen uit de verzameling. Maakt de verzameling leeg | .VERWIJDEREN |
| VERWIJDEREN (n) | Verwijdert het zoveelste element uit de verzameling. Als danth element NULL is, dan zal dit niets doen | .VERWIJDEREN(n) |
| VERWIJDEREN (m,n) | Verwijdert het element in het bereik mth naar nth in de collectie | .VERWIJDEREN(m,n) |
Voorbeeld1: Recordtype op subprogrammaniveau
In dit voorbeeld gaan we zien hoe we de verzameling kunnen vullen met 'BULK VERZAMELEN' en hoe u naar de verzamelgegevens kunt verwijzen.
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 Verklaring:
- Coderegel 2-8: Recordtype 'emp_det' wordt gedeclareerd met de kolommen emp_no, emp_name, salaris en manager van het gegevenstype NUMBER, VARCHAR2, NUMBER, NUMBER.
- Coderegel 9: De verzameling 'emp_det_tbl' van recordtype-element 'emp_det' maken
- Coderegel 10: De variabele 'guru99_emp_rec' declareren als 'emp_det_tbl' type en geïnitialiseerd met een null-constructor.
- Coderegel 12-15: Het invoegen van de voorbeeldgegevens in de 'emp'-tabel.
- Coderegel 16: Het vastleggen van de invoegtransactie.
- Coderegel 17: Het ophalen van de records uit de 'emp'-tabel en het in bulk vullen van de verzamelingsvariabele met behulp van het commando “BULK COLLECT”. Nu bevat de variabele 'guru99_emp_rec' alle records die aanwezig zijn in de tabel 'emp'.
- Coderegel 19-26: Het instellen van de 'FOR'-lus om alle records in de verzameling één voor één af te drukken. De verzamelmethode FIRST en LAST wordt gebruikt als onder- en bovengrens van de lus.
uitgang: Zoals u kunt zien in de bovenstaande schermafbeelding, krijgt u de volgende uitvoer wanneer de bovenstaande code wordt uitgevoerd
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 ----------------------------------------------
