BD Oracle ściąga

- 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

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

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)

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)

g znaków o zmiennej długo

ci. Maksymalna długo

c jest reprezentowana przez ilo

c bajtów

niezb

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

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)

g o stałej długo

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

NCHAR(rozmiar)

g o stałej długo

ci. Maksymalny rozmiar okre

lony ilo

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

y binarny plik o maksymalnym rozmiarze 4 GB.

BFILE

Zawiera lokacj

binarnego pliku przechowywanego na zewn

trz bazy danych.Maksymalny rozmiar 4

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

- 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

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

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

. 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

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

powodu

sprzeczno

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

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

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

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

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','(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,

- 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);