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

Co je sbírka?

Kolekce je uspořádaná skupina prvků určitého datového typu. Může se jednat o kolekci jednoduchého datového typu nebo komplexního datového typu, jako jsou uživatelsky definované typy nebo typy záznamů.

V kolekci je každý prvek identifikován termínem nazývaným "dolní index." Každé položce je přiřazen jedinečný dolní index a data lze manipulovat nebo načítat odkazem na tento jedinečný dolní index.

Kolekce jsou nejužitečnější, když je třeba zpracovat nebo manipulovat s velkým množstvím dat stejného typu. Kolekce lze naplnit a manipulovat s nimi jako 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:

• Indexované tabulky (známé také jako asociativní pole)
• Vnořené tabulky
• Varrays

Data v kolekci lze kdykoli označit třemi termíny: názvem kolekce, dolním indexem a názvem pole nebo sloupce, jako „ ( ). „O těchto kategoriích sbírek se dozvíte v níže uvedených částech.“

Typy kolekcí v kostce

Tyto tři typy kolekcí mají různé kompromisy. Níže uvedená tabulka je srovnává, než je každý z nich podrobněji rozebere.

Varrays

Varray je kolekce, ve které je velikost pole pevně daná a nelze ji překročit. Dolní index Varray je číselná hodnota. Atributy Varray jsou:

• Horní limit velikosti je pevně stanovený.
• Vyplňováno postupně počínaje dolním indexem '1'.
• Tento typ kolekce je vždy hustý; nemůžeme mazat jednotlivé prvky pole. Varray lze smazat jako celek nebo oříznout od konce.
• Protože je vždy hustý, má velmi malou flexibilitu.
• Je vhodnější, když je známa velikost pole a podobné činnosti se provedou na všech prvcích.
• Dolní index a počet v kolekci zůstávají vždy stabilní.
• Před použitím musí být inicializována. Jakákoli operace kromě EXISTS na neinicializované kolekci vyvolá chybu.
• Může být vytvořen jako databázový objekt viditelný v celé databázi nebo uvnitř podprogramu pro použití pouze tam.

Obrázek níže vysvětluje alokaci paměti pro Varray (hustou).

Syntaxe pro VARRAY:

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

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

Vnořené tabulky

Vnořená tabulka je kolekce, ve které velikost pole není pevně daná. Má číselný typ dolního indexu. Více o typu vnořené tabulky:

• Vnořená tabulka nemá horní limit velikosti.
• Protože horní limit není pevně stanovený, je nutné paměť před každým použitím rozšířit pomocí klíčového slova 'EXTEND'.
• Vyplňováno postupně počínaje dolním indexem '1'.
• Tento typ kolekce může být oba husté a řídké; můžeme jej vytvořit jako hustý a také náhodně mazat jednotlivé prvky, čímž se stane řídkým.
• Poskytuje větší flexibilitu při mazání prvků pole.
• Je uložen v systémem generované databázové tabulce a lze jej použít ve výběrovém dotazu k načtení hodnot.
• Dolní index a počet se mohou lišit.
• Před použitím musí být inicializována. Jakákoli operace kromě EXISTS na neinicializované kolekci vyvolá chybu.
• Může být vytvořen jako databázový objekt viditelný v celé databázi nebo uvnitř podprogramu pro použití pouze tam.

Obrázek níže vysvětluje alokaci paměti vnořené tabulky (husté a řídké). Prázdný prostor elementu označuje řídký element.

Syntaxe pro vnořenou tabulku:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

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

Indexování podle tabulky

Indexová tabulka je kolekce, ve které není velikost pole pevně daná. Na rozdíl od jiných typů kolekcí může dolní index indexové tabulky definovat uživatel. Atributy indexové tabulky jsou:

• Dolní index může být celé číslo nebo řetězec. Typ dolního indexu by měl být uveden při vytváření kolekce.
• 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. Jsou vytvářeny a používány v rámci konkrétní relace.
• Poskytují větší flexibilitu při udržování dolního indexu.
• Dolní indexy mohou být zápornou posloupností.
• Jsou vhodnější pro relativně menší hodnoty kolekcí používané v rámci stejného podprogramu.
• Před použitím je není nutné inicializovat.
• Nelze je vytvořit jako databázový objekt; vytvářejí se pouze uvnitř podprogramu.
• Funkce BULK COLLECT se s tímto typem kolekce nedá použít, protože dolní index musí být pro každý záznam zadán explicitně.

Obrázek níže vysvětluje alokaci paměti pro tabulku index-by (řídký typ). Prázdný prostor pro element označuje řídký element.

Syntaxe pro tabulku s indexem:

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

• Ve výše uvedené syntaxi je typ_name deklarován jako kolekce tabulek typu 'DATA_TYPE' řazená po indexech. Proměnná typu dolní index je zadána jako typ VARCHAR2 s maximální velikostí 10.

Konstruktor a inicializační koncept v kolekcích

Konstruktory jsou vestavěné funkce poskytované Oracle které mají stejný název jako objekt nebo kolekce. Jsou provedány jako první, kdykoli je na objekt nebo kolekci poprvé odkazováno v relaci. Důležité podrobnosti o konstruktoru v kontextu kolekce:

• U kolekcí musí být tyto konstruktory volány explicitně pro inicializaci kolekce.
• Jak tabulky typu Varray, tak vnořené tabulky je nutné inicializovat pomocí těchto konstruktorů, než se na ně v programu odkáže.
• Konstruktor implicitně rozšiřuje alokaci paměti pro kolekci (kromě Varray), takže může kolekci také přiřazovat proměnné.
• Přiřazování hodnot pomocí konstruktorů nikdy nezpůsobí řídký stav kolekce.

Metody sběru

Oracle poskytuje mnoho funkcí pro manipulaci a práci s kolekcemi. Tyto funkce určují a upravují různé atributy kolekce. Níže uvedená tabulka uvádí různé funkce a jejich popisy.

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

V tomto příkladu uvidíme, jak naplnit kolekci pomocí 'VELKÝ SBĚR„a jak odkazovat na data ze 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;
/

Code Vysvětlení

• Code řádek 2–8: Typ záznamu Proměnná „emp_det“ je deklarována se sloupci emp_no, emp_name, manager a salary datového typu NUMBER, VARCHAR2, NUMBER a NUMBER.
• Code řádek 9: Vytvoření kolekce 'emp_det_tbl' z elementu typu záznamu 'emp_det'.
• Code řádek 10: Deklarace proměnné 'guru99_emp_rec' typu 'emp_det_tbl' a její inicializace konstruktorem s hodnotou null.
• Code řádek 12–15: Vložení ukázkových dat do tabulky 'emp'.
• Code řádek 16: Potvrzení transakce vložení.
• Code řádek 17: Načítání záznamů z tabulky 'emp' a hromadné naplnění proměnné kolekce pomocí příkazu „BULK COLLECT“. Proměnná 'guru99_emp_rec' nyní obsahuje všechny záznamy přítomné v tabulce 'emp'.
• Code řádek 19–26: Nastavení smyčky 'FOR' pro tisk všech záznamů v kolekci jeden po druhém. Metody kolekce FIRST a LAST se používají jako dolní a horní limity smyčka.

Výstup: Po spuštění 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
----------------------------------------------

Nejčastější dotazy