Kolekce Oracle PL / SQL: Varrays, Nested & Index podle tabulek

Obsah:

Anonim

Co je kolekce?

Kolekce je uspořádaná skupina prvků konkrétních datových typů. Může to být kolekce jednoduchých datových typů nebo složitých datových typů (například uživatelem definované nebo typy záznamů).

V kolekci je každý prvek identifikován výrazem zvaným „dolní index“. Každá položka ve sbírce je přiřazena jedinečným dolním indexem. S daty v této kolekci lze manipulovat nebo je načíst 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. Kolekce lze naplnit a manipulovat s nimi jako celek pomocí možnosti HROMADNÉ v Oracle.

V tomto výukovém programu se naučíte

  • Co je kolekce?
  • Varrays
  • Vnořené tabulky
  • Rejstřík podle tabulky
  • Konstruktor a koncept inicializace ve sbírkách
  • Metody sběru

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

  • Index-by-tables (také známý jako asociativní pole)
  • Vnořené tabulky
  • Varrays

Data v kolekci lze kdykoli označit třemi termíny Název kolekce, Dolní index, Název pole / sloupce jako „ (). “. O těchto výše uvedených kategoriích sbírky se dozvíte dále v následující části.

Varrays

Varray je metoda kolekce, ve které je pevná velikost pole. Velikost pole nelze překročit, než je jeho pevná hodnota. Dolní index Varray má číselnou hodnotu. Následují atributy Varrays.

  • Velikost horního limitu je pevná
  • Osazeno postupně počínaje indexem „1“
  • Tento typ kolekce je vždy hustý, tj. Nemůžeme odstranit žádné prvky pole. Varray může být odstraněn jako celek nebo může být oříznut od konce.
  • Vzhledem k tomu, že má vždy hustou povahu, má velmi menší flexibilitu.
  • 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 vždy zůstávají 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 EXISTUJE) 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 vysvětlí alokaci paměti Varray (hustě) schematicky.

Dolní index 1 2 3 4 5 6 7
Hodnota Xyz Dfv Sde Cxs Vbc Nhu Qwe

Syntaxe pro VARRAY:

TYPE  IS VARRAY () OF ;
  • Ve výše uvedené syntaxi je type_name deklarován jako VARRAY typu „DATA_TYPE“ pro daný limit velikosti. Datový typ může být jednoduchý nebo složitý.

Vnořené tabulky

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

  • Vnořená tabulka nemá žádný horní limit velikosti.
  • Vzhledem k tomu, že horní limit velikosti není pevně stanoven, je nutné paměť pokaždé rozšířit, než ji použijeme. Kolekci můžeme rozšířit pomocí klíčového slova „EXTEND“.
  • Osazeno postupně počínaje indexem „1“.
  • Tento typ kolekce může být jak hustý, tak řídký , tj. Můžeme vytvořit kolekci jako hustou a můžeme také náhodně odstranit jednotlivý prvek pole, což ji činí řídkou.
  • Poskytuje větší flexibilitu, pokud jde o odstranění prvku pole.
  • Je uložen v databázové tabulce generované systémem a lze jej 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 EXISTUJE) v 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 schématicky vysvětlí alokaci paměti vnořené tabulky (hustá a řídká). Prostor černě zbarveného prvku označuje prázdný prvek v kolekci, tj. Řídký.

Dolní index 1 2 3 4 5 6 7
Hodnota (hustá) Xyz Dfv Sde Cxs Vbc Nhu Qwe
Hodnota (řídká) Qwe Asd Afg Asd My jsme

Syntaxe pro vnořenou tabulku:

TYPE  IS TABLE OF ;
  • Ve výše uvedené syntaxi je type_name deklarován jako kolekce vnořené tabulky typu 'DATA_TYPE'. Datový typ může být jednoduchý nebo složitý.

Rejstřík podle tabulky

Index-by-table je kolekce, ve které není pevná velikost pole. Na rozdíl od ostatních typů kolekcí může být v kolekci index-by-table index definován uživatelem. Následují atributy indexu podle tabulky.

  • Dolní index může být celé číslo nebo řetězce. V době vytváření kolekce by měl být uveden typ dolního indexu.
  • Tyto sbírky se neukládají postupně.
  • Jsou vždy řídké povahy.
  • Velikost pole není pevná.
  • Nelze je uložit do sloupce databáze. Budou vytvořeny a použity v jakémkoli programu v dané relaci.
  • Poskytují větší flexibilitu, pokud jde o zachování dolního indexu.
  • Dolní indexy mohou mít také negativní dolní indexovou sekvenci.
  • Jsou vhodnější použít pro relativně menší kolektivní hodnoty, ve kterých lze kolekci inicializovat a použít v rámci stejných podprogramů.
  • Než je začnete používat, nemusí být inicializovány.
  • 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 v tomto typu kolekce použít, protože index by měl být uveden výslovně pro každý záznam v kolekci.

Níže uvedený obrázek schématicky vysvětlí přidělení paměti vnořené tabulky (řídké). Prostor černě zbarveného prvku označuje prázdný prvek v kolekci, tj. Řídký.

Dolní index (varchar) PRVNÍ DRUHÝ TŘETÍ ČTVRTÝ PÁTÝ ŠESTÝ SEDMÝ
Hodnota (řídká) Qwe Asd Afg Asd My jsme

Syntaxe pro index podle tabulky

TYPE  IS TABLE OF  INDEX BY VARCHAR2 (10);
  • Ve výše uvedené syntaxi je type_name deklarován jako kolekce index-by-table typu 'DATA_TYPE'. Datový typ může být jednoduchý nebo složitý. Proměnná subsciprt / index je uvedena jako typ VARCHAR2 s maximální velikostí 10.

Konstruktor a koncept inicializace ve sbírkách

Konstruktory jsou vestavěnou funkcí poskytovanou Oracle, která má stejný název jako objekt nebo kolekce. Jsou provedeny jako první, kdykoli se objekt nebo kolekce dostanou poprvé v relaci. Níže jsou důležité podrobnosti konstruktoru v kontextu kolekce:

  • U kolekcí by tyto konstruktory měly být volány explicitně, aby se inicializovaly.
  • Před získáním odkazu do programu je třeba pomocí těchto konstruktorů inicializovat obě tabulky Varray i Nested.
  • Konstruktor implicitně rozšiřuje alokaci paměti pro kolekci (kromě Varray), proto konstruktor může také přiřadit proměnné kolekcím.
  • Přiřazení hodnot ke kolekci prostřednictvím konstruktorů nikdy neztratí kolekci.

Metody sběru

Oracle poskytuje mnoho funkcí pro manipulaci a práci se sbírkami. Tyto funkce jsou v programu velmi užitečné k určení a úpravě různých atributů sbírek. Následující tabulka uvádí různé funkce a jejich popis.

Metoda Popis SYNTAX
EXISTUJE (n) Tato metoda vrátí booleovské výsledky. Vrátí hodnotu 'TRUE', pokud v této kolekci existuje n- tý prvek, jinak vrátí hodnotu FALSE. V neinicializované kolekci lze použít pouze EXISTUJÍCÍ funkce .EXISTS (prvek_pozice)
POČET Poskytuje celkový počet prvků přítomných v kolekci .COUNT
OMEZIT Vrátí maximální velikost kolekce. U Varray vrátí pevnou velikost, která byla definována. U vnořené tabulky a indexu po tabulce dává hodnotu NULL .LIMIT
PRVNÍ Vrátí hodnotu první proměnné indexu (dolního indexu) kolekcí . PRVNÍ
POSLEDNÍ Vrátí hodnotu poslední proměnné indexu (dolního indexu) kolekcí . POSLEDNÍ
PRIOR (n) Vrátí předcházející indexovou proměnnou v kolekci n- tého prvku. Pokud neexistuje žádná předcházející hodnota indexu, vrátí se NULL .PRIOR (n)
DALŠÍ (n) Vrátí úspěšnou proměnnou indexu v kolekci n- tého prvku. Pokud neexistuje žádná úspěšná hodnota indexu, je vrácena NULL .NEXT (n)
ROZŠÍŘIT Na konci rozšiřuje jeden prvek v kolekci .EXTEND
ROZŠÍŘIT (n) Rozšiřuje n prvků na konci kolekce .EXTEND (n)
ROZŠÍŘIT (n, i) Rozšiřuje n kopií i- tého prvku na konci kolekce .EXTEND (n, i)
TRIM Odebere jeden prvek z konce kolekce .TRIM
TRIM (n) Odebere n prvků z konce kolekce .TRIM (n)
VYMAZAT Odstraní všechny prvky z kolekce. Kolekce je prázdná . DELETE
ODSTRANIT (n) Odstraní n-tý prvek z kolekce. Pokud je n- tý prvek NULL, pak to nic neudělá .DELETE (n)
ODSTRANIT (m, n) Odstraní prvek v rozmezí m th až n th ve sbírce .DELETE (m, n)

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

V tomto příkladu uvidíme, jak naplnit kolekci pomocí 'BULK COLLECT' a jak odkazovat na data kolekce.

DECLARETYPE 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();BEGININSERT 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_recFROM emp;dbms_output.put_line (‘Employee Detail');FOR i IN guru99_emp_rec.FIRST… guru99_emp_rec.LASTLOOPdbms_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án se sloupci emp_no, emp_name, plat a správce datového typu ČÍSLO, VARCHAR2, ČÍSLO, ČÍSLO.
  • Řádek kódu 9: Vytvoření kolekce 'emp_det_tbl' prvku typu záznamu 'emp_det'
  • Řádek kódu 10: Deklarace proměnné 'guru99_emp_rec' jako typu 'emp_det_tbl' a inicializace pomocí konstruktoru null.
  • Řádek kódu 12-15: Vložení ukázkových dat do tabulky „emp“.
  • Řádek kódu 16: Potvrzení transakce vložení.
  • Řádek kódu 17: Načítání záznamů z tabulky „emp“ a vyplnění proměnné kolekce hromadně pomocí příkazu „HROMADNĚ VYBRAT“. Proměnná 'guru99_emp_rec' nyní obsahuje všechny záznamy, které jsou v tabulce 'emp'.
  • Řádek kódu 19-26: Nastavení smyčky 'FOR' pro tisk všech záznamů ve sbírce jeden po druhém. Metoda sběru PRVNÍ a POSLEDNÍ se používá jako dolní a horní mez smyčky.

Výstup : Jak vidíte na výše uvedeném snímku obrazovky, když je proveden výše uvedený kód, získáte následující výstup

Employee DetailEmployee Number: 1000Employee Name: AAAEmployee Salary: 25000Employee Manager Number: 1000----------------------------------------------Employee Number: 1001Employee Name: XXXEmployee Salary: 10000Employee Manager Number: 1000----------------------------------------------Employee Number: 1002Employee Name: YYYEmployee Salary: 15000Employee Manager Number: 1000----------------------------------------------Employee Number: 1003Employee Name: ZZZEmployee Salary: 7500Employee Manager Number: 1000----------------------------------------------