Nested Tables. Tabele zagnieżdżone

Ciekawym , aczkolwiek z mojej perspektywy mało wygodną opcją w Oracle są tabele zagnieżdżone. Przedstawię tutaj ideę na przykładzie faktur i produktów w nich zawartych , a sami najlepiej ocenicie czy taki byt Wam się przyda czy nie.
Generalnie działa to tak , że każdy wiersz w tabeli ma zagnieżdżoną inną tabelę. Tutaj posłużyłem się relacją faktura <==> produkty w fakturze. Zaraz się podniesie larum , że jakto tak zwielokratnianie danych. Tymczasem takie rozwiązanie zamiast intersekcji jest jak najbardziej sensowne. Pomyślcie, wystawiacie tysiąc faktur z tym samym produktem. Rok później zmienia się cena produktu. Czy wobec tego wszystkie stare faktury powinny mieć nową zaktualizowaną cenę produktu? Raczej nie... A tak stałoby się gdybyśmy posłużyli się np. intersekcją. Zamiast tego stosuje się (w najprostszym rozwiązaniu) kopię danych produktu, a następnie referencję do tej kopii zapisuje się do intersekcji łączącej faktury z produktami. W tym przypadku założyłem że dla każdego wiersza w tabelce faktury będzie przypisana zagnieżdżona tabelka zawierająca listę produktów.

Podążaj proszę za przykładami, a na koniec artykułu wszystko stanie się jasne. Aby korzystać z tabel zagnieżdżonych w innych tabelach musimy najpierw stworzyć typ obiektowy:




Create Type Linia_W_Fakturze Is Object(
Nr Number,
Nazwa Varchar2(400),
Pkwiu Varchar2(10),
Cena_Netto Number
);


Ten typ jest mi potrzebny jako format przechowywania linii w fakturze - tj. pojedynczego produktu w fakturze. Następnie tworzę inny typ, który będzie formatem do przechowywania listy obiektów których typ stworzyłem powyżej. Z polskiego na nasze - to jest typ który określa format przechowywania listy produktów w fakturze (czyli tej zagnieżdżonej tabelki) :

Create Type Produkty_W_Fakturze Is Table Of Linia_W_Fakturze;

Kiedy już mam niezbędne typy danych, przystępuję do tworzenia samej tabeli:

Create Table Faktury (
Nr_Faktury Varchar2(20) Primary Key,
Data_Wystawienia Date,
Termin_Platnosci Date,
Data_Sprzedazy Date,
Produkty Produkty_W_Fakturze
) nested table produkty store as prod_w_fak;

Produkty to nazwa kolumny pod którą będzie kryć się nasza zagnieżdżona tabelka. Produkty_w_fakturze to nazwa typu danych tej kolumny. To jak np. cena number czyli nazwa_kolumny typ_danych.
Ta ostatnia linia określa sposób przechowywania danych związanych z tą zagnieżdżoną tabelą. Interesuje nas fragment "store as prod_w_fak". Na potrzeby przechowywania wierszy związanych z tabelkami zagnieżdżonymi tworzona jest dodatkowa tabela o nazwie "prod_w_fak".


Wrzucamy teraz dane do takiej tabelki:


Insert Into Faktury Values
('01/12/2011',Sysdate,Sysdate,Sysdate,
Produkty_W_Fakturze(      Linia_W_Fakturze(1,'lodówka','365635',1000)     )
);



Wszystko co nie dotyczy tabeli zagnieżdżonej jest całkiem zwyczajne. Nas jednak interesuje ta linijka:

Produkty_W_Fakturze(      Linia_W_Fakturze(1,'lodówka','365635',1000)     )

Zaznajomionym z programowaniem obiektowym zapewne w pierwszym skojarzeniu przyjdzie do głowy pojęcie konstruktora. I nie pomylą się. To jest właśnie wykorzystanie konstruktorów w typach obiektowych w Oracle. Przyjrzyjmy się bliżej:


Produkty_W_Fakturze(      blablablabla     )

To jest konstruktor listy. Zawsze możemy zamiast tego podać null. Wtedy dana faktura nie będzie miała związanych z nią produktów. Aby wstawić jakąkolwiek listę , musimy ją wpierw stworzyć. To jest właśnie ten moment. Teraz taką listę wypełniamy :


blablabla(      Linia_W_Fakturze(blablabla)     ) 

Ponieważ są to typy obiektowe, każdy element listy również trzeba stworzyć przy użyciu konstruktora. Podczas konstruowania takich obiektów jednocześnie zapełniamy je wartościami:

Linia_W_Fakturze(1,'lodówka','365635',1000)   

. Spójrz jeszcze raz na całość:


Insert Into Faktury Values
('01/12/2011',Sysdate,Sysdate,Sysdate,
Produkty_W_Fakturze(      Linia_W_Fakturze(1,'lodówka','365635',1000)     )
);

Stało się to już wyraźniejsze? Możemy też dodać więcej niż jedną linię do takiej zagnieżdżonej tabeli:


Insert Into Faktury Values
('02/12/2011',Sysdate,Sysdate,Sysdate,
  Produkty_W_Fakturze(   
        Linia_W_Fakturze(3,'pralka','3545635',1500),
        Linia_W_Fakturze(3,'wentyl od jelcza','4342',12)
        )
);




Jak korzystać z takich tabel? Porównaj wyniki tych 3 zapytań:



Select * From Faktury;
Select * From Faktury , Table(Produkty);
select * from faktury f, table(Produkty) p where p.nazwa like '%lodówka%';


 Taki byt jak tabele zagnieżdżone możemy również wykorzystywać w PL/SQL:





Objaśnienie (wg. nr. linii):

2: stworzenie pustej tablicy.
4-7: Zainicjalizowanie tablicy przy użyciu konstruktora, jednocześnie ładując do niej pierwsze dwa elementy (na takich zasadach jak przy insercie).
8: Wypisuję na outpucie nazwę drugiego produktu z listy. W tym przypadku będzie to "dętka od kamaza".
9: Nie mógłbym się odwołać do 3 elementu tej tablicy, skoro zawiera tylko dwa elementy (czyli inaczej niż w tablicach indeksowanych po binary_integer czy pls_integer). Z tego powodu rozszerzam ją o 4 pozycje.
10: Trzecią na razie pustą pozycję wypełniam nowym obiektem stworzonym przy użyciu konstruktora.