- 1 -

Tabele

Tabele w ORACLE tworzymy określając ich nazwę oraz poszczególne pola (ich nazwy oraz typu).

Jednocześnie możemy określić więzy integralności dotyczące tabeli.

create table przykl_tab1 (

numer number(5) primary key,

imie varchar(20) not null,

nazwisko varchar(20) not null,

wiek number(3) not null

);

Widoki

Są sposobem przeglądania danych zawartych w tabeli lub zbiorze tabel. Jest to obiekt wirtualny, który

sam w sobie nie zawiera danych, ale pozyskuje dane z bazowych tabel . Z tego powodu widok możemy
traktować jako obiekt wirtualny. Jeśli mamy tabelę zawierającą informacje a część z nich musimy udostępnić,
podczas gdy całość powinna pozostać poufna, należy skorzystać z widoku i udostępniamy jedynie wybrane
kolumny bez konieczności powielania danych do innej tabeli.

Widok jest obiektem struktury, którego używają administratorzy (DBAs) by zastrzec dostęp do niektórych

danych z tabel. Na widoku można wykonywać takie same operacje jak na zwykłej tabeli: tworzyć zapytania,
edytować, wprowadzać nowe dane oraz tworzyć na jego podstawie nowe widoki Są od tego wyjątki:
nie można modyfikować widoku zdefiniowanego jako połączenie dwóch lub więcej tabel - nie można w takim
widoku dodawać, usuwać i zmieniać danych. Wszystkie, działania na widoku powodują zmiany także w jego
bazowej tabeli. Ogólnie rzecz biorąc widok jest wirtualną kopią danych z jednej lub wielu tabel.

Przykład:

CREATE VIEW dept20

AS SELECT ename, sal*12 annual_salary

FROM emp

WHERE deptno = 20;

Przykład z łączeniem tabel:

CREATE VIEW ed AS

SELECT e.empno, e.ename, d.deptno, d.loc

FROM emp e, dept d

WHERE e.deptno = d.deptno

Sekwencje

Są to uporządkowane listy wartości dla specjalnej kolumny w tabeli. Dowolny zbiór liczb układający się

w ciąg arytmetyczny jest sekwencją.
Sposób użycia:

•

można się nią posłużyć przy znajdowaniu konkretnych rekordów wyszukując unikalny numer,

•

ułatwia sortowanie gdyż patrząc na numery można łatwo określić, jak rekordy zostały posortowane
( rosnąco czy malejąco ),

•

przyśpiesza wykonywanie transakcji w środowisku wieloużytkownikowym. Gdy tylko użytkownik
wprowadza nowy rekord serwer automatycznie nadaje mu następną wartość w sekwencji. Jeśli serwer
nie spełniałby tej centralnej roli, zadaniem każdej aplikacji byłoby zapewnienie tego by użytkownicy
nie powielali numerów(bardzo trudne w realizacji). Jednakże, gdy kilka aplikacji wprowadza dane do
tej samej tabeli serwer może podołać temu zadaniu.

•

może służyć jako klucz główny tabeli, ponieważ sekwencja gwarantuje unikalne wartości w rekordzie.

Tworzenie sekwencji "sequence" - bedzie to ciag 5 15 25 35 aż do 100 i w zależności od cycle/nocycle liczy
będą się powtarzać:

CREATE SEQUENCE sequence

INCREMENT BY 10

START WITH 5

MAXVALUE 100

CYCLE | NOCYCLE;

- 2 -

Wykorzystanie sekwencji:

INSERT INTO emp
VALUES (sequence.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20);

Możliwe są:

sequence.CURRVAL
sequence.NEXTVAL

Indeksy

Mechanizmy indeksowania są obecne we wszystkich chyba systemach zarządzania bazą danych. Ich

rolą jest wspomaganie realizacji zapytań o dane z bazy. Można utworzyć jeden lub więcej indeksów dla bazy
danych.

Indeks to struktura, która utrzymuje kolejność w zbiorze poszczególnych pozycji umożliwiając szybki

dostęp do określonych wartości.

Indeksy mają następującą postać:

Warto

ść

_pola_indeksowanego | Fizyczny adres

Natomiast rekord zawierający kolumnę dla której utworzono indeks ma postać:

Fizyczny adres | Warto

ść

_pola_indeksowanego | Warto

ść

_pola | itd.

Odwołując się do wartości pola indeksowanego system odwołuje się bezpośrednio do fizycznego

adresu rekordu, do którego odnosi się dany indeks, unikając dodatkowych operacji dyskowych, które byłyby
konieczne, gdyby system miał przeszukiwać każdy rekord oddzielnie.

Bez indeksu serwer musiałby skanować (przeszukiwać) całą bazę danych by znaleźć poszukiwaną

informację, natomiast z właściwym indeksem serwer przechodzi wprost do żądanej informacji. Tabela, która
posiada indeks nazywana jest tabelą o organizacji indeksowej. Zwykli użytkownicy nie muszą się przejmować
istnieniem indeksów. Są one ważne dla administratorów oraz projektantów baz danych.

Indeks musi być uaktualniany przy każdej zmianie danych w tabeli - co znacznie spowalnia operację

uaktualniania i dodawania w danym polu.

Jednocześnie indeksy znacznie przyśpieszają wyszukiwanie danych. Dlatego są użyteczne jeśli tabele są

przeszukiwane przez indeksowane kolumny.

Przykład:

CREATE INDEX emp_idx
ON scott.emp (ename)
PARALLEL 5;

Konwersja typów

Poniższe zdanie SQL wyłuska rok z daty:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"
FROM DUAL;

Wynik działania:

Year
----
1998

- 3 -

Ogólna postać programu

Poprawnie zbudowany program w PL/SQL składa się z dwóch rozłącznych części: deklarującej

i wykonującej. Część deklarująca zawiera przede wszystkim deklaracje typów, kursorów, stałych, zmiennych
i podprogramów zagnieżdżonych. Podprogramy wraz ze swoimi kodami źródłowymi umieszczane muszą być
na końcu części deklaracyjne). Poza tym kolejność innych elementów części deklaracyjnej jest dowolna.
Podprogramy mogą ponadto być umieszczane w bazie, jako jej niezależne obiekty. Dowiązanie to można
przeprowadzić używając poleceń

CREATE FUNCTION

i

CREATE PROCEDURE

, należących do języka SQL.

Ogólna postać programu:

[DECLARE
deklaracje na poziomie programu ]
BEGIN
instrukcje wykonywalne
[EXCEPTION
obsługa sytuacji wyj

ą

tkowych ]

END [literał];

Procedury

Wśród deklaracji na poziomie programu mogą być procedury i / lub funkcje. Składnia deklaracji

procedury jest następująca:

PROCEDURE nazwa [ ( parametr [,parametr,...]) ] IS
[ deklaracje lokalne ]
BEGIN
instrukcje wykonywalne
[ EXCEPTION
obsługa sytuacji wyj

ą

tkowych ]

END [ nazwa ];

Procedury - usuwanie

Usuwanie funkcji i procedury wygląda następująco:

DROP FUNCTION nazwa i DROP PROCEDURE nazwa.

Funkcje

Deklaracja funkcji wygląda następująco:

FUNCTION nazwa [ (parametr [,parametr,...]) ] RETURN typ IS

Każdy element z listy parametrów formalnych w nagłówku podprogramu ma następującą postać:

nazwa_zmiennej [ IN | OUT | IN OUT ] typ [{:= | DEFAULT } warto

ść

]

Przykład funkcji:

Deklaracja:

FUNCTION druga (n NATURAL) RETURN BOOLEAN ;

Definicja:

FUNCTION pierwsza (n NATURAL) RETURN BOOLEAN IS BEGIN
BEGIN
PROMPT n;
RETURN druga (n-1);
END ;

Procedury - wywoływanie

Wywołanie procedury może się odbywać w programie na dwa sposoby - np. procedura:

PROCEDURE p1 (x INTEGER, y REAL ) IS

mo

ż

e by

ć

poprawnie wywołana za pomoc

ą

nast

ę

puj

ą

cych instrukcji:

p1(a,b),

- styl pozycyjny

p1(x=>a, y=>b), - styl zwi

ą

zany

p1(y=>b, x=>a).

- styl zwi

ą

zany

- 4 -

Ograniczenia

Każdy podprogram, podobnie jak program podstawowy, może zawierać deklaracje następnych

podprogramów. Zmienne zadeklarowane na poziomie (pod)programu dostępne są we wszystkich
zadeklarowanych w nim podprogramach.

Uwaga! Z używaniem funkcji własnych w języku PL/SQL związane jest jedno ograniczenie. Nie mogą one

pojawiać się w poleceniach SQL'owych.

To znaczy, że następujące polecenie:

INSERT INTO

zbiór

VALUES (Funkcja(x));

jest niepoprawne, o

ile

Funkcja

symbolizuje funkcję własną użytkownika. Inne ograniczenie związane jest z typem wartości

parametrów formalnych zarówno dla procedur, jak i dla funkcji. Muszą to być tzw. typy nieograniczone
(nieokrojone). Poprawny jest więc w tym przypadku typ

CHAR

, a niepoprawny typ

CHAR(20)

.

Analogiczna uwaga dotyczy typów wartości wyrażeń zwracanych przez funkcje.

SELECT liczba FROM tablica WHERE liczba>silnia(4);

wygeneruje błąd! Należy wykonać np.:

x:=silnia(4);

SELECT liczba FROM tablica WHERE liczba>x;

Typy zmiennych, stałe

Typy zmiennych:

Typ

Opis

VARCHAR2(rozmiar)

Ci

ą

g znaków o zmiennej długo

ś

ci. Maksymalna długo

ś

c : 4000 znaków , minimalna - 1 znak.

Specyfikacja maksymalnej długo

ś

ci jest niezb

ę

dna.

NVARCHAR2(rozmiar)

Ci

ą

g znaków o zmiennej długo

ś

ci. Maksymalna długo

ś

c jest reprezentowana przez ilo

ś

c bajtów

niezb

ę

dn

ą

do reprezentacji pojedynczego znaku.Maksymalna długo

ś

c : 4000 znaków. Specyfikacja

maksymalnej długo

ś

ci jest niezb

ę

dna.

NUMBER(p,s)

Liczba maj

ą

ca p miejsc calkowitych i s miejsc po przecinku

LONG

Ci

ą

g znaków o zmiennej długo

ś

ci. Maksymalna długo

ś

c 2 GB

DATE

Data od 1 stycznia 4712 p.n.e do 31 grudnia 9999 n.e

RAW(rozmiar)

Czyste dane o dlugo

ś

ci równej ilo

ś

ci bajtów. Maksymalna długo

ś

c: 4000 bajtów

LONG RAW

Czyste dane o dlugo

ś

ci równej ilo

ś

ci bajtów. Maksymalna długo

ś

c: 2 GB

ROWID

Szestnastkowy ciag reprezentuj

ą

cy logiczny adres krotki zorganizowanej w indeks. Minimalny

rozmiar - 1 bajt.

UROWID

Szestnastkowy ciag reprezentuj

ą

cy logiczny adres krotki zorganizowanej w indeks. Maksymalny

(i defaultowy) rozmiar - 4000 bajtów.

CHAR(rozmiar)

Ci

ą

g o stałej długo

ś

ci. Maksymalny rozmiar - 2000 bajtów. Standardowy - 1 bajt.

NCHAR(rozmiar)

Ci

ą

g o stałej długo

ś

ci. Maksymalny rozmiar okre

ś

lony ilo

ś

ci

ą

bajtów na znak - 2000 bajtów.

Standardowy - 1 bajt.

CLOB

Obiekt zawieraj

ą

cy du

ż

e ilo

ś

ci tekstu (do 4 GB) gdzie jeden znak jest reprezentowany przez

jeden bajt.

NCLOB

Obiekt zawieraj

ą

cy du

ż

e ilo

ś

ci tekstu (do 4 GB) gdzie jeden znak jest reprezentowany przez

kilka bajtów.

BLOB

Du

ż

y binarny plik o maksymalnym rozmiarze 4 GB.

BFILE

Zawiera lokacj

ę

binarnego pliku przechowywanego na zewn

ą

trz bazy danych.Maksymalny rozmiar 4

GB

Składnia deklaracji zmiennych:

zmienna TYP [([NOT NULL] := warto

ść

_pocz

ą

tkowa ];

Przykłady:

x1 INTEGER(4,2) := 23.45 ;
x2 REAL NOT NULL := 1 ;
x3 VARCHAR2 NOT NULL ;<- to jest niepoprawne

^^^ ^^^wymusza nadanie pocz

ą

tkowej warto

ś

ci

- 5 -

W niektórych sytuacjach do nadawania typu może być pomocny atrybut

% TYPE

. Zwraca on typ

istniejącej już stałej, zmiennej lub kolumny w tablicy. Sposób używania atrybutu

% TYPE

ilustrują następujące

przykłady:

x1 NUMBER(7,2):= 1.00;
x3 x1 % TYPE := 2.00 ;
x4 scott.miasto.nr_miasta % TYPE ;

Atrybut

% TYPE

zwraca tylko odpowiedni typ, a ewentualną wartość początkową należy ustalić na

nowo. Metoda polegająca na użyciu atrybutu % TYPE może być użyteczna na przykład wtedy, gdy nie
pamiętamy, jakie typy wartości mają kolumny w tablicy, którą chcemy przetwarzać.

Instrukcje sterujące

Instrukcja warunkowa:

IF war_log1 THEN
instrukcje....
[ELSIF war_log2 THEN
instrukcje.....]
[......................]
[ELSE
instrukcje...........]
END IF;

Przykład:

DECLARE
x SMALLINT ;
y SMALLINT := 10;
BEGIN
IF y>1 THEN x:= 1 ;
ELSIF y >2 THEN x := 2;
ELSIF y >3 THEN x := 3;
ELSE x:=99;
ENDIF;
END;

Pętla:

Przykład:

DECLARE
wynik INTEGER := O ;
licznik SMALLINT := 1 ;

BEGIN
LOOP
wynik := wynik + POWER(licznik,2);
licznik := licznik+1 ;
IF licznik > 100 THEN EXIT ;
END IF ;
END LOOP;
END;

Pętla ograniczona jest dyrektywami

LOOP

i

END LOOP

. Wewnątrz pętli (i tylko tam) dopuszczalna jest

dyrektywa

EXIT

, która przenosi wykonanie za dolne ograniczenie pętli.

Dyrektywa

EXIT

może być rozszerzona o klauzulę

WHEN warunek_logiczny

. Przerwanie wykonania pętli

nastąpi wtedy, gdy, że

warunek_logiczny

będzie spełniony np.:

EXIT WHEN licznik > 100;

Dodatkowo przy instrukcjach pętli możliwe jest stosowanie etykiet co umożliwia dokładniejsze sterowanie
przebiegiem pętli :

LOOP

................
LOOP
......................
EXIT etykieta [ WHEN warunek ]
END LOOP ;

END LOOP etykieta ;

- 6 -

Przed górnym ograniczeniem pętli można umieścić dyrektywę:

WHILE warunek_logiczny

. Wtedy

przed każdym obrotem pętli warunek będzie sprawdzany. Obrót będzie wykonany pod warunkiem jego
spełnienia. Pętlę wyliczającą sumę kwadratów można również napisać w następujący sposób:

WHILE licznik <= 100
LOOP
wynik := wynik + POWER(licznik,2);
licznik := licznik + 1 ;
END LOOP;

Podobnie jak w innych językach nie zalecanie jest stosowanie instrukcji

GOTO

.

Składnia :

GOTO etykieta

.

Z instrukcją

GOTO

związanych jest kilka ograniczeń :

•

Nie wolno wykonywać skoków do wnętrza struktur IF -

END IF, LOOP - END LOOP, BEGIN - END

.

•

Nie wolno wykonywać skoków z wnętrza podprogramów.

•

Nie wolno przeskoczyć z handlera sytuacji wyjątkowych do związanego z nim podprogramu.

Stosowanie kursorów

Możliwości oferowane przez podstawowe instrukcje języka

SQL

takie jak:

INSERT, UPDATE, DELETE, SELECT

i

LOCK TABLE

zwiększają się znacznie na skutek zastosowania kursorów jawnych. Wraz z każdą operacją w

tablicy, ORACLE rezerwuje obszar roboczy i kursor, który uaktywnia kolejne wiersze w podzbiorze tablicy,
wynikającym z zakresu operacji. Po wykonaniu operacji w ostatnim wierszu kursor jest zamykany.
Kursor ten jednak jest niejawny co oznacza, że z zewnątrz nie jest dostępna informacja, który wiersz jest
aktualnie aktywny. Sytuacja ta ulega zmianie, jeśli zastosować mechanizm używania kursorów jawnych. Kursor
jawny można zadeklarować w każdej części deklaracyjnej programu

PL/SQL'a

. Jest on dostępny wszędzie

tam, gdzie dostępne są zmienne deklarowane równolegle z nim. Ogólna postać deklaracji kursora jawnego
jest następująca:

CURSOR nazwa_kursora IS zdanie_select

Następujące trzy instrukcje pozwalają przetwarzać kursor jawny:

•

OPEN nazwa_kursora

- uaktywnienie dostępnego kursora,

•

FETCH nazwa_kursora INTO lista_zmiennych

- przypisanie zmiennym pamięci wartości pól

aktywnego rekordu,

lista_zmiennych

musi być zgodna ze zbiorem wynikowym w zdaniu

SELECT

należącego do deklaracji kursora,

•

CLOSE nazwa_kursora

- zamknięcie kursora i związanego z nim obszaru roboczego.

Następujący program stanowi prosty przykład zastosowania mechanizmu kursorów jawnych:

DECLARE
imie przykl_tab1.imie % TYPE ;
nazwisko przykl_tab1.nazwisko % TYPE;
wiek przykl_tab1.wiek % TYPE;

CURSOR moj_kursor IS SELECT imie, nazwisko, wiek FROM przykl_tab1
WHERE imie = 'JAN'
ORDER BY imie ;
BEGIN
OPEN moj_kursor;
LOOP
FETCH moj_kursor INTO imie, nazwisko, wiek ;
PROMPT imie, nazwisko, wiek ,'\n';
PROMPT '***********************************************';
EXIT WHEN ( moj_kursor % NOTFOUND);
END LOOP ;
CLOSE moj_kursor;
END;

Deklaracje kursorów, podobnie jak podprogramów, mogą zawierać listę parametrów formalnych.

Składnia definicji kursora sparametryzowanego jest następująca:

CURSOR nazwa (parametr [.parametr,...]) IS SELECT ...

przy czym parametr określa się w następujący sposób:

nazwa_zmiennej [ IN] typ_warto

ś

ci [{:= | DEFAULT} warto

ść

]

- 7 -

Parametr kursora może być odbierany tylko w trybie

IN

. Fakt ten może być zapisany w jego określeniu

lub pominięty. Wartość początkowa ma identyczne znaczenie, jakie miała w deklaracjach podprogramów.

Parametry kursora współpracują z frazą

WHERE

zdania

SELECT

, które go określa. Kursor

sparametryzowany będzie obsługiwał różne podzbiory tablicy (lub kilku tablic), w zależności od wartości
parametrów aktualnych. Na przykład, jeśli kursor został zadeklarowany z pomocą polecenia:

CURSOR k (i CHAR :='JAN') IS SELECT imie, nazwisko, wiek FROM przykl_tab1 WHERE imie = i;

to otwarcie tego kursora poleceniem

OPEN k

zwiąże go z rekordami z tablicy

przykl_tab1

,w których

imie='JAN'

. Jeśli kursor otworzymy instrukcją

OPEN k('ANNA');

to zostanie on związany z rekordami, których

pole

imie='ANNA'

.

Kursorów można używać także do wykonywania operacji modyfikacji lub usuwania rekordów z tablic, w

których aktywny jest kursor. Odpowiednie zdanie SELECT, określające kursor, musi wtedy zawierać frazę

FOR

UPDATE

, a zdanie

UPDATE

lub

DELETE

frazę

WHERE CURRENT OF nazwa_kursora

.

Technikę tę ilustruje następujący przykład:

DECLARE
CURSOR kurs IS SELECT imie, wiek FROM przykl_tab1 FOR UPDATE ;
w przykl_tab1.wiek % TYPE ;
i przykl_tab1.imie % TYPE;

BEGIN
OPEN kurs ;
LOOP
FETCH kurs INTO i,w ;
EXIT WHEN kurs %NOTFOUND ;
IF i= 'JAN' THEN
UPDATE przykl_tab1 SET wiek=wiek+1 WHERE CURRENT OF kurs ;
ELSIF imie='ANNA' THEN
UPDATE przykl_tab1 SET wiek=wiek-1 WHERE CURRENT OF kurs ;
END IF ;
END LOOP ;

COMMIT ;
CLOSE kurs ;
END;

Stosowanie wyjątków

Każdy blok lub podprogram napisany w języku

PL/SQL

może zawierać moduł obsługi własnej błędów,

który należy umieścić nad kończącą go dyrektywą

END

. Moduł obsługi własnej należy rozpocząć, używając

dyrektywy

EXCEPTION

. Typowe wyjątki są sklasyfikowane i można je rozróżniać, używając predefiniowanych

literałów.

Przykład użycia:

DECLARE x NUMBER;
BEGIN
x:=1/0 ; x :=x+10 ;
EXIT;

EXCEPTION
WHEN ZERO_DIVIDE THEN
PROMPT 'NIE DZIEL PRZEZ ZERO!';
END;

Budowa modułu obsługi błędów:

EXCEPTION
WHEN wyj

ą

tek1 THEN

instrukcje ...
[WHEN wyj

ą

tek2 THEN

instrukcje ...]
[WHEN OTHERS THEN
instrukcje ...]

- 8 -

Lista predefiniowanych wyj

ą

tków w PL/SQL:

CURSOR_ALREADY_OPEN

powstaje w czasie próby otwarcia kursora ju

ż

otwartego. P

ę

tla FOR rekord IN kursor

automatycznie otwiera kursor, tote

ż

jej wykonanie po wcze

ś

niejszym otwarciu jawnym

kursora te

ż

wygeneruje ten wyj

ą

tek.

DUP_VAL_ON_INDEX

powstaje w czasie próby dopisania rekordu z wyra

ż

eniem indeksowym, identycznym jak

rekord ju

ż

istniej

ą

cy w tablicy. Dotyczy indeksów unikalnych, zało

ż

onych przy pomocy

polecenia CREATE UNIQUE INDEX.

INVALID CURSOR

powstaje w czasie próby wykonania nielegalnej operacji na kursorze, na przykład,
zamkni

ę

cia kursora nie otwartego.

INVALID_NUMBER

powstaje, gdy w poleceniu SQL'owym nast

ę

puje próba wpisania tekstu do pola

numerycznego.

LOGIN_DENIED

powstaje w czasie próby rejestrowania w bazie ORACLE, przy zastosowaniu niewła

ś

ciwej

nazwy u

ż

ytkownika lub hasła.

NO_DATA_FOUND

powstaje, gdy polecenie SELECT INTO nie zwraca

ż

adnego wiersza, lub przy próbie

odnoszenia si

ę

do nie zainicjowanego wiersza w tablicy PL/SQL'owej.

NO_LOGGED_ON

powstaje podczas próby kontaktowania si

ę

z baz

ą

bez wcze

ś

niejszego zarejestrowania

si

ę

w niej.

STORAGE_ERROR

powstaje, gdy program wyszedł poza pami

ęć

, lub gdy nast

ą

piła awaria pami

ę

ci.

TOO_MANY_ROWS

pojawia si

ę

, gdy polecenie SELECT INTO wybrało wi

ę

cej ni

ż

jeden rekord.

TRANSACTION_BACKED_OUT

powstaje,

gdy

ORACLE

wycofał

transakcj

ę

z

powodu

sprzeczno

ś

ci

wewn

ę

trznych

powstaj

ą

cych w bazie.

VALUE_ERROR

powstaje z powodu bł

ę

dów arytmetycznych i konwercyjnych w sytuacjach, których nie

obejmuje wyj

ą

tek INVALID_NUMBER.

ZERO_DIVIDE

powstaje przy próbie dzielenia liczby przez zero.

Uzupełnieniem wyjątków może być tworzenie transakcyjnych punktów kontrolnych :

BEGIN
i:=1;

SAVEPOINT punkt_kontrolny;
INSERT INTO przykl_tab1 VALUES (i,'TOMASZ','TOMASZEWSKI',23);
COMMIT;

EXEPTION
WHEN DUP_VAL_ON_INDEX
i:=i+1;
ROLLBACK TO punkt_kontrolny;

END;

Stosowanie triggersów

Triggers'y inaczej wyzwalacze to nic innego jak procedury napisane w języku PL/SQL, Java, czy C, które

wywoływane są w momencie gdy tablica lub widok(perspektywa) jest modyfikowana lub w sytuacji gdy
użytkownik wywoła pewne zdarzenia systemowe.

Podobnie jak funkcje i procedury także i triggersy są przechowywane jako obiekty bazodanowe.

Triggersy są podobne swą budową do procedur mogą się składać z linii kodu w PL/SQL jak też zawierać
wywołania wcześniej napisanych procedur. Główna różnicą pomiędzy wyzwalaczem, a procedurą jest fakt w
jaki sposób jest on wywoływany. Procedura jest wywoływana przez użytkownika, określony program lub
wyzwalacz. Wyzwalacz lub wyzwalacze są uruchamiane przez SZBD, w momencie gdy zaistnieje określone
zdarzenie w systemie bez względu na to kto jest zalogowany lub jakiej używa aktualnie aplikacji.
Zastosowania triggersów:

•

automatyzować przetwarzanie i uaktualnianie informacji w bazie

•

zapobiegać niedozwolonym transakcjom

•

zwiększać bezpieczeństwo bazy

•

zapewniać zachowanie więzów referencyjnych w różnych sytuacjach

•

zapewnić równoległa replikację obiektów BD

•

sporządzać różnorodne statystyki, informować o zdarzeniach, które zaistniały w BD

Mimo niewątpliwych zalet wyzwalaczy należy używać ich z duża rozważnością. Zbyt duża liczba triggersów
występujących w bazie może powodować trudności z zapanowaniem nad zdarzeniami pojawiającymi się w
BD, a także spowolnić działanie BD.

- 9 -

Przykład:

AFTER UPDATE OF WIEK ON PRZYKL_TAB1
WHEN (PRZYKL_TAB1.WIEK < 40)
FOR EACH ROW

BEGIN
dbms_output.put('NOWA WARTO

ŚĆ

: ' || :new.wiek);

dbms_output.put('STARA WARTO

ŚĆ

:' || :old.wiek);

END;

Triggersy typu zamiast

("INSTEAD-OF")

znajdują zastosowanie razem z perspektywami. Modyfikowanie

perspektyw za pomocą poleceń

DELETE, INSERT

idt.

Może powodować dwuznaczność polegającą na tym, iż nie wiadomo czy wstawiamy rekordy tylko do

perspektywy, czy też do tabel, które są źródłem perspektywy.

CREATE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- manager nowych informacji
FOR EACH ROW

DECLARE
empCount NUMBER;

BEGIN

SELECT COUNT(*) INTO empCount /* Najpierw upewnij si

ę

,

ż

e liczba pracowników działu jest wi

ę

kszy ni

ż

jeden

*/

FROM emp e
WHERE e.deptno = :n.deptno;

IF empCount >= 1 THEN /* Je

ś

li b

ę

dzie wystarczaj

ą

co du

ż

o pracowników, a nast

ę

pnie dokona

ć

go mened

ż

era */

UPDATE dept d
SET manager_num = :n.empno
WHERE d.deptno = :n.deptno;
END IF;
END;
/

Użytkownicy – tworzenie

Użytkownik systemu operacyjnego, konta email, lub systemu zarządzania bazą danych jak Oracle

Server - musi mieć tożsamość utrzymaną przez całą sesję użytkownika. Tak jak wiele innych systemów, Oracle
Server wprowadził identyfikację poprzez nazwę użytkownika (

username

). Przed tym jak użytkownik będzie mógł

korzystać z bazy danych, należy stworzyć (

username

) danego użytkownika oraz podać sposób jego

identyfikacji.

Przykład:

CREATE USER janek
IDENTYFIED BY mojeauto;

Profile

Limity zgrupowane w profile umożliwiają kontrolę jak dużo sesji użytkownik może otworzyć, ile danych

czytać podczas sesji, jak długo sesja może stać bezczynnie przed rozłączeniem jej.

Sposób użycia:

CREATE PROFILE db_student LIMIT
SESSIONS_PER_USER1
CONNECT_TIME

50

IDLE_TIME

10;

Przydzielanie profilu przy tworzeniu uzytkownika:

CREATE USER roman
IDENTIFIED BY superman
PROFILE db_student;

- 10 -

Lub jeśli użytkownik już istnieje:

ALTER USER staszek
PROFILE db_student;

Użytkownicy – usuwanie

Jeśli użytkownik nie stworzył żadnych obiektów:

DROP USER janek;

Jeśli użytkownik stworzył własne obiekty i chcemy je także usunąć:

DROP USER janek CASCADE;

Jeśli nie chcemy, aby wraz z usunięciem danego użytkownika zostały usunięte także jego obiekty

możemy skorzystać z innej metody. Zamiast usuwać go i jego obiekty możemy unieważnić przywilej

CREATE

SESSION

dla niego:

REVOKE CREATE SESSION
FROM janek;

Usunięcie przywileju tworzenia sesji, zabezpieczamy się przed połączeniem się użytkownika z Serwerem.

Tak więc, nawet jeśli użytkownik posiadał obiekty w szczególnej bazie danych, usunięcie komendy tworzenia
sesji efektywnie zabezpieczy dostęp użytkownika do tych obiektów.

Użytkownicy – przeglądanie

Informacje o użytkownikach są przechowywane w słowniku i są dostępne poprzez widok

DBA_USERS

.

Ten widok przechowuje nazwę użytkownika, zaszyfrowane hasło, oraz informacje określone podczas tworzenia
lub zmiany danych użytkownika. Jeśli chcemy zobaczyć wszystkich użytkowników, którzy są obecnie
podłączeni, możemy użyć komendy:

SELECT username
FROM dba_users;

Przywileje – przyznawanie

Przywileje są to prawa do wykonywania określonych operacji. The Oracle Server posiada wiele różnych

przywilejów do kontroli wszystkiego, począwszy od stworzenia schematu obiektów aż do przyznawania
przywilejów innym użytkownikom. Ustawienie przywilejów określamy podczas tworzenia użytkownika (

user's

security domain

). Zawiera ono tylko te przywileje, które zostały wyraźnie przyznane użytkownikowi, ale także

te, które użytkownik otrzymuje poprzez role.

•

Obiektowe są to prawa do wykonywania operacji na obiektach (

Update table, Insert rows,

Delete rows

).

•

Systemowe natomiast to prawa do wykonywania operacji ogólnych (

Create Any Cluster, Create

Database Link, Alter Any Index, Grant Any Procedure, Create Role, Select Any
Sequence, Alter Session, Drop Any Synonym, Alter Any Table, Drop Tablespace, Create

User

).

Przykład przydzielenia przywileju:

GRANT SELECT
ON klasa
TO staszek;

klauzula WITH GRANT OPTION pozwala na przekazywanie uprawnie

ń

przez Staszka.

klauzula WITH ADMIN OPTION pozwala na przekazywanie uprawnie

ń

systemowych.

Można udostępnić tylko wybrane kolumny:

GRANT SELECT
UPDATE (nazwisko, wiek)
ON klasa
TO staszek;

- 11 -

Przywileje – usuwanie

Usunięcie przywilejów pociąga za sobą efekt "następnego razu" np.: jeśli użytkownik jest zalogowany, a

my usuniemy mu możliwość logowania się, to dopiero gdy się wyloguje i będzie chciał się ponownie
zalogować, wtedy brak tego prawa uniemożliwi mu zalogowanie się.

Przykład usuwania przywileju systemowego:

REVOKE ALTER SESSION
FROM janek;

Przywileje dla procedur

Efektywną drogą do redukcji liczby wyraźnego przyznawania przywilejów użytkownikom jest

przyznawanie użytkownikom prawa wykonywania

EXECUTE

procedur, funkcji lub pakietów. Użytkownik, który

posiada prawo do procedury tworzenia nowego rekordu Studenci, nie potrzebuje mieć prawa do samej
tablicy Studenci. Procedura działa, ponieważ uruchamia się ona nie z prawami użytkownika, który ją wywołał,
ale z prawami użytkownika, który ją stworzył. Nadanie prawa wykonania

EXECUTE

części programu jest

podobne do nadania jakiegokolwiek obiektowego przywileju:

GRANT EXECUTE
ON create_student
TO janek;

Kolejna korzyść nadawania prawa wykonywania

EXECUTE

części programu wyszczególnionych

obiektów jest taka, że można dokładnie określić limit, co użytkownik jest w stanie zrobić.

Przywileje – przeglądanie

Dwa widoki słowników są przydatne do wyświetlenia informacji na temat przywilejów nadanych

użytkownikowi:

DBA_SYS_PRIVS

i

DBA_COL_PRIVS

. Dla każdego użytkownika lub roli, którym zostały nadane

przywileje Systemowe,

DBA_SYS_PRIVS

zawiera nazwę prawa oraz kiedy zostało nadane i przez kogo. W celu

zobaczenia tych informacji należy użyć następującej komendy:

SELECT *
FROM dba_sys_privs;

Aby uzyskać podgląd przywilejów określonego użytkownika użyj klauzuli

WHERE

:

SELECT *
FROM dba_sys_privs
WHERE grantee = 'janek';

Role – tworzenie

Rolę tworzy się tak samo jak tworzy się użytkownika, poprzez podanie nazwy i hasła:

CREATE ROLE ksi

ę

gowy

IDENTIFIED BY lubieliczby;

Bez hasła:

NOT IDENTIFIED

Po utworzeniu roli, należy dodać prawa do tej roli używając komendy

GRANT

:

GRANT SELECT, INSERT, UPDATE
ON finanse
TO ksi

ę

gowy;

W oddzielnej komendzie możemy dodać przywileje systemowe, takie jak tworzenie sesji:

GRANT CREATE SESSION
TO ksi

ę

gowy;

- 12 -

Ta separacja jest konieczna, ponieważ nie możemy dodać jednocześnie systemowych i obiektowych

przywilejów.

Role – przyznawanie

Jeśli rola jest już utworzona i zdefiniowana, możemy ją przyznać użytkownikom lub innym rolom.

Na przykład w celu nadania użytkownikom: janek, staszek, roman roli księgowy, należy wykonać następującą
komendę:

GRANT ksi

ę

gowy

To janek, staszek, roman;

Role – włączanie

Po dodaniu roli użytkownikowi, nie ma on natychmiastowego dostępu do wszystkich przypisanych mu

praw i ról. W celu włączenia ról, użytkownik musi wydać komendę, specyfikującą które role uruchomić:

SET ROLE ksi

ę

gowy

IDENTIFIED BY lubi

ę

liczby;

Wyjątkiem od tego wymagania, że użytkownik musi włączyć role, jest domyślna rola lub role. Nawet jeśli

domyślna rola jest zabezpieczona hasłem, użytkownik nie musi podawać hasła w celu otrzymania roli:
użytkownik otrzymuje ją automatycznie kiedy utworzy sesję. Do włączenia wszystkich ról, użytkownik może użyć
komendy:

SET ROLE ALL

Sposobem włączenia ról automatycznie przy każdym logowaniu się jest zdefiniowanie domyślnej roli dla

użytkownika. Jeśli stworzymy rolę po stworzeniu użytkowników, musimy użyć następującej komendy:

ALTER USER janek
DEFAULT ROLE ksi

ę

gowy;

Rezultatem tej komendy, za każdym razem jak JANEK będzie się łączył, będą przydzielone mu wszystkie

prawa z roli księgowego.

Role – usuwanie

Analogicznie do składni usuwania tabel:

DROP ROLE rola1;

Role – przeglądanie

Dwa słowniki są używane w celu obejrzenia informacji o rolach:

DBA_ROLES

i

BDA_ROLE_PRIVS

. Pierwszy

zawiera nazwy wszystkich ról występujących w bazie danych, razem z informacją czy hasło jest wymagane do
włączenia roli.

SELECT role
FROM dba_roles;

Podobny widok

DBA_ROLE_PRIVS

, zawierający informacje, które role są dodane różnym użytkownikom i innym

rolom.

SELECT grantee, granted_role
FROM dba_role_privs;

- 13 -

SQLLoader

Tabele w ORACLE możemy wypełnić danymi pochodzącymi z plików w formacie CSV. Aby załadować plik postaci:

0001,Taub,Caleb,S,24-APR-98
0002,Sandor,Penya,,12-MAR-92
0003,Glickman,Gayle,,27-JUN-94
0004,Murphy,Ann,,25-FEB-95
0005,Greene,Donald,G,16-JUL-70
0006,Greene,Jennifer,R,12-SEP-92
0007,Deutsch,Jon,,04-OCT-97
0008,Hurley,John,,02-MAR-98
0009,Klimczak,Rhonda,,09-MAR-70
0010,Kaplan,Todd,R,11-FEB-68
0011,Hudson,Hoyt,,05-AUG-94
0012,Buberel,Jason,,09-NOV-96
0013,Verberkmoes,Ryan,,17-DEC-97
0014,Booey,Baba,,12-NOV-95

należy stworzyć plik sterujący LOAD1.CTL postaci:

LOAD DATA
INFILE 'PLAYER.TXT'
INTO TABLE BASEBALL_PLAYER
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(player_id,last_name,first_name,middle_initial,start_date)

a następnie uruchomić SQLLoadera z następującymi parametrami:

SQLLDR system/manager CONTROL=LOAD1.CTL LOG=LOAD1.LOG
BAD=LOAD1.BAD DISCARD=LOAD1.DSC

EXP

To program służący do eksportu danych. Składnia eksportu jest następująca:

exp USERID=scott/tiger TABLES=dept

lub

exp USERID=system/manager TABLES=(scott.emp,scott.dept)

albo

exp USERID=system/manager OWNER=sales

jeśli chodzi nam o wszystkie obiekty danego

usera

.

IMP

To program służący do importu danych. Składnia importu jest następująca:

imp userid=scott/tiger file=expdat.dmp inne opcje]

Przegląd podstawowej składni języka SQL

Główna forma zapytania SELECT

DISTINC oznacza nie powtarzaj

ą

ce si

ę

wiersze.

SELECT [DISTINC] nazwy(a)_kolumny /*
FROM nazwa_tabeli ;

Selekcja wierszy z tabeli z warunkiem

SELECT [DISTINC] nazwy(a)_kolumny
FROM nazwa_tabeli
WHERE nazwa_kolumny operator_porównania warto

ść

;

- 14 -

Uporządkowanie wierszy

SELECT [DISTINC] nazwy(a)_kolumny /*
FROM nazwa_tabeli
ORDER BY nazwy(a)_kolumn(y) / numer(y)_kolumn(y) [ASC/DESC-rosn

ą

co/malej

ą

co] ;

Operatory porównania

= równe
< mniejsze ni

ż

!= nie równe
<= mniejsze ni

ż

lub równe

> wi

ę

ksze ni

ż

>= wi

ę

ksze ni

ż

lub równe

Warunek iloczynowy (AND)

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE warunek AND warunek ;

Alternatywny warunek zapytania

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE warunek OR warunek ;

Wyszukiwania zakresowe (BETWEEN)

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE nazwa_kolumny
[NOT] BETWEEN warto

ść

_1 AND warto

ść

_2 ;

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE warto

ść

[NOT] BETWEEN nazwa_kolumny_1 AND nazwa_kolumny_2

Poszukiwanie wzorców znakowych (LIKE)

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE nazwa_kolumny
[NOT] LIKE "napis" ;

Wyszukiwanie wartości NULL

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE nazwa_kolumny IS [NOT] NULL ;

Operatory porównania zbioru (IN)

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE nazwa_kolumny
[NOT] IN (warto

ść

_1, warto

ść

_2, .....) ;

Funkcje wbudowane

SELECT funkcja_wbudowana [DISTINCT] nazwa_kolumny
FROM nazwa_tabeli
[WHERE warunek] ;

Funkcje wbudowane w SQL

AVG
SUM
MIN
MAX
COUNT (mo

ż

e by

ć

u

ż

yta razem z *

Obliczenia

SELECT nazwy(a)_kolumn(y), wyra

ż

enie arytmetyczne

FROM nazwa_tabeli
[WHERE warunek]
[ORDER BY nazwy(a)_kolumn(y) / *] ;

- 15 -

Operatory arytmetyczne

+ dodawanie
- odejmowanie
* mno

ż

enie

/ dzielenie

Podzapytania

Zapytanie główne:
SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE nazwa_kolumny
operator_porównania / operator_porównania zbioru

pod zapytanie :
(SELECT nazwa_kolumny
FROM nazwa_tabeli
[WHERE warunek] ) ;

Grupowanie

SELECT nazwy(a)_kolumn(y), funkcja_wbudowana(argument)
FROM nazwa_tabeli
[WHERE warunek]
GROUP BY nazwy(a)_kolumn(y)
[HAVING warunek]
[ORDER BY nazwy(a)_kolumny / numer(y)_kolumn(y) [ASC/DESC]] ;

Złączanie tabel

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli, nazwa_tabeli [,nazwa_tabeli, .....]
WHERE warunek_zł

ą

czenia

[AND / OR warunek]
[ORDER BY nazwy(a)_kolumny / numer(y)_kolumn(y)] ;

Operowanie danymi

Wstawianie wierszy do tabeli
INSERT INTO nazwa_tabeli
[(nazwa_kolumny_1, nazwa_kolumny_2, ....)]
VALUES (warto

ść

_1, warto

ść

_2, ...);

INSERT INTO nazwa_tabeli
[(nazwa_kolumny_1, nazwa_kolumny_2, ....)]
VALUES (warto

ść

_1, warto

ść

_2, ...)

SELECT nazwy(a)_kolumn(y)
FROM nazwa_tabeli
WHERE warunek ;

INSERT INTO Klienci
VALUES (1,'Piotr','Kowalski','Aktualny','[phone]'); >
INSERT INTO Klienci (Imie,Nazwisko)
SELECT Imie,Nazwisko
FROM Klienci2
WHERE ID_Klienta >500;


UPDATE nazwa_tabeli / nazwa_perspektywy
SET nazwa_kolumny_1=warto

ść

/ wyra

ż

enie_arytmetyczne,

..........
nazwa_kolumny_n=warto

ść

/ wyra

ż

enie_arytmetyczne

[WHERE warunek];

UPDATE Klienci
SET ID_Klienta =
(SELECT ID_Klienta FROM Klienci,Adresy
WHERE Klienci.ID_Klienta=Adresy.ID_Klienta
AND Adres='Katawice');>

Kasowanie wierszy z tabeli
DELETE FROM nazwa_tabeli
[WHERE warunek] ;

Definiowanie danych, tworzenie tablic

CREATE TABLE nazwa_tabeli
(nazwa_kolumny_1 typ_danych [NOT NULL]
nazwa_kolumny_2 typ_danych [NOT NULL]
...........
nazwa_kolumny_n typ_danych [NOT NULL] ;

CREATE TABLE Klienci
(ID_Klienta Number(4) PRIMARY KEY,

- 16 -

Imie Varchar2(20) NOT NULL,
Nazwisko Varchar2(20) NOT NULL,
Status Varchar2(10) DEFAULT 'Aktualny');

CREATE TABLE Nieaktualne
AS SELECT * FROM Klienci
WHERE Status != 'Aktualny';

Definiowanie danych, zmiana tablic

ALTER TABLE nazwa_tabeli
ADD nazwa_kloumny typ_danych;

ALTER TABLE Klienci
ADD (Telefon Varchar2(10));

DROP TABLE nazwa_tabeli ;

Definiowanie danych, tworzenie indeksu

CREATE [UNIQUE] INDEX nazwa_indeksu
ON nazwa_tabeli (nazwy(a)_kolumny [ASC/DESC]);

Definiowanie danych, usuwanie indeksu

DROP INDEX (nazwa_indeksu);

Definiowanie danych, tworzenie synonimu

CREATE SYNONYM nazwa_synonimu
FOR nazwa_tabeli / nazwa_perspektywy;

Definiowanie danych, usuwanie synonimu

DROP SYNONYM nazwa_synonimu;

Definiowanie danych, tworzenie perspektywy

CREATE VIEW nazwa_perspektywy
AS instrukcja_zapytania_w_SQL ;

Definiowanie danych, usuwanie perspektywy

DROP VIEW nazwa_perspektywy;

Administrowanie danymi

Przyznanie uprawnienia:
GRANT ALL / SELECT / UPDATE / INSERT / DELETE / INDEX / ALTER

dla obiektu:
ON nazwy(a)_tabel(i) / nazwa_perspektyw(y)

dla u

ż

ytkownika:

TO nazwy(a)_u

ż

ytkowników(a);

Usuni

ę

cie uprawnie

ń

REVOKE ALL / SELECT / UPDATE / INSERT / DELETE / INDEX / ALTER

ON nazwy(a)_tabel(i) / nazwa_perspektyw(y)

TO nazwy(a)_u

ż

ytkowników(a);