Wykład 3

SQL - zaawansowane konstrukcje

 

Streszczenie

Na tym wykładzie kontynuujemy omawianie kolejnych obiektów i związanych z nimi instrukcji języka SQL. Przedstawiamy więc takie obiekty jak: perspektywy, synonimy, schematy, dziedziny atrybutów, asercje, tabele tymczasowe, sekwencje i klastry. Dodatkowo omawiamy problem reprezentowania w bazie danych metadanych (danych o danych) za pomocą słownika danych (inaczej nazywanego katalogiem systemowym) oraz podstawowy dla środowiska współbieżnego wykonywania operacji na bazie danych przez wielu użytkowników temat transakcji, blokad i poziomów izolacji.

 


3.1 Perspektywy

W sposób oczywisty dobrze byłoby zapamiętywać zapytania w bazie danych, aby je móc później użyć. Do tego celu służy pojęcie perspektywy. Perspektywa jest to wirtualna tabela. Jest określona przez zapytanie czyli instrukcję SELECT.
 
CREATE VIEW nazwa_perspektywy
AS zapytanie;

 
Można jej używać tak jakby była tabelą np. w instrukcji SELECT. Wiersze perspektywy nie są jednak przechowywane w bazie danych - mogą być tylko wyliczone na żądanie.
 

CREATE VIEW Urzędnicy (Empno, Ename, Sal)
AS SELECT Emp.Empno, Emp.Ename, Emp.Sal
   FROM Emp
   WHERE Emp.Job = 'CLERK';
W Standardzie podzapytanie jest ujmowane w nawiasy.
 
Perspektywy służą do dostosowania bazy danych do potrzeb różnych grup użytkowników. Stanowią widok, perspektywę, z jakiej dana klasa użytkowników widzi bazę danych. Różne grupy użytkowników mogą mieć odmienne "perspektywy" na dane w bazie danych.
 
Skoro perspektywy mają zastępować użytkownikom tabele, muszą także umożliwiać wykonywanie podobnych operacji, jakie są wykonywane na tabelach, a więc takich jak przeglądanie i wyszukiwanie danych, wstawianie, modyfikowanie i usuwanie danych z bazy danych.

Postulat dotyczący przeglądania i wyszukiwania danych jest w pełni spełniony. W instrukcjach SELECT perspektyw można używać na takich samych zasadach jak tabel, bez żadnych ograniczeń np.
 

SELECT u.Ename, u.Sal
FROM Urzędnicy u
WHERE u.Sal>5000
ORDER BY u.Ename;

Przedyskutujemy teraz możliwość wykonywania operacji INSERT, DELETE i UPDATE.

Przykład

Rozpatrzmy perspektywę:

CREATE VIEW Prac_sprzedaży
AS SELECT * FROM Emp
   WHERE Emp.Deptno= 20;
Używając tej perspektywy możemy przeprowadzać modyfikację danych w odniesieniu do pracowników z działu numer 20, np. możemy podnieść ich zarobki Sal o 10%:
UPDATE Prac_sprzedaży
SET Sal = Sal * 1.1;
Każdy system baz danych ogranicza zakres perspektyw, przez które można dokonywać zmian w bazie danych. W odniesieniu do Standardu i systemu Oracle mamy do czynienia z następującymi ograniczeniami:

1. w klauzuli SELECT nie może być słowa DISTINCT,

2. w klauzuli FROM może być tylko jedna nazwa tabeli lub jedna nazwa perspektywy - spełniająca definiowane kryteria - dotyczy to Standardu, gdyż Oracle umożliwia użycie więcej niż jednej tabeli,

3. na liście SELECT mogą znajdować się tylko nazwy kolumn,

4. w klauzuli WHERE nie może być podzapytania,

5. w zapytaniu nie mogą występować klauzule GROUP BY i HAVING.

Powyższe warunki zapewniają to, aby jednoznacznie był określony wiersz w tabeli, którego dotyczy zmiana. W Oracle możliwe jest dokonywanie ograniczonych zmian w bazie danych poprzez perspektywy zawierające złączenia. Można mianowicie w Oracle dokonywać zmian po stronie klucza obcego, ale nie głównego. Przy użyciu perspektywy:

CREATE VIEW Ed
AS SELECT e.Empno, e.Ename, d.Deptno, d.Loc
   FROM Emp e, Dept d
   WHERE e.Deptno = d.Deptno;

możliwe są zmiany (INSERT, DELETE, UPDATE) w odniesieniu do tabeli Emp, ale nie w odniesieniu do tabeli Dept. (Istotne przy tym jest aby kolumna Empno była kluczem głównym w tabeli EMP.)

Przykład

Przy użyciu instrukcji:

INSERT INTO Ed(Empno, Ename, Deptno)
VALUES (5600, 'Nowiński',20);

wstawimy pracownika o nazwisku 'Nowiński' do tabeli Emp ustalając jego numer działu na 20.

Istnieje też oczywiście możliwość usunięcia perspektywy z bazy danych. Instrukcja
 
DROP VIEW nazwa_perspektywy;
 
powoduje usunięcie perspektywy.

Tworzenie perspektyw z opcją sprawdzania

W celu zapewnienia ograniczenia danych operowanych przy użyciu perspektywy do danych określonych przez jej warunek WHERE, użyteczna jest dodatkowa opcja definicji perspektywy:
 
CREATE VIEW nazwa_perspektywy[(nazwa_kolumny,...)]
AS zapytanie
WITH CHECK OPTION;

 
Jeżeli została użyta opcja WITH CHECK OPTION, to przy wykonywaniu INSERT i UPDATE następuje sprawdzenie, czy wstawiany bądź modyfikowany wiersz spełnia warunek określony w klauzuli WHERE. Jeśli spełnia, operacja jest wykonywana. Jeśli nie spełnia, operacja nie zostanie wykonana.

Gwarantuje to dodatkową ochronę danych zapewniając, że zmiany dokonywane przez użytkowników przez perspektywę są wyłącznie ograniczone do zbioru danych, do których oglądania i modyfikowania jest uprawniony dany użytkownik. Jest to zgodne z ideą dopasowywania poziomu zewnętrznego do danej grupy użytkowników.

Przykład

Utwórz perspektywę pracowników będących na urlopie bezpłatnym.

CREATE VIEW Emp_na_urlopie_bezpłatnym
AS SELECT * 
   FROM Emp
   WHERE Emp.Sal = 0 OR Emp.Sal IS NULL
WITH CHECK OPTION;

Poprzez tę perspektywę modyfikacja danych jest możliwa tylko w stosunku do pracowników, których zarobki są równe 0 albo Null, ponieważ tylko tacy pracownicy są widoczni w tej perspektywie. Same zarobki mogą być jednak zmienione tylko na 0 albo Null, ponieważ po operacji warunek w klauzuli WHERE perspektywy też musi być spełniony. Oznacza to w praktyce zablokowanie zmiany zarobków poprzez tę perspektywę np. nie uda się zmiana wysokości zarobków wszystkim pracowników na urlopie bezpłatnym:

UPDATE Emp_na_urlopie_bezpłatnym
SET Sal = 10000;

Tworzenie perspektyw z opcją tylko do odczytu

Kolejnym elementem ochrony danych dostarczanym przez perspektywy jest opcja tylko do odczytu. Mianowicie można zabezpieczyć się przed dokonywaniem zmian poprzez perspektywę przez utworzenie jej z opcją WITH READ ONLY.

 
CREATE VIEW nazwa_perspektywy[(nazwa_kolumny,...)]
AS zapytanie
WITH READ ONLY;

 
Na przykład:

CREATE VIEW Pracownicy
AS SELECT * FROM Emp
   WITH READ ONLY;

Każdy uprawniony użytkownik może tylko oglądać dane o pracownikach bez możliwości wykonania wstawiania, modyfikowania czy usuwania wierszy.

Reasumując, perspektywy są obiektami, jakie udostępnia się konkretnym grupom użytkowników. Określają one widok na bazę danych zaprojektowany dla tej grupy użytkowników. Ułatwia to użycie bazy danych, jak również stanowi element ochrony przed niepowołanym lub nieprawidłowym dostępem do danych. Każdy użytkownik bazy danych ma dostęp tylko do danych dotyczących jego działalności w firmie. Ponadto jeśli w aplikacji korzystamy z perspektyw a nie z tabel to mamy zagwarantowaną niezależność logiczną danych tzn. po zmianie schematu tabel trzeba tylko ewentualnie zmienić definicje perspektyw a same aplikacje nie wymagają już zmiany.

Postulat modyfikowalności bazy danych przez perspektywy nie jest w pełni spełniony przez Standard SQL z 1992 roku. Do pełnej realizacji tego postulatu potrzebna jest dodatkowa konstrukcja - wyzwalacz INSTEAD OF - wprowadzona do Standardu SQL'1999 i omawiana w wykładzie 6.

Perspektywa zmaterializowana

Ze względu na użycie w rozproszonych bazach danych i w hurtowniach danych, kopię zawartości perspektywy można materializować tzn. zapisywać jako tabelę. Tego rodzaju perspektywa nosi nazwę perspektywy zmaterializowanej. Jej użycie zostanie przedstawione w wykładach 14 i 15.

 

Dokonamy teraz przeglądu najważniejszych dodatkowych konstrukcji języka SQL biorąc pod uwagę zarówno Standard, jak i system Oracle.
 


3.2 Tworzenie synonimów nazw tabel i perspektyw

Ze względu na możliwość sięgania do obiektów znajdujących się w innych bazach danych (również przez sieć), specyfikacje obiektów mogą być długie. Wygodnie jest więc zdefiniować krótką nazwę i używać jej zamiast długiej specyfikacji. Do tego właśnie celu służą synonimy.
 
CREATE SYNONYM nazwa_synonimu
FOR nazwa_tabeli_lub_perspektywy;

 
Przykład

Utwórz synonim tabeli Dept.

CREATE SYNONYM Dept
FOR Kadry.Dept@mojafirma.com.pl;
Instrukcja
 
DROP SYNONYM nazwa_synonimu;
 
powoduje usunięcie synonimu.

Synonimy uniezależniają aplikacje i użytkowników od zmian w ścieżkach do obiektów bazy danych np. gdy tabela z danymi o pracownikach wędruje z jednego konta, lub bazy danych na inne konto, lub do innej bazy danych.
 


3.3 Transakcje

Z charakteru aplikacji korzystających z baz danych wynika, że elementarną operacją na bazie danych często nie jest pojedyncza instrukcja SQL, ale ciąg takich instrukcji, nazywany transakcją. Na przykład, transfer  pieniędzy z jednego konta na drugie jest elementarną operacją z punktu widzenia aplikacji bankowej. Natomiast w SQL, użylibyśmy w tym celu co najmniej dwóch instrukcji UPDATE:
 

UPDATE Konta
SET Saldo = Saldo - 1000
WHERE Id_klienta = 1001;

UPDATE Konta
SET Saldo = Saldo + 1000
WHERE Id_klienta = 9999;

Załóżmy teraz, że pierwsza instrukcja wykonała się, a druga nie może zostać wykonana, na przykład z powodu tego, że 9999 jest błędnym identyfikatorem klienta albo z powodu awarii komputera. Z punktu widzenia aplikacji dane znalazły się w stanie niespójnym i pozostaje tylko jedna możliwość - wycofać (anulować) wynik pierwszej instrukcji UPDATE. Do tego celu służy instrukcja ROLLBACK.

Symetryczną operacją do ROLLBACK jest COMMIT, która zatwierdza zmiany w bazie danych bez możliwości późniejszego ich wycofania. Reasumując, użytkownik lub aplikacja po wykonaniu instrukcji wchodzących w skład transakcji, albo przekazuje do wykonania instrukcję COMMIT - zatwierdź zmiany i zakończ transakcję, albo przekazuje do wykonania instrukcję ROLLBACK - wycofaj zmiany i zakończ transakcję.

Istnieje możliwość wycofania w tył części ostatnio wykonywanych instrukcji, pod warunkiem, że zaznaczyliśmy tę część stawiając przed nią tzw. punkt zachowania (ang. savepoint):
 
SAVEPOINT punkt;

 
Instrukcja wycofania ma wtedy postać:
 
ROLLBACK TO SAVEPOINT punkt;

 
Uwaga W Oracle istnieje możliwość zagnieżdżania transakcji. Wymaga to użycia języka PL/SQL i dyrektywy PRAGMA (szczegóły w dalszych wykładach). Zagnieżdżona transakcja nazywa się autonomiczną. Oto przykład:
....
DECLARE
   PRAGMA autonomous_transaction;
BEGIN
   DELETE FROM Emp WHERE Emp.Job='GHOSTBUSTER';
   COMMIT;
END;
.......
Instrukcje SQL znajdujące się w bloku między słowami kluczowymi DECLARE i END są wykonywane jako niezależna, autonomiczna transakcja wewnątrz innej transakcji.

W trakcie wykonywania transakcji użytkownika SZBD blokuje dostęp innym  użytkownikom do obiektów bazy danych, których dotyczą operacje w transakcji. Szczegóły implementacyjne zostaną przedstawione w dalszych wykładach na temat budowy SZBD.
 


3.4 Blokowanie dostępu do tabel (Oracle)

Oracle zakłada automatycznie blokady na wierszach, na których są przeprowadzane operacje zmieniające te wiersze (INSERT, DELETE, UPDATE).

Programista może sam założyć blokadę na tabelę ograniczając możliwości jej zmiany przez innych użytkowników. Do tego celu służy następująca instrukcja.
 
LOCK TABLE nazwa_tabeli
IN [SHARE|EXCLUSIVE] MODE
[NOWAIT];
 
Podstawowymi trybami blokowania tabeli w bazie danych są:

Opcja NOWAIT specyfikuje, aby nie czekać na założenie blokady, gdy nie można jej uzyskać w danej chwili.

Przykład założenia wyłącznej blokady na całą tabelę:

LOCK TABLE Klienci
IN EXCLUSIVE MODE 
-- zablokowanie całej tabeli w trybie wyłącznym
NOWAIT;

W Oracle jest przyjęta zasada, że każdy użytkownik - mający odpowiednie uprawnienia - może odczytywać aktualne, zatwierdzone dane w tabeli, niezależnie od założonych blokad (dane te mogą być w trakcie zmieniania przez inne transakcje). Zatem może się zdarzyć sytuacja, że gdy za chwilę użytkownik odczyta ponownie te same dane, zobaczy, że w międzyczasie zostały one zmienione.

Zdjęcie blokady następuje przez wykonanie COMMIT lub ROLLBACK.

W systemie Oracle jest możliwe założenie blokady wyłącznej na wiersze będące wynikiem określonego zapytania. Do tego celu służy specjalna, dodatkowa klauzula dołączana na koniec instrukcji SELECT:
 
SELECT nazwa_kolumny,. . .
. . .
FOR UPDATE [NOWAIT];
 
Opcja NOWAIT oznacza kontynuowanie aplikacji gdy nie można założyć blokady na wiersze.

Wykonanie COMMIT lub ROLLBACK kończy blokadę wskazanych wierszy.

Przykład założenia wyłącznej blokady na wybrane wiersze:

SELECT * FROM Klienci
WHERE Kraj = 'Polska'
FOR UPDATE 
-- założenie blokady wyłącznej na klientów z Polski i współdzielonej na tabelę Klienci
NOWAIT;
-- gdy nie można założyć blokady, nie czekaj

Jest też możliwe "zamrożenie" widoku bazy danych do jej aktualnego stanu dla następnie wykonywanych w ramach transakcji instrukcji SELECT. Instrukcje SELECT korzystają wówczas jakby ze "zdjęcia migawkowego" bazy danych wykonanego w tym momencie. Zmiany wprowadzane w tym czasie do bazy danych nie są widoczne dla tych instrukcji SELECT. Instrukcja

SET TRANSACTION READ ONLY;
powinna być pierwszą instrukcją w transakcji – złożonej z ciągu instrukcji SELECT i LOCK TABLE. ''Odmrożenie'' bazy danych następuje przez wykonanie polecenia COMMIT.

Dla zachowania symetrii, rozpoczęcie transakcji typu nie READ ONLY można zacząć (nie jest to wymagane) od

SET TRANSACTION READ WRITE;

Poziomy izolacji transakcji

Poziom izolacji określa jakie zjawiska związane ze współbieżnym wykonywaniem transakcjami są dopuszczalne:

Standard ANSI/ISO definiuje cztery poziomy izolacji określające jakie zmiany widzą transakcje - zmiany dokonywane przez inne współbieżnie działające transakcje.

Poziom izolacji Niezatwierdzony odczyt Niepowtarzalny odczyt Fantomy
READ UNCOMMITED TAK TAK TAK
READ COMMITED NIE TAK TAK
REPEATABLE READS NIE NIE TAK
SERIALIZABLE NIE NIE NIE

 

Ustawianie poziomu izolacji w SQL

Programista, w zależności od potrzeb, może określić wymagany poziom izolacji transakcji. Składnia instrukcji określającej poziom izolacji jest następująca (są to wspólne opcje występujące zarówno w Standardzie, jak i w Oracle):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
.......
COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITED;
.......
COMMIT;

Wybierając poziom SERIALIZABLE (izolowana transakcja) mamy gwarancję, że transakcja działa na spójnych, niezmiennych na czas działania transakcji, danych. Jest to domyślny poziom w Standardzie.

Na poziomie READ COMMITED (transakcja z odczytem zatwierdzonych danych) transakcja działa na zmienianych jednocześnie przez innych użytkowników danych (ale dopiero po zatwierdzeniu przez nich zmian). Zapewnia większą współbieżność działania systemu baz danych. Jest to domyślny poziom w Oracle.

Wybierając drugi poziom, dane którymi zajmuje się transakcja, mogły być, już w czasie gdy transakcja była wykonywana, zmieniane przez inne transakcje (po czym zatwierdzone). Jeśli jest to dopuszczalne z punktu widzenia poprawności aplikacji, to ten drugi sposób, jako zapewniający większą współbieżność działania systemu baz danych, powinien być preferowany.

Nie wszystkie aplikacje będą działać poprawnie w tym trybie. Na przykład, jeśli aplikacja dokonująca rezerwacji biletów, najpierw sprawdza czy jest wolne miejsce w samolocie, następnie powiadamia klienta, że znalazła wolne miejsce, a następnie próbuje dokonać rezerwacji znalezionego miejsca, to w tym momencie miejsce w samolocie może nie być już wolne.

Pracując w trybie READ COMMITED, gdy nie chcemy aby wartości w wierszu zmieniły się, wystarczy przy odczytywaniu wiersza jednocześnie dokonać blokady tego wiersza. Możemy przed instrukcją SELECT postawić punkt zachowania (savepoint), aby w przypadku gdy wartości w wierszu nam nie odpowiadają dokonać wycofania do tego punktu zachowania. Unikniemy w ten sposób blokowania wiersza innym użytkownikom.
 


3.5 Słownik danych - informacja o obiektach bazy danych (w wersji używanej przez Oracle)

Słownik danych jest zbiorem informacji o obiektach bazy danych. Jest używany zarówno przez system zarządzania bazą danych, jak i przez użytkowników. Użytkownik ma uprawnienia tylko do odczytu informacji ze słownika danych. Słownik danych ma postać zbioru tabel i perspektyw.

Oto przykładowe perspektywy słownika danych Oracle:

1. z przedrostkiem User - informacja o wszystkich obiektach, których dany użytkownikiem jest właścicielem - o tabelach: User_Tables (Tabs), o tabelach i kolumnach: User_Tab_Columns (Cols), o więzach spójności User_Constraints oraz User_Cons_Columns, o indeksach User_Indexes (Ind) oraz User_Ind_Columns, o synonimach User_Synonyms (Syn), o perspektywach User_Views;

2. z przedrostkiem All - dotyczy wszystkich obiektów, do których użytkownik ma uprawnienia;

3. z przedrostkiem Dba - informacja o obiektach dostępnych dla administratorów systemu.

Najpowszechniej stosowaną przez użytkowników perspektywą jest perspektywa User_Tables.

Przykład

Instrukcja:

SELECT u.Table_Name
FROM User_Tables u;
wypisuje nazwy tabel, których właścicielem jest dany użytkownik. Instrukcja
SELECT u.Column_Name, u.Data_Type
FROM User_Tab_Columns u
WHERE u.Table_Name = 'EMP';

wypisuje nazwy i typy danych kolumn w tabeli Emp. To znaczy wypisuje tabelę wartości:

COLUMN_NAME                    DATA_TYPE
------------------------------ ----------------------
EMPNO                          NUMBER
ENAME                          VARCHAR2
JOB                            VARCHAR2
MGR                            NUMBER
HIREDATE                       DATE
SAL                            NUMBER
COMM                           NUMBER
DEPTNO                         NUMBER
 

3.6 Przyznawanie i odbieranie uprawnień w bazie danych

Do podstawowych obowiązków administratora bazy danych należy zakładanie kont użytkowników oraz przyznawanie im uprawnień do korzystania z zasobów i obiektów bazy danych. Zarówno w Standardzie, jak i Oracle mamy instrukcję do przyznawania uprawnień:
 
GRANT uprawnienie, ... 
ON nazwa_obiekt
TO użytkownik, ...|PUBLIC
[WITH GRANT OPTION]
 
gdzie:

  1. uprawnienie określa rodzaj operacji, do wykonywania której użytkownik uzyskuje uprawnienie, np. ALL PRIVILEGES, SELECT, DELETE, INSERT, UPDATE, REFERENCES (w przypadku INSERT, UPDATE, REFERENCES można ograniczyć uprawnienia do podzbioru kolumn), a w Oracle także INDEX, ALTER i EXECUTE;
  2. obiektem może być tabela, perspektywa a w Oracle także inne obiekty jak sekwencja, procedura, funkcja, pakiet;
  3. WITH GRANT OPTION oznacza, że obdarowany użytkownik uzyskane uprawnienia może przekazywać innym użytkownikom;
  4. PUBLIC oznacza przyznanie uprawnienia wszystkich użytkownikom.

Przykład
 
Nadaj księgowej uprawnienia dostępu do tabeli Emp w zakresie wykonywania instrukcji SELECT oraz również instrukcji UPDATE, ale tylko w odniesieniu do kolumny Sal.

GRANT SELECT, UPDATE(Sal) 
ON Emp
TO Księgowa;

Uprawnienia odwołuje się za pomocą instrukcji odwrotnej do instrukcji GRANT.
 
REVOKE uprawnienie, ... 
ON nazwa_obiektu 
FROM użytkownik, ...|PUBLIC;

W Oracle jest też postać instrukcji GRANT przyznającej uprawnienia systemowe do wykonywania instrukcji, np. CREATE TABLE, CREATE VIEW, CREATE SESSION, czyli uprawnienie do logowania się do bazy danych.

Administrator bazy danych Oracle tworzy konto użytkownika za pomocą instrukcji (jak to robić, Standard pozostawia tę kwestię nieokreśloną):
 
CREATE USER użytkownik IDENTIFIED BY hasło;
 
i w pierwszym kroku nadaje uprawnienia systemowe np.
 
GRANT CREATE SESSION, CREATE TABLE TO użytkownik;
 
Konto użytkownika może zostać usunięte (w Oracle) za pomocą instrukcji
 
DROP USER użytkownik [CASCADE];

 
Opcja CASCADE jest wymagana, gdy użytkownik jest właścicielem obiektów w bazie danych. Wtedy system usuwa użytkownika razem ze wszystkimi jego obiektami.

W celu ułatwienia przyznawania uprawnień dużej liczbie użytkowników, w Oracle zostało wprowadzone pojęcie roli, to jest powiązania grupy użytkowników ze zbiorem uprawnień. Z jednej strony, konkretnym użytkownikom przyznaje się uprawnienie do danej roli (przyznając im tę rolę). Z drugiej strony, roli przyznaje się konkretne uprawnienia do wykonywania operacji w bazie danych.

Przykład
 
Utwórz role Dyrektora i Urzędnika, przydziel im uprawnienia i zalicz do nich konkretnych użytkowników:

CREATE ROLE Dyrektor;

CREATE ROLE Urzędnik;

GRANT SELECT ON Emp TO Dyrektor;

GRANT SELECT, UPDATE ON Emp TO Urzędnik;

GRANT Dyrektor TO King, Leon;

GRANT Urzędnik TO Liza, Ewa, Marta;

Trzy role są z góry zdefiniowane:

Przy tworzeniu konta nowego użytkownika, któremu chcemy przyznać swobodę zakładania i operowania obiektami na jego koncie, przydziela się najczęściej role: CONNECT i RESOURCE – umożliwiające logowanie się na konto i pełne korzystanie z instrukcji SQL w odniesieniu do jego własnych obiektów.

Natomiast w przypadku, gdy użytkownik korzysta z gotowej aplikacji klienckiej przyznaje mu się tylko rolę CONNECT, a pozostałe role konieczne do zrealizowania aplikacji włącza się i wyłącza w trakcie realizacji aplikacji bez jego udziału. Mianowicie instrukcja SET ROLE powoduje wyłączenie wszystkich aktualnie dostępnych ról i włączenie ról określonych w instrukcji SET ROLE. Np. wykonanie instrukcji

SET ROLE agent;

spowoduje wyłączenie wszystkich dotychczasowych ról i włączenie tylko jednej o nazwie agent.
 


3.7 Schemat

Transakcje dotyczą wykonywania ciągu instrukcji INSERT, DELETE i UPDATE. Odpowiednikiem transakcji dla instrukcji definiujących obiekty i uprawnienia jest pojęcie schematu. Schemat tworzy grupę powiązanych obiektów. Jest realizowany za pomocą instrukcji:
 
CREATE SCHEMA nazwa_schematu
ciąg instrukcji CREATE TABLE, CREATE VIEW i GRANT (bez rozdzielających średników);

 
Przy wykonywaniu instrukcji CREATE SCHEMA ciąg składowych instrukcji jest realizowany jako jedna transakcja. To znaczy, albo są wykonywane wszystkie instrukcje albo żadna. W instrukcjach składowych mogą być odwołania cykliczne REFERENCES między tabelami (tabela A zawiera odwołanie do tabeli B, a tabela B zawiera odwołanie do tabeli A) - co przy normalnej implementacji nie byłoby możliwe do zrealizowania za pomocą samych instrukcji CREATE TABLE (bez ALTER TABLE).

W Standardzie jest też instrukcja (nie ma jej w Oracle)
 
DROP SCHEMA nazwa_schematu CASCADE;

która usuwa wszystkie obiekty danego schematu z bazy danych.

W Standardzie nie występuje w sposób jawny pojęcie bazy danych, za to obok pojęcia schematu występuje jeszcze pojęcie katalogu jako zbioru schematów oraz klastra jako zbioru katalogów. Klaster może być traktowany jako rozproszona baza danych składająca się ze zbioru katalogów, do których użytkownik ma dostęp w ramach jednej sesji. Schemat ma jednego właściciela. W skład katalogu mogą wchodzić schematy mające różnych właścicieli. Dla każdego katalogu powinien być określony jeden schemat nazywany schematem informacyjnym pełniący rolę słownika danych dla całego katalogu.
 


3.8 Sesje i połączenia

Zarówno w Standardzie, jak i w Oracle istnieją możliwości zmiany ustawień dla danej sesji użytkownika.

W Standardzie z każdą sesją jest związany zbiór połączeń z różnymi bazami danych, z których tylko jedno jest aktywne. Stosowane są następujące polecenia:

Niektóre własności sesji mogą być zmieniane w trakcie połączenia. W Oracle zmiany wprowadza się za pomocą instrukcji ALTER SESSION, na przykład:

    ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;

powoduje ustawienie poziomu izolacji transakcji w sesji na SERIALIZABLE.
 


3.9 Dziedziny atrybutów (Standard)

Dziedziny atrybutów to typy danych definiowane łącznie z więzami spójności.
 

Przykład

Zdefiniujmy dziedzinę numerów departamentów:

CREATE DOMAIN Dept# CHAR(3)
CHECK (VALUE IN ('A00','B01','D01','D11','D21', 'XXX'))
DEFAULT 'XXX';
Następnie możemy dziedzinę Dept# wielokrotnie używać przy określaniu typów danych kolumn w tabelach:
CREATE TABLE Dept
(DeptNo DOMAIN Dept# PRIMARY KEY,
...);
Występują także instrukcje ALTER DOMAIN nazwa_dziedziny i DROP DOMAIN nazwa_dziedziny.
 

3.10 Asercje (Standard)

Asercje to więzy spójności definiowane poza instrukcjami CREATE TABLE i ALTER TABLE.

Możliwe jest sformułowanie więzów spójności dotyczących całej tabeli

CREATE ASSERTION maxempl
CHECK (1000 <= SELECT COUNT (*)
               FROM Emp);
Więzy spójności można usunąć za pomocą instrukcji
 
DROP ASSERTION nazwa_asercji;
 
 

3.11 Tabele tymczasowe

Zarówno w Standardzie jak i w Oracle występuje konstrukcja tabeli tymczasowej, która jest częścią schematu bazy danych, ale której zawartość jest niszczona przy każdym zakończeniu sesji użytkownika (opcja ON COMMIT PRESERVE ROWS) lub już przy każdej operacji COMMIT (opcja ON COMMIT DELETE ROWS).

Przykład

Utwórz tabelę tymczasową
 

CREATE GLOBAL TEMPORARY TABLE Prac_zatrudniani_dziś
  (Nr_kolejny INTEGER PRIMARY KEY,
  Imię VARCHAR(40) NOT NULL,
  Nazwisko VARCHAR(50) NOT NULL,
  Informacja VARCHAR(1000))
ON COMMIT PRESERVE ROWS;

Tabele tymczasowe stosuje się do zapisu wyników, które następnie są używane wielokrotnie w ramach tej samej transakcji lub sesji. Wyniki mogą być wyliczane przez więcej niż jedną instrukcję SELECT.
 


3.12 Generowanie jednoznacznych numerów (Oracle)

W systemie Oracle jest specjalny obiekt - sekwencja (przechowywany w bazie danych), który służy do generowania jednoznacznych numerów dla wartości kluczy głównych i jednoznacznych. Oto składnia tworzenia sekwencji:
 
CREATE SEQUENCE nazwa_sekwencji
[INCREMENT BY k]
[START WITH n];
 
gdzie n jest pierwszą generowaną liczbą naturalną, a k jest wartością, o jaką wzrasta kolejno generowany numer.

Sekwencji używa się zgodnie ze składnią:

nazwa_sekwencji.NextVal - generowanie kolejnej wartości w sekwencji,

nazwa_sekwencji.CurrVal - ostatnio wygenerowana wartość w sekwencji.

Instrukcja
 
DROP SEQUENCE nazwa_sekwencji;
 
usuwa sekwencję.

Poniżej podajemy przykład użycia sekwencji do generowania jednoznacznych identyfikatorów przy tworzeniu działu i jego pracowników.

Przykład
 
Utwórz dwie sekwencje generujące jednoznaczne numery dla działów i dla pracowników.

CREATE SEQUENCE Dept_seq
INCREMENT BY 10
START WITH 10;

CREATE SEQUENCE Emp_seq
INCREMENT BY 1
START WITH 1000;

Oto instrukcja tworząca kolejny dział:
INSERT INTO Dept(Deptno, Dname, Loc)
VALUES (Dept_seq.NextVal, 'Sprzedaż', 'Warszawa');
Oto instrukcja tworząca kolejnego pracownika z wykorzystaniem wygenerowanego przed chwilą numeru działu:
INSERT INTO Emp VALUES (Emp_seq.NextVal, 'Stec', 
'MANAGER', NULL, '20-JAN-90', 10000, 0 , Dept_seq.CurrVal);

3.13 Klaster (Oracle)

Klaster to zbiór powiązanych ze sobą tabel, które zwykle w aplikacji są przetwarzane jednocześnie i dlatego wskazane jest aby ich wiersze były przechowywane obok siebie na dysku. Powiązanie tabel odbywa się za pomocą kolumn o wspólnych wartościach. Kolumny te tworzą indeks klastra. Najpierw tworzy się klaster, następnie tabele w klastrze i w końcu indeks klastra. Dopiero w tym momencie można rozpocząć wstawianie wierszy do tabel w klastrze.

Oto przykład klastra dla tabel Dept i Emp. Indeksem klastra, względem którego gromadzone są obok siebie na dysku wiersze tych tabel, jest numer działu Deptno. A więc razem jest trzymany wiersz opisujący dział, jak i wiersze pracowników przypisanych do tego działu.
 

CREATE CLUSTER Personnel
(Department_number NUMBER(2));

CREATE TABLE Dept
(Deptno NUMBER(2) PRIMARY KEY,
 Dname VARCHAR2(9), 
 Loc VARCHAR2(9))
CLUSTER Personnel (Deptno);

CREATE TABLE Emp
(Empno NUMBER PRIMARY KEY,
 Ename VARCHAR2(10) NOT NULL,
 Job VARCHAR2(9),
 Mgr NUMBER REFERENCES Emp(Empno),
 Hiredate DATE,
 Sal NUMBER(10,2),
 Comm NUMBER(9,0),
 Deptno NUMBER(2)NOT NULL REFERENCES Dept(Deptno))
CLUSTER Personnel (Deptno);

Zanim rozpocznie się korzystać z tabel w klastrze należy explicite utworzyć indeks klastra:

CREATE INDEX Idx_personnel ON CLUSTER Personnel;

W tym momencie można zacząć wstawiać dane do obu tabel znajdujących się w utworzonym klastrze.

 


3.14 Podsumowanie

W kolejnych trzech wykładach przedstawiliśmy podstawowe konstrukcje języka SQL zarówno w wersji Standardu ISO1992, jak i Oracle. Znajomość tych konstrukcji jest nieodzowna przy tworzeniu aplikacji baz danych. Więcej informacji o środowisku tworzenia aplikacji w systemie Oracle znajduje się w następnych wykładach.
 


3.15 Słownik pojęć

perspektywa - wirtualna tabela czyli tabela, której źródłem wartości są inne tabele w bazie danych. Jest określona przez zapytanie czyli instrukcję SELECT. Można ją używać tak jakby była tabelą (np. w instrukcji SELECT). Wiersze perspektywy nie są jednak przechowywane w bazie danych - mogą być tylko wyliczone na żądanie.

perspektywa z opcją sprawdzania - opcja ta zapewnia ograniczenie danych operowanych przy użyciu perspektywy do danych określonych przez jej warunek WHERE.

perspektywa z opcją tylko do odczytu - opcja ta zapewnia ograniczenie użycia perspektywy tylko do odczytywania danych bez możliwości ich zmiany. Stanowi element ochrony danych w bazie danych.

synonim nazwy tabeli lub perspektywy - skrócona nazwa dla obiektu bazy danych.

transakcja - ciąg instrukcji SQL odpowiadający jednostkowej operacji biznesowej jak np. przelanie pieniędzy z konta na konto.

blokada - ograniczenie użycia obiektu aż do chwili zakończenie transakcji, która założyła tę blokadę. Podstawowe dwa rodzaje blokad to: wyłączna (do wykonania zapisu na obiekcie) oraz współdzielona (do uniemożliwienia wprowadzania zmian przez innych).

poziom izolacji - stopień dopuszczalnej interakcji między dwiema transakcjami przy współbieżnym korzystaniu z tych samych obiektów.

słownik danych - informacje o danych w bazie danych. Są one zapisywane w wydzielonych tabelach bazy danych tak jak inne dane. Są udostępniane użytkownikom za pomocą perspektyw tylko do odczytu.

uprawnienie - przydzielone użytkownikowi prawo do wykonywania dokładnie określonych operacji na obiektach bazy danych.

schemat - tworzy grupę powiązanych obiektów.

sesja, połączenie - sesja reprezentuje pracę użytkownika ze zbiorem baz danych; z każdą sesją jest związany zbiór połączeń z różnymi bazami danych, z których tylko jedno jest aktywne.

dziedzina atrybutów - typ danych definiowany łącznie z więzami spójności.

asercja - więzy spójności definiowane poza instrukcjami CREATE TABLE i ALTER TABLE.

tabele tymczasowa - tabela, która jest częścią schematu bazy danych, ale której zawartość jest niszczona przy każdym zakończeniu sesji użytkownika (opcja ON COMMIT PRESERVE ROWS) lub już przy każdej operacji COMMIT (opcja ON COMMIT DELETE ROWS).

sekwencja - generator jednoznacznych identyfikatorów w Oracle.

klaster - struktura fizycznego przechowywania danych, w której kilka tabel jest zebranych razem według wartości wspólnej kolumny (kolumn) - lub wartości funkcji haszującej.
 


3.16 Zadania

  1. Utwórz perspektywę, w której dla każdej lokalizacji (Loc) jest podana liczba departamentów oraz liczba pracowników zatrudnionych w tych departamentach.
     
  2. Utwórz perspektywę, w które zebrane są wszystkie dane dotyczące pojedynczej osoby – mianowicie tej która korzysta z perspektywy. Dane powinny pochodzić zarówno z tabeli Emp jak i pozostałych tabel Dept i Salgrade. Zakładamy, że identyfikator użytkownika (dostępny za pomocą stałej user – sprawdź wynik zapytania: SELECT User FROM Dual;) jest taki sam jak wartość Ename. Utwórz wiersz w tabeli Emp ze swoim identifikatorem sxxxx jako Ename. Napisz instrukcję SELECT wykorzystującą zbudowaną perspektywę.
  3. Ułóż perspektywę, która wyświetla nazwy tabel, których jesteś właścicielem razem z ich liczbą kolumn oraz liczbą wierszy.
     
  4. Zidentyfikuj grupy użytkowników w bazie danych dla firmy dokonującej sprzedaży produktów. Dla każdej grupy użytkowników zdefiniuj  zestaw wymaganych perspektyw. Oto logiczny schemat bazy danych tej firmy:
    Pracownicy(Id_prac, Imię, Nazwisko, Data_urodzenia, Adres)
    Zatrudnienie(Id_prac, Data_pocz, Stanowisko, Data_koniec, Zarobki)
    Produkty(Id_produktu, Nazwa, Cena)
    Klienci(Id_klienta, Imie, Nazwisko, Adres)
    Sprzedaż(Id_sprzedaży, Id_klienta, Id_towaru, Ilość, Data, Id_prac)
  5. W schemacie bazy danych zadania 4 dokonano następujących zmian. Dodano tabelę Kategorie(Id_kat, Nazwa) oraz dodano kolumnę Id_kat do tabeli Produkty tzn. Produkty(Id_produktu, Nazwa, Cena) Dodano tabelę Premie(Id_prac, Data, Premia) Przeanalizuj jakie zmiany spowoduje to w definicji zidentyfikowanych poprzednio perspektyw.
  6. Aplikacja rezerwacji miejsc w samolocie działa w następujący sposób. Mając określony lot i liczbę miejsc do zarezerwowania, aplikacja rozpoczyna transakcję bazodanową od sprawdzenia czy można zarezerwować żądaną liczbę miejsc. Jeśli można, dokonuje rezerwacji i wykonuje COMMIT transakcji. Następnie wysyła klientowi e-mail potwierdzający wykonanie rezerwacji. Podaj instrukcje SQL w Oracle tworzące transakcję. Na jakim poziomie izolacji powinna być uruchomiana transakcja aby aplikacja działała poprawnie.
  7. Aplikacja dokonująca przelewu z konta na konto działa w następujący sposób. Mając dane numery rachunków oraz sumę przelewu, aplikacja sprawdza czy są określone rachunki o podanych numerach, następnie sprawdza czy na pierwszym rachunku jest wystarczająca kwota do dokonania przelewu. Jeśli tak, z pierwszego rachunku zostaje zdjęta kwota przelewu. Następnie, do drugiego rachunku zostaje dodana kwota przelewu. Podaj instrukcje SQL w Oracle tworzące transakcję. Na jakim poziomie izolacji powinna być uruchomiana transakcja aby aplikacja działała poprawnie.
  8. Dla bazy danych składanych przez klientów zamówień przeanalizuj możliwości zastosowania klastrów tabel.


Strona przygotowana przez Lecha Banachowskiego. Ostatnia aktualizacja - 12/19/06 .