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;
- 1 -
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
- 2 -
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
- 3 -
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
Ciąg znaków o zmiennej długości. Maksymalna długośc : 4000 znaków , minimalna - 1 znak.
VARCHAR2(rozmiar) Specyfikacja maksymalnej długości jest niezbędna.
Ciąg znaków o zmiennej długości. Maksymalna długośc jest reprezentowana przez ilośc bajtów
NVARCHAR2(rozmiar) 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
Szestnastkowy ciag reprezentujący logiczny adres krotki zorganizowanej w indeks. Minimalny
ROWID
rozmiar - 1 bajt.
Szestnastkowy ciag reprezentujący logiczny adres krotki zorganizowanej w indeks. Maksymalny
UROWID
(i defaultowy) rozmiar - 4000 bajtów.
CHAR(rozmiar)
Ciąg o stałej długości. Maksymalny rozmiar - 2000 bajtów. Standardowy - 1 bajt.
Ciąg o stałej długości. Maksymalny rozmiar określony ilością bajtów na znak - 2000 bajtów.
NCHAR(rozmiar)
Standardowy - 1 bajt.
Obiekt zawierający duże ilości tekstu (do 4 GB) gdzie jeden znak jest reprezentowany przez
CLOB
jeden bajt.
Obiekt zawierający duże ilości tekstu (do 4 GB) gdzie jeden znak jest reprezentowany przez
NCLOB
kilka bajtów.
BLOB
Duży binarny plik o maksymalnym rozmiarze 4 GB.
Zawiera lokację binarnego pliku przechowywanego na zewnątrz bazy danych.Maksymalny rozmiar 4
BFILE
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
- 4 -
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 ;
- 5 -
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ść ]
- 6 -
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 ...]
- 7 -
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.
- 8 -
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;
- 9 -
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;
- 10 -
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;
- 11 -
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;
- 12 -
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ść;
- 13 -
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) / *] ;
- 14 -
+ 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','(032)255-45-98'); >
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,
- 15 -
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);
- 16 -