Oracle PL/SQL-samlinger: Varrays, Nested & Index by Tables
Fiona Brown
Hvad er samling?
En samling er en ordnet gruppe af elementer af bestemte datatyper. Det kan være en samling af simple datatyper eller komplekse datatyper (som brugerdefinerede eller registreringstyper).
I samlingen er hvert element identificeret med et udtryk kaldet "subscript." Hvert element i samlingen er tildelt et unikt abonnement. Dataene i denne samling kan manipuleres eller hentes ved at henvise til det unikke subscript.
Samlinger er mest nyttige ting, når store data af samme type skal behandles eller manipuleres. Samlinger kan udfyldes og manipuleres som helhed ved at bruge 'BULK'-indstillingen i Oracle.
Samlinger klassificeres baseret på strukturen, abonnementet og lagringen som vist nedenfor.
- Indeks-for-tabeller (også kendt som Associative Array)
- Indlejrede borde
- Varrays
På ethvert tidspunkt kan data i samlingen henvises til med tre termer Samlingsnavn, Subscript, Felt/Kolonnenavn som " ( ). ”. Du kommer til at lære om disse ovennævnte samlingskategorier længere i nedenstående afsnit.
Varrays
Varray er en indsamlingsmetode, hvor størrelsen af arrayet er fastsat. Matrixstørrelsen kan ikke overskrides end dens faste værdi. Varray'ens underskrift har en numerisk værdi. Følgende er egenskaberne for Varrays.
- Den øvre grænsestørrelse er fast
- Udfyldes sekventielt startende med sænket "1"
- Denne samlingstype er altid tæt, dvs. vi kan ikke slette nogen array-elementer. Varray kan slettes som en helhed, eller den kan trimmes fra slutningen.
- Da det altid er tæt i naturen, har det meget mindre fleksibilitet.
- Det er mere passende at bruge, når array-størrelsen er kendt, og at udføre lignende aktiviteter på alle array-elementerne.
- Sænkningen og sekvensen forbliver altid stabile, dvs. sænkningen og antallet af samlingen er altid det samme.
- De skal initialiseres, før de bruges i programmer. Enhver handling (undtagen EXISTS operation) på en ikke-initialiseret samling vil give en fejl.
- Det kan oprettes som et databaseobjekt, som er synligt i hele databasen eller inde i underprogrammet, som kun kan bruges i det underprogram.
Nedenstående figur vil forklare hukommelsesallokeringen af Varray (tæt) diagrammatisk.
| Sænket | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Værdi | xyz | Dfv | Sde | Cxs | Vbc | Blød | Qwe |
Syntaks for VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- I ovenstående syntaks er typenavn erklæret som VARRAY af typen 'DATA_TYPE' for den givne størrelsesgrænse. Datatypen kan enten være enkel eller kompleks.
Indlejrede tabeller
En indlejret tabel er en samling, hvor størrelsen af arrayet ikke er fastsat. Den har den numeriske subscripttype. Nedenfor er flere beskrivelser om indlejret tabeltype.
- Det indlejrede bord har ingen øvre størrelsesgrænse.
- Da den øvre størrelsesgrænse ikke er fast, skal samlingen, hukommelsen udvides hver gang, før vi bruger den. Vi kan udvide samlingen ved at bruge 'EXTEND' nøgleordet.
- Udfyldes sekventielt startende med sænket "1".
- Denne samlingstype kan være af begge tæt og sparsomt, dvs. vi kan oprette samlingen som en tæt, og vi kan også slette det enkelte array-element tilfældigt, hvilket gør det så sparsomt.
- Det giver mere fleksibilitet med hensyn til sletning af array-elementet.
- Den er gemt i den systemgenererede databasetabel og kan bruges i valgforespørgslen til at hente værdierne.
- Sænkningen og sekvensen er ikke stabile, dvs. sænkningen og antallet af array-elementet kan variere.
- De skal initialiseres, før de bruges i programmer. Enhver operation (undtagen EXISTS operation) på den ikke-initialiserede samling vil give en fejl.
- Det kan oprettes som et databaseobjekt, som er synligt i hele databasen eller inde i underprogrammet, som kun kan bruges i det underprogram.
Nedenstående figur vil forklare hukommelsesallokeringen af Nested Table (tæt og sparsom) diagrammatisk. Det sortfarvede elementrum angiver det tomme element i en samling, dvs. sparsomt.
| Sænket | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Værdi (tæt) | xyz | Dfv | Sde | Cxs | Vbc | Blød | Qwe |
| Værdi (sparsom) | Qwe | asd | Afg | asd | Wer |
Syntaks for indlejret tabel:
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
- I ovenstående syntaks er typenavn erklæret som indlejret tabelsamling af typen 'DATA_TYPE'. Datatypen kan enten være enkel eller kompleks.
Indeks-for-tabel
Indeks-for-tabel er en samling, hvor array-størrelsen ikke er fast. I modsætning til de andre samlingstyper kan subscriptet i indeks-for-tabel-samlingen bestå defineres af brugeren. Følgende er egenskaberne for indeks-for-tabel.
- Den sænkede kan af heltal eller strenge. På tidspunktet for oprettelse af samlingen skal abonnenttypen nævnes.
- Disse samlinger gemmes ikke sekventielt.
- De er altid sparsomme i naturen.
- Matrixstørrelsen er ikke fast.
- De kan ikke gemmes i databasekolonnen. De skal oprettes og bruges i ethvert program i den pågældende session.
- De giver mere fleksibilitet i forhold til at opretholde abonnement.
- Sænkningen kan også være af negativ sænket sekvens.
- De er mere passende at bruge til relativt mindre kollektive værdier, hvor samlingen kan initialiseres og bruges inden for de samme underprogrammer.
- De behøver ikke initialiseres, før du begynder at bruge dem.
- Det kan ikke oprettes som et databaseobjekt. Det kan kun oprettes inde i underprogrammet, som kun kan bruges i det underprogram.
- BULK COLLECT kan ikke bruges i denne samlingstype, da subscriptet skal angives eksplicit for hver post i samlingen.
Nedenstående figur vil forklare hukommelsesallokeringen af Nested Table (spare) diagrammatisk. Det sortfarvede elementrum angiver det tomme element i en samling, dvs. sparsomt.
| Subscript (varchar) | FØRST | ANDEN | TREDJE | FJERDE | Femte | SJETTE | SYVENDE |
| Værdi (sparsom) | Qwe | asd | Afg | asd | Wer |
Syntaks for indeks-for-tabel
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
- I ovenstående syntaks er typenavn erklæret som en indeks-for-tabel samling af typen 'DATA_TYPE'. Datatypen kan enten være enkel eller kompleks. Subsciprt/indeksvariablen er angivet som VARCHAR2-type med maksimal størrelse som 10.
Konstruktør og initialiseringskoncept i samlinger
Konstruktører er den indbyggede funktion leveret af oraklet, der har samme navn som objektet eller samlingerne. De udføres først, når objekt eller samlinger henvises for første gang i en session. Nedenfor er de vigtige detaljer om konstruktør i samlingssammenhæng:
- For samlinger bør disse konstruktører kaldes eksplicit for at initialisere den.
- Både Varray- og Nested-tabeller skal initialiseres gennem disse konstruktører, før de henvises til programmet.
- Constructor udvider implicit hukommelsesallokeringen for en samling (undtagen Varray), og derfor kan konstruktøren også tildele variablerne til samlingerne.
- At tildele værdier til samlingen gennem konstruktører vil aldrig gøre samlingen sparsom.
Indsamlingsmetoder
Oracle giver mange funktioner til at manipulere og arbejde med samlingerne. Disse funktioner er meget nyttige i programmet til at bestemme og ændre samlingernes forskellige egenskaber. Den følgende tabel vil give de forskellige funktioner og deres beskrivelse.
| Metode | Description | SYNTAKS |
|---|---|---|
| FINDER (n) | Denne metode vil returnere booleske resultater. Det vil returnere 'TRUE' hvis nth element findes i den samling, ellers vil det returnere FALSE. Kun EXISTS-funktioner kan bruges i uinitialiseret samling | .EXISTS(element_position) |
| COUNT | Giver det samlede antal af elementerne i en samling | .TÆLLE |
| GRÆNSE | Det returnerer samlingens maksimale størrelse. For Varray vil den returnere den faste størrelse, der er blevet defineret. For indlejrede tabel og indeks-for-tabel giver det NULL | .BEGRÆNSE |
| FØRST | Returnerer værdien af den første indeksvariabel (sænket) af samlingerne | .FØRST |
| LAST | Returnerer værdien af den sidste indeksvariabel (sænket) af samlingerne | .SIDST |
| FORUD (n) | Returnerer går forud for indeksvariabel i en samling af nth element. Hvis der ikke er nogen forudgående indeksværdi returneres NULL | .PRIOR(n) |
| NÆSTE (n) | Returnerer efterfølger indeksvariabel i en samling af nth element. Hvis der ikke er nogen succes, returneres indeksværdien NULL | .NÆSTE(n) |
| FORLÆNGE | Udvider ét element i en samling i slutningen | .FORLÆNGE |
| FORLÆNG (n) | Udvider n elementer i slutningen af en samling | .EXTEND(n) |
| FORLÆNG (n,i) | Udvider n kopier af ith element i slutningen af samlingen | .EXTEND(n,i) |
| TRIMME | Fjerner ét element fra slutningen af samlingen | .TRIMME |
| TRIM (n) | Fjerner n elementer fra slutningen af samlingen | .TRIM (n) |
| SLET | Sletter alle elementer fra samlingen. Gør samlingen tom | .SLET |
| SLET (n) | Sletter det n'te element fra samlingen. Hvis såth element er NULL, så vil dette ikke gøre noget | .DELETE(n) |
| SLET (m,n) | Sletter elementet i området mth denne nth i samlingen | .DELETE(m,n) |
Eksempel 1: Optagetype på underprogramniveau
I dette eksempel skal vi se, hvordan man udfylder samlingen ved hjælp af 'MASSEINDSAMLING' og hvordan man henviser til indsamlingsdataene.
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;
/
Kodeforklaring:
- Kodelinje 2-8: Record type 'emp_det' er deklareret med kolonnerne emp_no, emp_name, løn og manager af datatype NUMBER, VARCHAR2, NUMBER, NUMBER.
- Kodelinje 9: Oprettelse af samlingen 'emp_det_tbl' af posttypeelementet 'emp_det'
- Kodelinje 10: Erklærer variablen 'guru99_emp_rec' som 'emp_det_tbl'-type og initialiseret med null-konstruktør.
- Kodelinje 12-15: Indsættelse af eksempeldata i 'emp'-tabellen.
- Kodelinje 16: Forpligtelse af indsættelsestransaktionen.
- Kodelinje 17: Henter posterne fra 'emp'-tabellen og udfylder samlingsvariablen som en bulk ved hjælp af kommandoen "BULK COLLECT". Nu indeholder variablen 'guru99_emp_rec' alle de poster, der er til stede i tabellen 'emp'.
- Kodelinje 19-26: Indstilling af 'FOR'-løkken med at udskrive alle poster i samlingen én for én. Indsamlingsmetoden FØRST og SIDST bruges som nedre og højere grænse for loop.
Produktion: Som du kan se på ovenstående skærmbillede, når ovenstående kode udføres, får du følgende output
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 ----------------------------------------------
