Oracle PL/SQL-samlinger: Varrays, Nested & Index by Tables
Fiona Brown
Hva er samling?
En samling er en ordnet gruppe elementer av bestemte datatyper. Det kan være en samling av enkel datatype eller kompleks datatype (som brukerdefinerte eller posttyper).
I samlingen er hvert element identifisert med et begrep kalt "subscript." Hvert element i samlingen er tildelt et unikt abonnement. Dataene i den samlingen kan manipuleres eller hentes ved å referere til det unike abonnementet.
Samlinger er mest nyttige ting når store data av samme type må behandles eller manipuleres. Samlinger kan fylles ut og manipuleres som helhet ved å bruke 'BULK'-alternativet i Oracle.
Samlinger er klassifisert basert på struktur, abonnement og lagring som vist nedenfor.
- Indeks for tabeller (også kjent som Associative Array)
- Nestede bord
- Varrays
Når som helst kan data i samlingen henvises til med tre termer Samlingsnavn, Subscript, Felt/Kolonnenavn som " ( ). ". Du kommer til å lære om disse ovennevnte samlingskategoriene lenger i avsnittet nedenfor.
Varrays
Varray er en innsamlingsmetode der størrelsen på matrisen er fast. Matrisestørrelsen kan ikke overskrides enn dens faste verdi. Subskriptet til Varray har en numerisk verdi. Følgende er attributtene til Varrays.
- Øvre grensestørrelse er fast
- Fylles sekvensielt og starter med abonnenten "1"
- Denne samlingstypen er alltid tett, dvs. vi kan ikke slette noen array-elementer. Varray kan slettes som en helhet, eller den kan trimmes fra slutten.
- Siden den alltid er tett i naturen, har den svært mindre fleksibilitet.
- Det er mer hensiktsmessig å bruke når matrisestørrelsen er kjent og å utføre lignende aktiviteter på alle matriseelementene.
- Abonnementet og sekvensen forblir alltid stabile, dvs. abonnenten og antallet av samlingen er alltid det samme.
- De må initialiseres før de brukes i programmer. Enhver operasjon (unntatt EXISTS-operasjon) på en uinitialisert samling vil gi en feil.
- Det kan opprettes som et databaseobjekt, som er synlig i hele databasen eller inne i underprogrammet, som kun kan brukes i det underprogrammet.
Figuren nedenfor vil forklare minnetildelingen til Varray (tett) skjematisk.
| Senket | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Verdi | X Y Z | Dfv | Sde | Cxs | Vbc | Myk | Qwe |
Syntaks for VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- I syntaksen ovenfor er type_name erklært som VARRAY av typen 'DATA_TYPE' for den gitte størrelsesgrensen. Datatypen kan enten være enkel eller kompleks.
Nestede tabeller
En nestet tabell er en samling der størrelsen på matrisen ikke er fast. Den har den numeriske subskripttypen. Nedenfor er flere beskrivelser om nestet tabelltype.
- Nested-bordet har ingen øvre størrelsesgrense.
- Siden den øvre størrelsesgrensen ikke er fast, må samlingen, minnet utvides hver gang før vi bruker den. Vi kan utvide samlingen ved å bruke søkeordet 'EXTEND'.
- Fylles i rekkefølge og starter med subskriptet '1'.
- Denne samlingstypen kan være av begge tett og sparsomt, dvs. vi kan lage samlingen som en tett, og vi kan også slette det enkelte array-elementet tilfeldig, noe som gjør det så sparsomt.
- Det gir mer fleksibilitet når det gjelder å slette array-elementet.
- Den er lagret i den systemgenererte databasetabellen og kan brukes i utvalgsspørringen for å hente verdiene.
- Subscript og sekvens er ikke stabile, dvs. subscript og telling av array-elementet kan variere.
- De må initialiseres før de brukes i programmer. Enhver operasjon (unntatt EXISTS-operasjon) på den uinitialiserte samlingen vil gi en feil.
- Det kan opprettes som et databaseobjekt, som er synlig i hele databasen eller inne i underprogrammet, som kun kan brukes i det underprogrammet.
Figuren nedenfor vil forklare minnetildelingen til Nested Table (tett og sparsomt) skjematisk. Det svarte elementrommet angir det tomme elementet i en samling, dvs. sparsomt.
| Senket | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Verdi (tett) | X Y Z | Dfv | Sde | Cxs | Vbc | Myk | Qwe |
| Verdi (sparsom) | Qwe | asd | Afg | asd | Wer |
Syntaks for nestet tabell:
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
- I syntaksen ovenfor er typenavn erklært som Nestet tabellsamling av typen 'DATA_TYPE'. Datatypen kan enten være enkel eller kompleks.
Indeks for tabell
Indeks-for-tabell er en samling der matrisestørrelsen ikke er fast. I motsetning til de andre samlingstypene, i indeks-for-tabell-samlingen kan subskriptet bestå av brukeren. Følgende er attributtene til indeks for tabell.
- Subskriptet kan ha heltall eller strenger. Ved opprettelse av samlingen skal abonnementstypen nevnes.
- Disse samlingene lagres ikke sekvensielt.
- De er alltid sparsomme i naturen.
- Matrisestørrelsen er ikke fast.
- De kan ikke lagres i databasekolonnen. De skal opprettes og brukes i et hvilket som helst program i den aktuelle økten.
- De gir mer fleksibilitet når det gjelder å opprettholde abonnement.
- Abonnementene kan også ha negativ sekvens.
- De er mer hensiktsmessige å bruke for relativt mindre kollektive verdier der samlingen kan initialiseres og brukes innenfor de samme underprogrammene.
- De trenger ikke initialiseres før du begynner å bruke dem.
- Det kan ikke opprettes som et databaseobjekt. Det kan bare opprettes i underprogrammet, som kun kan brukes i det underprogrammet.
- BULK COLLECT kan ikke brukes i denne samlingstypen da subscriptet skal gis eksplisitt for hver post i samlingen.
Figuren nedenfor vil forklare minnetildelingen til Nested Table (sparsom) skjematisk. Det svarte elementrommet angir det tomme elementet i en samling, dvs. sparsomt.
| Subscript (varchar) | FØRST | SECOND | TREDJE | FJERDE | FEMTE | SJETTE | SYVENDE |
| Verdi (sparsom) | Qwe | asd | Afg | asd | Wer |
Syntaks for indeks for tabell
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
- I syntaksen ovenfor er typenavn erklært som en indeks-for-tabell-samling av typen 'DATA_TYPE'. Datatypen kan enten være enkel eller kompleks. Abonnent/indeksvariabelen er gitt som VARCHAR2-type med maksimal størrelse som 10.
Konstruktør og initialiseringskonsept i samlinger
Konstruktører er den innebygde funksjonen levert av oraklet som har samme navn som objektet eller samlingene. De utføres først når objekt eller samlinger blir henvist for første gang i en økt. Nedenfor er de viktige detaljene om konstruktør i samlingssammenheng:
- For samlinger bør disse konstruktørene kalles eksplisitt for å initialisere den.
- Både Varray- og Nested-tabeller må initialiseres gjennom disse konstruktørene før de henvises til programmet.
- Konstruktør utvider implisitt minneallokeringen for en samling (unntatt Varray), og derfor kan konstruktør også tilordne variablene til samlingene.
- Å tildele verdier til samlingen gjennom konstruktører vil aldri gjøre samlingen sparsom.
Innsamlingsmetoder
Oracle gir mange funksjoner for å manipulere og å jobbe med samlingene. Disse funksjonene er svært nyttige i programmet for å bestemme og endre de forskjellige egenskapene til samlingene. Tabellen nedenfor vil gi de forskjellige funksjonene og deres beskrivelse.
| Metode | Tekniske beskrivelser | SYNTAKS |
|---|---|---|
| FINNES (n) | Denne metoden vil returnere boolske resultater. Det vil returnere 'TRUE' hvis nth elementet finnes i den samlingen, ellers vil det returnere FALSE. Bare EXISTS-funksjoner kan brukes i uinitialisert samling | .EXISTS(element_posisjon) |
| COUNT | Gir det totale antallet av elementene som er tilstede i en samling | .TELLE |
| BEGRENSE | Det returnerer den maksimale størrelsen på samlingen. For Varray vil den returnere den faste størrelsen som er definert. For Nestet tabell og Indeks-for-tabell gir det NULL | .BEGRENSE |
| FØRST | Returnerer verdien til den første indeksvariabelen (nedsett) av samlingene | .FØRST |
| SISTE | Returnerer verdien av den siste indeksvariabelen (underskrift) av samlingene | .SISTE |
| PRIOR (n) | Returnerer går foran indeksvariabel i en samling av nth element. Hvis det ikke er noen foranstående, returneres indeksverdien NULL | .PRIOR(n) |
| NESTE (n) | Returnerer etterfølger indeksvariabel i en samling av nth element. Hvis det ikke er noen suksess, returneres indeksverdien NULL | .NESTE(n) |
| FORLENGE | Utvider ett element i en samling på slutten | .FORLENGE |
| FORLENG (n) | Utvider n elementer på slutten av en samling | .EXTEND(n) |
| FORLENG (n,i) | Utvider n kopier av ith element på slutten av samlingen | .EXTEND(n,i) |
| TRIM | Fjerner ett element fra slutten av samlingen | .TRIM |
| TRIM (n) | Fjerner n elementer fra slutten av samlingen | .TRIM (n) |
| SLETT | Sletter alle elementene fra samlingen. Gjør samlingen tom | .SLETT |
| SLETT (n) | Sletter det n-te elementet fra samlingen. Hvis nth elementet er NULL, vil dette ikke gjøre noe | .DELETE(n) |
| SLETT (m,n) | Sletter elementet i området mth denne nth i samlingen | .DELETE(m,n) |
Eksempel 1: Opptakstype på underprogramnivå
I dette eksemplet skal vi se hvordan du fyller ut samlingen ved å bruke 'MASSESAMLING' og hvordan henvise innsamlingsdataene.
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 deklarert med kolonnene emp_no, emp_name, lønn og manager av datatype NUMBER, VARCHAR2, NUMBER, NUMBER.
- Kodelinje 9: Opprette samlingen 'emp_det_tbl' av posttypeelementet 'emp_det'
- Kodelinje 10: Erklærer variabelen 'guru99_emp_rec' som 'emp_det_tbl'-type og initialisert med null-konstruktør.
- Kodelinje 12-15: Setter inn eksempeldataene i 'emp'-tabellen.
- Kodelinje 16: Begår innsettingstransaksjonen.
- Kodelinje 17: Henter postene fra 'emp'-tabellen og fyller inn samlingsvariabelen som en bulk ved å bruke kommandoen "BULK COLLECT". Nå inneholder variabelen 'guru99_emp_rec' all posten som finnes i tabellen 'emp'.
- Kodelinje 19-26: Innstilling av 'FOR'-løkken med å skrive ut alle postene i samlingen én etter én. Innsamlingsmetoden FØRST og SISTE brukes som nedre og høyere grense for sløyfe.
Produksjon: Som du kan se i skjermbildet ovenfor når koden ovenfor utføres, vil du få følgende utgang
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 ----------------------------------------------
