Oracle PL / SQL-verzamelingen: Varrays, Nested & Index op tabellen

Inhoudsopgave:

Anonim

Wat is afhalen?

Een verzameling is een geordende groep elementen van bepaalde gegevenstypen. Het kan een verzameling van een eenvoudig gegevenstype of een complex gegevenstype zijn (zoals door de gebruiker gedefinieerde of recordtypen).

In de verzameling wordt elk element geïdentificeerd door een term die "subscript" wordt genoemd . Elk item in de collectie krijgt een uniek abonnement toegewezen. De gegevens in die verzameling kunnen worden gemanipuleerd of opgehaald door naar dat unieke subscript te verwijzen.

Verzamelingen zijn het nuttigst als grote gegevens van hetzelfde type moeten worden verwerkt of gemanipuleerd. Verzamelingen kunnen als geheel worden gevuld en gemanipuleerd met de optie 'BULK' in Oracle.

In deze tutorial leer je-

  • Wat is afhalen?
  • Varrays
  • Geneste tabellen
  • Index per tabel
  • Constructor en initialisatieconcept in collecties
  • Verzamelingsmethoden

Collecties worden geclassificeerd op basis van de structuur, het abonnement en de opslag, zoals hieronder wordt weergegeven.

  • Index-voor-tabellen (ook bekend als associatieve matrix)
  • Geneste tabellen
  • Varrays

Op elk moment kan naar gegevens in de verzameling worden verwezen met de drie termen Collectienaam, Subscript, Veld- / kolomnaam als " (). ". In het onderstaande gedeelte leert u meer over deze bovengenoemde verzamelcategorieën.

Varrays

Varray is een verzamelingsmethode waarbij de grootte van de array wordt vastgelegd. De array-grootte kan niet worden overschreden dan de vaste waarde. Het subscript van de Varray heeft een numerieke waarde. Hieronder volgen de kenmerken van Varrays.

  • Bovengrens grootte is vast
  • Opeenvolgend ingevuld beginnend met het subscript '1'
  • Dit type verzameling is altijd compact, dwz we kunnen geen array-elementen verwijderen. Varray kan als geheel worden verwijderd, of het kan vanaf het einde worden bijgesneden.
  • Omdat het altijd dicht van aard is, heeft het veel minder flexibiliteit.
  • Het is meer geschikt om te gebruiken wanneer de array-grootte bekend is en om vergelijkbare activiteiten uit te voeren op alle array-elementen.
  • Het subscript en de volgorde blijven altijd stabiel, dwz het subscript en de telling van de verzameling zijn altijd hetzelfde.
  • Ze moeten worden geïnitialiseerd voordat ze in programma's kunnen worden gebruikt. Elke bewerking (behalve EXISTS-bewerking) op een niet-geïnitialiseerde verzameling genereert een fout.
  • Het kan worden gemaakt als een databaseobject, dat zichtbaar is in de hele database of in het subprogramma, dat alleen in dat subprogramma kan worden gebruikt.

In de onderstaande afbeelding wordt de geheugentoewijzing van Varray (dicht) schematisch uitgelegd.

Abonnement 1 2 3 4 5 6 7
Waarde Xyz Dfv Sde Cxs Vbc Nhu Qwe

Syntaxis voor VARRAY:

TYPE  IS VARRAY () OF ;
  • In de bovenstaande syntaxis wordt type_name gedeclareerd als VARRAY van het type 'DATA_TYPE' voor de opgegeven maximale grootte. 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 over geneste tabeltypes.

  • De geneste tabel heeft geen maximale grootte.
  • Omdat de maximale grootte niet vaststaat, moet het geheugen van de verzameling elke keer worden uitgebreid voordat we het gebruiken. We kunnen de collectie uitbreiden met het trefwoord 'EXTEND'.
  • Opeenvolgend ingevuld beginnend met het subscript '1'.
  • Dit type verzameling kan zowel compact als spaarzaam zijn , dat wil zeggen dat we de verzameling als een dichte kunnen maken, en we kunnen ook het individuele array-element willekeurig verwijderen, waardoor het als spaarzaam 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, dwz het subscript en de telling van het array-element 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 genereert een fout.
  • Het kan worden gemaakt als een databaseobject, dat zichtbaar is in de hele database of in het subprogramma, 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.

Abonnement 1 2 3 4 5 6 7
Waarde (dicht) Xyz Dfv Sde Cxs Vbc Nhu Qwe
Waarde (schaars) Qwe Asd Afg Asd Wer

Syntaxis voor geneste tabel:

TYPE  IS TABLE OF ;
  • In de bovenstaande syntaxis wordt type_name gedeclareerd als geneste tabelverzameling van het type 'DATA_TYPE'. Het gegevenstype kan een eenvoudig of complex type zijn.

Index per tabel

Index-voor-tabel is een verzameling waarin de array-grootte niet vaststaat. In tegenstelling tot de andere soorten verzameling, kan in de verzameling per tabel het subscript door de gebruiker worden gedefinieerd. Hieronder volgen de kenmerken van index per tabel.

  • Het subscript kan gehele getallen of strings bevatten. Op het moment dat de collectie wordt gemaakt, moet het subscripttype worden vermeld.
  • Deze verzamelingen worden niet opeenvolgend opgeslagen.
  • Ze zijn altijd schaars van aard.
  • De grootte van de array staat niet vast.
  • Ze kunnen niet worden opgeslagen in de databasekolom. Ze worden gemaakt en gebruikt in elk programma in die specifieke sessie.
  • Ze bieden meer flexibiliteit bij het onderhouden van een abonnement.
  • De subscripts kunnen ook een negatieve subscriptvolgorde hebben.
  • Ze zijn geschikter om te gebruiken voor relatief kleinere collectieve waarden waarin de verzameling kan worden geïnitialiseerd en gebruikt binnen dezelfde subprogramma's.
  • Ze hoeven niet te worden geïnitialiseerd voordat u ze gaat gebruiken.
  • Het kan niet worden gemaakt als een databaseobject. Het kan alleen binnen het subprogramma worden gemaakt, dat alleen in dat subprogramma kan worden gebruikt.
  • BULK COLLECT kan niet worden gebruikt in dit type verzameling, aangezien het subscript expliciet voor elk record in de verzameling moet worden opgegeven.

In de onderstaande afbeelding wordt de geheugentoewijzing van de geneste tabel (dun) schematisch uitgelegd. De zwart gekleurde elementruimte geeft het lege element in een verzameling aan, dwz schaars.

Abonnement (varchar) EERSTE TWEEDE DERDE VIERDE VIJFDE ZESDE ZEVENDE
Waarde (schaars) Qwe Asd Afg Asd Wer

Syntaxis voor index per tabel

TYPE  IS TABLE OF  INDEX BY VARCHAR2 (10);
  • In de bovenstaande syntaxis wordt type_name gedeclareerd als een verzameling per tabel van het type 'DATA_TYPE'. Het gegevenstype kan een eenvoudig of complex type zijn. De variabele subsciprt / index wordt gegeven als type VARCHAR2 met een maximale grootte van 10.

Constructor en initialisatieconcept in collecties

Constructors zijn de ingebouwde functie die wordt geboden door het orakel met dezelfde naam als die van het object of de collecties. Ze worden als eerste uitgevoerd wanneer objecten of verzamelingen voor het eerst 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.
  • Constructor breidt impliciet de geheugentoewijzing voor een verzameling uit (behalve Varray), daarom kan de constructor de variabelen ook aan de verzamelingen toewijzen.
  • Het toewijzen van waarden aan de verzameling via constructors zal de verzameling nooit schaars maken.

Verzamelingsmethoden

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 volgende tabel geeft de verschillende functies en hun beschrijving.

Methode Omschrijving SYNTAXIS
BESTAAT (n) Deze methode retourneert Booleaanse resultaten. Het retourneert 'TRUE' als het n- de element in die verzameling voorkomt, anders wordt FALSE geretourneerd. Alleen EXISTS-functies kunnen worden gebruikt in een niet-geïnitialiseerde verzameling .EXISTS (element_positie)
AANTAL Geeft het totale aantal elementen in een verzameling weer .COUNT
BEGRENZING Het retourneert de maximale grootte van de verzameling. Voor Varray retourneert het de vaste grootte die is gedefinieerd. Voor geneste tabel en index per tabel geeft dit NULL .LIMIT
EERSTE Retourneert de waarde van de eerste indexvariabele (subscript) van de verzamelingen .FIRST
LAATSTE Retourneert de waarde van de laatste indexvariabele (subscript) van de verzamelingen .LAST
VOORAFGAAND (n) Geeft als resultaat de indexvariabele in een verzameling van het n- de element. Als er geen voorafgaande indexwaarde is, wordt NULL geretourneerd .PRIOR (n)
VOLGENDE (n) Retourneert de indexvariabele in een verzameling van het n- de element. Als er geen resultaat is, wordt de indexwaarde NULL geretourneerd .NEXT (n)
UITBREIDEN Verlengt aan het einde een element in een verzameling .EXTEND
VERLENGEN (n) Verlengt n elementen aan het einde van een verzameling .EXTEND (n)
VERLENGEN (n, i) Verlengt n kopieën van het ide element aan het einde van de verzameling .EXTEND (n, i)
TRIM Verwijdert één element uit het einde van de verzameling .TRIM
TRIM (n) Verwijdert n elementen uit het einde van de verzameling .TRIM (n)
VERWIJDEREN Verwijdert alle elementen uit de verzameling. Maakt de collectie leeg .DELETE
VERWIJDEREN (n) Verwijdert het n-de element uit de verzameling. Als het n- de element NULL is, zal dit niets doen .DELETE (n)
VERWIJDEREN (m, n) Verwijdert het element in het bereik m th tot n th in de verzameling .DELETE (m, n)

Voorbeeld 1: recordtype op subprogrammaniveau

In dit voorbeeld gaan we zien hoe we de verzameling kunnen vullen met 'BULK COLLECT' en hoe we de verzamelingsgegevens kunnen verwijzen.

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

Code Verklaring:

  • Coderegel 2-8 : Recordtype 'emp_det' wordt gedeclareerd met de kolommen emp_no, emp_name, salaris en manager van datatype NUMBER, VARCHAR2, NUMBER, NUMBER.
  • Coderegel 9: De verzameling 'emp_det_tbl' van recordtype-element 'emp_det' maken
  • Code regel 10: Declaratie van de variabele 'guru99_emp_rec' als 'emp_det_tbl' type en geïnitialiseerd met null constructor.
  • Coderegel 12-15: Invoegen van de voorbeeldgegevens in de 'emp'-tabel.
  • Coderegel 16: De invoegtransactie uitvoeren.
  • Coderegel 17: het ophalen van de records uit de 'emp'-tabel en het vullen van de verzamelingsvariabele als een bulk 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: Stel de 'FOR'-lus in met om alle records in de collectie één voor één af te drukken. De verzamelingsmethode EERSTE en LAATSTE wordt gebruikt als onder- en bovengrens van de lus.

Uitvoer : Zoals je in de bovenstaande schermafbeelding kunt zien, krijg je de volgende uitvoer wanneer de bovenstaande code wordt uitgevoerd

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