Kolekcje

Kolekcja jest uporządkowaną grupą elementów tego samego typu. Rozróżniamy trzy zasadnicze typy kolekcji: VARRAY, tabela zagnieżdżona i tabela INDEX BY.
Kolekcja typu VARRAY
Kolekcja typu VARRAY jest stosowana raczej do kolekcji mniejszych rozmiarów. Przy tego typu kolekcji przy definicji kolekcji wymagane jest podanie maksymalnej jej wielkości. Kolekcje te są zawsze gęste, tzn., ze operacje usuwania elementów nie powoduje powstawania luk w kolekcji.
Składnia definicji:
TYPE nazwa IS {VARRAY | VARYING ARRAY} (rozmiar) OF typ elementu [NOT NULL]
Tabela zagnieżdżona
Kolekcje te są początkowo gęste, lecz podczas usuwania elementów z kolekcji stają sie one rzadkie. W przypadku kolekcji typu TABLE nie podajemy jej rozmiaru. Poniższe typy danych nie być elementami tabel zagnieżdżonych: BINARY_INTEGER, pls_integer, boolean, blob, clob, (kolekcje typu VARRAY), long, long raws, natural, naturaln, nchar, nclob, nvarchar2, obiekty z elementami typu BLOB lub CLOB, positive, positiven, ref coursor, signtype, string, table, varray.
Składnia definicji:
TYPE nazwa IS TABLE OF typ elementu [NOT NULL]
Kolekcja typu INDEX BY
Kolekcje te są uporządkowanymi elementami dostępnymi jedynie w języku PL/SQL, a nie w bazie danych. Są one początkowo rzadkie. Kolekcja ta nie ma ograniczeń co do typu możliwych elementów, jak to ma miejsce w przypadku tabel zagnieżdżonych.
Składnia definicji:
TYPE nazwa IS TABLE OF typ elementu [NOT NULL] INDEX BY BINARY_INTEGER
Przykłady deklaracji kolekcji

type dates is VARRAY(366) OF DATE;
type list is table of mytab.col2%TYPE;
type v1 is VARRAY of coursor1%ROWTYPE;
type entry list is VARRAY OF somerecord;

Inicjowanie kolekcji
Inicjowanie kolekcji odbywa się poprzez konstruktor systemowy o nazwie odpowiadającej nazwie typy kolekcji. W konstruktorze tym jako parametry możemy podać początkowe elementy kolekcji
Przykład

type Names is VARRAY(60) OF VARCHAR2(30);
namelist1 Names;
namelist2 Names;
namelist1 := Names("Marcin","Zygmunt");
namelist2 := Names();

W powyższym przykładzie zdefiniowaliśmy typ kolekcji Names, następnie zadeklarowaliśmy kolekcje namelist1 oraz namelist2 typu Names. Zmienną namelist1 zainicjowaliśmy dwoma elementami, zmienna namelist2 natomiast zainicjowaliśmy pustym konstruktorem bezparametrowym
Odwoływanie się do elementów kolekcji
Odwołując się do elementów kolekcji używamy nazwy kolekcji wraz z podanym w nawiasach indeksem elementu w kolejce. Indeks ten zawiera się w przedziale od -2 do potęgi 31 do 2 do potęgi 31 dla tabel INDEX BY, od 1 do 2 do potęgi 31 dla tabel zagnieżdżonych oraz od 1 do wartości określonej w deklaracji dla kolekcji typu VARRAY.
Przykład

IF names(1)='Marcin' THEN
names(1)='Marcin K'
END IF;

Porównywanie kolekcji
W związku z tym, że tabele zagnieżdżone i kolekcje typu VARRAY mogą być automatycznie puste kolekcje te możemy tylko przyrównywać do wartości null określającej, że kolekcja jest pusta jak to ma miejsce w poniższym przykładzie.
IF members IS NULL THEN ...
Niestety kolekcje nie mogą być porównywane między sobą. Poniższa sentencja wywoła błąd.
IF coll1=coll2 THEN ...
Przykład wykorzystywania tabel zagnieżdżonych

create type Worker as Object (
name VARCHAR2(30),
firstname VARCHAR2(30));
create type WorkerList as table of Worker;
create table firms(
name VARCHAR2(30),
address VARCHAR2(60),
Workers WorkerList)
NESTED TABLE Workers STORE AS Workers_tab;

Przykład dodania rekordu do tabeli zawierającej kolekcje

insert into firms values ('Nazwa','Adres',
WorkerList(Worker('Jan','Kowalski'),
Worker('Edward','Nowak')));

W ten sposób dodaliśmy do bazy danych nowy rekord z zagnieżdżoną tabelą WorkerList zawierającą dwa wpisy.
Przykład aktualizacji rekordu

declare
new_Workers WorkerList:=WorkerList(
Worker('Jan','Kowalski'),
Worker('Jerzy', 'Kowal'),
Worker('Edward','Nowak'));
begin
update firms set workers=new_Workers where name='Nazwa';
end;

W ten sposób w rekordzie zmieniliśmy całą tabele zagnieżdżoną.
Przypisywanie tabeli zagnieżdżonej do zmiennej

declare
Workers1 WorkerList
begin
select Workers into Workers1 from firms where name='Nazwa';
...
end;

Funkcje TABLE w zapytaniach SQL
Funkcja ta przypisuje kolekcje do tabeli bazy danych (na kolekcjach możemy operować jak na zwykłych tabelach). Przykład

Insert into TABLE(select Workers from firms where name='Nazwa' values
('Jan','Kowalski');

Powyższe zapytanie dodaje rekord do listy pracowników firmy o podanej nazwie

update TABLE(select Workers from firms where name='Nazwa') set name='Bocian'
where name='?aba' and first_name='Eliza';

Zmienia nazwisko pani ?aby na Bocian

Uogólniając składnia zwracającą rekordy z tabeli zagnieżdżonej ma postać: TABLE(zapytanie SQL) , gdzie:
zapytanie SQL : zapytanie SQL określające o jaką tabelę zagnieżdżoną nam chodzi i jakiej tabeli 'standardowej' jest ona elementem.
Dodatkowo przez klauzulę where możemy zawęzić listę rekordów z tabeli zagnieżdĽonej

select * from TABLE(select tabela_zagnieżdżona from tabela_rodzic where kolumna_tabeli_zagnieżdzonej=jakaś_wartość);

Powyższy przykład wyświetla wszystkie rekordy z tabeli zagnieżdżonej, zawężając jednocześnie ilość popranych rekordów poprzez podany warunek.

Standardowe metody kolekcji

Metody te pozwalają w łatwy sposób operować na kolekcji. Wywołąnie następuje poprzez notacje kropkową: kolejka.metoda[(argument)]
Metody kolekcji nie mogą być wywoływane w zapytaniach SQL. Metody EXTEND i TRIM nie mogą być używane z tabelami typu INDEX BY. Metody EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR i NEXT są funkcjami, EXTEND, TRIM i DELETE są procedurami. Metody EXISTS, PRIOR, NEXT, EXTEND, TRIM i DELETE wywoływane są z argumentami.
W przypadku pustej kolekcji możemy wywołać tylko metodę EXISTS, w innym przypadku zostanie wygenerowany wyjątek COLLECTION_IS_NULL.

nl : COUNT
Dla kolekcji typu VARRAY zwraca tą samą wartość co funkcja LAST. W przypadku tabel zagnieżdżonych COUNT jest równoważne LAST tylko przed usunięciem jakiegokolwiek rekordu z kolekcji.
nl : DELETE
nl : DELETE(i)
nl : DELETE(i,j)
nl : EXISTS(i)
IF nestedtab.EXISTS(2) then ...
W przypadku podania jako argumentu wartości będącej poza dozwolonym przedziałem zostanie wygenerowany wyjątek SUBSCRIPT_OUTSIDE_LIMIT.
nl : EXTEND
nl : EXTEND[i]
nl : EXTEND(i,j)
nl : FIRST
nl : LAST
nl : LIMIT
ograniczeniem ilości rekordów, więc funkcja ta zwraca wartość NULL. Dla kolekcji typu VARRAY funkcja zwraca maksymalną ilość rekordów, jaką może pomieścić ta kolekcja. Wartość tą podaje się zawsze w deklaracji kolekcji typu VARRAY.
nl : PRIOR(i)
nl : NEXT(i)
Przykład przejścia po wszystkich elementach kolekcji

...
i:=collection.FIRST
while i IS NOT NULL LOOP
...
i:=collection.NEXT(i);
END LOOP

nl : TRIM
nl : TRIM(i)
Jeżeli n jest większe od wartości zwróconej przez metodę COUNT system wygeneruje wyjątek SUBSCRIPT_BEYOND_COUNT. Osobiście nie polecam korzystać z tej metody. Preferuje metodę DELETE, gdzie mam większą kontrolę przy usuwaniu elementów.

Wyjątki związane z kolekcją

COLLECTION_IS_NULL : Próba wykonania operacji na pustej kolekcji
NO_DATA_FOUND : Próba wykonania operacji na elemencie kolekcji, który nie istnieje
SUBSCRIPT_BEYOND_COUNT : Próba wykonania operacji na elemencie, którego indeks przewyższa liczbę elementów w kolekcji
SUBSCRIPT_OUTSIDE_LIMIT : Próba wykonania operacji na elemencie, którego indeks jest poza dozwolonym przez system Oracle zakresem (np. indeks o wartości -1)
VALUE_ERROR : Próba wykonania operacji na elemencie o indeksie nie będącym liczbą (np. wartość NULL)

Wiązania masowe

Jest to mechanizm stosowany przy kolekcjach pozwalający w zdecydowanym stopniu ograniczyć komunikację pomiędzy PL/SQL, a silnikiem SQL. WyobraĽmy sobie sytuację, kiedy musimy duży blok danych dodać lub pobrać z tabeli. Najprostrzy sposób dodania takiego bloku, to zastosowanie pętli FOR i instrukcji SQL - INSERT. W takim przypadku jednak przy każdym przejściu pętli następuje odwołanie do silnika bazy danych, co w znacznym stopniu wydłuża czas wykonania pętli.
Mechanizm wiązań masowych pozwala nam na użycie takiej pętli, przy której następuje tylko jedno połączenie z silnikiem bazy danych.
Do jednorazowego wykonania zapytań SQL z wykorzystaniem kolekcji używamy pętli FOR ALL
Przykład

declare
type NamesList IS VARRAY(20) OF NUMBER;
Names NamesList:= NamesList('Kowal','Nowak','Bocian');
Begin
...
FORALL i IN Names.FIRST..Names.LAST
INSERT INTO Namestab VALUES(Names(i));
END;

W powyższym przykładzie pętla FOR ALL wykona się 3 razy przy jednorazowym połączeniu z silnikiem SQL.
Pętla FORALL, a odwoływanie transakcji
W przypadku, kiedy w pętli FOR ALL jakaś instrukcja SQL wygeneruje błąd, który nie jest przechwytywany wszystkie instrukcje w transakcji są cofane. Jeśli natomiast błąd przechwycimy poprzednie instrukcje z pętli FORALL (te przed wygenerowanie wyjątku) nie są cofane. Dodatkowo następuje zatrzymanie pętli FORALL.
%BULK_ROWCOUNT
Jest to atrybut kursora SQL tworzony przez system podczas wykonywania zapytań SQL. Jest on wykorzystywany w pętli FORALL i określa indeks zapytania aktualnie wykonywanego w pętli. Wykorzystywany tylko i wyłącznie w zapytaniach typu UPDATE i DELETE. W przypadku zapytań typu INSERT wartość ta zawsze wynosi 1.
Ograniczenia pętli FORALL
Pętli FORALL można używać tylko w programach server-side (nie client-side). W zapytaniach INSERT, UPDATE, DELETE musi nastąpić przynajmniej jedno odwołanie do kolekcji. Wszystkie elementy w kolekcji muszą istnieć. Indeks kolekcji w zapytaniu nie może być wyrażeniem. W zapytaniach SQL i kolekcjach złożonych typów nie możemy się odwoływać do kolekcji, tylko bezpośrednio do danego atrybutu elementu kolekcji.

{%1}

FORALL I IN Workers.FIRST..WORKERS.LAST
INSERT INTO WorkersTab VALUES(Workers(I).name, Workers(I).firstname)
END;

Atrybut %BULK_ROWCOUNT nie może być przypisany do innych kolekcji. Nie może on być również podawany jako parametr wywoływanych podprogramów.
Klauzula BULK_COLLECT
Klauzula BULK_COLLECT jest odpowiednikiem pętli FORALL działającym w odwrotnym kierunku. Pozwala ona na pobranie danych do kolekcji z silnika za pomocą jednej transakcji pomiędzy silnikiem, a blokiem PL/SQL.
Przykład 1

declare
type Collecttab1 IS TABLE OF tab.column1%TYPE;
type Collecttab2 IS TABLE OF tab.column2%TYPE;
Collect1 Collecttab1
Collect2 Collecttab2
begin
select column1, column2 BULK COLLECT INTO Collect1, Collect2 from tab;
...
END;

Przykład 2

CREATE TYPE POINT AS OBJECT (x NUMBER, y NUMBER);
CREATE TABLE grids (num NUMBER, pointer POINT);
INSERT INTO grids VALUES (1, POINT(0,0));
INSERT INTO grids VALUES (2, POINT(20,60));
declare
type Points IS TABLE OF POINT;
points Pointstab;
begin
select pointer BULK COLLECT INTO points from grids;
...
END;

W powyższym przykładzie następuje zapisanie z tabeli grids do kolekcji points par liczb (0,0) oraz (20,60)
Przykład 3

declare
type NameList IS TABLE OF tab.column1%TYPE;
coursor c1 IS select  name from tab;
names NameList
...
begin
OPEN c1;
FETCH c1 BULK COLLECT INTO names;
...
END;

Klauzula LIMIT
Klauzula ta jest stosowana w wyrażeniach FETCH z opcją BULK COLLECT i pozwala ona ograniczyć liczbę rekordów pobranych z bazy. Jej składnia wygląda następująco:
FETCH ... BULK COLLECT INTO ... LIMIT liczba_rekordów
Liczba rekordów może być stała, zmienna, lub wyrażeniem.
Zwracanie BULK COLLECT
Sentencja BULK COLLECT może być również używana w klauzuli RETURNING INTO zapytań typu INSERT, UPDATE lub DELETE.
Przykład

declare
type NumList IS TABLE OF tab.col2%TYPE;
nums NumList;
begin
delete from tab1 where col1=10 returning col2 BULK COLLECT INTO nums;
...
END;

Powyższy przykład poza usunięciem konkretnych rekordów spowoduje zapisanie do kolekcji nums wartości kolumny col2 z tabeli tab1, gdzie kolumna col1 ma wartość 10.
Ograniczenia klauzuli BULK COLLECT
Klauzuli te można używać tylko w programach server-side (nie client-side). Zmienne zapisywane przez tę klauzulę muszą być kolekcją. Nie można używać sentencji FETCH w stosunku do kursora z klauzulą BULK COLLECT i kolekcją rekordów.
Przykład

declare
type RecsTab IS TABLE OF tab.col1%ROWTYPE;
Recs RecsTab;
coursor c1 is select col1 from tab1;
begin
OPEN c1;
FETCH c1 BULK COLLECT INTO Recs; -- niepoprawne
...
END;

Klauzula RETURNING INTO nie może być używana ze złożonymi danymi (np. obiektami). Połączenie  klauzuli FORALL i BULK COLLECT razem
Przykład

...
FORALL i IN depts.FIRST..depts.LAST
delete from emp where empno=depts(i) RETURNING empno BULK COLLECT INTO enums;
...

Nie można używać sentencji wyrażenia select ... BULK COLLECT w pętli FORALL.

Powrót