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

Vad är en samling?

En samling är en ordnad grupp av element av en viss datatyp. Det kan vara en samling av en enkel datatyp eller en komplex datatyp, såsom användardefinierade datatyper eller posttyper.

I en samling identifieras varje element med en term som kallas en "index." Varje objekt tilldelas ett unikt prenumerationsnummer, och informationen kan manipuleras eller hämtas genom att hänvisa till det unika prenumerationsnummeret.

Samlingar är mest användbara när en stor mängd data av samma typ behöver bearbetas eller manipuleras. Samlingar kan fyllas i och manipuleras som en helhet med hjälp av alternativet 'BULK' i Oracle.

Samlingar klassificeras baserat på struktur, prenumeration och lagring, enligt nedan:

• Indexerade tabeller (även kända som associativa arrayer)
• Kapslade bord
• Varrays

Data i en samling kan när som helst betecknas med tre termer: samlingsnamn, prenumeration och fält- eller kolumnnamn, som " ( ). Du kommer att lära dig mer om dessa samlingskategorier i avsnitten nedan.

Samlingstyper i korthet

De tre samlingstyperna gör olika avvägningar. Tabellen nedan presenterar dem sida vid sida innan var och en behandlas i detalj.

Varrays

En Varray är en samling där arrayens storlek är fast och inte kan överskridas. Indexet för en Varray är ett numeriskt värde. Attributen för Varrays är:

• Den övre gränsstorleken är fast.
• Ifylls sekventiellt med början med nedsänkt '1'.
• Denna samlingstyp är alltid tät; vi kan inte ta bort enskilda arrayelement. En Varray kan tas bort som helhet eller trimmas från slutet.
• Eftersom den alltid är tät har den väldigt liten flexibilitet.
• Det är mer lämpligt när arraystorleken är känd och liknande aktiviteter utförs på alla element.
• Samlingens prenumeration och antal förblir alltid stabila.
• Den måste initieras före användning. Alla operationer förutom EXISTS på en oinitialiserad samling genererar ett fel.
• Den kan skapas som ett databasobjekt som är synligt i hela databasen, eller inuti ett underprogram för användning endast där.

Figuren nedan förklarar minnesallokeringen för en Varray (tät).

Syntax för VARRAY:

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

• I syntaxen ovan deklareras type_name som en VARRAY av typen 'DATA_TYPE' för den givna storleksgränsen. Datatypen kan vara antingen enkel eller komplex.

Kapslade tabeller

En kapslad tabell är en samling där arrayens storlek inte är fast. Den har en numerisk nedsänkt tabelltyp. Mer om den kapslade tabelltypen:

• Den kapslade tabellen har ingen övre storleksgräns.
• Eftersom den övre gränsen inte är fast måste minnet utökas varje gång före användning med hjälp av nyckelordet 'EXTEND'.
• Ifylls sekventiellt med början med nedsänkt '1'.
• Denna samlingstyp kan vara både tät och gles; vi kan skapa den som tät och även ta bort enskilda element slumpmässigt, vilket gör den gles.
• Det ger mer flexibilitet för att ta bort arrayelement.
• Den lagras i en systemgenererad databastabell och kan användas i en urvalsfråga för att hämta värden.
• Prenumerationen och antalet kan variera.
• Den måste initieras före användning. Alla operationer förutom EXISTS på en oinitialiserad samling genererar ett fel.
• Den kan skapas som ett databasobjekt som är synligt i hela databasen, eller inuti ett underprogram för användning endast där.

Figuren nedan förklarar minnesallokeringen för en kapslad tabell (tät och gles). Ett tomt elementutrymme betecknar ett gles element.

Syntax för kapslade tabeller:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• I syntaxen ovan deklareras type_name som en kapslad tabellsamling av typen 'DATA_TYPE'. Datatypen kan vara antingen enkel eller komplex.

Indexera efter tabell

En indexerad tabell är en samling där arraystorleken inte är fast. Till skillnad från andra samlingstyper kan indexet för en indexerad tabell definieras av användaren. Attributen för en indexerad tabell är:

• Subskriptet kan vara ett heltal eller en sträng. Subskripttypen bör anges när samlingen skapas.
• Dessa samlingar lagras inte sekventiellt.
• De är alltid glesa i naturen.
• Arraystorleken är inte fast.
• De kan inte lagras i en databaskolumn. De skapas och används inom en viss session.
• De ger mer flexibilitet i att underhålla prenumerationen.
• Indexen kan vara en negativ sekvens.
• De är mer lämpliga för relativt mindre samlingsvärden som används inom samma delprogram.
• De behöver inte initialiseras före användning.
• De kan inte skapas som ett databasobjekt; de skapas endast inuti ett underprogram.
• BULK COLLECT kan inte användas med den här samlingstypen, eftersom prenumerationen måste anges explicit för varje post.

Figuren nedan förklarar minnesallokeringen för en indexerad tabell (sparse). Ett tomt elementutrymme betecknar ett sparse-element.

Syntax för indexering efter tabell:

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

• I syntaxen ovan deklareras type_name som en indexerad tabellsamling av typen 'DATA_TYPE'. Subscriptvariabeln anges av typen VARCHAR2 med en maximal storlek på 10.

Konstruktör och initieringskoncept i samlingar

Konstruktorer är inbyggda funktioner som tillhandahålls av Oracle som har samma namn som objektet eller samlingen. De körs först varje gång ett objekt eller en samling refereras till för första gången i en session. Viktiga detaljer om en konstruktor i samlingskontexten:

• För samlingar måste dessa konstruktorer anropas explicit för att initiera samlingen.
• Både Varray- och kapslade tabeller måste initieras genom dessa konstruktorer innan de refereras till i programmet.
• En konstruktor utökar implicit minnesallokeringen för en samling (förutom Varray), så den kan också tilldela variabler till samlingen.
• Att tilldela värden genom konstruktörer gör aldrig samlingen gles.

Insamlingsmetoder

Oracle erbjuder många funktioner för att manipulera och arbeta med samlingar. Dessa funktioner bestämmer och modifierar de olika attributen för en samling. Tabellen nedan visar de olika funktionerna och deras beskrivningar.

Exempel 1: Posttyp på underprogramnivå

I det här exemplet ser vi hur man fyller samlingen med hjälp av 'MASSAMLAoch hur man hänvisar till samlingsdata.

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 Förklaring

• Code rad 2-8: Inspelningstyp 'emp_det' deklareras med kolumnerna emp_no, emp_name, manager och salary av datatyperna NUMBER, VARCHAR2, NUMBER och NUMBER.
• Code rad 9: Skapar samlingen 'emp_det_tbl' av posttypelementet 'emp_det'.
• Code rad 10: Deklarerar variabeln 'guru99_emp_rec' som typen 'emp_det_tbl' och initierar den med en nullkonstruktor.
• Code rad 12-15: Infoga exempeldata i 'emp'-tabellen.
• Code rad 16: Begår infogningstransaktionen.
• Code rad 17: Hämtar posterna från tabellen 'emp' och fyller i samlingsvariabeln i bulk med hjälp av "BULK COLLECT". Variabeln 'guru99_emp_rec' innehåller nu alla poster som finns i tabellen 'emp'.
• Code rad 19-26: Ställer in 'FOR'-loopen för att skriva ut alla poster i samlingen en efter en. Insamlingsmetoderna FIRST och LAST används som nedre och övre gränser för slinga.

Produktion: När ovanstående kod körs får du följande 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
----------------------------------------------

Vanliga frågor