Opcja obiektowa w SQL

Od dłuższego czasu trwają prace nad nowymi kocepcjami w bazach danych polegającymi na zastąpieniu modelu relacyjnego przez obiektowy. Jeden z kierunków rozwoju polega na dołączaniu cech obiektowych do istniejącego modelu relacyjnego. Otrzymujemy w ten sposób model obiektowo-relacyjny i faktycznie na takim modelu jest w tej chwili oparty Standard języka SQL:1999. Wykład jest podzielony na dwie części. W pierwszej części przedstawiamy informacje o pojęciach obiektowych występujących w Standardzie SQL:1999. W drugiej części pokazujemy użycie opcji obiektowej w Oracle.

Najpierw przypomnimy ogólne znaczenie typów obiektowych. Realizują one zasadę abstrakcji w dwóch postaciach:

• abstrakcji proceduralnej polegającej na ukryciu szczegółów złożonych algorytmów poprzez opakowanie ich w procedury i funkcje; gdy w razie potrzeby zmieniamy procedurę - nie musimy zmieniać aplikacji jej używających;
• abstrakcji danych polegającej na ukryciu złożoności struktury danych przed użytkownikiem, który korzysta z tych danych; podobnie jak poprzednio, gdy w razie potrzeby zmieniamy strukturę danych – nie musimy zmieniać aplikacji jej używających.

Oto znane powszechnie zalety użycia typów obiektowych:

• Ułatwione modelowanie rzeczywistych obiektów gospodarczych.
• Zmniejszenie złożoności tworzenia aplikacji przez podział zadania na części. Modularność i wielokrotne użycie części.
• Przeniesienie używanego kodu po stronie serwera w jedno miejsce do metod typu obiektowego. Uzyskanie większej kontroli nad kodem. Ułatwienie dokonywania zmian. Ukrycie szczegółów implementacyjnych przed użytkownikiem.

Opcja obiektowa w SQL:1999

W SQL:1999 wprowadzono dwa nowe złożone typy danych: ARRAY i ROW. Typ ARRAY umożliwia przechowywanie kolekcji wartości bezpośrednio w kolumnie bazy danych. Na przykład typ

NumeryTel VARCHAR(12) ARRAY[7]

umożliwia przechowywanie kolekcji numerów telefonów danej osoby w jednym wierszu w bazie danych.

Typ ROW z kolei jest rozszerzeniem typu wierszowego używanego już wcześniej w Standardzie przy definiowaniu semantyki operacji na bazie danych. Umożliwia to przechowywanie strukturalnych wartości w pojedynczych kolumnach bazy danych. W tabeli

CREATE TABLE Pracownicy(
  Prac_id INTEGER,
  PełneNazwisko ROW (Imię     VARCHAR(30),
                     Nazwisko VARCHAR(30)),
  Adres         ROW (Ulica    VARCHAR(30),
                     Numer    VARCHAR( 8)),
  Salary REAL
);

występują dwie kolumny typu ROW – wskazane pogrubioną czcionką. Dostęp do pojedynczych danych odbywa się przy użyciu dostępu kropkowego, jak w poniższej instrukcji

SELECT P.PełneNazwisko.Nazwisko
  FROM Pracownik P;

Podstawową konstrukcją w SQL:1999 wspomagającą obiektowość jest typ strukturalny definiowany przez użytkownika. Oto podstawowe cechy typów strukturalnych:

• Mogą mieć jeden lub więcej atrybutów, każdy z nich albo typu prostego jak INTEGER, albo typu kolekcji (ARRAY lub ROW) albo innego typu strukturalnego (dowolnie zagnieżdżonego).
• Wszystkie aspekty zachowania się egzemplarzy typów strukturalnych są określone przez metody, funkcje i procedury.
• Atrybuty są chronione (opakowane hermetycznie) poprzez użycie generowanych przez system funkcji typu observer i mutator („get” i „set”), które dostarczają wyłącznego do nich dostępu. Te generowane przez system funkcje nie mogą być przeciążane. Wszystkie inne funkcje i metody mogą być przeciążane.
• Porównania wartości są dokonywane tylko poprzez funkcje zdefiniowane przez użytkownika.
• Mogą wchodzić w skład hierarchii typów, w których bardziej wyspecjalizowane typy (podtypy) mają wszystkie atrybuty i podprogramy pochodzące od bardziej ogólnych typów (nadtypów), ale mogą mieć dodatkowe atrybuty i podprogramy.

Oto przykład definicji typu strukturalnego:

CREATE TYPE Typ_pracownika UNDER Typ_osoby AS (
  Id_prac INTEGER,
  Zarobki REAL
)
  INSTANTIABLE
  NOT FINAL
  REF (Id_prac)
  INSTANCE METHOD Podwyżka (Amount REAL) RETURNS REAL;

Ten nowy typ jest podtypem innego typu strukturalnego Typ_osoby, który w zamierzeniu opisuje osoby w ogólności obejmując takie atrybuty, jak Nazwisko czy Adres. Typ_pracownika obejmuje specyficzne atrybuty pracownika, takie jak identyfikator i zarobki. Zadeklarowaliśmy, że dla danego typu można tworzyć egzemplarze (INSTANTIABLE) oraz podtypy (NOT FINAL). Ponadto, wszystkie odwołania do tego typu są wyprowadzane z wartości identyfikatora pracownika. Zdefiniowaliśmy także metodę Podwyżka, którą można stosować do egzemplarzy tego typu.

Typ zdefiniowany przez użytkownika, w którym występuje metoda, jest typem danych wyróżnionego argumentu metody - pierwszego, niezadeklarowanego argumentu. Treści metod są zapisywane przy użyciu specjalnego języka wprowadzonego przez Standard, o nazwie SQL/PSM (odpowiadającego językowi PL/SQL w Oracle) albo w jednym z kilku tradycyjnych języków programowania takich jak Java.

egzemplarze typów strukturalnych mają charakter wartości bez tożsamości obiektowej tak, jak egzemplarze typów standardowych takich, jak INTEGER, REAL. Swoją tożsamość obiektową uzyskują dopiero w chwili wpisania do tabeli. Tabela może zostać utworzona w oparciu o typ strukturalny z definicjami kolumn wyprowadzanymi z atrybutów typu strukturalnego – po jednej kolumnie dla każdego atrybutu typu.

CREATE TABLE Empls OF Employee;

Funkcje, metody i procedury zdefiniowane na egzemplarzach typu działają na wierszach takiej tabeli. Wiersze tabeli są więc wartościami lub egzemplarzami tego typu. Każdy wiersz uzyskuje jednoznaczną tożsamość mającą podobne właściwości jak OID (identyfikator obiektowy). SQL:1999 wprowadza specjalny typ nazywany typem referencyjnym (REF), którego wartościami są jednoznaczne identyfikatory wierszy tabeli. Dany typ REF jest zawsze związany z pewnym określonym typem strukturalnym. Oto przykład definicji kolumny Kierownik typu referencyjnego:

Kierownik REF(Typ_pracownika)

Wartość typu REF albo identyfikuje wiersz tabeli określonego typu strukturalnego albo nie identyfikuje niczego będąc pozostałością po usuniętym identyfikatorze wiersza.

Wszystkie typy REF muszą mieć z góry określony zakres w tym sensie, że tabela, do której wiersze się odwołują, musi być znana w chwili deklarowania kolumny lub zmiennej typu referencyjnego. Określenie typu REF odpowiada zdefiniowaniu więzów spójności referencyjnej – ułatwiając ich implementację w bazie danych.

SQL:1999 wprowadza dla referencji operator „przejścia po referencji” służący do uzyskiwania atrybutów wartości typu strukturalnego. Na przykład jego użycie w klauzuli

SELECT Prac.Kierownik -> PełneNazwisko.Nazwisko

umożliwia w oparciu o referencję do wiersza kierownika danego pracownika uzyskać wartość typu wierszowego PełneNazwisko, a z niej za pomocą zwykłego dostępu kropkowego już samo nazwisko kierownika.

Opcja obiektowa w Oracle

W opcji obiektowej Oracle są dwa nowe rodzaje typów danych definiowanych przez użytkownika:

• typy kolekcji:
  • VARRAY - który odpowiada typowi ARRAY w Standardzie i
  • TABLE - który częściowo odpowiada typowi ARRAY a częściowo wychodzi poza Standard, ponieważ umożliwia definiowanie tabeli zagnieżdżonej, której nie ma w Standardzie,
• typy obiektowe: OBJECT -  odpowiadają typowi ROW w Standardzie.

W Oracle występuje niejednorodność podejścia: sposób posługiwania się powyższymi typami częściowo zależy od tego, czy używamy ich w instrukcji SQL, czy poza instrukcją SQL - w ramach języka PL/SQL!

Tak jak w Standardzie w Oracle występują dwa typy tabel:

• zwykłe relacyjne, których atrybuty mogą być typów złożonych, jak kolekcje czy obiekty - wiersze tabeli nie mają tożsamości obiektowej,
• obiektowe, które są definiowane poprzez typ obiektowy - wiersze tabeli mają wówczas tożsamość obiektową.

Kolekcje

Kolekcja jest uporządkowanym ciągiem elementów tego samego typu. Każdy element kolekcji ma jednoznacznie określony wskaźnik określający jego położenie w kolekcji.

Są dwa rodzaje kolekcji:

• tabele zagnieżdżone  - wartości typu TABLE,
• tablice zmiennej długości - wartości typ VARRAY.

Kolekcje mają tylko jeden wymiar i są indeksowane liczbami całkowitymi. Kolekcje można tworzyć zarówno

• w PL/SQL - na czas wykonania procedury lub sesji, jak i
• na trwale w bazie danych - tworząc odpowiedni typ kolekcji, a następnie używając go jako typu danych kolumny (w języku SQL).

Elementami kolekcji mogą być wartości typów obiektowych i na odwrót, kolekcje mogą być atrybutami wartości typów obiektowych. Kolekcje mogą być przekazywane poprzez parametry procedur i funkcji.

Tabele zagnieżdżone to pojęcie nie występujące w Standardzie. Oto jego wyjaśnienie.

• W PL/SQL występują podobne do tabel zagnieżdżonych tzw. tabele indeksowane ograniczone w użyciu do języka PL/SQL - definiowane przy użyciu klauzuli INDEX BY BINARY_INTEGER
• W bazie danych tabele zagnieżdżone można uważać za jednokolumnowe tabele bazy danych - bez porządku elementów.
• Natomiast po podstawieniu na zmienną PL/SQL elementy mają swoją numerację począwszy od 1. W PL/SQL z zagnieżdżonej tabeli można usuwać elementy, co powoduje, że powstają "dziury". Metoda NEXT określona na wskaźnikach istniejących pozycji tabeli umożliwia przejście przez wszystkie elementy tabeli zagnieżdżonej.
• Tabele zagnieżdżone wymagają jawnego rozszerzania za pomocą metody EXTEND, można je przycinać za pomocą metody TRIM.
• Nie zainicjowana tabela zagnieżdżona jest NULL, tj. nie ma żadnych elementów.
• Tabela zagnieżdżona ma charakter rzadki - nie wszystkie pozycje muszą być zajęte; nie ma ograniczenia na liczbę wierszy. Tabela zagnieżdżona jest przechowywana w bazie danych w specjalnej tabeli bazy danych generowanej przez system bazy danych.
• Tablica VARRAY ma określony maksymalny rozmiar specyfikowany przy jej definiowaniu. Jej wskaźniki przebiegają od 1 do aktualnego jej rozmiaru (<= maksymalnego rozmiaru) - bez żadnych "dziur", tj. nie można usunąć pojedynczego jej elementu. W przeciwieństwie do tabel zagnieżdżonych tablice VARRAY są przechowywane tak jak inne wartości w wierszu - z zachowaniem wskaźników i uporządkowania.

Typ kolekcji

Przy tworzeniu typu kolekcji w SQL używamy słowa kluczowego CREATE, w PL/SQL nie. Może być używana opcja NOT NULL wymagająca, aby wartością elementu kolekcji nie była pseudo-wartość NULL. Oto składnia tworzenia tabeli zagnieżdżonej i następnie tablicy VARRAY:

CREATE [OR REPLACE] TYPE Typ IS TABLE OF Typ_elementu [NOT NULL]; 
CREATE [OR REPLACE] TYPE Typ IS VARRAY(maks) OF Typ_elementu [NOT NULL];

gdzie Typ_elementu nie może być BOOLEAN, NCHAR, NCLOB, NVARCHAR2, ani typem obiektowym zawierającym atrybut TABLE lub VARRAY, ani typem REF CURSOR, TABLE lub VARRAY.

Definicja typu obiektowego na razie bez uzwzględnienia metod wygląda następująco:

CREATE TYPE Typ AS OBJECT (atrybut typ_atrybutu, ...);

Nie można tworzyć typu obiektowego w PL/SQL – można natomiast go tam używać.

Oto przykłady zastosowania powyższych typów do zdefiniowania tabel z kolumnami przechowującymi wartości nowych typów danych. Oto definicja typu obiektowego o nazwie Projekt.

CREATE TYPE Projekt AS OBJECT (
  Num_proj NUMBER(3),
  Tytuł    VARCHAR2(35),
  Koszt    NUMBER(7,2)
);

Oto definicja typu kolekcji tablicy VARRAY projektów.

CREATE TYPE Lista AS VARRAY(3) OF Projekt;

Oto definicja tabeli, w której jeden z atrybutów o nazwie Projekty jest typu tablicowego Lista.

CREATE TABLE Wydziały (
  Id_wydz  NUMBER(2),
  Nazwa    VARCHAR2(15),
  Budżet   NUMBER(11,2),
  Projekty Lista
);

Do inicjacji służy funkcja konstruktora o tej samej nazwie co typ kolekcji lub typ obiektowy. A więc do tabeli Wydziały wstawiamy wartości atrybutów kolekcji w następujący sposób – należy pamiętać aby zgodnie ze specyfikacją tablica Projekty nie miała więcej niż trzy elementy (ale może mieć mniej).

INSERT INTO Wydziały VALUES(1,'Informatyka',100000,
                            Lista(Projekt(1,'Analiza',123),
                                  Projekt(2,'Projekt',456)));

Również w takiej postaci system wypisuje wartości kolekcji przy wykonywaniu instrukcji SELECT. Wynik instrukcji

SELECT Nazwa, Projekty FROM Wydziały;

jest wypisywany w postaci:

NAZWA       PROJEKTY(NUM_PROJ, TYTUŁ, KOSZT)
----------- --------------------------------------------------------
Informatyka LISTA(PROJEKT(1,'Analiza',123),PROJEKT(2,'Projekt',456))

Funkcja konstruktora kolekcji i obiektu jest dostępna zarówno w języku SQL, jak i PL/SQL. Konstruktory mogą być używane wszędzie tam, gdzie można używać funkcji, w tym w klauzulach SELECT, VALUES i SET.

Natomiast tylko w kodzie PL/SQL do poszczególnych elementów kolekcji można dostawać się używając składni:

Nazwa_kolekcji (wskaźnik)

Oto podstawowe właściwości kolekcji:

• Kolekcje mogą być testowane względem NULL przy użyciu operatorów IS NULL oraz IS NOT NULL. Nie mogą natomiast być porównywane między sobą za pomocą operatorów równości i nierówności. Za to obiekty mogą – o ile zdefiniuje się odpowiednie metody służące do wykonywania porównania.
• Można używać instrukcji przypisania: Kolekcja1 := Kolekcja2; o ile obie kolekcje są dokładnie tego samego typu.
• Wartością funkcji może być kolekcja.

Oto przykład z typem kolekcji tabeli zagnieżdżonej. Najpierw definiujemy typ obiektowy Wykł.

CREATE TYPE Wykł AS OBJECT(
  Id_wykładu  VARCHAR2(4),
  Tytuł       VARCHAR2(35),
  Liczba_godz NUMBER(1)
);

Następnie definiujemy typ tabelowy ListaWykładów.

CREATE TYPE Lista AS TABLE OF Wykł;

W końcu tworzymy tabelę, w której atrybut Wykłady jest typu tabelowego. Wartością tego atrubutu jest tabela zagnieżdżona. Oracle wymaga specyfikacji nazwy dodatkowej tabeli (poniżej Tab_wykładów), w której będą przechowywane wiersze wszystkich tabel zagnieżdżonych zapisywanych w danej kolumnie. Na tabeli tej można dokonywać pewnych operacji, jak zakładanie indeksów i wprowadzanie więzów spójności – będzie o tym mowa w drugiej części wykładu.

CREATE TABLE Wydziały (
  Nazwa    VARCHAR2(20),
  Dyrektor VARCHAR2(20),
  Wykłady  Lista
)
  NESTED TABLE Wykłady STORE AS Tab_wykładów;

Oto przykład wstawienia wiersza do takiej tabeli – w odróżnieniu od poprzednio rozważanej tablicy VARRAY liczba elementów wstawianych do tablicy zagnieżdżonej jest dowolna.

INSERT INTO Wydziały VALUES ('Informatyka', 'Jan Kowalski', 
                             Lista(Wykł(1000,'Analiza I', 4),                       
                                   Wykł(1001,'Geometria', 4)));

Wynik wypisania zawartości tabeli z tabelą zagnieżdżoną jest podobny jak w przypadku tablicy VARRAY. Mianowicie dla instrukcji

SELECT Nazwa, Wykłady FROM Wydziały;

otrzymujemy wynik:

NAZWA           WYKŁADY(ID_WYKŁADU, TYTUŁ, LICZBA_GODZ)
--------------- -----------------------------------------------------------------
Informatyka     LISTA(WYKŁ('1000', 'Analiza I', 4), WYKŁ('1001', 'Geometria', 4))

Wydobywanie wartości z tabeli zagnieżdżonej najłatwiej wykonywać w kodzie PL/SQL.

DECLARE
  wyk Lista;
BEGIN
  SELECT Wykłady INTO wyk FROM Wydziały
    WHERE Nazwa = 'Informatyka';

W podobny sposób operujemy tablicami VARRAY. Aby operować na poszczególnych wartościach będących tablicami VARRAY, trzeba użyć PL/SQL. A mianowicie trzeba przepisać kolekcję VARRAY na zmienną i użyć metod kolekcji.

Natomiast w SQL do operacji na tabelach zagnieżdżonych służy operator THE, którego argumentem jest jednoelementowa kolekcja, której jedynym elementem jest tabela zagnieżdżona; wartością operatora jest właśnie ta tabela zagnieżdżona.

Oto przykład wstawienia wiersza do tabeli zagnieżdżonej.

INSERT INTO THE(SELECT Wykłady 
                  FROM Wydziały
                  WHERE Nazwa = 'Informatyka')
       VALUES (3340, 'Superkomputery',4);

Oto przykład aktualizowania zawartośc tabeli zagnieżdżonej.

UPDATE THE(SELECT Wykłady 
             FROM Wydziały 
             WHERE Nazwa = 'Informatyka')
  SET Liczba_godz = Liczba_godz +2
  WHERE Id_wykładu IN (2200, 3340);

Oto przykład usuwania wierszy z tabeli zagnieżdżonej.

DELETE THE(SELECT Wykłady 
             FROM Wydziały 
             WHERE Nazwa = 'Informatyka')
  WHERE Liczba_godz = 1;

Oto przykład wydobywania danych z tabeli zagnieżdżonej.

SELECT Id_wykładu, Tytuł  
  FROM THE(SELECT Wykłady 
             FROM Wydziały 
             WHERE Nazwa = 'Informatyka')
  WHERE Id_wykładu = 3340;

W PL/SQL są dostępne metody kolekcji. Umożliwiają one wykonywanie operacji na elementach kolekcji. Oto schemat dostępu do wartości kolekcji:

DECLARE
  i BINARY_INTEGER;
BEGIN
  i := tabela.FIRST;     -- indeks pierwszego elementu w tabeli
  WHILE i IS NOT NULL LOOP
    -- Przetwórz wartość elementu  tabela(i)
    i := tabela.NEXT(i); -- indeks kolejnego elementu w tabeli
  END LOOP;
END;

Typ obiektowy

Oto definicja typu obiektowego z uwzględnieniem metod, czyli procedur i funkcji:

CREATE TYPE Typ AS OBJECT (
  atrybut typ_danych, ...
  MEMBER sygnatura procedury lub funkcji
  [,klauzula PRAGMA],
  ...
);

Tak jak w przypadku pakietów, kod procedur i funkcji wchodzących w skład typu obiektowego podaje się przy użyciu osobnej instrukcji określającej implementację typu obiektowego:

CREATE TYPE BODY Typ AS 
  MEMBER implementacja procedury lub funkcji;
  ...
END;

Następnie definiujemy tabelę obiektową zdefiniowanego powyżej typu obiektowego:

CREATE TABLE Tabela OF Typ [(więzy spójności)];

Zauważmy, że więzy spójności nie określa się w definicji typu obiektowego, tylko w definicji samej tabeli.

Oto prosty przykład typu obiektowego punktów z operacjami brania współrzędnych punktu i obliczania odległości między punktami:

CREATE OR REPLACE TYPE Punkt AS OBJECT (
  x  INTEGER,
  y  INTEGER,
  MEMBER FUNCTION WeźX RETURN INTEGER,
  MEMBER FUNCTION WeźY RETURN INTEGER,
  MEMBER FUNCTION Odl(w IN Punkt) RETURN NUMBER,
  PRAGMA RESTRICT_REFERENCES(DEFAULT, RNDS, WNDS, RNPS, WNPS)
);

Konstrukcja:

PRAGMA RESTRICT_REFERENCES 

stanowi deklarację  braku efektów ubocznych dla funkcji w pakietach oraz w typach obiektowych. Aby móc wykonać instrukcję SQL, która wywołuje metodę obiektu, Oracle musi znać poziom "czystości" funkcji tj. zakres, w jakim funkcja jest wolna od odczytów i zapisów do tabel bazy danych i do zmiennych w pakietach. Przypominamy składnię:

PRAGMA RESTRICT_REFERENCES ([DEFAULT | nazwa_metody] lista_opcji);

gdzie możliwe są następujące opcje:

• RNDS (nie odczytuje bazy danych);
• WNDS (nie zapisuje do bazy danych);
• RNPS (nie odczytuje stanu pakietów);
• WNPS (nie zapisuje stanu pakietów).

Następnie definiujemy implementację typu obiektowego Punkt.

CREATE OR REPLACE TYPE BODY Punkt AS 
  MEMBER FUNCTION WeźX RETURN INTEGER IS
  BEGIN
    RETURN x;
  END WeźX;

  MEMBER FUNCTION WeźY RETURN INTEGER IS
  BEGIN
    RETURN y;
  END WeźY;
  
  MEMBER FUNCTION Odl(w IN Punkt)RETURN NUMBER IS
  BEGIN
    RETURN SQRT((x-w.WezX())*(x-w.WezX())+
                (y-w.WezY())*(y-w.WezY())); 
  END Odl;
END;

Zauważmy, że jeden argument w metodach jest domyślny i reprezentowany przez bezpośrednie odwołanie się do atrybutów typu obiektowego, czyli x i y. Można też używać nazwy argumentu SELF typu IN OUT, na przykład kod definiowanych powyżej funkcji WeźX i WeźY można zapisać odpowiednio w następujący sposób:

RETURN SELF.x;
RETURN SELF.y;

Zdefiniujmy tabelę obiektową złożoną z obiektów typu Punkt.

CREATE TABLE Punkty OF Punkt;

Oto przykłady operacji na tabeli obiektowej Punkty.

INSERT INTO Punkty VALUES(1,2);        -- bez użycia konstruktora
INSERT INTO Punkty VALUES(Punkt(3,4)); -- użycie konstruktora

Wypisywanie wartości odbywa się w taki sam sposób, jak w przypadku zwykłej tabeli relacyjnej. Mianowicie odwołujemy się do atrybutów typu obiektowego Punkt tak jak do kolumn np.

SELECT x,y FROM Punkty;

otrzymujemy następujący wynik (bez konstruktora typu):

         X          Y
---------- ----------
         1          2
         3          4

Zamiast wypisywać bezpośrednio współrzędne punktów, można użyć specjalnego operatora VALUE, który zwraca wartość konstruktora typu obiektowego, na którym jest oparta tabela obiektowa. W instrukcji SELECT możemy teraz skorzystać z przygotowanych metod. Wynikiem wykonania instrukcji:

SELECT VALUE(w), w.Odl(Punkt(0,0)) AS "Odległość punktu od (0,0)"
  FROM Punkty w
  WHERE w.WeźX()+w.WeźY < 10

jest

VALUE(W)(X, Y)  Odległość punktu od (0,0)
--------------- ------------------------- 
PUNKT(1, 2)                    2.23606798
PUNKT(3, 4)                             5

A oto przykład z instrukcją UPDATE.

UPDATE Punkty w
  SET w.x = -w.x
  WHERE w.WeźX() < 0

Są dwa rodzaje obiektów w Oracle:

• obiekty wierszowe (elementy tabeli obiektowej) - mające swoją tożsamość obiektową,
• obiekty kolumnowe (elementy kolumny) - nie mające tożsamości obiektowej.

A więc obiekty wstawiane do kolumn w tabeli:

CREATE TABLE Pary(
  Pierwszy Wektor,
  Drugi    Wektor
);

nie mają tożsamości obiektowej.

Oto podstawowe zasady dotyczące użycia obiektów w Oracle.

• Typem danych atrybutu w typie obiektowym nie może być LONG, LONG RAW, NCHAR, NCLOB, NVARCHAR2, ROWID ani typy danych PL/SQL (jak BOOLEAN, BINARY_INTEGER, %TYPE, %ROWTYPE). Atrybut może być typu obiektowego.
• Pierwszym parametrem (zdefiniowanym explicite lub implicite)  metody jest zawsze SELF IN OUT. W metodach można opuszczać kwalifikator SELF przy atrybutach typu.
• Nazwy metod mogą być przeładowywane tak jak w przypadku procedur i funkcji w pakietach.
• Każdy typ obiektowy ma tworzoną przez system metodę konstruktora obiektu tego typu.
• Wartości typów obiektowych można przekazywać poprzez argumenty procedur i funkcji.
• Dla atrybutu przy definiowaniu typu obiektowego podaje się tylko nazwę jego typu danych (bez więzów spójności).
• Przy tworzeniu obiektu należy podać wartości wszystkich jego atrybutów.

Oracle tak jak Standard wprowadza typ referencyjny do obiektów, umożliwiający współdzielenie obiektów w bazie danych:

REF typ-obiektowy

Oto użycie typu referencyjnego jako typu atrybutu tabeli:

CREATE TYPE Typ_osoba AS OBJECT (
  Imię           VARCHAR2(20),
  Nazwisko       VARCHAR2(30),
  DzieńUrodzin   DATE,
  Matka          REF Typ_osoba,     -- członkowie rodziny odwołują się do siebie
  Ojciec         REF Typ_osoba,
  Współmałżonek  REF Typ_osoba, 
  Dom            REF Typ_mieszkania -- mieszkanie współdzielone przez rodzinę 
);

gdzie Typ_mieszkania jest osobnym typem obiektowym opisującym mieszkanie.

CREATE TABLE Osoby OF Typ_osoba (
  Dom           SCOPE IS Mieszkania,
  Matka         SCOPE IS Osoby,
  Ojciec        SCOPE IS Osoby,
  Współmałżonek SCOPE IS Osoby
);

W tabeli obiektowej Osoby typu Typ_osoba więzy spójności postaci

Dom [WITH ROWID] SCOPE IS Mieszkania 

ograniczają wartości w kolumnie Dom do referencji do obiektów w tabeli Mieszkania. Pozostałe więzy SCOPE ograniczają wartości do referencji do wierszy w tej samej tabeli Osoby. Przy zastosowaniu opcji WITH ROWID - razem z referencją do obiektu jest przechowywany identyfikator wiersza ROWID, co przyśpiesza operacje dereferencji. Jak widać, typy referencyjne stanowią alternatywę dla więzów spójności klucza obcego.

W PL/SQL nie można bezpośrednio nawigować przez referencje - trzeba użyć operatora DEREF, który daje w wyniku obiekt, na którego wskazuje referencja. W SQL można nawigować poprzez referencje np. jeśli x jest aliasem tabeli Osoby obiektowego typu Typ_osoby, a Kierownik jest atrybutem typu REF Typ_osoby - to instrukcja

SELECT x.Nazwisko,
       x.Kierownik.Nazwisko AS "Nazwisko kierownika"
  FROM Osoby x;

wypisze nazwiska wszystkich osób w zestawieniu z nazwiskami ich kierowników. Czyli nie ma różnicy notacyjnej przy przejściu przez typ referencyjny w stosunku do typu kolekcji i typu obiektowego.

W Oracle mamy możliwość zastosowania deklaracji zapowiadającej określającej niepełny obiektowy typ danych, która umożliwia rekurencyjne odwoływanie się atrybutów w różnych typach obiektowych. Oto przykład:

CREATE TYPE Wydział; -- deklaracja zapowiadająca definicję typu

CREATE TYPE Pracownik AS OBJECT (
  ...

  Miejsce_pracy REF Wydział
);

CREATE TYPE Wydział AS OBJECT (
  ...
  Kierownik REF Pracownik,
  ...
);

Wspomnieliśmy już o dwóch funkcjach standardowych związanych z obiektami: VALUE oraz THE. Oprócz tego istotne są:

• REF(zmienna korelacyjna) - referencja do obiektu (OID)

  SELECT REF(p) INTO p_ref   
    FROM Osoby p
    WHERE p.Nazwisko = 'Kowalski';
  
  UPDATE Osoby p 
    SET p = Osoba('Jan','Kowal',...)  -- lub p.Nazwisko= 'Kowal'
    WHERE REF(p) = p_ref;

• referencja IS DANGLING - czy istnieje wskazywany obiekt:

  UPDATE Wydziały 
    SET Kierownik = NULL 
    WHERE Kierownik IS DANGLING;

• DEREF(referencja) - obiekt, na który wskazuje referencja

  SELECT DEREF(p_ref) INTO p1 
    FROM DUAL; 
  
    Nazwisko := p1.Nazwisko;  -- konieczne dla nawigacji w PL/SQL

Porównywanie wartości typów obiektowych

Bez dodatkowej specyfikacji Oracle może wykonywać tylko porównania =, <> dla wartości typów obiektowych. Aby umożliwić porównania "mniejsze, większe", trzeba zdefiniować metodę typu:

• MAP MEMBER - dla obiektu jest obliczana wartość skalarna dająca podstawę do porównywania obiektów, bądź,
• ORDER MEMBER - dla dwóch obiektów danego typu określa się, kiedy jeden z nich jest mniejszy, równy bądź większy od drugiego.

Użycie nowych rodzajów metod prześledzimy na przykładzie typu adresowego:

CREATE TYPE Typ_adresowy AS OBJECT (
  Ulica  VARCHAR2(50),
  Miasto VARCHAR2(50),
  Kraj   VARCHAR2(25),
  Kod    VARCHAR2(10),
  MAP MEMBER FUNCTION adres_skalar RETURN VARCHAR2
);

Oto przykładowa implementacja funkcji Adres_skalar:

MAP MEMBER FUNCTION Adres_skalar RETURN VARCHAR2 IS
BEGIN
  RETURN kod||miasto||ulica;
END Adres_skalar;

Jeśli tabela obiektowa TabX zawiera atrybut Adres typu Typ_adresowy, to możemy dokonać sortowania wyników instrukcji SELECT przy użyciu zdefiniowanej funkcji:

SELECT VALUE(t)
  FROM TabX t
  ORDER BY t.Adres;

Alternatywna metoda określania porządku obiektów polega na bezpośrednim obliczeniu wyniku porównania dwóch wartości typu obiektowego (-1 oznacza <, 0 oznacza =, a 1 oznacza >):

ORDER MEMBER FUNCTION Adres_porównaj (drugi_arg Typ_adresowy) RETURN INTEGER IS
  w1 VARCHAR2(150) := SELF.kod || SELF.miasto || SELF.ulica;
  w2 VARCHAR2(150) := drugi_arg.kod || drugi_arg.miasto || drugi_arg.ulica;
BEGIN 
  IF w1 < w2 THEN RETURN -1;
  ELSIF w1 > w2 THEN RETURN 1;
  ELSE RETURN 0;
  END IF;
END Adres_porównaj;

Perspektywy obiektowe

Oprócz zwykłych perspektyw relacyjnych są też perspektywy obiektowe, których wartością jest zbiór egzemplarzy, ustalonego przy tworzeniu perspektywy, typu obiektowego. Rozpatrzmy typ obiektowy osób:

CREATE OR REPLACE TYPE Typ_osoby AS OBJECT (
  Id        INTEGER,
  Nazwisko  VARCHAR2(50)
);

oraz tabelę

CREATE TABLE Osoby (
  Id       INTEGER,
  Nazwisko VARCHAR2(50)
);

Możemy teraz utworzyć perspektywę obiektową typu obiektowego Typ_osoby:

CREATE VIEW Ob_soby OF Typ_osoby 
  WITH OBJECT IDENTIFIER (Id)
  AS 
    SELECT Id, Nazwisko
      FROM Osoby;

gdzie klauzula WITH OBJECT IDENTIFIER (Id) określa jednoznaczny identyfikator dla tworzonych obiektów perspektywy. (Przed wersją Oracle8i klauzula ta miała postać WITH OBJECT OID (Id)).

Tabela Osoby jest zwykłą tabelę relacyjną, natomiast perspektywa Ob_osoby jest  jej reprezentacją obiektową w postaci zbioru obiektów. W ten sposób na zwykłej relacyjnej bazie danych można określić jej widok będący obiektową bazą danych i w konsekwencji posługiwać się nią tak, jakby była obiektową bazą danych.

Rozszerzony przykład - baza zamówień

Rozważymy rozszerzony przykład definiując obiektową bazę dla zamówień. Są możliwe trzy rozwiązania:

• rozwiązanie relacyjne - tabele relacyjne korzystające z wbudowanych typów danych;
• rozwiązanie obiektowe - tabele obiektowe korzystające z typów danych definiowanych przez użytkownika;
• rozwiązanie kompromisowe - tabele relacyjne, obiektowość uzyskana przez perspektywy obiektowe definiowane przez użytkownika.

Ograniczymy się tutaj tylko do zaprezentowania rozwiązania nr 2.

Identyfikacja encji i związków

Encje:

• Klient – wśród atrybutów znajdują się dwa atrybuty złożone: adres i lista numerów telefonicznych,
• Towar,
• Zamówienie.

Związki:

• Klient - Zamówienie 1:n,
• Zamówienie - Towar n:m.

Rozwiązanie obiektowe

Użyjemy trzech tabel obiektowych do przechowywania egzemplarzy podstawowych encji: Osoby, Zamówienia, Towary. Wprowadzimy typy złożone dla atrybutów:

• adres -  OBJECT,
• lista numerów telefonicznych - VARRAY,
• pozycje zamówień w jednym zamówieniu - tabela zagnieżdżona TABLE,
• odwołania do obiektów istniejących w innych tabelach - REF OBJECT.

Definicje typów kolekcji i typów obiektowych

CREATE TYPE Typ_adresowy AS OBJECT (
  Ulica   VARCHAR2(50), 
  Miasto  VARCHAR2(50),
  Kraj    VARCHAR2(25),
  Kod     VARCHAR2(10)
);

CREATE TYPE Typ_list AS VARRAY(10) OF VARCHAR2(20); -- tablica VARRAY

CREATE TYPE Typ_klientów AS OBJECT (
  Id_klienta         INTEGER,
  Nazwisko           VARCHAR2(50),
  Imię               VARCHAR2(50),
  Adres              Typ_adresowy,
  Lista_telefonów    Typ_list,
  ORDER MEMBER FUNCTION Kol_klientów (x IN Typ_klientów) RETURN INTEGER, 
  PRAGMA RESTRICT_REFERENCES(Kol_klientów, WNDS, WNPS, RNPS, RNDS)
);

CREATE OR REPLACE TYPE BODY Typ_klientów AS
  ORDER MEMBER FUNCTION Kol_klientów (x IN Typ_klientów) RETURN INTEGER IS
  BEGIN -- porównywanie klientów przez tę wartość
    RETURN Id_klienta - x.Id_klienta;
  END; -- < 0 oznacza SELF < x, =0 SELF = x, >0 oznacza SELF > x
END;

CREATE TYPE Typ_towarów AS OBJECT (
  Id_towaru INTEGER,
  Opis      VARCHAR2(10), 
  Cena      NUMBER,
  VAT       NUMBER
);

CREATE TYPE Typ_pozycji AS OBJECT(
  Id_pozycji  INTEGER,
  RefTowaru   REF Typ_Towarów, -- referencja do towaru
  Ilość       NUMBER
);

Z kolei zdefiniujemy typ "tabelowy" pozycji zamówień (tabela zagnieżdżona):

CREATE OR REPLACE TYPE Lista_pozycji AS TABLE OF Typ_pozycji;

Możemy już zdefiniować podstawowy w przykładzie typ zamówień:

CREATE OR REPLACE TYPE Typ_zamówień AS OBJECT (
  Id_zamówienia   INTEGER,
  Klient REF      Typ_klientów,
  Data_zam        DATE,
  Pozycje_zam     Lista_pozycji,
  MAP MEMBER FUNCTION Numer_zam RETURN NUMBER,
  PRAGMA RESTRICT_REFERENCES(Numer_zam, WNDS, WNPS, RNPS, RNDS), 
  -- suma wartości pozycji
  MEMBER FUNCTION Suma RETURN NUMBER,
  PRAGMA RESTRICT_REFERENCES(Suma, WNDS, WNPS) 
);

CREATE OR REPLACE TYPE BODY Typ_zamówień  AS

MEMBER FUNCTION Suma RETURN NUMBER IS
    i          INTEGER;
    towar      Typ_towarów;
    pozycja    Typ_pozycji;
    sumuj      NUMBER := 0;
    cena       NUMBER;
BEGIN
  FOR i IN 1..SELF.Pozycje_zam.COUNT  LOOP
     pozycja := SELF.Pozycje_zam(i);
     SELECT DEREF(pozycja.RefTowaru) INTO towar FROM DUAL;
     sumuj := sumuj + pozycja.Ilość * towar.Cena ;
  END LOOP;
  RETURN sumuj;
END;

MAP MEMBER FUNCTION Numer_zam RETURN NUMBER IS
  BEGIN -- podstawa porównań obiektów zamówień
    RETURN Id_zamówienia;
  END;
END;

Utworzenie tabel obiektowych bazy zamówień

Utworzymy trzy tabele obiektowe wchodzące w skład naszej bazy zamówień.

CREATE TABLE Klienci OF Typ_klientów (Id_klienta PRIMARY KEY);

Przypominamy, że więzy spójności występują w definicji tabeli a nie jej typu! Dostęp do atrybutów złożonych odbywa się za pomocą notacji kropkowej, np. Adres.Kod

CREATE TABLE Towary OF Typ_towarów (Id_towaru PRIMARY KEY);

CREATE TABLE Zamówienia OF Typ_zamówień (
  Id_zamówienia PRIMARY KEY,
  Klient        SCOPE IS Klienci
)
  NESTED TABLE Pozycje_zam STORE AS Pozycje; 

Potrzebne jest ograniczenie zakresu referencji w kolumnie RefTowaru w tabelach zagnieżdżonych będących wartościami kolumny Poz_za. Realizujemy to poprzez tabelę Pozycje przechowującą wszystkie te wartości.

ALTER TABLE Pozycje ADD (SCOPE FOR(RefTowaru) IS Towary);

Założymy teraz dwa indeksy na tabeli Pozycje. Pierwszy indeks zakładamy na dodawanej przez system kolumnie NESTED_TABLE_ID, której wartości identyfikują tabelę zagnieżdżoną:

CREATE INDEX Ind_Pozycje ON Pozycje (NESTED_TABLE_ID);

Dodanie tego indeksu spowoduje przyśpieszenie dostępu do pozycji w tabelach zagnieżdżonych.

Następny indeks jest potrzebny do zagwarantowania warunku jednoznaczności Id_pozycji w ramach tego samego zamówienia.

CREATE UNIQUE INDEX Unique_Pozycje ON Pozycje (NESTED_TABLE_ID, Id_pozycji);

Oto przykładowe instrukcje INSERT, DELETE i UPDATE na utworzonych tabelach obiektowych:

INSERT INTO Towary VALUES(1004,'Cukier', 2.00, 10);

INSERT INTO Klienci VALUES (1, 'Kowalski', 'Jan',
            Typ_adresowy('Majowa 50m12', 'Łódź', 'Polska', '95054'),
            Typ_list('765-2345', '501-2345’)) ;

INSERT INTO Zamówienia
  SELECT  1001, REF(k), SYSDATE, Lista_pozycji()
    FROM   Klienci k
    WHERE  k.Id_klienta= 1 ;

INSERT INTO THE(SELECT z.Pozycje_zam 
                  FROM Zamówienia z
                  WHERE  z.Id_zamówienia = 1001)
  SELECT 2001, REF(t), 12 
    FROM Towary t
    WHERE t.Id_towaru = 1004;

UPDATE THE (SELECT z.Pozycje_zam
              FROM Zamówienia z
              WHERE  z.Id_zamówienia = 1001) tb  
  SET tb.Towar = (SELECT REF(t)
                    FROM  Towary t
                    WHERE t.Id_towaru = 1011)
  WHERE tb.Id_pozycji = 11;

Operacje na obiektach tabel można wykonywać także w PL/SQL:

DECLARE
  Refklienta REF Typ_klientów;
  Reftowaru  REF Typ_towarów;
BEGIN
  SELECT REF(k) INTO Refklienta 
    FROM Klienci k
    WHERE k.Nazwisko = :Nazwisko_klienta;
  SELECT REF(t) INTO Reftowaru 
    FROM Towary t
    WHERE t.Opis = :Nazwa_towaru;
  INSERT INTO Zamówienia VALUES (Typ_zamówień(1, Refklienta, Sysdate,  
                         Lista_pozycji(Typ_pozycji(1,Reftowaru,20))));
END;

Usuwanie obiektów z tabeli obiektowej odbywa się w taki sam sposób, jak usuwanie wierszy z tabeli relacyjnej. Na przykład, następująca instrukcja powoduje usunięcie zamówienia o wskazanym numerze:

DELETE FROM Zamówienia
  WHERE Id_zamówienia = 1001;

Zauważmy, że usuwając tabelę Zamówienia usuwamy także wszystkie pozycje zamówień, które są zapisane w tabelach zagnieżdżonych wewnątrz tabeli Zamówienia.

Przykłady sprowadzania danych z bazy danych

Odczytanie wartości atrybutu referencyjnego jest prostsze niż złączenie w zapytaniu relacyjnym:

SELECT z.Id_zamówienia, 
       z.Klient.Nazwisko,
       z.Klient.Imię,
       z.Klient.Adres.Miasto
  FROM Zamówienia z;

SELECT p.Id_pozycji, p.RefTowaru.Opis, p.Ilość
  FROM THE(SELECT Pozycje_zam
             FROM Zamówienia 
             WHERE Id_zamówienia = 1001) p;

SELECT  DEREF(z.Klient),             -- cała informacja o kliencie
        z.Data_zam, z.Id_zamówienia, -- informacja o zamówieniu
        z.Pozycje_zam                -- informacja o pozycjach zamówienia
  FROM  Zamówienia z
  WHERE z.Id_zamówienia = 1001;

SELECT  z.Id_zamówienia, z.Suma() 
  FROM  Zamówienia z; -- numer zamówienia i sumaryczna wartość

Rozważmy następujące zadanie: Wypisz pełną informację o złożonych zamówieniach. W SQL możemy wypisać raport dotyczący konkretnego zamówienia:

SELECT z.Klient.Nazwisko, z.Id_zamówienia,
            tab.Id_pozycji, tab.RefTowaru.Opis, tab.Ilość
  FROM THE (SELECT  z.Pozycje_zam
              FROM  Zamówienia  z
              WHERE z.Id_zamówienia  = 1001) tab, 
       Zamówienia  z
WHERE z.Id_zamówienia = 1001;

W PL/SQL jest możliwe zbudowanie pełnego raportu o wszystkich złożonych zamówieniach. Procedura Oblicz tworzy raport w pomocniczej tabeli Pomoc.

CREATE TABLE Pomoc (
  Nazwisko        VARCHAR2(50),
  Id_zamówienia   INTEGER, 
  Id_pozycji      INTEGER, 
  Opis            VARCHAR2(10)
);

CREATE OR REPLACE PROCEDURE Oblicz AS
  z       Zamówienia%ROWTYPE;       
  k       Typ_klientów;
  Tab     Lista_pozycji;   
  pozycja Typ_pozycji;
  towar   Typ_towarów;
BEGIN
  DELETE FROM Pomoc;
  FOR z IN (SELECT * FROM Zamówienia) LOOP
    SELECT DEREF(z.Klient) INTO k FROM DUAL;
    Tab := z.Pozycje_zam;
    FOR i IN 1..Tab.COUNT LOOP
      pozycja :=tab(i);
      SELECT DEREF(pozycja.RefTowaru)
        INTO towar FROM DUAL;
      INSERT INTO Pomoc VALUES (k.Nazwisko,z.Id_Zamówienia, tab(i).Id_pozycji, towar.Opis);
    END LOOP;
  END LOOP;
END;

Po wykonaniu procedury Oblicz, aby wypisać pełny raport obliczony w pomocniczej tabeli Pomo wykonujemy instrukcję:

SELECT * FROM Pomoc;