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

Structured Query Language

/Persistent

Stored Modules, and was developed by the

American National Standards Institute

(ANSI) as an extension

to

SQL

.

[1]

It was first adopted in 1996,

[2]

and it provides

procedural programmability

in addition to the querying

commands of SQL.

The SQL/PSM extension is defined by

ISO

/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

database

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.