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

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 zade-klarowanych 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

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

IFy>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 ograni-czenie 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 ;

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 instrukcja 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ść ]

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 uzycia:

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 ...]

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.

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 -- new manager information

FOR EACH ROW

DECLARE

empCount NUMBER;

BEGIN

/* First check to make sure that the number of employees

* in the department is greater than one */

SELECT COUNT(*) INTO empCount

FROM emp e

WHERE e.deptno = :n.deptno;

/* If there are enough employees then make him or her the manager */

IF empCount >= 1 THEN

UPDATE dept d

SET manager_num = :n.empno

WHERE d.deptno = :n.deptno;

END IF;

END;

/

---