Oracle PL/SQL-collecties: Varrays, genest en geïndexeerd op tabellen

Wat is verzamelen?

Een verzameling is een geordende groep elementen van bepaalde gegevenstypen. Het kan een verzameling eenvoudige gegevenstypen zijn of complex gegevenstype (zoals door de gebruiker gedefinieerde typen 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 vaststaat. De arraygrootte kan niet groter zijn dan de vaste waarde. Het subscript van de Varray heeft een numerieke waarde. Volgwing zijn 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 kunnen worden gebruikt. Elke bewerking (behalve de EXISTS-bewerking) op een niet-geïnitialiseerde verzameling genereert een fout.
  • 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 typenaam gedeclareerd als VARRAY van het type 'DATA_TYPE' voor de opgegeven groottelimiet. Het gegevenstype kan eenvoudig of com zijnplex type.

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 kunnen worden gebruikt. Elke bewerking (behalve EXISTS-bewerking) op de niet-geïnitialiseerde verzameling levert een fout op.
  • 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 een geneste tabelverzameling van het type 'DATA_TYPE'. Het gegevenstype kan eenvoudig of com zijnplex type.

Index per tabel

Index per tabel is een verzameling waarin de arraygrootte niet vastligt. In tegenstelling tot de andere verzamelingstypen kan bij de index-per-tabelverzameling het subscript door de gebruiker worden gedefinieerd. Volgwing zijn de attributen 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) EERSTE 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-per-tabelverzameling van het type 'DATA_TYPE'. Het gegevenstype kan eenvoudig of com zijnplex type. De subsciprt/index-variabele wordt gegeven als VARCHAR2-type met een maximale grootte van 10.

Constructor- en initialisatieconcept in collecties

Constructors zijn de ingebouwde functie van de oracle die dezelfde naam heeft als het object of de collecties. Ze worden als eerste uitgevoerd wanneer objecten of collecties voor de eerste keer in een sessie worden doorverwezen. Hieronder staan ​​de belangrijke details van constructor in verzamelingscontext:

  • 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 attributen van de collecties te bepalen en te wijzigen. De Following tabel geeft de verschillende functies en hun beschrijving.

Methode Omschrijving 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
EERSTE 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.

Recordtype op subprogrammaniveau

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 je in de bovenstaande schermafbeelding kunt zien, krijg je het volgende te zien wanneer de bovenstaande code wordt uitgevoerdwing uitgang

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