Wyk
ład
6
Wyk
ład
6
Aplikacja bazodanowa,
więzy spójności,
SQL*Plus
Aplikacja
bazodanowa
• Tworząc aplikację, należy podjąć decyzję, jaka
jej część ma się znajdować, na:
• serwerze bazy danych (czyli po stronie
serwera),
• komputerze użytkownika (czyli po stronie
klienta).
• Dane oraz mechanizm wykonywania instrukcji
SQL – po stronie serwera.
• “Przyjazny” interfejs użytkownika – po stronie
klienta.
• Przetwarzanie danych:
• na serwerze – centralna kontrola nad
spójnością danych,
• na stacji klienckiej – precyzyjniejsza
diagnostyka i obsługa błędów.
Aplikacja
bazodanowa
Elementy aplikacji umieszczane po stronie
serwera:
– deklaratywne więzy spójności,
– procedury, funkcje i pakiety,
– wyzwalacze bazy danych,
– procedury/klasy (serwlety) hipertekstowe
tworzące dynamiczne strony WWW,
– rozproszone komponenty jak klasy Javy, EJB.
Rozszerzenie języka SQL o
elementy języka programowania
• SQL/PSM – Standard SQL:1999
(
SQL/PSM stands for
/Persistent
Stored Modules, and was developed by the
American National Standards Institute
(ANSI) as an extension
to
It was first adopted in 1996,
and it provides
commands of SQL.
The SQL/PSM extension is defined by
/IEC 9075-4:2003.
SQL/PSM standardizes procedural extensions for SQL,
including flow of control, condition handling, statement
condition signals and resignals, cursors and local variables,
and assignment of expressions to variables and parameters. In
addition, SQL/PSM formalizes declaration and maintenance of
persistent
language routines (e.g., "stored
procedures").
)
• Oracle PL/SQL
Więzy spójności
• Zadaniem więzów spójności jest
zagwarantowanie tego, aby dane w bazie
danych wiernie odzwierciedlały świat
rzeczywisty, dla którego baza danych została
zaprojektowana.
• System bazy danych ma zapewnić, aby więzy
spójności pozostawały prawdziwe przy
wszystkich operacjach wykonywanych na bazie
danych w postaci transakcji, wyzwalaczy,
ładowania danych do bazy danych czy
ich importu.
Deklaratywne więzy spójności
• Więzy spójności mogą być sprawdzane zarówno po
stronie serwera jak i po stronie klienta:
• Bardziej naturalnym miejscem sprawdzania jest
serwer, który obsługuje wszystkie możliwe
aplikacje utworzone dla bazy danych. Lepiej jest
mieć jeden mechanizm gwarantujący spełnienie
więzów spójności niż wiele, potencjalnie
niespójnych.
• Podstawowe metody określania więzów spójności na
serwerze:
• deklaratywne więzy spójności – w definicjach
tabel (instrukcje
CREATE TABLE i ALTER
TABLE
),
• wyzwalacze bazy danych,
• osobny interfejs programistyczny API.
Więzy spójności encji
Ograniczają możliwe wartości w wierszu tabeli:
• Więzy klucza głównego
PRIMARY KEY
– wartości w
określonych kolumnach jednoznacznie identyfikują wiersz.
W kolumnach klucza głównego nie jest dopuszczalna
wartość NULL. Na kolumnach tworzących klucz główny
jest automatycznie zakładany indeks. W tabeli może być
tylko jeden klucz główny.
• Więzy klucza jednoznacznego
UNIQUE
– wartości w
określonych kolumnach jednoznacznie identyfikują wiersz.
W kolumnach klucza jednoznacznego jest dopuszczalna
wartość NULL. Na kolumnach tworzących klucz
jednoznaczny jest automatycznie zakładany indeks. W
tabeli może być więcej niż jeden klucz jednoznaczny.
Więzy spójności encji
• Więzy
NOT NULL
– w kolumnie nie jest
dozwolona wartość NULL.
• Więzy
CHECK
– warunek, który ma być
prawdziwy dla wszystkich wierszy w tabeli. Nie
może zawierać podzapytania ani funkcji
zmiennych w czasie jak Sysdate lub User. Może
zawierać nazwy jednej lub więcej kolumn.
Więzy spójności
referencyjnej
• Zbiór wartości w kolumnach klucza obcego jest
zawsze podzbiorem zbioru wartości
odpowiadającego mu klucza głównego lub
jednoznacznego.
• Wartością klucza obcego może być NULL –
wówczas klucz obcy nie wskazuje na żaden
obiekt.
• System zapewnia, aby obiekt wskazywany przez
wartość klucza obcego zawsze istniał, przy
wszystkich możliwych operacjach na tabelach, w
których biorą udział
klucze główne, jednoznaczne i obce.
Klucze obce
• Przy definiowaniu klucza obcego nie można się
odwoływać ani do synonimu tabeli ani do tabeli
znajdującej się w innej (odległej) bazie danych.
• Na kolumnach klucza obcego nie jest
automatycznie tworzony indeks.
• Aby przyśpieszyć wyszukiwanie i złączanie,
projektant bazy danych może sam zaplanować
założenie indeksu na kluczu obcym używając
instrukcji
CREATE INDEX
.
Składnia więzów w-linii
• [NOT] NULL
• {UNIQUE|PRIMARY KEY}
• CHECK (warunek)
• REFERENCES tabela[(kolumna)][ON DELETE
CASCADE]
• REFERENCES tabela[(kolumna)][ON DELETE
SET NULL]
Składnia więzów poza-
linią
• {UNIQUE|PRIMARY KEY} (kolumna,…)
• CHECK (warunek)
• FOREIGN KEY (kolumna,…) REFERENCES
tabela(kolumna,…)[ON DELETE [CASCADE|SET
NULL]]
CONSTRAINT nazwa_więzów
• Nadanie nazwy, przy pomocy której można się do
nich odwoływać, np. włączając je, wyłączając lub
usuwając.
CREATE TABLE Pożyczki(
Num_konta NUMBER(6,0),
Num_pożyczki NUMBER(6,0),
Typ_pożyczki VARCHAR2(8)
CONSTRAINT ck_Typ_poż
CHECK (Typ_pożyczki IN ('AUTO', 'DOM',
'OSOBISTE')),
Wielkość NUMBER(8,0)
CONSTRAINT nn_Wielk
NOT NULL,
Data_pożyczki DATE
DEFAULT Sysdate
,
Zaakceptowana VARCHAR2(25)
CONSTRAINT ko_Zaakcept
REFERENCES Kierownicy
(Nazwisko_kier),
CONSTRAINT kg_Poż
PRIMARY KEY(Num_konta,
Num_pożyczki),
CONSTRAINT ko_Num_konta
FOREIGN KEY (Num_konta)
REFERENCES
Klienci
(Num_konta)
);
Przykład
DEFAULT
DEFAULT Sysdate
• Ustala datę pożyczki na dzień bieżący, czyli
dzień wprowadzania informacji o pożyczce do
bazy danych.
• W specyfikacji wartości domyślnej mogą
występować
stałe i funkcje SQL także User, Sysdate.
• Nie mogą występować ani nazwy kolumn ani
funkcje PL/SQL ani sekwencje.
CHECK
• W przypadku warunku występującego w
CHECK akceptowane są wiersze, których
wartością jest
TRUE
lub
NULL
System nie pozwala, aby w tabeli pojawił się
wiersz dający
wartość FALSE (sygnalizowany jest błąd).
Akcje referencyjne przy DELETE
• Domyślnie ustawiona jest opcja
RESTRICT
. Jeśli
ma być usunięty wiersz tabeli nadrzędnej, do
którego są odwołania przez klucz obcy z tabeli
podrzędnej, jest błąd i DELETE nie zostaje
wykonane.
• ON DELETE CASCADE
– razem z wierszem tabeli
nadrzędnej zostają usunięte wszystkie wiersze
tabeli podrzędnej, w których wartości klucza
obcego wskazują na usuwany wiersz,
• pod warunkiem, że usunięcie tych wierszy jest
możliwe bez naruszenia innych więzów
referencyjnych.
• ON DELETE SET NULL
– przy usuwaniu wiersza,
do którego są odwołania przez wartości kluczy
obcych, następuje wstawienie pseudo-wartości
NULL jako wartości klucza obcego.
Inne akcje referencyjne
Standardu (nie realizowane przez
Oracle)
• ON DELETE SET DEFAULT
–
oznaczająca
wstawienie wartości domyślnej jako wartości
klucza obcego;
• ON UPDATE [CASCADE|SET NULL|SET
DEFAULT]
• W Oracle można je zaprogramować za pomocą
wyzwalaczy bazy danych, pod warunkiem, że
nie zdefiniujemy deklaratywnych więzów
klucza obcego.
CHECK
• Gdy klucz obcy składa się z kilku kolumn i w
jednej z nich jest wartość NULL, wtedy system
akceptuje taką wartość wiersza bez żadnych
dodatkowych sprawdzeń.
• Gdybyśmy chcieli realizować semantykę
polegającą na tym, że albo wszystkie wartości
klucza obcego są NULL albo żadna z nich nie
jest, wówczas należałoby zagwarantować to za
pomocą odpowiedniego warunku CHECK np. :
CONSTRAINT A_B_ck CHECK (((A IS NOT
NULL) AND (B IS NOT NULL)) OR((A IS NULL)
AND (B IS NULL)))
Operacje na więzach
ALTER TABLE Emp
ADD (CONSTRAINT Sal_ck CHECK
(sal >500));
ALTER TABLE Emp
ENABLE CONSTRAINT Sal_ck;
ALTER TABLE Emp
DISABLE CONSTRAINT Sal_ck;
ALTER TABLE emp
DROP CONSTRAINT Sal_ck;
Więzy klucza głównego
i jednoznacznego
można włączać,
wyłączać i usuwać
używając ich
naturalnych nazw
PRIMARY KEY i
UNIQUE.
SQL*Plus – prosty interfejs do
bazy danych
• SET AUTOCOMMIT ON
– wykonywanie operacji
zatwierdzania COMMIT po każdej zmianie danych
(a nie tylko na koniec pomyślnej transakcji);
• HOST polecenie
– wykonanie polecenia systemu
operacyjnego;
• EXIT
– zakończenie sesji SQL*Plus – wylogowanie
się z Oracle;
• DESCRIBE nazwa
– wyświetlenie schematu
obiektu (tabeli, perspektywy, procedury, funkcji) o
podanej nazwie;
• EXECUTE nazwa_procedury (...)
– wykonanie
procedury.
Pliki
w SQL*Plus
• START
nazwa_pliku
– wykonaj skrypt poleceń SQL i
SQL*Plus zapisany w pliku o podanej nazwie
(zamiast START można użyć@);
• ED
nazwa_pliku
– wywołaj standardowy edytor (np.
notepad albo vim);
• SPOOL
nazwa_pliku
– zapisuj kolejne polecenia i
ich wyniki w pliku o podanej nazwie;
• SPOOL OFF
– zamknij poprzednio otwarty przez
SPOOL plik.
Formatowanie
w SQL*Plus
• COLUMN
nazwa
FORMAT
An
– szerokość
wyświetlania kolumny napisowej o podanej nazwie;
• COLUMN
nazwa
FORMAT 99999
– szerokość
wyświetlania kolumny liczbowej o podanej nazwie (9
reprezentuje jedną cyfrę);
• SET PAGESIZE
n
– ustalenie długości jednej strony
na n linii;
• SET LINESIZE
n
– ustalenie maksymalnej liczby
znaków w linii;
• SET PAUSE ON
– zatrzymywanie przewijania
wyświetlania wyników po wyświetleniu zawartości
ekranu;
Inne zmienne systemowe w
SQL*Plus
• HEADING ON
– wyświetlanie nagłówków kolumn w
tabelach zawierających wyniki zapytań;
• NULL tekst
– ustalanie tekstu wyświetlanego
zamiast NULL;
• SQLPROMPT
tekst
– ustalenie znaku zachęty;
• TIME ON
– włączanie wyświetlania bieżącej godziny
przed znakiem zachęty;
• TIMING ON
– włączanie wyświetlania czasu
wykonania instrukcji SQL;
Zmienne w SQL*Plus
• VARIABLE X NUMBER
(lub
CHAR(n)
lub
VARCHAR2(n)
, (n>0) – deklaracja zmiennej
wiązania;
• EXECUTE :X := wyrażenie;
– przypisz na zmienną
wiązania
:X
wartość wyrażenia. Po ustaleniu
wartości można zmienne wiązania poprzedzone
dwukropkiem używać w instrukcjach SQL i PL/SQL.
• PRINT X
– wypisz wartość zmiennej wiązania X;
• ACCEPT Zmienna PROMPT 'Podaj wartość
zmiennej: '
– utworzenie zmiennej podstawienia i
wczytanie na nią wartości. Następnie można
zmienne podstawienia poprzedzone znakiem &
używać w instrukcjach SQL i PL/SQL.