plik

SYSTEMY BAZ DANYCH

cd języka zapytań SQL

Opracowanie : Dr hab. Bożena Śmiałkowska



Logiczny (boolowski)

BOOLEN

z wartościami TRUE,

FALSE oraz UNKNOWN (TRUE>FALSE)



Znakowy

CHARACTER [VARYING] [długość

] – można

skrócić do CHAR - Jeśli za łańcuch stałej długości
podstawimy mniejszą liczbę znaków, niż wskazuje jego
długość, to zostanie on uzupełniony na końcu spacjami
aż do osiągnięcia wymaganej długości.



VARCHAR

- w przypadku łańcucha zmiennej długości

zapamiętane zostaną jedynie wpisane znaki (dane będą
zajmować mniej pamięci).



Bitowy

BIT, BIT VARYING

lub

BIT [VARYING] [DŁUGOŚĆ]

Typy danych ISO SQL



Dokładny liczbowy

NUMERIC [rozmiar[,dokładność]],
DECIMAL [rozmiar[,dokładność]],
INTEGER,
SMALLINT

gdzie INTEGER możemy skrócić do INT, a DECIMAL do
DEC



Przybliżony liczbowy

FLOAT[dokładność],
REAL,
DOUBLE PRECISION

gdzie zapis m10c , gdzie m-mantysa (l.rzeczywista), c-cecha
(l.całkowit.) - dokładność określa rozmiar mantysy

Typy danych ISO SQL



Daty i czasu

DATE, TIME, TIMESTAMP

DATE jest stosowany do dat i zawiera pola

YEAR, MONTH, DAY

TIME jest stosowany do czasu i zawiera pola

HOUR, MINUTE, SECOUND

TIMESTAMP jest stosowany do czasu z

uwzględnieniem daty i godziny

Typy danych ISO SQL

Waga typu atrybutu w procesie
wstawiania i aktualizowania bazy danych

Typ danych uniemożliwia wprowadzenie do

atrybutu tabeli bazy danych takiej wartości,
która jest niezgodna z typem atrybutu
określonym w CREATE TABLE – zapewnia to
automatyczną kontrolę wartości wstawianych
do bazy danych (INSERT) i aktualizowanych
(UPDATE)

Są również inne mechanizmy ochrony bazy
danych przed niewłaściwym wprowadzaniem i
aktualizowaniem danych – tymi mechanizmami są

więzy integralności

Wprowadzanie

więzów

integralności bazy

danych w SQL

Rodzaje więzów integralności



Dane wymagane



Więzy dziedzinowe



Więzy referencyjne



Więzy globalne (asercje)



Więzy specjalne (trigerry)

Dane wymagane

Umieszczenie w poleceniu CREATE i ALTER

TABLE przy kolumnie klauzuli

NOT NULL

oznacza konieczność podania wartości dla
atrybutów, dla których podano klauzulę NOT
NULL

Np..

CREATE TABLE Pracownik
(…

ZAWOD char(30) NOT NULL

,…);

Definiowanie więzów dziedzinowych

Są następujące sposoby:



Polecenie tworzenia dziedziny wartości
atrybutu (-ów)

(CREATE DOMAIN)



Klauzula

CHECK

użyta w CREATE DOMAIN i

CREATE TABLE

Więzy integralności - dziedzinowe



Klauzula

CHECK (warunek_selekcji)

, używa się

do ograniczenia dopuszczalnych wartości atrybutu
tabeli

np..

SEX CHAR NOT NULL

CHECK

(SEX IN (‘M’, ‘K’))

Definiowanie dziedziny wartości

Postać klauzuli

CREATE DOMAIN

NazwaDziedziny [AS]

typ_danych

[

DEFAULT

wartość_domyslna]

CHECK

(warunek_selekcji)

PŁEĆ

CHAR NOT NULL

CHECK

(PŁEĆ IN (‘M’,

‘K’))

CREATE DOMAIN

LICZBA_OCZEK

SMALLINT DEFAULT 1

CHECK

(VALUE IN

(1,2,3,4,5,6));

CREATE DOMAIN

TYP_DEPT

SMALLINT

CHECK

(VALUE IN (SELECT DEPTNO FROM

DEPT));

Przykłady dziedzinowych więzów
integralności

Przykłady definiowania dziedziny

CREATE DOMAIN

KodPocztowy

VARCHAR(5);

CREATE DOMAIN

Adres

VARCHAR(55);

CREATE DOMAIN

TypNieruchomosci

CHAR(1) CHECK (VALUE

IN(‘B’,’C’,’M’,’F’,’S’));

CREATE DOMAIN

L_Pokoj

AS SMALLINT

CHECK(VALUE BETWEEN 1 AND 15);

Przykłady definiowania dziedziny –
cd..

CREATE DOMAIN

NumeryWlascicieli

VARCHAR(5) CHECK (VALUE IN

(SELECT wlasciciele FROM

WlascicielPrywatny));

CREATE DOMAIN

NumeryPracownikow

VARCHAR(5) CHECK (VALUES IN

(SELECT

pracownikNr FROM Persony));

cd… przykładu

CREATE TABLE

NIERUCHOMOSCI

(

NrNieruchomosci VARCHAR(5) NOT NULL UNIQUE,

Wlasciciel

NumeryWlascicieli

NOT NULL,

Pracownik Nr

NumeryPracownikow

NOT NULL,

Kodadres

KodPocztowy

Adres_miejsce

Adres

NOT NULL,

Typ

TypNieruchomosci

NOT NULL DEFAULT ‘M’,

Ilosc_pokoi

L_Pokoj

NOT NULL DEFAULT 4,

PRIMARY KEY Wlasciciel);

Więzy integralności - dziedzinowe



Dziedzinę można usunąć za pomocą

DROP DOMAIN NazwaDziedziny

[RESTRICT|CASCADE]

Integralność encji – więzy referencyjne



Klauzula

PRIMARY KEY

(atryb_n {, atryb_n})

dla klucza głównego



Klauzula

UNIQUE

(atryb_n {, atryb_n}) dla

kluczy alternatywnych

Integralność referencyjna



Klauzula FOREIGN KEY



Cztery tryby postępowania w przypadku
kasowania (modyfikowania) rekordów:



RESTRICT i NO ACTION



CASCADE



SET NULL



SET DEFAULT

FOREIGN KEY

(DEPTNO)

REFERENCES

DEPT

ON DELETE SET NULL

Np.

Opcje restrykcyjne więzów
referencyjnych



restrykcyjne (

RESTRICT, NO ACTION

) –

usunięcie wiersza nadrzędnego nie jest
możliwe, jeśli istnieją wiersze podrzędne
(logicznie z nim powiązane),

Opcje więzów referencyjnych – cd..



z wstawianiem wartości pustych (

SET NULL

)

– usunięcie wiersza nadrzędnego powoduje
automatyczne wstawienie wartości pustych w
miejsce kluczy obcych wierszy podrzędnych,

Opcje więzów referencyjnych – cd..



kaskadowe (

CASCADE

) – usunięcie wiersza

nadrzędnego powoduje automatyczne
usunięcie wierszy podrzędnych (logicznie z
nim powiązanych).

Przykład

CREATE TABLE

DOSTAWY

(IdDost char(5),

IdTow char(10),

…

FOREIGN KEY IdDost REFERENCES

DOSTAWCA

ON DELETE CASCADE);

Określono tu zależność referencyjną wg której każda niezerowa
wartość

DOSTAWY.IdDost

musi wystąpić w

DOSTAWCA.IdDost

Określa to również, że jeśli zostanie wprowadzony wiersz w tabeli

DOSTAWY

to taka operacja zostanie odrzucona jeśli w tabeli

DOSTAWCA

wartość IdDost nowo wprowadzana nie istnieje.

Opcje więzów referencyjnych – cd..



z wstawianiem wartości pustych (

SET

DEFAULT

) – usunięcie wiersza nadrzędnego

powoduje automatyczne wstawienie wartości
podanej po DEFAULT w miejsce kluczy
obcych wierszy podrzędnych,

Przykład

ZESPOLY (

nr_zespolu

, nazwa, nr_projektu)

PRACOWNICY(

Nr_prac

, nazwisko, nr_zespolu)

WYPLATY(

Nr_prac, nr_tematu

, kwota)

TEMATY(

nr_tematu

, nazwa, nr_projektu)

SET NULL

CASCADE

RESTRICT

Ograniczenia na usuwanie i
modyfikację wierszy

ON DELETE

{ NO ACTION | RESTRICT |

CASCADE |

SET NULL | SET DEFAULT }

ON UPDATE

{ NO ACTION | RESTRICT |

CASCADE |

SET NULL | SET DEFAULT }

Inny przykład więzów

dziedzinowe

referencyjne

Więzy globalne (ogólne) - Po co nam
takie więzy?



Wszystkie dotychczas omówione więzy dotyczyły
pojedynczej krotki (więzy wartości atrybutów),
ewentualnie prostych związków między krotkami z
różnych relacji (więzy integralności)



czasami chcielibyśmy napisać warunek, który
dotyczy całej relacji (tabeli) lub kilku relacji (tabel).



Przykładem takich więzów są asercje.



Asercje nie są związane z definicją tabeli (CREATE
TABLE)

Więzy integralności ogólnej – składnia
asercji

CREATE ASSERTION

NazwaAsercji

CHECK

(warunek_selekcji)

CREATE ASSERTION

PracownikNadzorującyNieZaDużo

CHECK

(NOT EXISTS (SELECT pracownikNr FROM
Nieruchomości GROUP BY pracownikNr
HAVING COUNT(*)>100))

Np.

Przykład więzów globalnych

W bazie są następujące relacje:

Dyrektor

(nazwisko:varchar(10),adres:varchar(10),

cert:varchar(10),cenaSieci:int)

Studio

(nazwa:varchar(10),adres:varchar(10),

prezes:varchar (10) references Dyrektor(cert))

Chcielibyśmy, aby szefem (prezesem) studia była

osoba, której sieć ma wartość co najmniej 10 mln $

Definicja asercji z przykładu

CREATE ASSERTION

BogatyPrezes

CHECK

(NOT EXISTS

(SELECT *

FROM Studio,Dyrektor

WHERE prezes=cert AND
cenaSieci<10000000

));

Asercja opisująca warunek bycia
bogatym prezesem

Gdybyśmy chcieli zdefiniować bogatych
prezesów w tabeli Studio używając CHECK?

CREATE TABLE Studio (

nazwa CHAR(30) PRIMARY KEY,

adres VARCHAR(255),

prezC INT REFERENCES Dyrektor(cert),

CHECK (prezes NOT IN

(SELECT cert FROM Dyrektor

WHERE cenaSieci<10000000)

));

Gdyby zdefiniowano bogatych prezesów
w tabeli Studio używając CHECK to:



nie uzyskamy tego samego efektu co przy
pomocy asercji.



Dzieje się tak ponieważ warunek, który jest
opisany w więzie CHECK, będzie
sprawdzany przy wstawianiu i modyfikacji
krotki tylko z relacji Studio.



Przy zmianach w relacji Dyrektor więzy
CHECK w relacji Studio nie będą
sprawdzane.

Wnioski z przykładu „Bogaci prezesi”



Jeśli uczynimy kogoś prezesem studia a potem
będziemy chcieli obniżyć wartość jego sieci to

asercja na to nie pozwoli

natomiast

więz krotkowy

tak

, ponieważ on jest sprawdzany przy modyfikacji

krotki z relacji Studio a nie krotki z relacji Prezes.



DLACZEGO: ponieważ więzy krotkowe powinny
dotyczyć krotek z danej relacji a w tej relacji nic się
nie zmieniło. Ten sposób traktowania więzów
krotkowych wynika głównie z wymogów
wydajnościowych! Wyobraźmy sobie, jaką pracę
musiałaby wykonać baza, gdyby musiała zapewnić
wiele więzów krotkowych podobnych temu.

Inny przykład

Film

(tytuł:varchar(10),rok:date,długość:int,

czyKolor:boolean,nazwaStudia:varchar(10),

producentC:varchar(10));

chcielibyśmy, aby długość wszystkich filmów w
danym studiu miała co najmniej 10000 minut.

Mamy relację:

CREATE ASSERTION SumDlugość

CHECK (10000 <= ALL

(SELECT SUM(długość) FROM Film

GROUP BY nazwaStudia));

W tym przypadku istnieje silna pokusa, aby zamiast
tworzyć asercję dodać linijki 2-4 jako więz CHECK do
relacji Film.
Wydaje się,że również zadziałałoby.
Jednak również w tym wypadku zauważmy, co
mogłoby się zdarzyć przy usuwaniu filmów!!

Porównanie więzów (krotkowych)
CHECK i asercji

Przy dowolnej modyfikacji relacji
(wstawianie, modyfikacja lub
usuwanie dowolnej krotki) której
dotyczy ta asercja

Asercje

Przy wstawianiu lub zmianie
wartości w krotce (NIE przy
usuwaniu krotki z relacji) tylko z
tej relacji

Krotkowe CHECK

Warunki uruchomienia

więzów

Typ więzów

Inne potrzeby integralności



Czasami chcielibyśmy reagować w bardziej aktywny
sposób na sytuacje, gdy dochodzi do naruszenia
jakiegoś warunku lub niezmiennika niż tylko nie
dopuszczając do tych modyfikacji bazy danych, która
ten warunek narusza (być może dla pewnych sytuacji
umiemy temu zaradzić).



Czasami chcielibyśmy decydować, kiedy warunek ma
być sprawdzany (choćby ze względu na wydajność),
a w przypadku np. asercji to system o tym decyduje.



Poza tym asercje są „drogie”. Dlatego chcielibyśmy

mieć coś „tańszego” od asercji, co pozwala nam na
nakładanie pewnych więzów na bazę.

WYZWALACZE

Wyzwalacze



Wyzwalacze są testowane tylko przy zajściu
określonego zdarzenia (dołączanie,
usuwanie, modyfikacja krotki) określonego
przez programistę (projektanta bazy) (w
przypadku asercji i więzów krotkowych
CHECK decyduje o tym SZBD)



Testują warunek w chwili zajścia zdarzenia (a
nie uprzedzają go)



Jeśli warunek zostanie spełniony to
przetwarzana jest akcja związania z
wyzwalaczem

Składnia wyzwalaczy

CREATE TRIGGER <

nazwa_wyzwalacza

{BEFORE|INSTEAD OF |AFTER}

[OF <

nazwa_kolumny

>] ON <

nazwa_tabeli

REFERENCING

{

OLD|OLD_TABLE

} AS

nazwa_zmiennej1

{

NEW|NEW_TABLE

} AS

nazwa_zmiennej2

WHEN (<

warunek

lista_poleceń_do_wykonania

[FOR EACH ROW]

Cechy wyzwalaczy



Akcja może być wykonana przed (BEFORE),
po (AFTER) lub zamiast (INSTEAD OF)
zdarzenia



W akcji dostępne są wartości sprzed (OLD,
OLD_TABLE) zajścia zdarzenia jak i nowe
(NEW,NEW_TABLE) wartości



Można określać czy akcja ma być
wykonywana dla każdej modyfikowanej krotki
(FOR EACH ROW) czy tylko raz dla
wszystkich krotek zmodyfikowanych w
pojedynczej operacji

Wyzwalacze (trigger) - przykład

INSERT w „Pracownicy” oznacza, że
akcja reguły zostanie odpalona
bezpośrednio po wykonaniu pojedynczej
operacji wstawienia krotki (fraza

AFTER)

. Fraza "

for each row

" określa, że

akcja realizowana jest na pojedynczej
krotce. Prefiks

New

poprzedzający

nazwę atrybutu „IdZesp” w ciele akcji
reguły jest odwołaniem do wartości tego
atrybutu we wstawianej krotce.

Przykład wyzwalacza

CREATE TRIGGER CenaSieciWyzw

AFTER UPDATE OF cenaSieci ON Dyrektor

REFERENCING

OLD AS Stara

NEW AS Nowa

WHEN (Stara.cenaSieci > Nowa.cenaSieci)

UPDATE Dyrektor SET cenaSieci= Stara.cenaSieci

WHERE cert = Nowa.cert

FOR EACH ROW

Uniemożliwienie obniżenie ceny sieci

Inny przykład wyzwalacza

CREATE TRIGGER WyzwalaczSrCenySieci
INSTEAD OF UPDATE OF cena sieci ON Dyrektor
REFERENCING

OLD_TABLE AS Stara
NEW_TABLE AS Nowa

WHEN (500000 <= (SELECT AVG(cenaSieci) FROM

((Dyrektor EXCEPT Stare) UNION Nowe))

DELETE FROM Dyrektor WHERE

(nazwisko,adres,cert,cenaSieci) IN Stare;

INSERT INTO Dyrektor (SELECT * FROM Nowe);

Zabronienie spadku średniej ceny sieci poniżej 500000

BEZPIECZEŃSTWO

DOSTĘPU DO

BAZY DANYCH

Składowe SQL

Ochrona bezpieczeństwa bazy danych
w SQL



Bezpieczeństwo bazy danych jest bardzo istotną
kwestią w każdej firmie czy korporacji.



Zapewnienie dostępu do systemu bazodanowego

tylko dla osób korzystających z systemu
bazodanowego nie gwarantuje jego bezpieczeństwa.



Takie sytuacje mają miejsce, kiedy dana grupa
użytkowników, lub użytkownik posiadają nadmiarowy
dostęp do bazy danych



Aby zapobiec tego typu sytuacjom, wykorzystuje się

warstwę DCL języka SQL (dodatkowa warstwa obok
DDL, DML, DQL).

Role w bazie danych



Istotną kwestią bezpieczeństwa w każdym SBD są role



Warstwa DDL obejmuje jedynie tworzenie, zmianę i
usuwanie ról



Aby zarządzać rolami należy się odwołać do części
DCL języka SQL, w której to określono jak przydzielać i
poszerzać role.



Są 3 typy ról w SBD:



Systemowe



Bazy danych



Aplikacji

Tworzenie roli

CREATE ROLE

role_name

[ AUTHORIZATION

owner_name

]



W miejscu

role_name

należy podać unikalną w

przestrzeni BD nazwę roli.



Administrator tworząc role może ją „przekazać” dla

innego użytkownika jako jego własną.



Do tego służy opcja AUTHORIZATION, gdzie jako

owner_name

podaje się nazwę użytkownika, który ma

być jego właścicielem.



Pominięcie opcji AUTHORIZATION, powoduje

automatycznie nadanie praw własności do roli osobie
ją tworzącej.

Nadawanie praw innym użytkownikom

GRANT [

lista_praw

|ALL PEIVILIGES]

NazwaObiektu

TO [

lista_identyfikatorów_użytkowników

|PUBLIC]

[WITH GRANT OPTION]

GRANT DELETE, UPDATE (

premia,pensja

)

pracownik

kierownik

;

Wszystkie prawa

jednocześnie

Możliwość przekazywania

praw innym użytkownikom

Wszystkim

użytkownikom

Odbieranie praw dostępu użytkownikom

REVOKE

[GRANT OPTION FOR]

[

lista praw

|ALL PRIVILIGES]

NazwaObiektu

FROM [

lista_identyfikatorów

PUBLIC|RESTRICT|CASCADE]

W wyniku odbierania praw mogłyby powstać tzw. obiekty porzucone dla
których właściciel przestał mieć prawa, które były konieczne do
stworzenia obiektu. Polecenie REVOKE nie jest wykonywane w takim
przypadku, chyba że zawiera opcję CASCADE powodującą wydanie
polecenia DROP dla wszystkich porzuconych perspektyw, dziedzin,
więzów i asercji.
Polecenie REVOKE wydane przez użytkownika służy do odebrania praw
nadanych tylko przez niego.

Indeksy (poza ISO SQL)



Indeks jest pomocniczą (redundantną) strukturą danych

przechowywaną w bazie danych dla potrzeb optymalizacji
dostępu do danych.



Administrator bazy danych zarządza pulą indeksów,

generując nowe indeksy, o ile rozpozna ich potrzebę, lub
je usuwając, jeżeli pewne indeksy są nieprzydatne.



Tak jak indeksy na końcu książki, służące do szybkiego

wyszukiwania stron, indeksy w bazach danych służą do
szybkiego wyszukiwania obiektów.



Zaletą indeksu jest jego stosunkowo mały rozmiar (w

porównaniu do całości bazy danych) oraz
jednoaspektowość wyszukiwania, co umożliwia ich bardzo
efektywną organizację.

Indeksy (poza ISO SQL) cd..



Indeks należy rozumieć jako dwu-kolumnową tablicę,

gdzie pierwsza kolumna zawiera wartości kluczowe, zaś
druga – wartości nie-kluczowe, najczęściej referencje do
obiektów.



Wartości kluczowe są unikalne i służą jako wejście dla

procedury wyszukiwania w indeksie.



Wynikiem wyszukiwania wg danej wartości kluczowej są

wartości niekluczowe w tym samym wierszu tablicy.



Wartości kluczowe są zapamiętane w określonych

atrybutach obiektów bazy danych (dla indeksów gęstych)
lub są reprezentantami przedziałów wartości (dla
indeksów zakresowych).



Istnieją bardziej złożone indeksy odbiegające od tego

schematu.

Baza danych ... ...i indeksy

Prac

Nazwisko ”Nowak”

Zar 2500

PracujeW

Dział

Nazwa ”Sprzedaż”

Lokacja ”Radom”

Zatrudnia

Dział

Nazwa ”Produkcja”

Lokacja ”Kielce”

Zatrudnia

Lokacja ”Kraków”

Prac

Nazwisko ”Barski”

Zar 900

PracujeW

Miasto ”Radom”

Ulica ”Wolska”

NrDomu 12

Adres

Prac

Nazwisko ”Kowalski”

Zar 2000

PracujeW

Indeks dla obiektów Prac
wg atrybutu Nazwisko

Wartość
kluczowa
Barski
Kowalski
Nowak

Wartość
nie-kluczowa
i

Indeks dla obiektów Dział
wg atrybutu Lokacja

Wartość
kluczowa
Kielce
Kraków
Radom

Wartość
nie-kluczowa
i

Inne przykłady indeksów

Indeks tzw. gęsty dla
obiektów Prac wg atrybutu
Stan

Wartość
kluczowa
analityk
kierownik
operator
programista
radca

Wartość
nie-kluczowa
i

, i

Indeks zakresowy dla
obiektów Prac wg
atrybutu Zar

Wartość
kluczowa
500
1000
1500
2500
4500
5000
7500

Wartość
nie-kluczowa
i

, i

Zakładanie indeksu

CREATE [UNIUE] INDEX

NazwaIndeksu

NazwaTabeli

(nazwaKolumny[

Asc|Desc

][,…])

CREATE UNIQUE INDEX

IndNPr

Per

(

PracNr

);

CREATE INDEX

IndeksWynajmow

Nieruchomości

(

Miasto

Czynsz

);

SZBD będzie kontrolował unikalność wartości

kolumn

System zarządzania bazą danych

(DBMS)

Baza danych

DBMS

serwer

Formularze

internetowe

Aplikacje

Interfejs

SQL

Funkcje DBMS:

• Tworzenie nowej bazy

• Przechowywanie danych

• Obsługa zapytań (ang. query)

• Zapewnienie wielodostępności

• Zapewnienie integralności danych

• Ochrona danych (np. archiwizacja)

Komendy SQL

Dostęp do bazy

danych z

poziomu języka

programowania

Język PL/SQL



to rozszerzenie SQL o elementy
programowania



proceduralnego i obiektowego
umożliwiające wykorzystanie:



zmiennych, stałych



Struktur kontrolnych (instrukcji warunkowych),
etykiet i skoków, pętli, kursorów



Wyjątków i mechanizmu obsługi błędów

Struktura blokowa programu w
PL/SQL



Program składa się z jednostek zwanych blokami.



Każdy blok odpowiada problemowi
(podproblemowi)



Bloki mogą być dowolnie zagnieżdżone



Każdy blok składa się z trzech części:



deklaracji (o)



rozkazów (w)



obsługi błędów (o)



bloki mogą być zagnieżdżane w części rozkazów
lub/i części obsługi błędów

Struktura blokowa programu w
PL/SQL

deklaracje

rozkazy

obsługa błędów

Zmienne w PL/SQL



Zmienne proste (np. typu numerycznego,
znakowego, daty)



Zmienne podtypu (np. zdefiniowanego przez
użytkownika)



Zmienne złożone (np. rekordy, tablice, kolekcje,
obiekty)

DECLARE nazwa_zmiennej typ(długość)

[DEFAULT wartość domyślna]

[NOT NULL];

Zmienne rekordowe



Przed zadeklarowaniem zmiennej rekordowej
trzeba zdefiniować typ rekordowy

TYPE Osoba IS RECORD (

nazwisko VARCHAR(60),

imie VARCHAR(30),

pesel…

);

Zmienne tablicowe

Istnieją trzy rodzaj tablic:

•

Tablice indeksowane (index-by tables):
nieograniczony rozmiar, automatycznie rozszerzane,
nie mogą być typem atrybutu w bazie danych, indeks
tablicy może być ujemny

•

Tablice zagnieżdżone (nested tables):
nieograniczony rozmiar, rozszerzane za pomocą
procedury EXTEND, mogą być typem atrybutu w
relacji, mogą być użyte w poleceniach DML, nie
zainicjalizowana tablica jest pusta, przechowywane
fizycznie w osobnej relacji (store table), indeks tablicy
musi być dodatni

Zmienne tablicowe

•

Kolekcje o zmiennym rozmiarze (varrays):
ograniczony i rozszerzalny rozmiar, przechowywane
w zwartej postaci in-line, indeks kolekcji musi być
dodatni

TYPE PracTab is TABLE OF VARCHAR(100)
INDEX BY BINARY_INTEGER

TYPE TabRecOs is TABLE OF Osoba

TYPE Niedziele is TABLE OF DATE

Atrybuty %TYPE, %ROWTYPE

•

% TYPE pozwala nam zadeklarować
zmienną która jest takiego typu jak inna
zmienna lub atrybut w bazie danych

•

Atrybut %ROWTYPE zawiera typ rekordowy
reprezentujący strukturę pojedynczej krotki z
danej relacji. Atrybuty w krotce i
odpowiadające im pola w rekordzie maja te
same nazwy i typy.

DECLARE

nazwisko OSOBA.NAZWISKO%TYPE

osobaRec OSOBA%ROWTYPE

Nadawanie wartości zmiennym

•

Nadanie wartości poprzez przypisanie

•

Nadanie wartości przez wczytanie danych z bazy
danych do zmiennej poleceniem SELECT ... INTO ...

•

Nadanie wartości przez przekazanie zmiennej jako
parametru typu IN OUT lub OUT do procedury lub
funkcji

•

Nadanie wartości przez wczytanie danych z bazy
danych do zmiennej za pomocą klauzuli
RETURNING poleceń INSERT/UPDATE/DELETE

STAŁE

•

Stałe deklarujemy z uyciem słowa
kluczowego CONSTANT. Stała musi zostac
zainicjalizowana podczas deklaracji. Po
utworzeniu stałej jakiekolwiek modyfikacje jej
wartości są niedozwolone.

DECLARE

conGodz CONSTANT NUMBER:=42;

conNazwa CONSTANT VARCHAR(20):=‘’baza_danych”

Typy Danych

•

Typy danych dostępne w PL/SQL nie odpowiadają
dokładnie analogicznym typom dostępnym w SQL.

•

Typy BINARY_INTEGER i PLS_INTEGER: -2

, 2

Ich podtypami są typy: POSITIVE, POSITIVEN,
NATURAL, NATURALN i SIGNTYPE (-1, 0, 1).

•

Typ NUMBER: 10

-130

¸ 10

125

. Jego podtypami są typy:

DECIMAL, INTEGER, FLOAT, REAL, NUMERIC.

•

Typy CHAR, VARCHAR2, RAW, LONG: 32767
bajtów

•

Typ BOOLEAN: TRUE, FALSE, NULL

Podtypy

Każdy typ danych definiuje zbiór poprawnych wartości i

zbiór operatorów, które mogą być zastosowane do
zmiennej danego typu. Podtyp definiuje ten sam zbiór
operatorów co jego typ nadrzędny, lecz zawęża zbiór
poprawnych wartości.

SUBTYPE nazwa IS typ bazowy [ (ograniczenie) ] [ NOT NULL ];

Instrukcja Warunkowa

IF warunek THEN

sekwencja poleceń;

END IF;

IF warunek THEN

sekwencja

poleceń;
ELSE

sekwencja

poleceń;
END IF;

IF warunek1 THEN

sekwencja poleceń;

ELSEIF warunek2
THEN

sekwencja poleceń;

ELSE

sekwencja poleceń;

END IF;

Instrukcja CASE

Instrukcja CASE może występować z selektorem

(selektorem może być dowolnie złożone wyrażenie,
ale najczęściej jest to jedna zmienna) lub z lista
wyrażeń (searched CASE)

CASE selektor

WHEN wartość1 THEN polecenie1;
…
ELSE polecenieN;

END CASE;

CASE selektor

WHEN wyrażenie1 THEN polecenie1;
…
ELSE polecenieN;

END CASE;

Pętla LOOP

Prosta pętla wykonuje się w nieskończoność. Wyjście z

pętli jest możliwe tylko jako efekt wykonania
polecenia EXIT lub EXIT WHEN. W każdym
przebiegu pętli wykonuje się sekwencja poleceń. Po
ich wykonaniu kontrola powraca do początku pętli.

LOOP

sekwencja poleceń;
IF warunek THEN

EXIT;

END IF;

END LOOP;

LOOP

sekwencja poleceń;
EXIT WHEN warunek;

END LOOP;

Pętla While

WHILE warunek LOOP

sekwencja poleceń;

END LOOP;

Pętla FOR

•

Pętla FOR wykonuje się określona liczbę razy. Liczba iteracji
jest określona przez zakres podany miedzy słowami kluczowymi
FOR i LOOP. Zakres musi być typu numerycznego, w
przedziale –2

¸ 2

•

Słowo kluczowe REVERSE odwraca kierunek iteracji

•

Wewnątrz pętli nie wolno nadawać wartości zmiennej iterującej

•

Jeśli dolna granica jest wyższa ni górna granica to pętla nie
wykona się ani razu

•

Obie granice zakresu iteracji nie muszą być statyczne

•

Zmienna iterująca nie musi być wcześniej deklarowana ani
inicjalizowana

•

Do wcześniejszego wyjścia z pętli można użyć polecenia EXIT

Pętla FOR

FOR licznik IN

[REVERSE] dl_gr.. gór_gr LOOP

sekwencja poleceń;

END LOOP;

Polecenia sterujące GOTO i NULL

•

Polecenie GOTO bezwarunkowo przekazuje kontrolę
wykonywania programu do miejsca wskazywanego przez
etykietę związaną z poleceniem. Polecenie NULL nie wykonuje
żadnej akcji.

•

Etykieta musi poprzedzać polecenie wykonywalne

•

GOTO nie może przeskakiwać do warunkowych części poleceń
IF-THEN-ELSE, CASE, do polecenia LOOP i do bloku
podrzędnego

•

GOTO nie może wyskakiwać z podprogramu oraz procedury
obsługi błędu

GOTO etykieta;

…

<<etykieta>>

NULL

;

Kursory

Każde zapytanie SQL umieszczone w programie PL/SQL może zwrócić

zero, jedna bądź wiele krotek. Aby efektywnie przetworzyć krotki
zwrócone przez zapytanie korzystamy z kursorów. Kursor jest obiektem
związanym z zapytaniem. Programista może:

•

Otworzyć kursor (zidentyfikować zbiór wynikowy)

•

Pobrać dana do kursora (odczytać kolejną krotkę z wyniku zapytania i
wpisać ja do kursora)

•

Zamknąć kursor (zwolnić obszar pamięci przydzielony kursorowi)

Kursor to nazwa obszaru roboczego, w którym mieści się wynik zapytania

(result set). Wewnątrz kursora wyróżniamy bieżacy wiersz (current
row). Kursor może być jawny (explicit) lub niejawny (implicit).

Deklarowanie kursora

DECLARE CURSOR nazwa [(lista

parametrów)]

[RETURN typ zwracany] IS zapytanieSQL;

Parametr [IN] typ [{:=|DEFAULT} wartość]

Nazwa kursora nie jest zmienn

, lecz identyfikatorem. Do

kursora nie mo

na przypisa

warto

ci. Parametry s

widoczne

tylko wewn

trz kursora, nie mo

na zwi

z nimi

adnych

ogranicze

Otwieranie kursora

Otwarcie kursora powoduje wykonanie związanego z

nim zapytania i zidentyfikowanie zbioru wynikowego,
zawierającego krotki spełniające kryteria
wyszukiwania.

OPEN nazwa_kursora [(lista parametrów

aktualnych)]

Pobieranie z kursora

•

Każde wykonanie polecenia FETCH powoduje
odczytanie bieżącego wiersza kursora i przesuniecie
znacznika kursora na kolejny wiersz.

•

Na liście zmiennych musi się znajdować taka sama
liczba zmiennych jak liczba atrybutów w kursorze.

•

Odpowiednie zmienne i atrybuty musza się zgadzać
co do typu.

FETCH nazwa_kursora INTO

lista_zmiennych | rekord

Zamykanie kursora

Zamkniecie kursora powoduje, e kursor staje się

nieaktywny a zbiór wynikowy związany z kursorem
staje się niezdefiniowany. Zamknięty kursor można
powtórnie otworzyć, np. z innymi parametrami. Każde
odwołanie się do zamkniętego (lub jeszcze nie
otwartego) kursora powoduje błąd
INVALID_CURSOR.

CLOSE nazwa_kursora;

Atrybuty kursora

•

%FOUND – wartością atrybutu jest TRUE jeśli ostatnia operacja
FETCH odczytała krotkę z kursora. W przeciwnym wypadku
(tzn. kiedy odczyt się nie udał) atrybut przyjmuje wartość
FALSE. Przed pierwszym odczytem atrybut ma wartość NULL

•

%NOTFOUND – wartością atrybutu jest FALSE jeśli ostatnia
operacja FETCH odczytała krotkę z kursora. W przeciwnym
wypadku (tzn. kiedy odczyt się nie udał) atrybut przyjmuje
wartość TRUE. Przed pierwszym odczytem atrybut ma wartość
NULL

•

%ROWCOUNT – wartością atrybutu jest liczba odczytanych z
kursora krotek. Przed pierwszym odczytem atrybut ma wartość
0

•

%ISOPEN – wartością atrybutu jest TRUE jeśli kursor jest
otwarty i FALSE jeśli kursor jest zamknięty.

Obsługa wyjątków w PL/SQL



Błąd lub ostrzeżenie nazywamy w PL/SQL wyjątkiem
(ang. exception). Wyjątki mogą być systemowe
(dzielenie przez zero, brak wolnej pamięci, brak praw do
obiektu) lub definiowane przez użytkownika (za niski
budżet, za wysoka płaca, zbyt mała ilość towaru w
magazynie).



Wystąpienie błędu jest sygnalizowane przez wywołanie
wyjątku. Błędy systemowe sygnalizowane są
automatycznie, błędy definiowane przez użytkownika są
wywoływane ręcznie za pomocą polecenia RAISE.



Po wystąpieniu wyjątku kontrola przechodzi do
procedury obsługi wyjątku (ang. exception handler). Po
jej wykonaniu kontrola przechodzi do kolejnego bloku
nadrzędnego. Jeśli procedura obsługi danego błędu nie
zostanie znaleziona, to wykonywanie programu
zostanie przerwane.

Kontrola błędów

BŁĄD

Predefiniowane wyjątki systemowe

Zmienne podstawienia

Zmienne definiowane przez użytkownika mogą być użyte w

miejsce nazw relacji, atrybutów lub jako wartości atrybutów.
Zmienna podstawienia nie może być pierwszym słowem
polecenia. Zmienne podstawienia są zawsze typu CHAR.
Polecenie DEFINE bez parametrów wyświetla listę wszystkich
zmiennych podstawienia.

DEFINE myTable = ‘’PRACOWNICY”

DEFINE myValue =‘’PROFESOR”

SELECT * FROM &myTable WHERE etat=&myValue

UNDEFINE myTabel

UNDEFINE myValue

Zmienne wiązane

Zmienne wiązane to zmienne deklarowane w SQL*Plus lub innym

środowisku zewnętrznym, które są dostępne w programach
PL/SQL. Mogą służyć do przekazywania wartości z PL/SQL do
SQL i do optymalizacji zapytań. W PL/SQL zachowują się jak
zwykłe zmienne.

VARIABLE x NUMBER

BEGIN

SELECT COUNT (*) INTO:x FROM PRACOWNICY;

END;

PRINT x;

Zmienne wiązane kursorowe

Zmienne wiązane kursorowe pozwalają na odczytanie w SQL*Plus

wyniku zapytania umieszczonego w bloku PL/SQL. Mogą być
stosowane zarówno w anonimowych blokach PL/SQL, jak i jako
parametr lub typ wynikowy procedury lub funkcji.

VARIABLE x REFCURSOR

BEGIN

OPEN:x FOR SELECT * FROM PRACOWNICY;

END;

PRINT x;

Procedury

Procedury (wykonują określone akcje), funkcje (wykonują obliczenia i

zwracają wartości) i pakiety (zbierają w całość logicznie powiązane
procedury, funkcje, zmienne i kursory):

•

przechowywane w bazie danych w postaci skompilowanej i źródłowej
(źródło dostępne poprzez USER_SOURCE)

•

postać skompilowana zwiększenie szybkości działania

•

współdzielone przez wielu użytkowników

Zalety:

•

rozszerzalność

•

Modularność

•

łatwość pielęgnowania kodu

•

możliwość wielokrotnego użycia kodu

•

ukrycie szczegółów implementacji

Definiowanie procedury

CREATE [OR REPLACE] PROCEDURE nazwa_procedury

[(argument,…)] IS
……………….

BEGIN

……………….

END [nazwa_procedury];

•

nazwa procedury musi by

unikalna w ramach schematu (lub

pakietu)

•

dzy s

owami kluczowymi IS i BEGIN umieszczamy

deklaracje

wszystkich zmiennych i kursor

w lokalnych

•

dzy s

owami kluczowymi BEGIN i END umieszczamy kod

PL/SQL, kt

ry wykonuje dana procedura

Argumenty procedur i funkcji

nazwa [IN|OUT| IN OUT] typ [DEFAULT wartość]

•

w li

cie argument

w nie podajemy rozmiaru argumentu (tylko typ)

•

argument formalny: u

ywany w deklaracji procedury i w cz

ęś

wykonywalnej PL/SQL

•

argument aktualny: u

ywany przy wywo

ywaniu procedury

Definiowanie funkcji

CREATE [OR REPLACE] FUNCTION nazwa_funkcji [(argument,…)]

RETURN typ IS
……………….

BEGIN

……………….

END [nazwa_funkcji];

•

nazwa funkcji musi by

unikalna w ramach schematu (lub pakietu)

•

po s

owie kluczowym RETURN umieszczamy typ zwracany przez funkcj

•

dzy s

owami kluczowymi IS i BEGIN umieszczamy deklaracje

wszystkich zmiennych i kursor

w lokalnych

•

dzy s

owami kluczowymi BEGIN i END umieszczamy kod PL/SQL,

wykonuje dana funkcja

•

w kodzie PL/SQL musi si

znale

źć

instrukcja RETURN

Wywołanie procedur i funkcji

VARIABLE nr_zesp NUMBER
VARIABLE l_asyst NUMBER
BEGIN

:nr_zesp:=20
spr_asyst(:nr_zesp,:l_asyst);

END;
PRINT l_asyst;

PL/SQL

SQL

SELECT moja_fun(argument) FROM tabela;

UWAGA: wo

anie funkcji z poziomu SQL jest mo

liwe tylko wtedy

gdy funkcja posiada odpowiedni poziom

„

czysto

”

Czystość funkcji

Aby funkcja mogła być wywoływana z poziomu SQL, musi

ona posiadać odpowiedni poziom czystości

• funkcja wywoływana z instrukcji SELECT nie może

modyfikować żadnych wartości w bazie danych

• funkcja wywoływana z instrukcji INSERT, UPDATE,

DELETE nie może odczytywać i modyfikować żadnej
tabeli, której dotyczy instrukcja

• funkcja wywoływana z instrukcji SELECT, INSERT,

UPDATE, DELETE nie może zawierać instrukcji
sterujących sesją i transakcjami oraz instrukcji DDL

Kompilowanie i usuwanie procedur
lub funkcji

ALTER PROCEDURE | FUNCTION nazwa

COMPILE;

DROP PROCEDURE | FUNCTION nazwa;

Słownik bazy danych

USER_OBJECTS - informacja o obiektach w

schemacie użytkownika

SELECT object_name, object_type, status

FROM user_objects

WHERE object_type IN (‘PROCEDURE’,’FUNCTION’);

USER_SOURCE - kod źródłowy procedur i

funkcji użytkownika

SELECT text

FROM user_source

WHERE name=‘NAZWA PROCEDURY’

ORDER BY line;

Funkcje tablicowe

Funkcje tablicowe dają w wyniku kolekcję krotek. Nazwy tych

funkcji mogą być wykorzystywane zamiast nazw tabel. Funkcje
tablicowe mogą również przyjmować kolekcję krotek jako
parametr. Funkcje mogą być zrównoleglone oraz potokowane,
co zwiększa efektywność przetwarzania poprzez:

•

wielowątkowe wykonanie funkcji

•

eliminację przechowywania wyników pośrednich

•

zmniejszenie czasu odpowiedzi na pierwsze wyniki

•

iteracyjne dostarczanie kolejnych krotek wyniku

W przypadku funkcji potokowanych do dostarczenia krotki do

wyniku służy komenda PIPE ROW (...)

Przykład funkcji tablicowej

CREATE TYPE CharTyp AS TABLE OF VARCHAR2(20);

CREATE OR REPLACE FUNCTION FPracownicy(p_etat CHAR)
RETURN CharTyp PIPELINED AS
BEGIN

FOR x IN ( SELECT * FROM pracownicy WHERE etat=p_etat)
LOOP

PIPE ROW (x.nazwisko);

END LOOP;
RETURN;

END FPracownicy;

SELECT * FROM TABLE( FPracownicy('ASYSTENT') );
SELECT * FROM TABLE( FPracownicy('PROFESOR') );

100

Procedury wyzwalane

Procedura wyzwalana (ang. trigger) to program w języku PL/SQL

(równie Java lub C) który reaguje na zdarzenia zachodzące w
bazie danych i wykonuje się po zajściu określonych warunków.

Typy procedur wyzwalanych:

•

BEFORE - uruchamiane przed wykonaniem polecenia INSERT,
UPDATE, DELETE

•

AFTER - uruchamiane po wykonaniu polecenia INSERT,
UPDATE, DELETE

•

INSTEAD OF – uruchamiane zamiast polecenia INSERT,
UPDATE, DELETE

•

systemowe – uruchamiane po zajściu określonego zdarzenia w
schemacie lub bazie danych

101

Cele stosowania procedur
wyzwalanych

•

Zaawansowane śledzenie użytkowników

•

Ochrona przed nieprawidłowymi transakcjami

•

Wymuszanie więzów referencyjnych (albo więzów nie
wspieranych przez deklaratywne więzy integralnościowe albo
więzów między węzłami rozproszonej bazy danych)

•

Wymuszanie złożonych reguł biznesowych

•

Wymuszanie złożonych polityk bezpieczeństwa

•

Zapewnianie przezroczystego zapisu wydarzeń

•

Wypełnianie atrybutów wartościami domyślnymi

•

Modyfikacja złożonych perspektyw

•

Śledzenie wydarzeń systemowych

102

Definiowanie procedury wyzwalanej

CREATE [OR REPLACE] TRIGGER nazwa
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE } ON { tabela |perspektywa }
[ WHEN warunek ]
[ FOR EACH ROW]
[ DECLARE /* deklaracje zmiennych i kursorów */ ]
BEGIN
/* ciało procedury wyzwalanej */
END;

•

INSTEAD OF: wyzwalacz mo

e by

zdefiniowany tylko na

perspektywie

•

WHEN: wyzwalacz wykonuje si

tylko dla tych krotek, dla kt

rych

jest spe

niony warunek

•

FOR EACH ROW: wyzwalacz wykonuje si

dla ka

dej

modyfikowanej krotki

103

Definiowanie procedury wyzwalanej

Dla procedur wyzwalanych uruchamianych na skutek uaktualnienia

krotek, możemy określić listę atrybutów relacji, których
uaktualnienie uruchomi procedurę.

CREATE OR REPLACE TRIGGER test

AFTER UPDATE OF placa_pod, id_zesp ON

pracownicy ...

104

Definiowanie procedury wyzwalanej

Ta sama procedura może być wrażliwa na kombinacje instrukcji

DML (tj. INSERT, UPDATE, DELETE). (niezgodne ze
standardem)

CREATE OR REPLACE TRIGGER test

AFTER INSERT OR UPDATE OR DELETE ON

pracownicy

BEGIN

IF INSERTING THEN ...

ELSIF UPDATING THEN ...

END IF;

END;

105

Klauzula FOR EACH ROW i WHEN

CREATE OR REPLACE TRIGGER test
BEFORE UPDATE ON pracownicy
FOR EACH ROW WHEN (OLD.placa_dod < 100)
BEGIN

IF (:NEW.placa_pod <= 100) THEN ... END IF;
IF (:NEW.etat != :OLD.etat) THEN ... END IF;

END;

•

w klauzuli WHEN i ciele wyzwalacza FOR EACH ROW mo

uzyska

dost

p do starej i nowej warto

ci atrybutu

•

domy

lnie stara i nowa wersja rekordu s

dost

pne przez nazwy

OLD i NEW (w ciele wyzwalacza poprzedzane dwukropkiem),

na to zmieni

za pomoc

klauzuli REFERENCING NEW AS

•

dla instrukcji INSERT stara warto

ść

jest pusta, dla instrukcji

DELETE nowa warto

ść

jest pusta

106

Procedura wyzwalana INSTEAD OF

Pozwala na zapewnianie modyfikowalności złożonych perspektyw.

CREATE OR REPLACE VIEW zesp_count AS

SELECT z.nazwa, count(*) AS pracownicy

FROM pracownicy p, zespoly z WHERE z.id_zesp = p.id_zesp

GROUP BY z.nazwa;

CREATE OR REPLACE TRIGGER trig_instead

INSTEAD OF INSERT ON zesp_count

FOR EACH ROW

BEGIN

INSERT INTO zespoly(id_zesp,nazwa,adres)

VALUES(80,:NEW.nazwa,NULL);

END;

107

Zarządzanie procedurami
wyzwalanymi

Wszystkie procedury wyzwalane związane z dana relacja można

zablokować (odblokować) pojedynczym poleceniem:

ALTER TABLE nazwa_relacji DISABLE [ENABLE] ALL TRIGGERS;

da procedura wyzwalana mo

e by

w jednym z dw

stan

w: odblokowania lub zablokowania. Do zablokowania

(odblokowania) pojedynczej procedury wyzwalanej s

polecenie:

ALTER TRIGGER nazwa DISABLE [ENABLE];

Do usuni

cia wyzwalacza s

y polecenie

DROP TRIGGER nazwa;

108

Słownik bazy danych

Informacje o procedurach wyzwalanych użytkownika mieszczą się w

perspektywie systemowej USER_TRIGGERS

Informacje o zależnościach można podejrzeć w perspektywie słownika

bazy danych USER_DEPENDENCIES

SELECT TRIGGER_NAME, TRIGGER_TYPE,

TRIGGERING_EVENT, TABLE_NAME, TRIGGER_BODY

FROM USER_TRIGGERS;

SELECT NAME, TYPE, REFERENCED_TYPE

FROM USER_DEPENDENCIES

WHERE REFERENCED_NAME = 'PRACOWNICY';

109

Fazy cyklu
życia
aplikacji z
bazą danych

Konceptualne

projektowanie bazy danych

Planowanie

bazy danych

Definicja systemu

Gromadzenie

i analiza wymagań

Projektowanie

bazy danych

Selekcja SZBD

Projektowanie aplikacji

Logiczne projektowanie

bazy danych

Fizyczne projektowanie

bazy danych

Tworzenie prototypów

Implementacja

Konwersja

i przenoszenie danych

Testowanie

Bieżąca konserwacja

Procedury, operatory, podtablice,
programy…

Typy definiowane przez użytkownika

Abstrakcyjne typy danych (ADT-
abstract data type)

Abstrakcyjne typy danych-cd…

Typy referencyjne

Przykład użycia referencji

Kolekcje – nowy typ danych

Wyszukiwanie z kolekcji

Duże obiekty BLOB (

inary

arge

ject) CLOB

(

haracter

arge

ject)

ADT - dziedziczenie

Tablice z ADT, operacje na ADT

Podtablice i nadtablice

Procedury w SQL3

Rozszerzenia proceduralne w SQL3

Rozszerzenia SQL3 w zakresie
wyzwalaczy

Podsumowanie

Podsumowanie…cd…