Kolekcje

Autor: Marcin Kasiński
21.01.2011 13:24:00 +0200

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.

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łądy 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 2³¹ dla tabel INDEX BY, od 1 do 2³¹ 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:

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.

Poniżej znajduje sie przykład przejścia po wszystkich elementach kolekcji

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

Wyjątki związane z kolekcją

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

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

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

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;<font color=red>-- niepoprawne
...
END;

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

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

 powrót

Zachęcam do przedstawienia swoich uwag i opinii w polu komentarzy.

Komentarze

Dodaj Komentarz

Temat:

Treść

Podpis / Twój adres email: