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.
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. |
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; |
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;
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.
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.
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;
InstrukcjaDROP 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 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:
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.
|
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]; |
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
-- zablokowanie całej tabeli w trybie wyłącznym
IN EXCLUSIVE MODE
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]; |
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
-- założenie blokady wyłącznej na klientów z Polski i współdzielonej na tabelę Klienci
WHERE Kraj = 'Polska'
FOR UPDATE
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;
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 |
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.
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
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_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;
Uprawnienia odwołuje się za pomocą instrukcji odwrotnej do instrukcji GRANT.
REVOKE uprawnienie, ... ON nazwa_obiektu FROM użytkownik, ...|PUBLIC; |
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;
|
GRANT CREATE SESSION, CREATE TABLE TO użytkownik;
|
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.
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.
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';
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
.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ą instrukcjiDROP ASSERTION nazwa_asercji; |
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.
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]; |
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; |
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');
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);
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.
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.
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.
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ę.
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)
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.