Oprogramowanie serwera Oracle7(PL/SQL, procedury, wyzwalacze)

Aplikacje bazodanowe w Internecie

Oprogramowanie serwera Oracle7 (PL/SQL, procedury, wyzwalacze)

Spis tresci

Deklaratywne więzy spójności
System SQL*Plus
Jezyk PL/SQL
Literatura

1. Deklaratywne więzy spójności.

Są dwie podstawowe metody określania więzów spójności na serwerze: albo za pomocą mechanizmu deklaratywnej spójności - w definicjach tabel (instrukcje CREATE TABLE i ALTER TABLE) albo za pomocą mechanizmu wyzwalaczy bazy danych.

Zadaniem więzów spójności jest zagwarantowanie tego, aby dane w bazie danych wiernie odzwierciedlały świat rzeczywisty, dla którego baza danych została zaprojektowana.

Są dwa typy więzów spójności:

więzy spójności encji,
więzy spójności referencyjnej.

Więzy spójności encji ograniczają możliwe wartości, jakie mogą się pojawić w wierszu tabeli.

Wiezy klucza głównego PRIMARY KEY - w określonych kolumnach jednoznacznie identyfikują wiersz. Nie jest dopuszczalna wartość NULL w kolumnach klucza głównego. Automatycznie jest zakładany index na kolumnach tworzących klucz główny. Może być określony tylko jeden klucz główny dla jednej tabeli. wartości w określonych kolumnach jednoznacznie identyfikują wiersz.
Więzy klucza jednoznacznego UNIQUE - Jest dopuszczalna wartość NULL w kolumnach klucza jednoznacznego. Automatycznie jest zakładany jest indeks na kolumnach tworzących klucz jednoznaczny. Może być określony więcej niż jeden klucz jednoznaczny dla jednej tabeli.
Więzy NOT NULL - w kolumnie nie jest dozwolona wartość NULL.
Więzy CHECK - warunek, który ma być prawdziwy dla wszystkich wierszy w tabeli. Nie może zawierać podzapytania ani funkcji zmiennych w czasie jak Sysdate lub User. Może zawierać nazwy jednej lub więcej kolumn.

Więzy spójności referencyjnej zapewniają, że zbiór wartości w kolumnach klucza obcego jest zawsze podzbiorem zbioru wartości odpowiadającego mu klucza głównego lub jednoznacznego.

Deklaratywne więzy spójności mogą być definiowane albo razem z definicją kolumny w-lini albo poza definicją kolumny poza-linią.

w-lini :

[NOT] NULL
{UNIQUE|PRIMARY KEY}
CHECK (warunek)
REFERENCES tabela[(kolumna)][ON DELETE CASCADE]

poza-linią :

{UNIQUE|PRIMARY KEY} (kolumna,...)
CHECK (warunek) FOREIGN KEY (kolumna,...)
REFERENCES tabela(kolumna,...) [ON DELETE CASCADE]

W każdym z powyższych przypadków można poprzedzic więzy napisem CONSTRAINT nazwa_więzów nadając w ten sposób im nazwę, przy pomocy której można się do nich odwoływać, np. wyłączenia lub włączenia.

Klucz główny złożony z jednej kolumny można utwożyć zarówno przy użyciu więzów-w-lini jak więzów-poza-linią. Natomiast klucz główny złożony z więcej niż jednej kolumny może być utworzony tylko przy użyciu więzów-poza-linią.

Przykład :

CREATE TABLE Faktury (
Numer_faktury NUMBER (6,0) PRIMARY KEY
...);
CREATE TABLE Pożyczki (
Numer_konta NUMBER (6,0) ,
Numer_pożyczki NUMBER (6,0) ,
Typ_pożyczki VARCHAR2 (8) CONSTRAINT  typ_poż_ck
		CHECK (Typ_pożyczki IN ('AUTO', 'DOM', 'OSOBISTE')),
Wielkość NUMBER (8,0) CONSTRAINT Wielk_nn NOT NULL,
Data_pożyczki DATE DEFAULT Sysdate,
Zaakceptowana VARCHAR2 (25) CONSTRAINT Zaakcept_ko
				REFERENCES Kierownicy (Nazwisko_kier),
CONSTRAINT Pożyczki_kg PRIMAR KEY (Numer_konta, Numer_pożyczki),
CONSTRAINT Konto_ko FOREIGN KEY (Numer_konta) 
				REFERENCES Klienci (Numer_konta));

2. System SQL*Plus

Jest to interakcyjny system umożliwiający wprowadzanie i wykonywanie:

instrukcji SQL
skryptów SQL i SQL*Plus
bloków PL/SQL
wykonywanie procedur

Można go wykorzystać do tworzenia bardzo prostych aplikacji.

Polecenia:

START plik lub @plik
Wykonaj skrypt poleceń SQL lub PL/SQL
ED plik
Wywołaj edytor standardowy
SPOOL plik
Zapisuj polecenia i wyniki do pliku
SPOOL off
Zamknij plik otwarty przez SPOLL
SET PAUSE ON
Zatrzymaj po wypisaniu jednej stronie
SET PAGESIZE n
Ustaw dlugosc strony w liniach
HOST polecenie
Wykonaj polecenie systemu operacyjnego
EXIT
Wyjscie
DESCRIBE nazwa_tabeli
DESCRIBE PROCEDURE nazwa
DESCRIBE FUNCTION nazwa
Wypisz opisy tabeli/procedury/funkcji
EXECUTE nazwa(...)
Wykonaj procedure/funkcje
SHOW ERRORS
Pokaz bledy

3. Jezyk PL/SQL

Jest to podstawowy język pisania aplikacji w Oraclu. Jest dostępny w wielu programach narzędziowych Oracla.

3.1. Bloki

Są to podstawowe jednostki PL/SQL

    [DECLARE
        -- sekcja dekalracji ]
    BEGIN
        -- instrukcje
    [EXCEPTION
        -- obsluga wyjątków ]
    END;

Bloki mogą być zagnieżdżone. W sekcji instrukcji oprócz konstrukcji
PL/SQL mogą wystąpią instrukcje SQL: DELETE, INSERT, UPDATE, SELECT, COMMIT, ROLLBACK

3.2. Zmienne i stałe.

identyfikator typ_danych [CONSTANT] [NOT NULL] [:=wyrażenie];

Typy dostępne z SQL-a, ponadto:

BOOLEAN
BINARY_INTEGER
wierszowe z tabel SQL (patrz 3.7)
kolumnowe z tabel SQL: nazwa_tabeli.nazwa_kolumny%TYPE
tablicowe
rekordowe
kursory

Uwaga: nazwy zmiennych muszą być różne od nazw kolumn.

Można używać innych zmiennych:

zmiennych aplikacji ( :nazwa)
zmiennych podstawienia SQL*Plusa ( &nazwa)

3.3. Instrukcja SELECT

Umieszczona w bloku PL/SQL nie powoduje wypisywania wyników na ekran.

Ma też szczególną składnie:

SELECT wyrażenie,wyrażenie,...
INTO zmienna,zmienna,... 
FROM tabela,tabela...
[WHERE...]
[GROUP BY...]
[HAVING...]
[FOR UPDATE OF...]

Taka instrukcja musi zawsze zwrócić jeden wiersz. Wpp zostaną podniesione odpowiednie wyjątki: too_many_rows lub no_data_found.

Klazula INTO powoduje umieszczenie wyniku zapytania na zmiennych.
Znaczenie FOR UPDATE OF...

Atrybuty zapytań (dotyczy ostatnio wykonywanego zapytania):

SQL%ROWCOUNT - ile wierszy zwróciło zapytanie
SQL%FOUND=TRUE - gdy przetworzono co najmniej jeden wiersz
SQL%NOTFOUND=TRUE - wpp

3.4. Obsluga bledow.

Zmienne systemowe związane z obsługą błędów:

SQLCODE - kod liczbowy błędu
SQLERRM - tekstowa informacja o błędzie

Wypisywanie na ekranie:

    dbms_output.Put_line(wyrażenie_napisowe)

Ale przedtem w SQL*Plusie ustawiamy:

    SET ServerOutput ON

3.5. Instrukcje PL/SQL.

Standardowe konstrukcje:

    IF warunek THEN instrukcje; 
    [ELSIF warunek THEN instrukcje; ] 
    [ELSIF ...]     
    [ELSE instrukcje; ]
    END IF; 

    LOOP 
       instrukcje z 
          EXIT lub EXIT WHEN warunek;
    END LOOP; 

    WHILE warunek LOOP 
        instrukcje;
    END LOOP; 

    FOR licznik IN [REVERSE] dolna_granica..gorna_granica LOOP 
        instrukcje;
    END LOOP;

Ponadto instrukcja GOTO:

     ...    
     GOTO gdzieś;    -- skocz do instrukcji za etykieta
     ...
     <gdzieś>      -- etykieta
     ...

Instrukcja pusta:

     NULL;

3.6. Typ wierszowy.

W deklaracji zmiennych typu wierszowego piszemy na określenie typu:

        nazwa_tabeli%ROWTYPE

Nie mozna odwolywac sie do calego rekordu - uzywamy notacji kropkowej.

Np.:

    DECLARE 
      Rekosoba OsobaTbl%ROWTYPE; 
    BEGIN 
      SELECT * 
      INTO Rekosoba
      FROM OsobaTbl
      WHERE Name='Ziutek';
      dbms_output.putline(Rekosoba.street||'-'||to_char(Rekosoba.id));
    END;

3.7. Kursory.

Kursor jest specjalnym buforem umożliwiającym dostęp do wyników zapytań.

    CURSOR nazwa IS instrukcja_select; -- bez INTO

Korzystanie:

    OPEN nazwa;                     -- otwórz kursor

W pętli wykonujemy:

FETCH nazwa INTO zmienna,...; -- przechwyć rekord(y)
EXIT WHEN nazwa%NOTFOUND; -- sprawdź czy jesteśmy na końcu

Na koniec:

CLOSE(nazwa); -- zamknij kursor

Atrybuty kursora:

kursor%FOUND - czy sprowadzono kolejny wiersz
kursor%NOTFOUND - czy koniec
kursor%ISOPEN - czy jest otwarty
kursor%ROWCOUNT - liczba sprowadzonych wierszy

Chcąc wykonywać modyfikakcje na wierszach (usuwanie, zmiany) należy założyć na nie blokady. Służy do tego klauzula w instrukcji SELECT:

FOR UPDATE OF kolumna, kolumna...,

którą należy umieścić w deklaracji kursora.
Następnie w instrukcji INSERT lub DELETE zamieścić klauzule:

   WHERE CURRENT OF kursor

Np.:

    DECLARE 
      Rekosoba OsobaTbl%ROWTYPE;  
      CURSOR kursor IS  
        SELECT name,id FROM OsobaTbl 
        FOR UPDATE OF id;
    BEGIN 
      OPEN kursor;
      LOOP 
        FETCH kursor INTO Rekosoba; 
        EXIT WHEN kursor%NOTFOUND;
        IF Rekosoba.id=0
          THEN UPDATE Osoba SET id=id+1  
          WHERE CURRENT OF kuror;
        END IF;
      CLOSE kursor;
    END;

3.8. Wyjątki.

Wyjątki definiowane przez programistę:

    nazwa_wyjątku EXCEPTION;

Podnoszenie (sekcja instrukcji):

  RAISE nazwa_wyjątku;

Obsługa (sekcja EXCEPTION):

   WHEN nazwa_wyjątku THEN instrukcje;       -- obsłuż wyjątek nazwa_wyjątku
   WHEN others THEN instrukcje;     -- obsłuż wszystkie nie obsłużone

Podnoszenie wyjątku powoduje przerwanie wykonywania instrukcji i przekazanie sterowania do sekcji wyjątków. Jesli tam nie nastapi jego obsługa to przekazanie nastapi do sekcji nadrzędnej itd. Jesli nikt nie obsłuzy wyjątku to zostanie przekazany do aplikacji wołajacej bloki PL/SQL-a.

3.9. Rekordy PL/SQL.

Rekordy PL/SQL sa rozszerzeniem typu wierszowego. Ma podobne do niego wlasności:

 DECLARE:  
     TYPE typ_rec_nazwa IS RECORD    
       ( 
          ... deklaracje kolumn ...
       );
     rec_nazwa typ_rec_nazwa;

3.10. Tabele PL/SQL.

Są to dwuwymiarowe i dwukolumnowe tabele. Pierwszą kolumną jest indeks typu BINARY_INTEGER. Druga kolumna jest wartoącią i może byż dowolnego typu z typów wyżej wymienionych. Liczba wierszy jest nieograniczona. Umożliwiają przesyłanie dużych ilosci danych.

  TYPE nazwa_typu IS 
  TABLE OF typ_danych [NOT NULL]
  INDEX BY BINARY_INTEGER;

Np.: typ_danych może być postaci:

  table.column%TYPE
  table%ROWTYPE
  jakis_typ_rekordowy
  jakis_typ_standardowy

Deklaracja zmiennej typu tabelowego:

  tab nazwa_typu;

Funkcje i procedury mogą przyjmować i zwracać wartości typu tabelowego.
Do zmiennych typu tabelowego odwołujemy sie przez indeks:

    tab(i);

Atrybuty tabel PL/SQL:

  tab.EXISTS(i)             -- czy i-ty jest określony 
  tab.COUNT                 -- liczba elementów
  tab.FIRST                 -- pierwszy element tabeli
  tab.LAST                  -- ostatni element tabeli
  tab.PRIOR(i)              -- poprzednik i-tego 
  tab.NEXT(i)               -- następnik i-tego
  tab.DELETE                -- usuń zawartość tabeli
  tab.DELETE(i)             -- usuń i-ty element
  tab.DELETE(i,j)           -- usuń elementy od i do j

Przykład użycia tabeli PL/SQL:

   LOOP
        i:=i+1;                 -- zmienna typu BINARY_INTEGER
        FETCH c1 INTO tab(i);   -- c1 - kursor 
        EXIT WHEN c1%NOTFOUND;  
   END LOOP;

   FOR rec IN (SELECT name FROM anySQLtable) 
   LOOP
        n:=n+1;                -- zmienna typu BINARY_INTEGER
        tab(n):=rec.name;       
   END LOOP;

3.11. Procedury i funkcje.

Definicja procedury:

CREATE [OR REPLACE] 
PROCEDURE nazwa (lista_parametrów_formalnych) 
[AS|IS] 
blok PL/SQL bez słowa DECLARE

Klauzula OR REPLACE pozwala na zmiane istniejacych procedur (i rownież innych obiektów). Lista parametrów formalnych ma postać:

 zmienna [IN|OUT|IN OUT] type [, ...]

Typy parametrów:

IN - parametr przekazywany przez wartość
OUT - parametr przekazywany z procedury do bloku wołajacego
IN OUT - parametr we/wy, domyślny

Definicja funkcji:

CREATE [OR REPLACE] 
FUNCTION nazwa (lista_parametrów_formalnych) 
RETURN typ
[AS|IS] 
blok PL/SQL bez słowa DECLARE

Procedura lub funkcja może podnieść swój własny błąd:

  Raise_application_error(numer_błędu,treść_komunikatu)

Nr błędu powinien być z przedziału: -20000..-20999

Informacje o procedurach i funkcjach:

  DESCRIBE PROCEDURE nazwa
  DESCRIBE FUNCTION nazwa

Jeśli zmieniają się obiekty związane z funkcją lub procedurą, to Oracle automatycznie dokonuje ich kompilacji. Dane o kompilacji mozna odczytać w nastepujacy sposob:

  SELECT status 
  FROM user_objects
  WHERE object_name = 'nazwa_procedury'

Jeśli status jest równy INVALID to procedura wymaga kompilacji, którą możemy przeprowadzić:

  ALTER PROCEDURE nazwa_procedury COMPILE;

Uprawnienia do użycia procedury można nadać tak:

  GRANT EXECUTE 
  ON nazwa_procedury
  TO jakas_osoba;

Funkcje i procedury można przeładowywać.

3.12. Pakiety.

Umożliwiają grupowanie kursorów, zmiennych, stałych, procedur, funkcji i wyjątków w wieksze jednostki.

Pakiety składają się z części publicznej (specyfikacja) i prywatnej (implementacja).

Deklaracja specyfikacji:

  CREATE [OR REPLACE] 
  PACKAGE nazwa_pakietu 
  AS 
    deklaracje obiektów publicznych
    specyfikacja nagłówków fcji/procedur
  END name;

Część implementacyjna:

  CREATE [OR REPLACE] 
  PACKAGE BODY nazwa_pakietu 
  AS   
    Definicje obiektów publicznych i prywatnych
  END nazwa_pakietu;

3.13. Wyzwalacze.

Są to specjalne procedury uruchamiane podczas zajścia operacji na bazie danych. Służą przede wszystkim do testowania warunków spójności i wykonywanie pewnych stałych czynności na b.d.

Wyróżniamy wyzwalacze wierszowe (wykonywane dla każdego wiersza związanego z instrukcją modyfikującą - FOR EACH ROW) lub wyzwalacze dla instrukcji. Zależą od tego czy ma być wykonywany przed (BEFORE) czy po (AFTER) operacji. Wyzwalacze dotyczą instrukcji: INSERT, DELETE, UPDATE.

  CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
  [BEFORE|AFTER] 
  [specyfikacja instrukcji]
  ON tabela
  [FOR EACH ROW [WHEN wyrażenie]] 
  blok PL/SQL bez DECLARE

'specyfikacja instrukcji' jest ciągiem nazw: INSERT -- wyzwalacz dla instrukcji INSERT UPDATE [OF kol,...] -- wyzwalacz dla instrukcji UPDATE [kolumn kol,...] DELETE -- wyzwalacz dla instrukcji DELETE połączonych słowem OR.

Klauzula WHEN z warunkiem (występujaca z FOR EACH ROW) powoduje uruchomienie wyzwalacza dla wiersza jesli jest spełniony warunek.

Wykonanie Raise_application_error(nr_błędu,tekst) powoduje anulowanie wykonania instrukcji, z którą jest związany.

Kolejność wykonywania wyzwalaczy:

przed instrukcją
przed pierwszym operowanym wierszem
po pierwszym wierszu ...
przed ostatnim wierszem
po ostatnim wierszu
po instrukcji

W wyzwalaczu można odwoływać się do starych i nowych wartości w wierszu:

:OLD -- wiersz przed zmianą
:NEW -- wiersz po zmianie

Specjalne zmienne systemowe informują o typie operacji:

INSERTING -- wyzwalacz uruchomiony dla INSERT
DELETING -- wyzwalacz uruchomiony dla DELETE
UPDATING[(kol,...)] -- wyzwalacz uruchomiony dla UPDATE [i kolumny kol,...]

W wyzwalaczach nie wolno używac operacji zwiazanych z transakcjami: COMMIT, ROLLBACK.

Ograniczenia w użyciu wyzwalaczy:

Nie można zmieniać wartości w kolumnie klucza głównego, jednoznacznego lub obcego w tabeli ograniczającej, tzn. takiej z której następuje bezpośrednie lub pośrednie odwołanie do tabeli wyzwalacza za pomocą więzów spójności deklaratywnej.
W wyzwalaczu nie wolno czytać wartości (SELECT... w wyzwalaczu AFTER) w tabeli zmienianej, tzn. w tabeli wyzwalacza lub tabeli odwołującej się do tabeli wyzwalacza poprzez więzy ON DELETE CASCADE.

W przypadku użycia niedozwolonego wyzwalacza wystąpi błąd.

Operacja na wyzwalaczach:

  ALTER TRIGGER nazwa_wyzwalacza [ENABLE|DISABLE]  -- włącz/wyłącz wyzwalacz
  DROP TRIGGER nazwa_wyzwalacza                     -- usuń

Dane o wyzwalaczach są przechowywane w perspektywie User_triggers.

4. Literatura.

"Bazy danych - tworzenie aplikacji" dr. Lech Banachowski
Oracle WebServer User's Guide
referaty z lat ubiegłych.