Wykład 4

Obiektowość w obiektowo-relacyjnych bazach danych na przykładzie Oracle

Streszczenie

Współczesne bazy danych posiadają cechy zarówno relacyjne jak i obiektowe. W analizie wymagań stawianych przed SZBD skupialiśmy się dotąd na cechach relacyjnych baz danych, teraz czas na przestudiowanie cech obiektowych.

Zaprezentujemy zarówno nowe możliwości implementacji związków jeden-do-wiele jak i sposoby składowania nowego rodzaju obiektów (referencji, kolekcji).

Rozważymy dwa rodzaje obiektów, które można przechowywać w obiektowo-relacyjnej bazie danych:

• obiekty będące instancjami typów obiektowych,
• duże obiekty LOB.

4.1 Znaczenie obiektowości w bazach danych

Od przeszło dwudziestu już lat trwają prace nad nowymi koncepcjami w bazach danych polegającymi na dołączeniu cech obiektowych do istniejących relacyjnych baz danych. Otrzymano w ten sposób model obiektowo-relacyjny i faktycznie na takim modelu jest w tej chwili oparty aktualny Standard języka SQL3.

Przypomnimy najpierw 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 modyfikować 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 modyfikować aplikacji jej używających.

Oto podsumowanie zalet użycia obu rodzajów abstrakcji w bazach danych:

1. Modelowanie danych zachodzi na wyższym poziomie abstrakcji przez co na ogół jest ułatwione.
2. Zmniejszenie złożoności tworzenia aplikacji przez podział zadania na części. Modularność aplikacji. Ułatwienie dokonywania zmian. Ukrycie szczegółów implementacyjnych przed użytkownikiem.
3. Możliwość wielokrotnego użycia komponentów w tej samej lub w różnych aplikacjach.
4. Zgrupowanie używanego kodu po stronie serwera wokół obiektów, na których działa kod. Uzyskanie większej kontroli nad kodem.
5. Zastosowanie obiektowo-relacyjnego modelu danych prowadzi do zmniejszenia rozbieżności w modelach danych samej bazy danych i aplikacji bazodanowej napisanej w obiektowym języku programowania. Oba modele można oprzeć o te same pojęcia: klasy (typu obiektowego) i instancji klasy (obiektu). Jednak chociaż obiekt w bazie danych może mieć tę samą strukturę co obiekt w programie, to jednak przejście między nimi trzeba dokonywać za pomocą kodu (po stronie serwera bazy danych).

Używane współcześnie bazy danych są na ogół relacyjne, a aplikacje klienckie obiektowe.  Dlaczego więc nie przechodzimy na obiektowe bazy danych?

1. Prawe wszystkie dane są aktualnie przechowywane w tabelach relacyjnych.
2. Obiektowe bazy danych wydają się być mniej wydajne.
3. Relacyjne bazy danych mogą być projektowane i używane przez osoby, które nie są programistami.

Model obiektowo-relacyjny stara się zbliżyć do siebie oba niespójne względem siebie modele danych?
 

4.2 Typ obiektowy

Rys. 4.1 Struktura obiektu

Struktura typu obiektowego

Osobno specyfikujemy publiczny interfejs obiektów składający się z deklaracji atrybutów i specyfikacji metod oraz prywatną implementację  obiektów składającą się z definicji (ciał) metod.

Rys. 4.2 Struktura typu obiektowego

Oto składnia specyfikacji typu obiektowego (w wersji podstawowej):
 

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

Oto składnia implementacji typu obiektowego (w wersji podstawowej):
 

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

Jest też możliwość używania CREATE OR REPLACE ...zamiast CREATE a także instrukcji show errors.

Przykład

Zdefiniujemy dwa typy obiektowe: typ nazwisk Name_typ oraz typ pracowników Emp_typ:

CREATE TYPE Name_typ
AS OBJECT(
   F_name VARCHAR2(25),
   L_name VARCHAR2(25),
   Initials VARCHAR2(7),
   MEMBER FUNCTION Full_name RETURN VARCHAR2,
   PRAGMA RESTRICT_REFERENCES(Full_name, WNDS, RNDS, WNPS, RNPS ));
/

CREATE TYPE BODY Name_typ AS
   MEMBER FUNCTION Full_name RETURN VARCHAR2 IS
   BEGIN
      RETURN (L_name || ' ' || F_name );
   END Full_name;
END;
/

CREATE TYPE Emp_typ
AS OBJECT(
   Emp_id NUMBER(7),
   Name Name_typ,           -- typ obiektowy
   Street VARCHAR2(25),
   City VARCHAR2(15),
   State CHAR(2),
   Zip INTEGER,
   MEMBER FUNCTION Get_name RETURN VARCHAR2,
   PRAGMA RESTRICT_REFERENCES(Get_name, WNDS, RNDS, WNPS, RNPS),
   MEMBER PROCEDURE Set_l_name (v_name VARCHAR2));
/

CREATE TYPE BODY Emp_typ AS
   MEMBER FUNCTION Get_name RETURN VARCHAR2 IS
   BEGIN
      RETURN (Name.L_name ||' '|| Name.F_name);
   END;
   MEMBER PROCEDURE Set_l_name (v_name VARCHAR2) IS
   BEGIN
     Name.L_name := v_name;
   END;
END;
/


Typy obiektowe i ich implementacje są składowane w słowniku danych taka samo jak pakiety i ich implementacje (metoda jest funkcją lub procedurą z dodatkowym argumentem dla obiektu, z którego jest wywoływana).

Metody

Metody to funkcje lub procedury, które tworzymy w definicji typu obiektowego w celu zaimplementowania zachowania się obiektów danego typu. Aplikacja wywołuje metody aby uzyskać w efekcie to zachowanie. Są trzy typy metod:

1. metody składowe obiektów (metody typu MEMBER),
2. metody konstruktorów obiektów (metody typu CONSTRUCTOR) oraz 
3. metody statyczne, czyli odnoszące się do całego typu (metody typu STATIC).

W tym wykładzie rozważamy tylko metody składowe obiektów oraz metody konstruktorów obiektów implementowane przez system – oprócz tego są jeszcze metody konstruktorów definiowane przez użytkowników.

Szczególnym przypadkiem metod składowych obiektu są metody porównywania obiektów:

1. MAP – przez odwzorowanie układu wartości atrybutów na liczbę;
2. ORDER – bezpośrednio wylicza wynik porównania dwóch obiektów < to -1, = to 0, > to 1.

Oto dwa ich przykłady:

CREATE TYPE rectangle_typ AS OBJECT
( len NUMBER, wid NUMBER, MAP MEMBER FUNCTION area RETURN NUMBER);

CREATE TYPE BODY rectangle_typ AS
MAP MEMBER FUNCTION area RETURN NUMBER IS
BEGIN RETURN len * wid; END area; END;

CREATE TYPE location_typ AS OBJECT
( building_no NUMBER, city VARCHAR2(40),
ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER );

CREATE TYPE BODY location_typ AS
ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER IS
BEGIN
   IF building_no < l.building_no THEN RETURN -1;
   ELSIF building_no > l.building_no THEN RETURN 1;
   ELSE RETURN 0;
   END IF;
END;
 END;

Dziedziczenie

Między typami obiektowymi obowiązuje dziedziczenie tak jak w językach programowania - realizowane przez operator UNDER. Na przykład:

CREATE TYPE Person
AS OBJECT (
  First VARCHAR2(50),
  Last VARCHAR2(50)) NOT FINAL;
/
CREATE TYPE Emp
UNDER Person (
  Salary NUMBER) FINAL;
/

Obiekt typu Emp ma wszystkie cechy obiektu typu Person a poza tym jeszcze cechy wprowadzone w specyfikacji typu Emp.

DECLARE
  x Emp:=Emp('Jan','Kowalski',10000);
BEGIN
  DBMS_OUTPUT.Put_line(x.first||' '||x.last||' '||x.salary);
END;
/

Wymagana jest specyfikacja NOT FINAL dla typów obiektowych, które mogą być wzorcami dla podtypów podlegając dalszemu dziedziczeniu czyli nie są one końcowe w hierarchii dziedziczenia. Domyślną specyfikacją jest FINAL - co oznacza, że typ obiektowy nie podlega już  dalszemu dziedziczeniu.

Typ obiektowy dla którego nie można utworzyć instancji wymaga specyfikacji NOT INSTANTIABLE. Nie posiada on konstruktora. Nie można tworzyć obiektów tego typu.

CREATE TYPE Address_t AS OBJECT(...) NOT INSTANTIABLE NOT FINAL;

CREATE TYPE USAddress_t UNDER Address_t(...);

CREATE TYPE IntlAddress_t UNDER Address_t(...);

Typ bez instancji może być definiowany jako podtyp typu z instancjami. Typ bez instancji nie może być FINAL.

Metoda dla której typ nie dostarcza implementacji wymaga specyfikacji NOT INSTANTIABLE. Typ, który zawiera metody bez instancji, musi sam być NOT INSTANTIABLE. Domyślną specyfikacją w obu przypadkach jest INSTANTIABLE.

Polimorfizm

Przedefiniując dziedziczone metody w podtypie mamy możliwość zdefiniowania innego sposobu wykonania metod na obiektach podtypu. Obiekt podtypu może być użyty w kodzie w miejsce obiektu nadtypu – definiując dla różnych podtypów różne działania.

Przesłanianie

Tak jak w językach programowania jest możliwość zastosowania przesłaniania metod.

CREATE TYPE Figure
AS OBJECT (
  NOT INSTANTIABLE MEMBER FUNCTION area RETURN NUMBER
) NOT INSTANTIABLE NOT FINAL;
/
CREATE TYPE Rect
UNDER Figure
  (x NUMBER,
   y NUMBER,
   OVERRIDING MEMBER FUNCTION area RETURN NUMBER) FINAL;
/
CREATE TYPE BODY Rect AS
  OVERRIDING MEMBER FUNCTION area RETURN NUMBER IS
  BEGIN
     RETURN x*y;
  END;
END;
/

Informacje w słowniku danych Oracle

Informacje o typach obiektowych znajdują się w perspektywie słownika danych o nazwie:

• USER_OBJECTS

Informacje o metodach typów obiektowych znajdują się odpowiednio w perspektywach słownika danych:

• USER_METHOD_PARAMS
• USER_METHOD_RESULTS
• USER_TYPE_METHODS

Usuwanie typów obiektowych i typów kolekcji

4.3 Tabela obiektowa

Zdefiniowanego typu obiektowego można używać do tworzenia tabel obiektowych tego typu.
 

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

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

W utworzonej tabeli obiektowej są zapisywane obiekty typu obiektowego na tej samej zasadzie co wiersze tabeli relacyjnej. To znaczy, wartości atrybutów tworzą wiersz, na którym działają metody określone w danym typie obiektowym.

CREATE TABLE Name_table OF Name_typ;  -- tabela obiektowa

INSERT INTO Name_table
VALUES('Jan','Kowalski','JK');

INSERT INTO Name_table
VALUES('Anna','Kowalska','AK');

SELECT nt.F_name, nt.Full_name()
FROM Name_table nt;

Tabeli obiektowej można używać na dwa sposoby:

• jako tabeli jedno-kolumnowej: każdy wiersz jest obiektem; można wykonywać operacje obiektowe.
• jako tabeli wielokolumnowej: każdemu atrybutowi obiektu odpowiada jedna kolumna.

W związku z tym mamy do czynienia z obiektami wierszowymi oraz kolumnowymi.

Metoda konstruktora obiektu

Dla każdego typu obiektowego automatycznie jest tworzona metoda konstruktora obiektu tego typu. Ma ona taką samą nazwę jak typ obiektowy oraz ma argumenty takich typów jak atrybuty typu obiektowego. Rozważmy typ obiektowy:

CREATE TYPE Tv_type
AS OBJECT (
  Tv_category VARCHAR2(20),
  Screen_size NUMBER(4));
/

Oto przykłady użycia jego konstruktora w bloku PL/SQL:

DECLARE
  v_new_tv Tv_type := Tv_type('WEB tv', 32);
  v_alt_tv Tv_type;
BEGIN
  v_alt_tv := Tv_type('Big Screen', 72);
END;
/

oraz w instrukcji INSERT do tabeli obiektowej:

CREATE TABLE Tv OF Tv_type;

INSERT INTO Tv VALUES(Tv_type('Color tv', 28));

Wyszukiwanie (SELECT) w tabeli obiektowej

Wynikiem instrukcji:

SELECT *
FROM Name_table p
WHERE p.L_name LIKE 'K%';

F_NAME         L_NAME        INITIAL
-------------- ------------- -------
Jan            Kowalski      JK
Anna           Kowalska      AK

Wynikiem instrukcji:

SELECT VALUE(p) AS Osoba
FROM Name_table p
WHERE p.L_name LIKE 'K%';

jest zbiór obiektów reprezentowanych przy użyciu konstruktora typu obiektowego (VALUE(p) - oznacza obiekt wskazywany przez p):

OSOBA(F_NAME, L_NAME, INITIALS)
-----------------------------------
NAME_TYP('Jan', 'Kowalski', 'JK')
NAME_TYP('Anna', 'Kowalska', 'AK')

Można ograniczyć wyszukiwanie do obiektów z podtypu:

SELECT VALUE(p) FROM person_obj_table p
WHERE VALUE(p) IS OF (part_time_student_typ);

lub

SELECT VALUE(p) FROM person_obj_table p
WHERE VALUE(p) IS OF (ONLY part_time_student_typ);

Wstawianie (INSERT) do tabeli obiektowej

Obiekty wstawiamy do atbeli obiektowej albo bezpośrednio tak jak do tabeli relacyjnej - podając wartości ich atrybutów:

INSERT INTO Name_table
VALUES('Jan','Kowalski','JK');

INSERT INTO Name_table
VALUES(Name_typ('Jan','Kowalski','JK'));

Modyfikacja (UPDATE) na tabeli obiektowej

Modyfikujemy albo bezpośrednio, tak jak w tabeli relacyjnej:

UPDATE Name_table p
SET p.L_name = 'Janusz'
WHERE p.L_name = 'Jan';

UPDATE Name_table p
SET p = Name_typ('Janusz','Kowalski','JK')
WHERE p.L_name = 'Jan';

Usuwanie obiektów (DELETE) z tabeli obiektowej

Obiekty z tabeli obiektowej usuwamy tak jak wiersze z tabeli relacyjnej:

DELETE FROM Name_table p
WHERE p.L_name = 'Janusz';

Tabela relacyjna w obiektowo-relacyjnej bazie danych

Oprócz nowego rodzaju tabel obiektowych możemy także używać tabel relacyjnych z kolumnami typów obiektowych. Wartościami wpisywanymi do kolumn tabeli mogą więc być instancje typów obiektowych. Na przykład:

CREATE TABLE Emp1(  -- tabela relacyjna
   Empno NUMBER PRIMARY KEY,
   Name Name_typ,   -- kolumna typu obiektowego
   Sal NUMBER,
   Kier REFERENCES Emp1);

OID, REF

Każdy obiekt wierszowy jest jednoznacznie identyfikowany przez swój systemowy, obiektowy identyfikator OID, 16 bajtowy – stanowiący dodatkową, indeksowaną, jednoznaczną kolumnę w tabeli obiektowej (opcja OBJECT IDENTIFIER IS SYSTEM GENERATED w CREATE TABLE) – opcja domyślna. Gdy istnieje klucz główny, można jego wartości użyć jako OID (opcja OBJECT IDENTIFIER IS PRIMARY KEY w CREATE TABLE). Natomiast referencją REF jest logiczny wskaźnik do obiektu wierszowego konstruowany z OID obiektu i metadanych.

Typ referencyjny

Dla każdego typu obiektowego Type jest automatycznie definiowany jego typ referencyjny oznaczany przez REF Type. Typu tego można używać jako typu atrybutów obiektów bądź kolumn w tabeli relacyjnej. Daje to możliwość wiązania wartości atrybutu bądź wartości w kolumnie z innym obiektem podobnie jak klucz obcy odwołuje się do klucza głównego. Jest to alternatywny sposób tworzenia powiązań.

CREATE TYPE Dept_Type
AS OBJECT(
   Name VARCHAR2(10),
   Loc VARCHAR2(50));

CREATE TYPE Emp_Type
AS OBJECT(
   Name VARCHAR2(20),
   Sal NUMBER,
   Dept_ref REF Dept_Type);

CREATE TABLE Obj_Dept OF Dept_Type;

CREATE TABLE Obj_Emp OF Emp_Type -- tabela obiektowa
(Dept_ref SCOPE IS Obj_Dept);

CREATE TABLE Rel_emp(            -- tabela relacyjna
  Id NUMBER PRIMARY KEY,
  Name VARCHAR2(20),
  Sal Number,
  Dept_ref REF Dept_Type SCOPE IS Obj_Dept);

Ze względu na możliwość istnienia wielu tabel obiektowych tego samego typu, definiując typ referencyjny trzeba wskazać do obiektów której tabeli on się odwołuje. Służy do tego klauzula SCOPE IS – proszę zwrócić uwagę na jej użycie w powyższych przykładach.

W instrukcjach SQL można bezpośrednio nawigować przez referencje w taki sam sposób, jak to ma miejsce dla obiektów. Gdybyśmy chcieli dla każdego pracownika wypisać nazwę jego departamentu, użylibyśmy następującej instrukcji SELECT:

SELECT e.Name, e.Dept_ref.Name
FROM Obj_emp e;

Dla tabeli relacyjnej instrukcja SELECT jest taka sama.

SELECT e.Name, e.Dept_ref.Name
FROM Rel_emp e;

Natomiast w kodzie PL/SQL nawigacja przez referencje jest niedozwolona i wymaga użycia explicite specjalnej funkcji SQL:
 

DEREF(p) - identyfikator obiektu na który wskazuje referencja p

Jest też funkcja odwrotna:
 

REF(o) - referencja do obiektu o identyfikatorze o

Posługiwanie się referencjami wymaga stosowania kodu PL/SQL. Na przykład, aby wstawić wiersz do tabeli obj_emp, najpierw trzeba obliczyć referencję do departamentu, a następnie wstawić ją do wiersza wstawianego pracownika.

DECLARE
x REF Dept_Type;
BEGIN
   SELECT ref(o) INTO x FROM obj_dept o WHERE o.name='KADRY';
   INSERT INTO obj_emp VALUES('KOWALSKI',2000,x);
END;

Referencja jest liczbą heksadecymalną. Wypisując otrzymujemy:

SQL> SELECT * FROM obj_emp;

NAME                        SAL
-------------------- ----------
DEPT_REF
--------------------------------------------------------------------------------
KADRY                      2000
00002202086B7D6BE769DE4B2E8273BFDA10FBB9D9473186A7171D452487C81A3BA7A16752

  1* SELECT o.name, o.Dept_Ref.name FROM obj_emp o
SQL> /

NAME                 DEPT_REF.NAME
-------------------- -------------
KOWALSKI             KADRY

Reasumując, w obiektowo-relacyjnej bazie danych pojawiają się dwa nowe rodzaje wartości związane z zapisem danych na dysku: referencje do obiektów oraz jednoznaczne identyfikatory obiektów. Każda implementacja obiektowo-relacyjnej bazy danych może używać innych zbiorów tych wartości, co powoduje, że obiektowo-relacyjna baza danych przestaje być przenaszalna (jako zbiór tabel z atomowymi, skalarnymi wartościami) między różnymi systemami. Tracimy więc wielką zaletę relacyjnych baz danych.

Tworzenie tabeli obiektowej z obiektami różnych podtypów

CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table VALUES (person_typ(12, 'Bob Jones', '111-555-121'));

INSERT INTO person_obj_table VALUES (student_typ(5, 'Joe Lan', ‘6555-131', 12, 'HISTORY'));

INSERT INTO person_obj_table VALUES (employee_typ(55, 'Jane Smith', ‘6555-776', 100, 'Jennifer Nelson'));

INSERT INTO person_obj_table VALUES (part_time_student_typ(52, 'Kim Patel', '1555-1232', 14, 'PHYSICS', 20));

Ograniczanie wartości w kolumnie tabeli obiektowej

CREATE TYPE office_typ AS OBJECT
(office_id VARCHAR(10), location location_typ, occupant person_typ ) NOT FINAL;

CREATE TABLE office_tab OF office_typ
COLUMN occupant IS OF (ONLY employee_typ);

Kolekcje

Atrybut typu obiektowego lub kolumna tabeli nie koniecznie musi być typu prostego ale może być typu zbiorowego (kolekcji). Są dwa rodzaje kolekcji:

1. VARRAY – typ tablicy jednowymiarowej (jak wektor);
2. TABLE – typ tabeli zagnieżdżonej. A więc wartością atrybutu może być cała tabela! Tego typu nie będziemy rozważać na wykładzie.

Oto przykład zastosowania typu VARRAY. Najpierw definicja typu obiektowego o nazwie Projekt.

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

CREATE TYPE Lista AS
 VARRAY(3) OF Projekt;

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

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

SELECT w.Nazwa, w.Projekty
 FROM Wydziały w;

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

Przykład - reprezentacja związku klucz główny-klucz obcy w tabelach obiektowych

Przy reprezentowaniu departamentów i pracowników za pomocą dwóch tabel obiektowych Obj_Dept i Obj_Emp odpowiednio typów Dept_Type i Emp_Type, łatwo jest wyznaczyć dla pracownika jego departament (poprzez atrybut Dept_ref). Natomiast wobec braku klucza obcego i indeksu na nim, trudno wyznaczyć dla danego departamentu wszystkich pracowników zatrudnionych w tym departamencie. Rozwiązaniem tego problemu jest zastosowanie kolekcji. Najpierw definiujemy typ kolekcji:

CREATE TYPE Pracownicy AS VARRAY(100) OF REF Emp_Type;

CREATE TYPE Dept_Type; -- niepełna specyfikacja typu Dept_Type

/

CREATE TYPE Emp_Type
AS OBJECT(
   Name VARCHAR2(20),
   Sal NUMBER,
   Dept_ref REF Dept_Type);  --  departament pracownika

/

CREATE TYPE Pracownicy AS VARRAY(100) OF REF Emp_Type;

/

CREATE TYPE Dept_Type  -- powtórna, pełna specyfikacja typu Dept_Type
AS OBJECT(
   Name VARCHAR2(10),
   Loc VARCHAR2(50),
   Kadra Pracownicy);        --  pracownicy w departamencie
/

CREATE TABLE Obj_Dept OF Dept_Type;

CREATE TABLE Obj_Emp OF Emp_Type
(Dept_ref SCOPE IS Obj_Dept);

Jednak przy takiej reprezentacji nie ma możliwości zdefiniowania więzów spójności referencyjnej w sposób deklaratywny:

W kolekcji Kadra obiektu typu Dept_Type są dokładnie wszyscy pracownicy zatrudnieni w tym departamencie tzn. których Dept_ref wskazuje na ten departament.

Potrzebne jest użycie wyzwalaczy do zdefiniowania więzów spójności referencyjnej tego rodzaju.

Przykład

Zaprezentujemy przetwarzanie wartości w kolekcji przy użyciu kodu PL/SQL. Wypiszemy wydziały razem z projektami realizowanymi w tych wydziałach. Użyjemy metody Count zdefiniowanej dla kolekcji. Wartością Nazwa_kolekcji.Count jest liczba elementów w kolekcji. Metoda ta pozwala przejrzeć wszystkie elementy kolekcji.

CREATE OR REPLACE PROCEDURE Wypisz AS
   lis Lista;
BEGIN
   FOR z IN (SELECT * FROM Wydzialy) LOOP
      DBMS_OUTPUT.Put_line('Projekty Wydziału: '||z.Nazwa);
      lis:=z.Projekty;
      FOR i IN 1..lis.Count LOOP
        DBMS_OUTPUT.Put_line(lis(i).Num_proj||' '||lis(i).Tytul);
      END LOOP;
   END LOOP;
END;

Realizacja polecenia:

    Exec Wypisz

spowoduje wyświetlenie (należy pamiętać o włączeniu opcji SET SERVEROUTPUT ON):

Projekty Wydziału: Informatyka
1 Analiza
2 Projekt

Wszystkie elementy VARRAY są przechowywane albo razem z innymi elementami wiersza w postaci wartości RAW albo osobno w postaci dużego obiektu BLOB (typu danych omawianego w dalszej części tego wykładu) - w zależności od rozmiaru tablicy VARRAY.

Natomiast dla kolumny typu tabeli zagnieżdżonej TABLE budowana jest tabela wewnętrzna zawierająca sumę wszystkich tabel zagnieżdżonych w tej kolumnie.

Składowanie obiektów

Każdy obiektowy typ danych określa drzewo, w którego liściach znajdują się:

1. atrybuty prostych typów danych jak NUMBER, VARCHAR2 lub REF;
2. atrybuty typów kolekcji;
3. atrybuty typów obiektowych rozwijają się w poddrzewa odpowiadające ich typom, jak również w poddrzewa wszystkich podtypów ich typów.

Tabela obiektowa jest reprezentowana przez tabelę relacyjną, której kolumny odpowiadają atrybutom tego drzewa plus kolumny odpowiadające atrybutom drzew wszystkich podtypów danego typu. Oracle dodaje dodatkowe kolumny do tworzonej tabeli relacyjnej:

1. dla generowanego przez system identyfikatora obiektu;
2. dla każdego reprezentowanego typu – wartość logiczna czy NULL;
3. dla każdego reprezentowanego typu posiadającego podtypy kolumnę określającą podtyp aktualnego obiektu (typeid).

Ponadto, VARRAY jest zapisywane jako pojedyncza wartość INLINE lub BLOB a dla kolumny typu tabeli zagnieżdżonej jest określona jedna tabela relacyjna z wierszami wszystkich tabel zagnieżdżonych zapisywanej w tej kolumnie.

Perspektywy obiektowe

Alternatywnie, można samemu zdefiniować relacyjną bazę danych i udostępniać ją aplikacjom jako obiektową za pomocą perspektyw obiektowych. Przebiega to w następujących krokach:

1. Zdefiniowanie typu obiektowego: atrybuty odpowiadają istniejącym kolumnom tabel relacyjnych.
2. Zapisanie zapytania określającego jak wydobywać dane z tabel relacyjnych – kolumny w takiej samej kolejności jak atrybuty typu obiektowego.
3. Określenie jednoznacznego identyfikatora umożliwiającego tworzenie wskaźników do obiektów w perspektywie (np. istniejący klucz główny).
4. W przypadku skomplikowanej perspektywy używa się wyzwalaczy INSTEAD OF do przeprowadzania modyfikacji danych w perspektywie obiektowej.

Przykład

CREATE TABLE emp_table (
empnum NUMBER (5),
ename VARCHAR2 (20),
salary NUMBER (9,2),
job VARCHAR2 (20));

CREATE TYPE employee_t AS OBJECT (
empno NUMBER (5),
ename VARCHAR2 (20),
salary NUMBER (9,2),
job VARCHAR2 (20));

CREATE VIEW emp_view OF employee_t WITH OBJECT IDENTIFIER (empno) AS
SELECT e.empnum, e.ename, e.salary, e.job
FROM emp_table e WHERE e.job = 'Developer';

4.4 Duże obiekty LOB

Język SQL i jego rozszerzenie PL/SQL w dotychczas omawianej postaci umożliwiają dostęp do standardowych typów danych zapisanych w bazie danych i umożliwiają wykonywanie na nich operacji. Są jednak sytuacje, gdy może się to okazać niewystarczające.

Z taką sytuacją mamy do czynienia w przypadku danych, których format wykracza poza format standardowych typów danych. Przykładem takich danych są duże obiekty, jak rysunki (grafika), fotografie, zdjęcia satelitarne i rentgenowskie (zdjęcia binarne), video, animacje, wywiady (audio/video), muzyka, odgłosy dźwiękowe (sound waveforms), skrypty, dokumenty tekstowe, książki. Wprowadzono więc, zarówno do Standardu SQL, jak i do Oracle nowe typy danych dla dużych obiektów tzw. LOB (ang. large objects). Oprócz samych dużych obiektów wspomnimy również krótko o narzędziach do operowania na nich dostarczonych w standardowym pakiecie Oracle o nazwie DBMS_LOB.

Operowanie dużymi obiektami w Oracle (pakiet DBMS_LOB)

W Oracle są cztery rodzaje dużych obiektów LOB:
 

BLOB – binarny duży obiekt - strumień bitów jak w przypadku LONG RAW. CLOB – znakowy duży obiekt - strumień znaków. NCLOB – uogólniony (dla języków narodowych wielobajtowych) znakowy duży obiekt. BFILE – plik binarny przechowywany poza bazą danych.

Rys. 4.3 Rodzaje dużych obiektów

Duże obiekty są przechowywane albo wewnętrznie - wewnątrz bazy danych – dotyczy to wartości typów CLOB, NCLOB, BLOB, albo zewnętrznie w plikach systemu operacyjnego – dotyczy to wartości typu BFILE, które są dostępne z serwera Oracle tylko w trybie odczytu. Serwer Oracle nie potrafi automatycznie dokonywać konwersji danych między różnymi typami danych LOB, np. z wartości typu CLOB do wartości typu BLOB.

Z każdym dużym obiektem LOB jest związana:

1. wartość LOB: przechowywana w bazie danych zawartość dużego obiektu,
2. lokalizator LOB: wskaźnik do wartości LOB przechowywanej w bazie danych.

Zasada jest zwykle taka, że w kolumnie typu LOB jest zapisywany lokalizator LOB – nie cała jego zawartość. Jednak w przypadku gdy duży obiekt LOB ma mały rozmiar, system zapisuje go razem z innymi elementami w wierszu.

Interfejsy programistyczne operujące wartościami LOB używają lokalizatorów w podobny sposób, jak używa się uchwytów do plików w systemie operacyjnym. Przypisując lokalizator do nowej zmiennej lub nowego wiersza następuje skopiowanie wartości dużego obiektu i wygenerowanie nowego lokalizatora. W przypadku zewnętrznych wartości LOB nie są one kopiowane (a więc wskaźnik do tego samego pliku BFILE może występować w różnych wierszach w tabeli).

Poniższy rysunek ilustruje fakt, że wartością atrybutu, kolumny lub zmiennej jest lokalizator (wskaźnik) do dużego obiektu LOB, który jest zapisany albo w samej bazie danych albo poza nią (w przypadku wartości BFILE).

Rys. 4.4 Reprezentacja dużego obiektu

Przykład

Rozważmy tabelę pracowników, której kolumny są typów LOB. Dla każdego pracownika zamieszczamy jego życiorys (Resume), zdjęcie (Picture) oraz film video (Video).

CREATE TABLE Employee(
   Empno NUMBER,
   Ename VARCHAR2(35),
   Resume CLOB,
   Picture BLOB,
   Video BFILE);

Zewnętrzne obiekty LOB typu BFILE

Zewnętrzne obiekty LOB nie są zapisywane w bazie danych lecz każdy w osobnym pliku systemu operacyjnego.

W systemie Oracle został wprowadzony nowy rodzaj obiektu bazodanowego o nazwie katalog związany z typem danych BFILE. Mianowicie, katalog jest to obiekt bazodanowy reprezentujący katalog systemu operacyjnego. Służy do administrowania dostępem do obiektów bazy danych typu BFILE przechowywanych w plikach poza bazą danych. Fizyczny katalog jest tworzony pod systemem operacyjnym z uprawnieniami odczytu dla procesów Oracle. Pliki w tym katalogu nie mogą być ani zmieniane ani usuwane przez system Oracle.

Na przykład, instrukcja:

CREATE DIRECTORY Videos AS '/oracle/lob/';

tworzy obiekt bazodanowy reprezentujący katalog /oracle/lob

Rys. 4.5 Obiekt bazy danych typu katalog

Zilustrujemy użycie obiektów LOB w instrukcjach SQL na kilku przykładach.

Wstawianie obiektów LOB

INSERT INTO Employee VALUES
(7897,'Jan Kowalski','Znakomity aktor', NULL, NULL);

INSERT INTO Employee VALUES
(7898,'Piotr Jankowski', Empty_CLOB(), Empty_BLOB(), Bfilename('VIDEOS','J.IMG'));

UPDATE Employee e
  SET e.Resume =(SELECT f.Resume FROM Employee1 f
                 WHERE f.Ename='Kowalski')
WHERE e.Empno = 4508;

W powyższym przykładzie NULL oznacza brak lokalizatora obiektu LOB, Empty_BLOB() i Empty_CLOB() oznaczają lokalizator do pustego obiektu. Stałe tekstowe zostają automatycznie przekształcone do typu CLOB. Natomiast, funkcja Bfilename('VIDEOS','J.IMG')) zwraca lokalizator dużego obiektu przechowywanego w pliku J.IMG.

Dopisywanie do obiektów LOB w PL/SQL

Na zawartościach obiektów BLOB i CLOB można operować za pomocą procedur i funkcji specjalnego pakietu DBMS_LOB w podobny sposób jak to się robi na zawartościach plików binarnych.

DECLARE
  lobloc CLOB; -- lokalizator LOB
  text VARCHAR2(2000);
  amount NUMBER;
  offset INTEGER;
BEGIN
  text := 'tekst do wpisania do LOB';
  SELECT e.Resume INTO lobloc
    FROM Employee e WHERE e.Empno = 5887
  FOR UPDATE; -- konieczna blokada wiersza
  offset := DBMS_LOB.Getlength(lobloc) + 1;
  amount := Length(text);
  DBMS_LOB.Write(lobloc, amount, offset, text);
  COMMIT;
  DBMS_OUTPUT.Put_line('Wpisano ' || To_char(amount) ||' znaków');
END;
/

W szczególności, procedura DBMS_LOB.Write(Lobdst, Amount, Offset, Buffer) dopisuje do istniejącej zawartości obiektu o lokalizatorze Lobdst zawartość Buffer o długości Amount począwszy od miejsca Offset. Oto jej sygnatura:

PROCEDURE Write( Lobdst IN OUT BLOB|CLOB|NCLOB, -- trzy możliwości Amount IN OUT BINARY_INTEGER, Offset IN INTEGER := 1, Buffer IN RAW|VARCHAR2) -- RAW dla BLOB, VARCHAR2 dla CLOB

Jak widać, nie jest przewidziana możliwość wpisywania danych do obiektu typu BFILE!

Oto sygnatura funkcji Read odczytującej określoną przez Amount ilość danych dużego obiektu BLOB, CLOB lub BFILE od jego pozycji offset i zapisującą je do bufora Buffer:

PROCEDURE Read( Lobsrc IN BFILE|BLOB|CLOB|NCLOB , -- cztery możliwości! Amount IN OUT BINARY_INTEGER, Offset IN INTEGER, Buffer OUT RAW|VARCHAR2) -- RAW dla BLOB i BFILE, VARCHAR2 dla CLOB

 

Jest też możliwość załadowania zawartości obiektu BFILE (czyli pliku binarnego) do bazy danych w postaci obiektu BLOB lub CLOB. Służy do tego procedura Loadfromfile o następującej sygnaturze:

Oto przykład użycia procedury Loadfromfile do wstawienia zdjęcia osoby dostępnego jako obiekt typu BFILE - do tabeli Emp w postaci obiektu wewnętrznego typu BLOB:

CREATE TABLE Emp(
  Empno INTEGER(5);
  Ename VARCHAR2(50);
  Zdjecie BLOB);
 
DECLARE
  b BLOB;
  f BFILE;
BEGIN
  INSERT INTO Emp(1, 'Kowalski', Empty_BLOB());
  SELECT Zdjecie INTO b FROM Emp WHERE Empno=1 FOR UPDATE;
  f:=Bfilename('KATALOG','Kowalski.gif');
  DBMS_LOB.Loadfromfile(b,f,DBMS_LOB.Lobmaxsize);
END;
/

Usuwanie obiektów LOB

Nie ma bezpośredniej operacji usuwania obiektu LOB. W celu usunięcia go, można albo usunąć cały wiersz z obiektem, albo zastąpić go w wierszu innym obiektem albo uczynić NULL. Najpierw, usunięcie wiersza z obiektem LOB wygląda tak:

DELETE FROM Employee e
WHERE e.Ename = 'Jan Kowalski';

UPDATE Employee e SET e.Resume = Empty_CLOB()
  WHERE e.Ename = 'Jan Kowalski';

4.5 Podsumowanie

Zapoznaliśmy się ze sposobem przechowywania w bazie danych i posługiwania się w języku SQL:

• obiektami typów obiektowych definiowanych przez użytkownika oraz
• dużymi obiektami tekstowymi i binarnymi.

Wykład ten stanowi tylko wprowadzenie do tematu posługiwania się obiektami w bazie danych. Zachęcamy czytelnika do wykonania podanych poniżej zadań domowych oraz przestudiowania dokumentacji Oracle: Application Developer's Guide - Object-Relational Features i Application Developer's Guide - Large Objects (LOBs).

Rodzi się naturalne pytanie. Czy lepiej konstruować relacyjne czy obiektowe-relacyjne bazy danych?

• Po pierwsze, relacyjne bazy danych są łatwiejsze w użyciu i bardziej efektywne w działaniu, bo są prostsze oraz bardziej pasują do tabelkowego interfejsu większości aplikacji biurowych. Po drugie, relacyjne bazy danych są łatwiej przenaszalne jako zbiory tabel wartości – mają  reprezentację niezależną od bazy danych.
• Z kolei obiektowo-relacyjne (i czysto obiektowe) bazy danych dają możliwość bardziej naturalnego modelowania obiektów aplikacji takich jak osoby, faktury, towary razem z ich zachowaniem. Po drugie, zmniejszają one dystans między reprezentacją obiektów w bazie danych a reprezentacją obiektów w językach obiektowych, które są używane do pisania bardziej skomplikowanych aplikacji – ułatwia to posługiwanie się obiektami zarówno na poziomie koncepcyjnym jak i implementacyjnym.

Skoro więc trapią nas wątpliwości, który rodzaj baz danych wybrać, może wybierzemy trzeci rodzaj baz danych, które właśnie na naszych oczach powstają – tzw. bazy XMLowe. Wprowadzenie do nich jest tematem osobnego wykładu.
 

4.6 Słownik pojęć

typ obiektowy - definiowany przez użytkownika typ danych, który hermetyzuje strukturę danych łącznie z metodami potrzebnymi do operowania na strukturze danych. Można stosować dziedziczenie własności między typami obiektowymi i ich przesłanianie (powtórne definiowanie w podtypie).

tabela obiektowa - tabela, w której są zapisywane obiekty typu obiektowego na tej samej zasadzie co wiersze tabeli relacyjnej. To znaczy, wartości atrybutów obiektu tworzą wiersz, na którym działają metody określone w typie obiektowym.

typ referencyjny - typ wartości reprezentujący odwołania do obiektów w bazie danych. Jest obiektową alternatywą do wiązania tabel przez wartość klucza obcego.

kolekcja - typ wartości w bazie danych reprezentujący zbiory wartości prostych lub obiektów.

DBMS_LOB - pakiet z operacjami na dużych obiektach LOB (w Oracle).

4.7 Sprawdzenie wiedzy

1. W tabeli obiektowej Oracle są zapisywane:
   obiekty typu obiektowego języka programowania Java
   obiekty typu obiektowego Oracle
   wiersze tabeli relacyjnej

2. Wartością kolekcji Oracle może być:
   obiekt typu obiektowego
   tabela relacyjna
   tabela zagnieżdżona
   tablica wartości

3. Jakie konstrukcje stanowią alternatywę dla związków klucz obcy - klucz główny modelu relacyjnego:
   tabela obiektowa
   kolekcja
   referencja

4. Które typy należą do kategorii typów dużych obiektów LOB:
   LONG RAW
   BLOB
   CLOB
   NCLOB
   BFILE

5. Jakie konstrukcje są realizowane dla dużych obiektów LOB:
   lokalizator
   tabela zagnieżdżona
   katalog
   schemat

    

4.8 Zadania

1. Wykonaj kolejno wszystkie przykłady z wykładu.
2. Zbuduj obiektowo-relacyjną bazę zamówień, w której wszystkie pozycje danego zamówienia znajdują się na liście będącej wartością atrybutu obiektu zamówienia. Przyjmijmy, że maksymalna liczba pozycji na zamówieniu wynosi 20.
   1. Napisz i wykonaj instrukcje INSERT wpisujące przykładowe dane do bazy. Wprowadź co najmniej 2 klientów, co najmniej 5 towarów i co najmniej 4 zamówienia.
   2. Napisz zapytanie, które wypisuje wszystkie zamówienia z bazy.
   3. Napisz procedurę PL/SQL, która wypisuje wszystkie zamówienia z bazy - wzorując się na przykładzie dla wydziałów i projektów.
3. Napisz procedury PL/SQL:
   1. tworzącą pusty CLOB;
   2. dopisującą argument tekstowy do obiektu CLOB;
   3. wypisującą wartość CLOB na ekran.