Zakładamy, że czytelnik zna zarówno język SQL jak i PL/SQL w zakresie książki: "Systemy baz danych. Wykłady i ćwiczenia", L.Banachowski, E.Mrówka-Matejewska, K.Stencel, Wyd. PJWSTK, 2004. W tym wykładzie przypomnimy tylko bardziej zaawansowane, ale ważne dla zrozumienia działania SZBD, pojęcia języka SQL, w tym: perspektywy, transakcje, blokady, poziomy izolacji, słownik danych i wyzwalacze.
Perspektywa to wirtualna tabela. 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 - są wyliczane na żądanie.
CREATE VIEW Urzędnicy (Empno, Ename, Sal)
AS SELECT Emp.Empno, Emp.Ename, Emp.Sal
FROM Emp
WHERE Emp.Job = 'CLERK';| 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;Dynamiczne perspektywy
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;UPDATE Prac_sprzedaży
SET Sal = Sal * 1.1;1. w klauzuli SELECT nie może być słowa kluczowego 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órej 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, e.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; |
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 podzapytanie 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ę nie uda się zmiana wysokości zarobków pracownika o nazwisku 'Kowalski', jeśli jego Zarobki są równe 0 lub Null.
UPDATE Emp_na_urlopie_bezpłatnym
SET Sal = 10000
WHERE Ename = 'Kowalski';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 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, korzystanie z perspektyw przez aplikacje gwarantuje niezależność logiczną danych po zmianie schematu tabel zmieniamy tylko definicje perspektyw a same aplikacje nie wymagają już zmiany.
Postulat modyfikowalności przez perspektywy nie jest w pełni spełniony przez Standard SQL z 1992. Do pełnej realizacji tego postulatu potrzebna jest dodatkowa konstrukcja - wyzwalacz - wprowadzona do Standardu SQL i omawiana w dalszej części tego wykładu.
W Oracle są też lokalne perspektywy definiowane do wykonania pojedynczej instrukcji SQL. Pozą tą instrukcją są one niewidoczne. Oto przykład:
WITH
dept_costs AS
(SELECT dname, SUM(sal) dept_total
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY dname),
avg_cost AS
(SELECT SUM(dept_total)/COUNT(*) aveg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total > (SELECT aveg FROM avg_cost)
ORDER BY dname;Konstrukcja ta umożliwia rekurencyjną definicję lokalnej perspektywy, co do tej pory było niemożliwe w SQL.
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. Synonimy mogą być też użyteczne w sytuacji, gdy istniejące
aplikacje korzystają z innej nazwy obiektu niż aktualna nazwa (np. po jej
zmianie). Aby nie zmieniać kodu aplikacji wygodniej jest wprowadzić nowy
synonim. Stanowi to element niezależności logicznej danych.
| 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;| DROP SYNONYM nazwa_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.
Z charakteru aplikacji korzystających z baz danych wynika, że elementarną operacją na bazie danych nie jest często pojedyncza instrukcja SQL, ale ciąg takich instrukcji, nazywany transakcją. Na przykład, przelanie 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ć 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ę.
Zagnieżdżone wycofywanie transakcji do SAVEPOINT
Istnieje możliwość wycofania w tył części ostatnio
wykonywanych instrukcji, pod warunkiem, że zaznaczyliśmy tę część
stawiając przed nią etykietę nazywana punktem zachowania (ang. savepoint):
|
|
Zagnieżdżone transakcje
W Oracle istnieje możliwość zagnieżdżania transakcji. Wymaga to użycia języka PL/SQL i dyrektywy PRAGMA. Zagnieżdżona transakcja nazywa się autonomiczną. Oto przykład:
....
DECLARE
PRAGMA autonomous_transaction;
BEGIN
DELETE FROM Emp WHERE Emp.Job='GHOSTBUSTER';
COMMIT;
END;
.......
ROLLBACK;PRAGMA autonomous_transaction. Natomiast efekt wykonania instrukcji
znajdujących się przed tym blokiem zostanie anulowany
później występującą instrukcją ROLLBACK.W trakcie wykonywania transakcji użytkownika SZBD blokuje dostęp innym użytkownikom do obiektów bazy danych, których dotyczą operacje w transakcji.
Implementacja transakcji przez SZBD musi spełniać cztery aksjomaty ACID (atomowość-spójność-izolacja-trwałość). Szczegóły zostaną przedstawione w dalszych wykładach na temat budowy SZBD.
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 zmian przez innych użytkowników. Do tego celu służy następująca instrukcja.
| LOCK TABLE nazwa_tabeli IN [SHARE|EXCLUSIVE] MODE [NOWAIT]; |
Opcja NOWAIT specyfikuje, aby nie czekać na założenie blokady, gdy nie można jej uzyskać w danej chwili.
Zdjęcie blokady następuje przez wykonanie COMMIT lub ROLLBACK.
Klauzula FOR UPDATE
W systemie Oracle jest możliwe założenie blokady 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]; |
Instrukcja SET TRANSACTION READ ONLY
Jest też możliwe "zamrożenie" widoku bazy danych do jej aktualnego stanu dla następnie wykonywanych 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; |
Dla zachowania symetrii, rozpoczęcie transakcji typu nie READ ONLY można zacząć (nie jest to wymagane) od
| SET TRANSACTION READ WRITE; |
Dla konkretnej transakcji użytkownik może określić jej poziom izolacji od innych współbieżnie wykonywanych transakcji. Bierze się pod uwagę następujące cechy związane ze współbieżnie wykonywanymi transakcjami:
Standard ANSI/ISO definiuje cztery poziomy izolacji w zależności od ustawienia powyższych trzech opcji, zobacz Tab. 3.1.
| Poziom izolacji | Niezatwierdzony odczyt | Niepowtarzalny odczyt | Fantomy |
| Read Uncommited | TAK | TAK | TAK |
| Read Committed | NIE | TAK | TAK |
| Repeatable Reads | NIE | NIE | TAK |
| Serializable | NIE | NIE | NIE |
Tab. 3.1 Definicja czterech poziomów izolacji.
Ustawianie poziomu izolacji w SQL
Programista, w zależności od potrzeb, może określić wymagany poziom izolacji
swojej 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):
|
Reasumując, realizując transakcję na poziomie SERIALIZABLE (izolowana transakcja) mamy gwarancję, że transakcja działa na spójnych, nie zmienianych przez inne transakcje danych. Jest to domyślny poziom w Standardzie.
Natomiast 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.
Na poziomie READ COMMITED, po tym jak jedna transakcja odczytała dane, mogą one zostać zmienione przez inną transakcję, zanim pierwsza się zakończy. Po zatwierdzeniu drugiej transakcji, zmienione dane są dostępne dla pierwszej transakcji. 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. Gdyby jednak pierwsza transakcja chciała nie tylko odczytywać dane, ale również je zmieniać, musiałaby te dane zablokować i wtedy druga transakcja nie mogłaby już ich zmienić przed zakończeniem pierwszej transacji.
Nie wszystkie aplikacje będą działać poprawnie w tym trybie. Na przykład, jeśli aplikacja dokonująca rezerwacji biletów najpierw tylko sprawdza czy jest wolne miejsce w samolocie, następnie wysyła e-mail do klienta, z informacją że znalazła wolne miejsce, a następnie próbuje dokonać rezerwacji znalezionego miejsca, to w tym momencie znalezione miejsce może być już zajęte.
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 tak samo jak dane użytkowników.
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ępna dla administratorów systemu.
Najpowszechniej stosowaną przez użytkowników perspektywą jest perspektywa User_Tables.
Przykład
Instrukcja:
SELECT Table_Name
FROM User_Tables;SELECT Column_Name, Data_Type
FROM User_Tab_Columns
WHERE 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
Podstawowymi instrukcjami administratora bazy danych są instrukcje zakładania kont użytkowników i przyznawania 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_obiektu TO użytkownik, ...|PUBLIC [WITH GRANT OPTION] |
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;Pozbawianie uprawnień
Przyznane drugiemu użytkownikowi uprawnienie można odwołać za pomocą instrukcji odwrotnej do instrukcji GRANT.
| REVOKE uprawnienie, ... ON nazwa_obiektu FROM użytkownik, ...|PUBLIC; |
Uprawnienie zostaje użytkownikowi odebrane, o ile dany użytkownik uzyskał go wyłącznie od odwołującego uprawnienie użytkownika.
Uprawnienia systemowe
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:
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.
Role
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;Włączanie ról
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, dopasowanej do konkretnej sytuacji o nazwie agent.
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.
Bazy danych w Standardzie
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.
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:
| CONNECT TO nazwa_serwera; |
| SET CONNECTION nazwa_serwera; |
| DISCONNECT nazwa_serwera; |
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
powoduje ustawienie poziomu izolacji transakcji w sesji na SERIALIZABLE.
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';CREATE TABLE Dept
(DeptNo DOMAIN Dept# PRIMARY KEY,
...);ALTER DOMAIN nazwa_dziedziny i DROP DOMAIN nazwa_dziedziny.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);| DROP ASSERTION nazwa_asercji; |
Asercje są trudne do implementacji przez SZBD, która by zagwarantowała efektywne działanie instrukcji SQL w bazie danych.
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 kasowana przy każdym koń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 tymczasowych 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.
Implementacja tabel tymczasowych przez SZBD jest prostsza, ponieważ nie trzeba zapewniać odtwarzalności danych po awarii (ale trzeba zapewnić możliwość wycofania nie zatwierdzonych zmian).
Reasumując, tabele dzielimy na:
SELECT MAX(p.NumEmp) maxNE, MAX(p.AvgSal) maxAS
FROM
(SELECT Deptno, Count(Empno) NumEmp, Avg(Sal) AvgSal
FROM EMP
GROUP BY Deptno
)p;Są dwa rodzaje ważnych, z punktu widzenia praktyki, obiektów bazy danych, które występują w Oracle, a których nie ma w Standardzie.
W systemie Oracle jest specjalny obiekt - sekwencja (ang. sequence) 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]; |
Sekwencji używa się zgodnie ze składnią:
Instrukcjanazwa_sekwencji.NextVal- generowanie kolejnej wartości w sekwencji,
nazwa_sekwencji.CurrVal- ostatnio wygenerowana wartość w sekwencji.
| DROP SEQUENCE nazwa_sekwencji; |
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. |
Oto instrukcja tworząca kolejny dział:CREATE SEQUENCE Dept_seq
INCREMENT BY 10
START WITH 10;
CREATE SEQUENCE Emp_seq
INCREMENT BY 1
START WITH 1000;
INSERT INTO Dept(Deptno, Dname, Loc)
VALUES (Dept_seq.NextVal, 'Sprzedaż', 'Warszawa');INSERT INTO Emp VALUES (Emp_seq.NextVal, 'Stec',
'MANAGER', NULL, '20-JAN-90', 10000, 0 , Dept_seq.CurrVal);
Klaster (ang. cluster) 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 wszystkie 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. 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.
Wyzwalacze bazy danych są procedurami:
Wyzwalacze bazy danych służą głównie do programowania więzów spójności i do programowania stałych czynności, które powinny być wykonywane w każdej aplikacji korzystającej z bazy danych.
Najczęściej używane są wyzwalacze tabelowe. Dla każdej tabeli można określić 12 typów wyzwalaczy (dla jednej tabeli może być więcej niż jeden wyzwalacz jednego typu). Typ wyzwalacza zależy przede wszystkim od tego, czy dotyczy operacji na pojedynczym wierszu (wyzwalacz wierszowy) czy dotyczy wykonania całej instrukcji. Ponadto, zależy od tego, czy ma być wykonywany przed operacją (typ BEFORE) czy po (typ AFTER). Wreszcie, wyzwalacz może dotyczyć każdej z trzech instrukcji języka operowania danymi: INSERT, DELETE i UPDATE.
Wyzwalacze zostały wprowadzone do Standardu SQL w roku 1999. Dzięki wyzwalaczom baza danych staje się "żywa".
Dla perspektywy mamy możliwość zdefiniowania specjalnego rodzaju wyzwalacza:
CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
|
Wyzwalacz ten jest odpalany zamiast instrukcji w odniesieniu do perspektywy określonych w definicji wyzwalacza. Daje to możliwość pełnej realizacji postulatu, aby zmiany w bazie danych można było dokonywać z poziomu użytkowego – czyli perspektyw.
Przykład
Rozważmy perspektywę będącą złączeniem trzech tabel.
CREATE VIEW Manager_info AS
SELECT e.Ename, e.Empno, d.Dname, d.Deptno, p.Level, p.Projno
FROM Emp e, Dept d, Project p
WHERE e.Empno = d.Mgr_no
AND d.Deptno = p.Resp_dept;CREATE TRIGGER Manager_info_insert
INSTEAD OF INSERT ON Manager_info
FOR EACH ROW
DECLARE
p NUMBER;
BEGIN
SELECT Count(*) INTO p FROM Emp
WHERE Emp.empno = :NEW.Empno;
IF p=0 THEN
INSERT INTO Emp VALUES(:NEW.Empno, :NEW.Ename);
ELSE
UPDATE Emp SET Emp.Ename = :NEW.Ename
WHERE Emp.Empno = :NEW.Empno;
END IF;
SELECT Count(*) INTO p FROM Dept
WHERE Dept.Deptno = :NEW.Deptno;
IF p=0 THEN
INSERT INTO Dept VALUES(:NEW.Deptno, :NEW.Dname);
ELSE
…
Wyzwalacze systemowe Oracle mają składnię:
| CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza [BEFORE|AFTER|INSTEAD OF][zdarzenie_bazodanowe|zdarzenie_DDL] ON [DATABASE|SCHEMA] blok_PL/SQL |
Zdarzenia można łączyć ze sobą w jednym wyzwalaczu systemowym za pomocą słowa kluczowego OR.
Przykład
Rozważmy wyzwalacz, który rejestruje w dzienniku wszystkie pojawiające się błędy serwera bazy danych. Dla błędu nr 1017 oznaczającego "niepoprawne logowanie" jest przewidziana specjalna obsługa.
CREATE TRIGGER Log_errors AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
<wykonaj specjalną obsługę w tym
przypadku>
ELSE
<zapisz w dzienniku informację o błędzie>
END IF;
END;Rozważmy wyzwalacz, który zapisuje informacje o wykonywaniu na koncie użytkownika instrukcji tworzenia nowego obiektu w bazie danych.
CREATE TRIGGER Audit_db_object AFTER CREATE ON SCHEMA
<zapisz w dzienniku informację o utworzeniu obiektu>Przykład
Szczególne znaczenie ma wyzwalacz dla zdarzenia logowania się użytkownika. Umożliwia on sprawdzenie, skąd loguje się użytkownik a także umożliwia utworzenie dla danego użytkownika odpowiedniego kontekstu w postaci zbioru wartości atrybutów – do użycia przez aplikację. Oracle wprowadza nawet w tym celu specjalny rodzaj obiektu w bazie danych o nazwie kontekst, z którego procedurę wywołuje wyzwalacz przy logowaniu się użytkownika.
Włączanie/wyłączanie wyzwalaczy
Wyzwalacz bazy danych może być włączany i wyłączany za pomocą instrukcji:
|
|
Informacje o wyzwalaczach w słowniku danych
Informacje o wyzwalaczach znajdują się w perspektywie słownika danych User_triggers. Aby je obejrzeć, piszemy (nazwę wyzwalacza dużymi literami):
SELECT u.Trigger_type, u.Table_name, u.Triggering_event
FROM User_triggers u
WHERE u.Trigger_name = 'wyzwalacz';SELECT u.Trigger_body
FROM User_triggers u
WHERE u.Trigger_name = 'wyzwalacz';
Konstrukcje dynamicznego SQL stosujemy, gdy w chwili kompilacji nie znamy jeszcze pełnej postaci instrukcji SQL w tym instrukcji DDL. Szczegóły mogą być ustalane w trakcie działania programu, jak np. z której tabeli wyświetlić wiersze lub którą tabelę usunąć.
Język SQL jest rozszerzany o dwie konstrukcje:
1. Polecenie wykonania instrukcji SQL z podanym jej tekstem:
|
2. Rozszerzenie instrukcji operowania na kursorach o zmienne kursorowe:
|
Przykład 1
DECLARE
sql_stmt VARCHAR2(100);
my_deptno NUMBER(2) := 50;
my_dname VARCHAR2(15) := 'PERSON';
my_loc VARCHAR2(15) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
sql_stmt := 'INSERT INTO Dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, my_loc;
sql_stmt := 'SELECT * FROM Emp WHERE Empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788;
EXECUTE IMMEDIATE 'DELETE FROM Dept WHERE Deptno = :n' USING
my_deptno;
EXECUTE IMMEDIATE 'CREATE TABLE Bonus (Id NUMBER, Am NUMBER)';
END;Przykład 2
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp; emp_rec emp%ROWTYPE;
sql_stmt VARCHAR2(100);
my_job VARCHAR2(15) := 'CLERK';
BEGIN
sql_stmt := 'SELECT * FROM Emp WHERE Job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
... -- process record
END LOOP;
CLOSE emp_cv;
END;Od wersji 9i Oracle umożliwia wyświetlanie danych według stanu bazy danych z przeszłości. Jest to jednak ograniczone możliwością odtworzenia danych przy użyciu tzw. dziennika wycofań (będzie o nim mowa w wykładzie 11), w którym miejsca są cyklicznie nadpisywane. Zapytania retrospektywne (ang. flashback queries) są przydatne przy analizie danych jak również wtedy gdy chcemy przywrócić poprawne dane po tym jak błędnie je wprowadziliśmy, zmieniliśmy lub usunęliśmy.
Na przykład, gdy chcemy odczytać jak wyglądała zawartość tabeli Emp przed godziną, piszemy:
SELECT * FROM Emp
AS OF TIMESTAMP (Sysdate-1/24);Więcej o zapytaniach retrospektywnych znajduje się w wykładzie 11.
W tym wykładzie dokonaliśmy przeglądu ważnych, zaawansowanych konstrukcji języka SQL w tym perspektyw, transakcji, blokad, poziomów izolacji, słownika danych, wyzwalaczy i zapytań retrospektywnych.
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. Wspomaga realizację postulatu logicznej niezależności 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 schemacie 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 bazy danych.
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 za pomocą instrukcji CREATE ASSERTION poza instrukcjami CREATE TABLE i ALTER TABLE.
tabela 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.
wyzwalacz - procedura wywoływana przez system przy zajściu odpowiedniego zdarzenia, które może być albo zdarzeniem systemowym albo jedną z instrukcji INSERT, DELETE lub UPDATE skierowaną do wykonania na tabeli lub perspektywie w bazie danych.
zapytanie retrospektywne - zapytanie o dane jakie były aktualne w bazie danych w przeszłości.
CREATE ASSERTION maxempl
CHECK (1000 <= SELECT COUNT (*)
FROM Emp);
Pracownicy(Id_prac, Imię, Nazwisko, Data_urodzenia, Adres)
Zatrudnienie(Id_prac, Data_pocz, Stanowisko, Data_koniec,
Zarobki)
Produkty(Id_produktu, Nazwa, Cena, Ilość_w_magazynie)
Klienci(Id_klienta, Imie, Nazwisko, Adres)
Sprzedaż(Id_sprzedaży, Id_klienta, Id_produktu, Ilość, Data, Id_prac)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.