Oracle PL/SQL-samlinger: Varrays, Nested & Index by Tables

Hva er en samling?

En samling er en ordnet gruppe av elementer av en bestemt datatype. Det kan være en samling av en enkel datatype eller en kompleks datatype, for eksempel brukerdefinerte datatyper eller posttyper.

I en samling identifiseres hvert element med et begrep som kalles en "subscript." Hvert element er tilordnet et unikt abonnement, og dataene kan manipuleres eller hentes ved å referere til det unike abonnementet.

Samlinger er mest nyttige når en stor mengde data av samme type må behandles eller manipuleres. Samlinger kan fylles ut og manipuleres som en helhet ved å bruke alternativet «MASSE» i Oracle.

Samlinger klassifiseres basert på struktur, abonnement og lagring, som vist nedenfor:

• Indekserte tabeller (også kjent som assosiative matriser)
• Nestede bord
• Varrays

Data i en samling kan når som helst refereres til med tre termer: samlingsnavn, indeks og felt- eller kolonnenavn, som « ( ). Du vil lære om disse samlingskategoriene i avsnittene nedenfor.

Oversikt over samlingstyper

De tre samlingstypene har ulike avveininger. Tabellen nedenfor viser dem side om side før hver enkelt blir dekket i detalj.

Varrays

En Varray er en samling der størrelsen på tabellen er fast og ikke kan overskrides. Indekset til en Varray er en numerisk verdi. Attributtene til Varrayer er:

• Den øvre grensen for størrelsen er fast.
• Fylles ut sekvensielt, startende med senket '1'.
• Denne samlingstypen er alltid tett; vi kan ikke slette individuelle array-elementer. En Varray kan slettes som en helhet eller trimmes fra slutten.
• Fordi den alltid er tett, har den svært liten fleksibilitet.
• Det er mer passende når arraystørrelsen er kjent og lignende aktiviteter utføres på alle elementene.
• Samlingens abonnement og antall forblir alltid stabilt.
• Den må initialiseres før bruk. Enhver operasjon unntatt EXISTS på en uinitialisert samling gir en feil.
• Den kan opprettes som et databaseobjekt som er synlig i hele databasen, eller i et underprogram for bruk kun der.

Figuren nedenfor forklarer minneallokeringen til en Varray (tett).

Syntaks for VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• I syntaksen ovenfor er type_name deklarert som en VARRAY av typen 'DATA_TYPE' for den gitte størrelsesgrensen. Datatypen kan være enten enkel eller kompleks.

Nestede tabeller

En nestet tabell er en samling der størrelsen på tabellen ikke er fast. Den har en numerisk indeksert type. Mer om den nestede tabelltypen:

• Den nestede tabellen har ingen øvre størrelsesgrense.
• Fordi den øvre grensen ikke er fast, må minnet utvides hver gang før bruk, ved å bruke nøkkelordet 'EXTEND'.
• Fylles ut sekvensielt, startende med senket '1'.
• Denne samlingstypen kan være begge deler tett og sparsomt; vi kan lage den som tett og også slette individuelle elementer tilfeldig, noe som gjør den sparsom.
• Det gir mer fleksibilitet for å slette array-elementer.
• Den lagres i en systemgenerert databasetabell og kan brukes i en utvalgsspørring for å hente verdier.
• Subskriptet og antallet kan variere.
• Den må initialiseres før bruk. Enhver operasjon unntatt EXISTS på en uinitialisert samling gir en feil.
• Den kan opprettes som et databaseobjekt som er synlig i hele databasen, eller i et underprogram for bruk kun der.

Figuren nedenfor forklarer minneallokeringen til en nestet tabell (tett og sparsom). Et tomt elementrom betegner et sparsomt element.

Syntaks for nestet tabell:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• I syntaksen ovenfor er type_navn deklarert som en nestet tabellsamling av typen 'DATA_TYPE'. Datatypen kan være enten enkel eller kompleks.

Indekser etter tabell

En indeksert tabell er en samling der arraystørrelsen ikke er fast. I motsetning til andre samlingstyper kan indeksen til en indeksert tabell defineres av brukeren. Attributtene til en indeksert tabell er:

• Indekset kan være et heltall eller en streng. Indekstypen bør nevnes når samlingen opprettes.
• Disse samlingene lagres ikke sekvensielt.
• De er alltid sparsomme i naturen.
• Matrisestørrelsen er ikke fast.
• De kan ikke lagres i en databasekolonne. De opprettes og brukes i en bestemt økt.
• De gir mer fleksibilitet i vedlikeholdet av abonnementet.
• Indeksene kan være en negativ sekvens.
• De er mer passende for relativt mindre samlingsverdier som brukes i samme underprogram.
• De trenger ikke å initialiseres før bruk.
• De kan ikke opprettes som et databaseobjekt; de opprettes bare i et underprogram.
• BULK COLLECT kan ikke brukes med denne samlingstypen, fordi abonnementet må oppgis eksplisitt for hver post.

Figuren nedenfor forklarer minneallokeringen til en indeksert tabell (spars). Et tomt elementrom betegner et spars element.

Syntaks for indeksert tabell:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• I syntaksen ovenfor er type_name deklarert som en indeksert tabellsamling av typen 'DATA_TYPE'. Den indekserte variabelen er gitt som VARCHAR2-typen med en maksimal størrelse på 10.

Konstruktør og initialiseringskonsept i samlinger

Konstruktører er innebygde funksjoner levert av Oracle som har samme navn som objektet eller samlingen. De kjøres først hver gang et objekt eller en samling refereres til for første gang i en økt. Viktige detaljer om en konstruktør i samlingskonteksten:

• For samlinger må disse konstruktørene kalles eksplisitt for å initialisere samlingen.
• Både Varray- og nestede tabeller må initialiseres gjennom disse konstruktørene før de refereres til i programmet.
• En konstruktør utvider implisitt minneallokeringen for en samling (unntatt Varray), slik at den også kan tilordne variabler til samlingen.
• Å tilordne verdier gjennom konstruktører gjør aldri samlingen sparsom.

Innsamlingsmetoder

Oracle tilbyr mange funksjoner for å manipulere og arbeide med samlinger. Disse funksjonene bestemmer og endrer de ulike attributtene til en samling. Tabellen nedenfor viser de ulike funksjonene og beskrivelsene av dem.

Eksempel 1: Posttype på underprogramnivå

I dette eksemplet ser vi hvordan man fyller ut samlingen ved hjelp av 'MASSESAMLINGog hvordan man refererer til samlingsdataene.

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 Forklaring

• Code linje 2–8: Record type 'emp_det' deklareres med kolonnene emp_no, emp_name, manager og salary av datatypene NUMBER, VARCHAR2, NUMBER og NUMBER.
• Code linje 9: Oppretter samlingen 'emp_det_tbl' av posttypeelementet 'emp_det'.
• Code linje 10: Deklarerer variabelen 'guru99_emp_rec' som typen 'emp_det_tbl' og initialiserer den med en nullkonstruktør.
• Code linje 12–15: Setter inn eksempeldataene i 'emp'-tabellen.
• Code linje 16: Begår innsettingstransaksjonen.
• Code linje 17: Henter postene fra tabellen 'emp' og fyller ut samlingsvariabelen samtidig ved hjelp av «BULK COLLECT». Variabelen 'guru99_emp_rec' inneholder nå alle postene som finnes i tabellen 'emp'.
• Code linje 19–26: Setter «FOR»-løkken til å skrive ut alle postene i samlingen én etter én. Innsamlingsmetodene FIRST og LAST brukes som nedre og øvre grenser for sløyfe.

Utgang: Når koden ovenfor kjøres, får du følgende utdata.

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

Spørsmål og svar