Oracle Kolekcje PL/SQL: Varrays, zagnieżdżone i indeksowane według tabel

Co to jest kolekcja?

Kolekcja to uporządkowana grupa elementów określonych typów danych. Może to być kolekcja prostego typu danych lub złożonego typu danych (takich jak typy zdefiniowane przez użytkownika lub rekordy).

W kolekcji każdy element jest oznaczony terminem tzw "indeks." Każdemu elementowi kolekcji przypisany jest unikalny indeks dolny. Dane w tej kolekcji można manipulować lub pobierać, odwołując się do tego unikalnego indeksu dolnego.

Kolekcje są najbardziej przydatne, gdy trzeba przetworzyć lub manipulować dużymi danymi tego samego typu. Kolekcje można zapełniać i manipulować nimi w całości, korzystając z opcji „BULK” w pliku Oracle.

Zbiory są klasyfikowane na podstawie struktury, indeksu dolnego i sposobu przechowywania, jak pokazano poniżej.

Indeks według tabel (znany również jako tablica asocjacyjna)
Tabele zagnieżdżone
Varrays

W dowolnym momencie dane w kolekcji można opisać trzema terminami: Nazwa kolekcji, Indeks dolny, Nazwa pola/kolumny jako „ ( ). ”. O wyżej wymienionych kategoriach kolekcji dowiesz się więcej w poniższej sekcji.

Varrays

Varray to metoda kolekcji, w której rozmiar tablicy jest stały. Rozmiar tablicy nie może być większy niż jej stała wartość. Indeks dolny Varray ma wartość liczbową. Poniżej przedstawiono atrybuty Varray.

Rozmiar górnego limitu jest stały
Wypełniane sekwencyjnie, zaczynając od indeksu dolnego „1”
Ten typ kolekcji jest zawsze gęsty, tzn. nie możemy usunąć żadnych elementów tablicy. Varray można usunąć w całości lub przyciąć od końca.
Ponieważ ma zawsze gęstą naturę, ma bardzo mniejszą elastyczność.
Bardziej odpowiednie jest użycie, gdy znany jest rozmiar tablicy i wykonanie podobnych działań na wszystkich elementach tablicy.
Indeks dolny i kolejność zawsze pozostają stabilne, tj. indeks dolny i liczba kolekcji są zawsze takie same.
Muszą zostać zainicjowane przed użyciem w programach. Każda operacja (oprócz operacji EXISTS) na niezainicjowanej kolekcji spowoduje błąd.
Można go utworzyć jako obiekt bazy danych, widoczny w całej bazie danych lub wewnątrz podprogramu, który może być wykorzystany tylko w tym podprogramie.

Poniższy rysunek wyjaśni schematycznie alokację pamięci Varray (gęstej).

indeks	1	2	3	4	5	6	7
wartość	Xyz	Dfw	Sde	Cxs	Vbc	Nhu	Qwe

Składnia VARRAY:

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

W powyższej składni type_name jest deklarowane jako VARRAY typu 'DATA_TYPE' dla podanego limitu rozmiaru. Typ danych może być prosty lub złożony.

Tabele zagnieżdżone

Tabela zagnieżdżona to kolekcja, w której rozmiar tablicy nie jest ustalony. Ma numeryczny typ indeksu dolnego. Poniżej znajduje się więcej opisów dotyczących typu tabeli zagnieżdżonej.

Tabela zagnieżdżona nie ma górnego limitu rozmiaru.
Ponieważ górny limit rozmiaru nie jest ustalony, pamięć kolekcji musi zostać każdorazowo rozszerzona przed jej użyciem. Kolekcję możemy rozszerzyć za pomocą słowa kluczowego 'EXTEND'.
Wypełniane sekwencyjnie, zaczynając od indeksu dolnego „1”.
Ten typ kolekcji może należeć do obu typów kolekcji gęsty i rzadki, czyli możemy stworzyć kolekcję jako gęstą, a także możemy losowo usunąć pojedynczy element tablicy, co sprawi, że będzie ona rzadka.
Daje większą elastyczność w zakresie usuwania elementu tablicy.
Jest on przechowywany w wygenerowanej przez system tabeli bazy danych i może zostać użyty w zapytaniu wybierającym w celu pobrania wartości.
Indeks dolny i sekwencja nie są stabilne, tj. indeks dolny i liczba elementów tablicy mogą się różnić.
Muszą zostać zainicjowane przed użyciem w programach. Każda operacja (oprócz operacji EXISTS) na niezainicjowanej kolekcji spowoduje błąd.
Można go utworzyć jako obiekt bazy danych, widoczny w całej bazie danych lub wewnątrz podprogramu, który może być wykorzystany tylko w tym podprogramie.

Poniższy rysunek wyjaśni schematycznie alokację pamięci w tabeli zagnieżdżonej (gęstej i rzadkiej). Czarna przestrzeń elementów oznacza pusty element w kolekcji, tj. rzadki.

indeks	1	2	3	4	5	6	7
Wartość (gęsta)	Xyz	Dfw	Sde	Cxs	Vbc	Nhu	Qwe
Wartość (rzadka)	Qwe		asd	afg		asd	Wer

Składnia tabeli zagnieżdżonej:

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

W powyższej składni type_name jest zadeklarowane jako Zagnieżdżona kolekcja tabel typu 'DATA_TYPE'. Typ danych może być prosty lub złożony.

Indeks po tabeli

Index-by-table to kolekcja, w której rozmiar tablicy nie jest stały. W przeciwieństwie do innych typów kolekcji, w kolekcji index-by-table indeks dolny może być zdefiniowany przez użytkownika. Poniżej przedstawiono atrybuty index-by-table.

Indeks dolny może zawierać liczbę całkowitą lub ciąg znaków. W momencie tworzenia kolekcji należy podać typ indeksu dolnego.
Zbiory te nie są przechowywane sekwencyjnie.
Mają one zawsze nieliczny charakter.
Rozmiar tablicy nie jest stały.
Nie można ich przechowywać w kolumnie bazy danych. Należy je utworzyć i używać w dowolnym programie w tej konkretnej sesji.
Dają większą elastyczność w zakresie utrzymywania indeksu dolnego.
Indeksy dolne mogą mieć również ujemną sekwencję indeksu dolnego.
Są bardziej odpowiednie do stosowania w przypadku stosunkowo mniejszych wartości zbiorczych, w których kolekcja może zostać zainicjowana i wykorzystana w tych samych podprogramach.
Nie trzeba ich inicjować przed rozpoczęciem ich używania.
Nie można go utworzyć jako obiektu bazy danych. Można go utworzyć tylko wewnątrz podprogramu, którego można używać tylko w tym podprogramie.
W tym typie kolekcji nie można używać funkcji BULK COLLECT, ponieważ indeks dolny powinien być podany jawnie dla każdego rekordu w kolekcji.

Poniższy rysunek wyjaśni schematycznie alokację pamięci w tabeli zagnieżdżonej (rzadkiej). Czarna przestrzeń elementów oznacza pusty element w kolekcji, tj. rzadki.

Indeks dolny (varchar)	PIERWSZY	DRUGIE	TRZECI	CZWARTY	PIĄTY	SZÓSTY	SIÓDMY
Wartość (rzadka)	Qwe		asd	afg		asd	Wer

Składnia indeksu według tabeli

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

W powyższej składni type_name jest zadeklarowane jako kolekcja indeksów po tabelach typu 'DATA_TYPE'. Typ danych może być prosty lub złożony. Zmienna subsciprt/index jest podana jako typ VARCHAR2 o maksymalnym rozmiarze 10.

Konstruktor i koncepcja inicjalizacji w kolekcjach

Konstruktory to wbudowane funkcje dostarczane przez Oracle, które mają taką samą nazwę jak obiekt lub kolekcja. Są one wykonywane jako pierwsze, gdy obiekt lub kolekcja są po raz pierwszy odwoływane w sesji. Poniżej znajdują się ważne szczegóły konstruktora w kontekście kolekcji:

W przypadku kolekcji te konstruktory należy wywołać jawnie, aby je zainicjować.
Zarówno tabele Varray, jak i Nested muszą zostać zainicjowane za pomocą tych konstruktorów, zanim zostaną odniesione do programu.
Konstruktor domyślnie rozszerza alokację pamięci dla kolekcji (z wyjątkiem Varray), dlatego konstruktor może również przypisywać zmienne do kolekcji.
Przypisywanie wartości do kolekcji za pomocą konstruktorów nigdy nie spowoduje, że kolekcja będzie rzadka.

Metody zbierania

Oracle zapewnia wiele funkcji do manipulowania i pracy ze zbiorami. Funkcje te są bardzo przydatne w programie do określania i modyfikowania różnych atrybutów zbiorów. Poniższa tabela zawiera różne funkcje i ich opis.

Metoda wykonania	Opis	SKŁADNIA
ISTNIEJE (n)	Ta metoda zwróci wyniki logiczne. Zwróci wartość „TRUE”, jeśli n^th element istnieje w tej kolekcji, w przeciwnym razie zwróci FAŁSZ. W niezainicjowanej kolekcji można używać wyłącznie funkcji EXISTS	.ISTNIEJE(pozycja_elementu)
COUNT	Podaje całkowitą liczbę elementów obecnych w kolekcji	.LICZYĆ
LIMIT	Zwraca maksymalny rozmiar kolekcji. W przypadku Varray zwróci stały rozmiar, który został zdefiniowany. W przypadku tabeli zagnieżdżonej i indeksu według tabeli daje wartość NULL	.LIMIT
PIERWSZY	Zwraca wartość pierwszej zmiennej indeksowej (indeks dolny) kolekcji	.PIERWSZY
LAST	Zwraca wartość ostatniej zmiennej indeksowej (indeks dolny) kolekcji	.OSTATNI
PRZED (n)	Zwraca poprzedza zmienną indeksową w kolekcji n^th element. Jeśli nie ma poprzedzającego indeksu, zwracana jest wartość NULL	.PRZED(n)
DALEJ (n)	Zwraca następującą po zmiennej indeksowej w kolekcji n^th element. Jeśli nie powiedzie się, zwracana jest wartość indeksu NULL	.DALEJ(n)
POSZERZAĆ	Na końcu rozszerza jeden element w kolekcji	.ROZSZERZYĆ
WYDŁUŻ (n)	Rozszerza n elementów na końcu kolekcji	.ROZDŁUŻ(n)
WYDŁUŻ (n,i)	Rozszerza n kopii i^th element na końcu kolekcji	.EXTEND(n,i)
TRIM	Usuwa jeden element z końca kolekcji	.PRZYCINAĆ
PRZYCINANIE (n)	Usuwa n elementów z końca kolekcji	.TRIM (n)
DELETE	Usuwa wszystkie elementy z kolekcji. Sprawia, że kolekcja jest pusta	.USUWAĆ
USUŃ (n)	Usuwa n-ty element z kolekcji. Jeśli następnie^th element ma wartość NULL, to nic nie da	.USUŃ(n)
USUŃ (m, n)	Usuwa element z zakresu m^th do n^th w kolekcji	.USUŃ(m,n)

Przykład 1: Typ rekordu na poziomie podprogramu

W tym przykładzie zobaczymy, jak zapełnić kolekcję za pomocą „ZBIÓRKA ZBIOROWA' i jak odnosić się do danych kolekcji.

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

Wyjaśnienie kodu:

Linia kodu 2-8: Typ rekordu „emp_det” deklaruje się z kolumnami emp_no, emp_name, pensja i menedżer o typie danych NUMBER, VARCHAR2, NUMBER, NUMBER.
Linia kodu 9: Tworzenie kolekcji „emp_det_tbl” elementu typu rekord „emp_det”
Linia kodu 10: Deklarowanie zmiennej „guru99_emp_rec” jako typu „emp_det_tbl” i inicjacja konstruktorem zerowym.
Linia kodu 12-15: Wstawianie przykładowych danych do tabeli „emp”.
Linia kodu 16: Zatwierdzanie transakcji wstawiania.
Linia kodu 17: Pobieranie rekordów z tabeli „emp” i zbiorcze wypełnianie zmiennej kolekcji za pomocą polecenia „BULK COLLECT”. Teraz zmienna „guru99_emp_rec” zawiera wszystkie rekordy obecne w tabeli „emp”.
Linia kodu 19-26: Ustawienie pętli „FOR” służącej do drukowania wszystkich rekordów w kolekcji jeden po drugim. Metoda zbierania FIRST i LAST stosowana jest jako dolna i górna granica pętla.

Wydajność:Jak widać na powyższym zrzucie ekranu, po wykonaniu powyższego kodu otrzymasz następujący wynik

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

Możesz lubić: