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

Co je kolekce?

Kolekce je uspořádaná skupina prvků konkrétních datových typů. Může se jednat o kolekci jednoduchých datových typů nebo komplexních datových typů (jako jsou uživatelem definované typy nebo typy záznamů).

V kolekci je každý prvek identifikován termínem tzv "dolní index." Každé položce v kolekci je přiřazen jedinečný dolní index. S daty v této kolekci lze manipulovat nebo je načítat odkazem na tento jedinečný dolní index.

Kolekce jsou nejužitečnější věci, když je třeba zpracovat nebo manipulovat s velkými daty stejného typu. Sbírky lze naplnit a manipulovat s nimi jako s celkem pomocí možnosti 'BULK' v Oracle.

Kolekce jsou klasifikovány na základě struktury, dolního indexu a úložiště, jak je uvedeno níže.

• Indexování podle tabulek (také známé jako asociativní pole)
• Vnořené tabulky
• Varrays

Na data v kolekci lze kdykoli odkazovat třemi pojmy Název kolekce, Dolní index, Název pole/sloupce jako „ ( ). “. O těchto výše uvedených kategoriích kolekcí se dozvíte dále v níže uvedené sekci.

Varrays

Varray je metoda sběru, ve které je velikost pole pevná. Velikost pole nemůže být překročena, než je jeho pevná hodnota. Dolní index Varray má číselnou hodnotu. Následují atributy Varrays.

• Horní limit velikosti je pevný
• Vyplňuje se postupně počínaje dolním indexem '1'
• Tento typ kolekce je vždy hustý, tj. nemůžeme smazat žádné prvky pole. Varray lze smazat jako celek, nebo jej lze od konce oříznout.
• Vzhledem k tomu, že má vždy hustou povahu, má velmi menší pružnost.
• Je vhodnější použít, když je známa velikost pole a provádět podobné činnosti na všech prvcích pole.
• Dolní index a sekvence zůstávají vždy stabilní, tj. dolní index a počet kolekce jsou vždy stejné.
• Před použitím v programech je třeba je inicializovat. Jakákoli operace (kromě operace EXISTS) na neinicializované kolekci vyvolá chybu.
• Může být vytvořen jako databázový objekt, který je viditelný v celé databázi nebo uvnitř podprogramu, který lze použít pouze v tomto podprogramu.

Níže uvedený obrázek schematicky vysvětlí alokaci paměti Varray (hustá).

Syntaxe pro VARRAY:

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

• Ve výše uvedené syntaxi je název_typu deklarován jako VARRAY typu 'DATA_TYPE' pro daný limit velikosti. Datový typ může být jednoduchý nebo komplexní typ.

Vnořené tabulky

Vnořená tabulka je kolekce, ve které není velikost pole pevná. Má číselný typ dolního indexu. Níže jsou uvedeny další popisy typu vnořených tabulek.

• Vnořená tabulka nemá horní limit velikosti.
• Vzhledem k tomu, že horní limit velikosti není pevně daný, je potřeba paměť kolekce rozšířit pokaždé, než ji použijeme. Kolekci můžeme rozšířit pomocí klíčového slova 'EXTEND'.
• Vyplňuje se postupně počínaje dolním indexem '1'.
• Tento typ kolekce může být obojího husté a řídké, tj. můžeme vytvořit kolekci jako hustou, a také můžeme náhodně odstranit jednotlivé prvky pole, čímž se stane řídkou.
• Poskytuje větší flexibilitu při odstraňování prvku pole.
• Je uložena v systémově generované databázové tabulce a lze ji použít ve výběrovém dotazu k načtení hodnot.
• Dolní index a sekvence nejsou stabilní, tj. dolní index a počet prvků pole se mohou lišit.
• Před použitím v programech je třeba je inicializovat. Jakákoli operace (kromě operace EXISTS) na neinicializované kolekci vyvolá chybu.
• Může být vytvořen jako databázový objekt, který je viditelný v celé databázi nebo uvnitř podprogramu, který lze použít pouze v tomto podprogramu.

Níže uvedený obrázek schematicky vysvětlí alokaci paměti vnořené tabulky (hustá a řídká). Černě zbarvený prostor prvku označuje prázdný prvek v kolekci, tj. řídký.

Syntaxe pro vnořenou tabulku:

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;

• Ve výše uvedené syntaxi je název_typu deklarován jako kolekce vnořených tabulek typu 'DATA_TYPE'. Datový typ může být jednoduchý nebo komplexní typ.

Index podle tabulky

Index-by-table je kolekce, ve které není velikost pole pevná. Na rozdíl od jiných typů kolekcí může v kolekci index-by-table dolní index sestávat z definování uživatelem. Následují atributy index-by-table.

• Dolní index může mít celé číslo nebo řetězce. Při vytváření kolekce by měl být uveden typ dolního indexu.
• Tyto kolekce se neukládají sekvenčně.
• V přírodě jsou vždy řídké.
• Velikost pole není pevná.
• Nelze je uložit do sloupce databáze. Musí být vytvořeny a použity v jakémkoli programu v dané konkrétní relaci.
• Poskytují větší flexibilitu, pokud jde o udržování dolního indexu.
• Indexy mohou mít také zápornou sekvenci indexů.
• Je vhodnější je použít pro relativně menší hromadné hodnoty, ve kterých lze kolekci inicializovat a používat v rámci stejných podprogramů.
• Není třeba je inicializovat, než je začnete používat.
• Nelze jej vytvořit jako databázový objekt. Lze jej vytvořit pouze uvnitř podprogramu, který lze použít pouze v tomto podprogramu.
• BULK COLLECT nelze použít v tomto typu kolekce, protože dolní index by měl být uveden výslovně pro každý záznam v kolekci.

Níže uvedený obrázek schematicky vysvětlí alokaci paměti vnořené tabulky (řídké). Černě zbarvený prostor prvku označuje prázdný prvek v kolekci, tj. řídký.

Syntaxe pro indexování podle tabulky

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

• Ve výše uvedené syntaxi je název_typu deklarován jako kolekce indexů podle tabulky typu 'DATA_TYPE'. Datový typ může být jednoduchý nebo komplexní typ. Proměnná subsciprt/index je uvedena jako typ VARCHAR2 s maximální velikostí 10.

Konstruktor a inicializační koncept v kolekcích

Konstruktory jsou vestavěné funkce poskytované věštcem, které mají stejný název jako objekt nebo kolekce. Jsou spuštěny jako první vždy, když jsou objekt nebo kolekce poprvé v relaci odkazovány. Níže jsou uvedeny důležité podrobnosti konstruktoru v kontextu kolekce:

• U kolekcí by tyto konstruktory měly být volány explicitně, aby je inicializovaly.
• Jak tabulky Varray, tak vnořené tabulky musí být inicializovány prostřednictvím těchto konstruktorů, než budou odkazovány do programu.
• Konstruktor implicitně rozšiřuje alokaci paměti pro kolekci (kromě Varray), takže konstruktor může také přiřadit proměnné ke kolekcím.
• Přiřazení hodnot kolekci pomocí konstruktorů nikdy neučiní kolekci řídkou.

Metody sběru

Oracle poskytuje mnoho funkcí pro manipulaci a práci s kolekcemi. Tyto funkce jsou v programu velmi užitečné pro určení a úpravu různých atributů kolekcí. V následující tabulce jsou uvedeny různé funkce a jejich popis.

Příklad1: Typ záznamu na úrovni podprogramu

V tomto příkladu uvidíme, jak naplnit kolekci pomocí 'VELKÝ SBĚR“ a jak odkazovat na data sbírky.

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

Vysvětlení kódu:

• Řádek kódu 2-8: Typ záznamu 'emp_det' je deklarováno se sloupci emp_no, emp_name, plat a manažer datového typu NUMBER, VARCHAR2, NUMBER, NUMBER.
• Řádek kódu 9: Vytvoření kolekce 'emp_det_tbl' prvku typu záznamu 'emp_det'
• Řádek kódu 10: Deklaruje proměnnou 'guru99_emp_rec' jako typ 'emp_det_tbl' a inicializuje ji konstruktorem null.
• Řádek 12–15 kódu: Vložení ukázkových dat do tabulky 'emp'.
• Řádek kódu 16: Potvrzení transakce vložení.
• Řádek kódu 17: Načtení záznamů z tabulky 'emp' a naplnění proměnné kolekce jako hromadné pomocí příkazu „BULK COLLECT“. Nyní proměnná 'guru99_emp_rec' obsahuje všechny záznamy, které jsou přítomny v tabulce 'emp'.
• Řádek 19–26 kódu: Nastavení smyčky 'FOR' pro tisk všech záznamů v kolekci jeden po druhém. Metoda sběru FIRST and LAST se používá jako spodní a horní hranice smyčka.

Výstup: Jak můžete vidět na výše uvedeném snímku obrazovky, po provedení výše uvedeného kódu získáte následující výstup

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