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

⚡ Smart opsummering

PL/SQL-samlinger er ordnede grupper af elementer af samme datatype, der hver nås via et sænket script. Tre typer, Varray, indlejret tabel og indekseret tabel, adskiller sig i, om størrelsen er fast, hvordan sænket script fungerer, og om de kan gemmes i databasen.

  • 🧺 Definition: En samling indeholder mange elementer af én type, der hver især er identificeret med et unikt sænket skrift.
  • 📐 Varray: Fast øvre størrelse, altid tæt, numerisk sænket skrift, skal initialiseres før brug.
  • 📚 Indlejret tabel: Ingen størrelsesgrænse, kan være tæt eller sparsom, gemt i en systemtabel, udvidet med EXTEND.
  • 🔑 Indekseret efter tabel: Ingen størrelsesgrænse, sænket skrift kan være en streng eller et negativt heltal, og gemmes ikke i databasen.
  • 🏗️ Bygger: Varray og indlejrede tabeller kræver en eksplicit konstruktør for at initialisere før reference.
  • 🛠️ Metoder: COUNT, EXISTS, FIRST, LAST, EXTEND, TRIM og DELETE administrerer en samling.
  • ⚡ Bulk: BULK COLLECT udfylder en samling i ét trin for hurtig behandling.

Oracle PL/SQL-samlinger

Hvad er en samling?

En samling er en ordnet gruppe af elementer af en bestemt datatype. Det kan være en samling af en simpel datatype eller en kompleks datatype, såsom brugerdefinerede eller posttyper.

I en samling er hvert element identificeret med et udtryk kaldet en "subscript." Hvert element er tildelt et unikt indeks, og dataene kan manipuleres eller hentes ved at referere til dette unikke indeks.

Samlinger er mest nyttige, når en stor mængde data af samme type skal behandles eller manipuleres. Samlinger kan udfyldes og manipuleres som en helhed ved hjælp af 'BULK'-indstillingen i Oracle.

Samlinger klassificeres baseret på struktur, abonnement og lagring, som vist nedenfor:

  • Indekseringsbaserede tabeller (også kendt som associative arrays)
  • Indlejrede borde
  • Varrays

Data i en samling kan til enhver tid betegnes med tre termer: samlingsnavn, indeks og felt- eller kolonnenavn, som " ( ). Du kan læse mere om disse samlingskategorier i afsnittene nedenfor.

Overblik over samlingstyper

De tre samlingstyper indebærer forskellige afvejninger. Tabellen nedenfor viser dem side om side, før hver enkelt gennemgås i detaljer.

Aspect Varray Indlejret tabel Indeks efter tabel
Størrelse Fast øvre grænse Ingen grænse Ingen grænse
Sænket Numerisk Numerisk Heltal eller streng
Density Altid tæt Tæt eller sparsom Altid sparsom
Gemt i databasen Ja Ja Ingen
Skal initialiseres Ja Ja Ingen

Varrays

En Varray er en samling, hvor størrelsen af ​​arrayet er fast og ikke kan overskrides. Indekset for en Varray er en numerisk værdi. Attributterne for Varrays er:

  • Den øvre grænse for størrelsen er fast.
  • Udfyldes sekventielt startende med indeks '1'.
  • Denne samlingstype er altid tæt; vi kan ikke slette individuelle array-elementer. En Varray kan slettes som helhed eller trimmes fra enden.
  • Fordi den altid er tæt, har den meget lidt fleksibilitet.
  • Det er mere passende, når array-størrelsen er kendt, og lignende aktiviteter udføres på alle elementer.
  • Samlingens abonnement og antal forbliver altid stabile.
  • Den skal initialiseres før brug. Enhver handling undtagen EXISTS på en ikke-initialiseret samling udløser en fejl.
  • Den kan oprettes som et databaseobjekt, der er synligt i hele databasen, eller inde i et underprogram til brug kun der.

Figuren nedenfor forklarer hukommelsesallokeringen af ​​en Varray (tæt).

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 type_name deklareret som en VARRAY af typen 'DATA_TYPE' for den givne størrelsesgrænse. Datatypen kan være enten simpel eller kompleks.

Indlejrede tabeller

En indlejret tabel er en samling, hvor størrelsen på arrayet ikke er fast. Den har en numerisk sænket type. Mere om den indlejrede tabeltype:

  • Den indbyggede tabel har ingen øvre størrelsesgrænse.
  • Da den øvre grænse ikke er fast, skal hukommelsen udvides hver gang før brug ved hjælp af nøgleordet 'EXTEND'.
  • Udfyldes sekventielt startende med indeks '1'.
  • Denne samlingstype kan være både tæt og sparsomt; vi kan gøre den tæt og også slette individuelle elementer tilfældigt, hvilket gør den sparsom.
  • Det giver mere fleksibilitet til at slette array-elementer.
  • Den gemmes i en systemgenereret databasetabel og kan bruges i en udvælgelsesforespørgsel til at hente værdier.
  • Abonnementet og antallet kan variere.
  • Den skal initialiseres før brug. Enhver handling undtagen EXISTS på en ikke-initialiseret samling udløser en fejl.
  • Den kan oprettes som et databaseobjekt, der er synligt i hele databasen, eller inde i et underprogram til brug kun der.

Figuren nedenfor forklarer hukommelsesallokeringen af ​​en indlejret tabel (tæt og sparsom). Et tomt elementrum angiver et sparsomt element.

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 <type_name> IS TABLE OF <DATA_TYPE>;
  • I ovenstående syntaks er type_name deklareret som en indlejret tabelsamling af typen 'DATA_TYPE'. Datatypen kan være enten simpel eller kompleks.

Indeks efter tabel

En index-by-tabel er en samling, hvor arraystørrelsen ikke er fast. I modsætning til andre samlingstyper kan indekset for en index-by-tabel defineres af brugeren. Attributterne for en index-by-tabel er:

  • Indekset kan være et heltal eller en streng. Indekstypen bør nævnes, når samlingen oprettes.
  • Disse samlinger gemmes ikke sekventielt.
  • De er altid sparsomme i naturen.
  • Matrixstørrelsen er ikke fast.
  • De kan ikke gemmes i en databasekolonne. De oprettes og bruges i en bestemt session.
  • De giver mere fleksibilitet i forbindelse med vedligeholdelse af abonnementet.
  • Indeksene kan være en negativ sekvens.
  • De er mere passende til relativt mindre samlingsværdier, der bruges inden for det samme underprogram.
  • De behøver ikke at blive initialiseret før brug.
  • De kan ikke oprettes som et databaseobjekt; de oprettes kun i et underprogram.
  • BULK COLLECT kan ikke bruges med denne samlingstype, fordi abonnementet skal angives eksplicit for hver post.

Figuren nedenfor forklarer hukommelsesallokeringen af ​​en indekseret tabel (sparse). Et tomt elementrum angiver et sparse-element.

Subscript (varchar) FØRST ANDEN TREDJE FJERDE Femte SJETTE SYVENDE
Værdi (sparsom) Qwe asd Afg asd Wer

Syntaks for indekseret tabel:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
  • I ovenstående syntaks er type_name deklareret som en indekseret tabelsamling af typen 'DATA_TYPE'. Den sænkede variabel er angivet som VARCHAR2-typen med en maksimal størrelse på 10.

Konstruktør og initialiseringskoncept i samlinger

Konstruktører er indbyggede funktioner leveret af Oracle der har samme navn som objektet eller samlingen. De udføres først, hver gang et objekt eller en samling refereres til for første gang i en session. Vigtige detaljer om en konstruktør i samlingskonteksten:

  • For samlinger skal disse konstruktører kaldes eksplicit for at initialisere samlingen.
  • Både Varray- og indbyggede tabeller skal initialiseres via disse konstruktører, før der refereres til dem i programmet.
  • En konstruktør udvider implicit hukommelsesallokeringen for en samling (undtagen Varray), så den også kan tildele variabler til samlingen.
  • Tildeling af værdier via konstruktører gør aldrig samlingen sparse.

Indsamlingsmetoder

Oracle indeholder mange funktioner til at manipulere og arbejde med samlinger. Disse funktioner bestemmer og ændrer de forskellige attributter for en samling. Tabellen nedenfor viser de forskellige funktioner og deres beskrivelser.

Metode Beskrivelse Syntaks
FINDER (n) Returnerer et boolsk resultat. Returnerer TRUE, hvis det n'te element findes, ellers FALSE. Kun EXISTS kan bruges på en ikke-initialiseret samling. .EXISTS(element_position)
COUNT Angiver det samlede antal elementer, der findes i en samling. .TÆLLE
GRÆNSE Returnerer samlingens maksimale størrelse. For Varray returneres den faste størrelse; for indbyggede og indekserede tabeller returneres NULL. .BEGRÆNSE
FØRST Returnerer værdien af ​​det første abonnement i samlingen. .FØRST
LAST Returnerer værdien af ​​det sidste indeks i samlingen. .SIDST
FORUD (n) Returnerer det foregående sænkede script for det n'te element. Hvis intet, returneres NULL. .PRIOR(n)
NÆSTE (n) Returnerer det efterfølgende sænkede script for det n'te element. Hvis intet, returneres NULL. .NÆSTE(n)
FORLÆNGE Udvider et element i slutningen af ​​en samling. .FORLÆNGE
FORLÆNG (n) Udvider n elementer i slutningen af ​​en samling. .EXTEND(n)
FORLÆNG (n,i) Udvider n kopier af det i'te 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 og gør den tom. .SLET
SLET (n) Sletter det n'te element. Hvis det n'te element er NULL, sker der ingenting. .DELETE(n)
SLET (m,n) Sletter elementerne i området mth til nth i samlingen. .DELETE(m,n)

Eksempel 1: Posttype på underprogramniveau

I dette eksempel ser vi, hvordan man udfylder samlingen ved hjælp af 'MASSEINDSAMLING' og hvordan man henviser til samlingsdataene.

PL/SQL-samling udfyldt med BULK COLLECT-eksempel

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 kolonnerne emp_no, emp_name, manager og salary af datatypene NUMBER, VARCHAR2, NUMBER og NUMBER.
  • Code linje 9: Opretter samlingen 'emp_det_tbl' af posttypeelementet 'emp_det'.
  • Code linje 10: Deklarering af variablen 'guru99_emp_rec' som typen 'emp_det_tbl' og initialisering med en null-konstruktør.
  • Code linje 12-15: Indsættelse af eksempeldata i 'emp'-tabellen.
  • Code linje 16: Forpligtelse af indsættelsestransaktionen.
  • Code linje 17: Hentning af poster fra tabellen 'emp' og udfyldning af samlingsvariablen samlet ved hjælp af "BULK COLLECT". Variablen 'guru99_emp_rec' indeholder nu alle poster, der findes i tabellen 'emp'.
  • Code linje 19-26: Indstilling af 'FOR'-løkken til at udskrive alle poster i samlingen én efter én. Indsamlingsmetoderne FIRST og LAST bruges som de nedre og øvre grænser for loop.

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

Ofte Stillede Spørgsmål

En Varray har en fast øvre størrelse og er altid tæt. En indlejret tabel har ingen størrelsesgrænse, kan være sparse og kan udvides, hvilket gør den mere fleksibel til voksende eller hullede data.

Fordi det er et associativt array i hukommelsen, ikke en gemt tabel. Indekset fungerer som en opslagsnøgle, så en streng eller et negativt heltal fungerer, hvilket er nyttigt til opslag med nøgler i en session.

En ikke-initialiseret Varray eller indlejret tabel er atomisk null, så reference til et element forårsager en fejl. Kald af konstruktøren allokerer det, hvorefter EXTEND og assignments kan tilføje data.

Ja. Givet om størrelsen er fast, om dataene er gemt, og om en strengnøgle er nødvendig, kan AI pege på en Varray, en indlejret tabel eller en indekseret tabel og forklare afvejningen.

BULK COLLECT indlæser alle rækker i en samling i et enkelt kontekstskift mellem SQL- og PL/SQL-motorerne i stedet for ét skift pr. række, hvilket reducerer overhead betydeligt på store resultatsæt.

Opsummer dette indlæg med: