Laboratorium Baz Danych

O R A C L E

PL/SQL

1. Wstęp

PL/SQL jest proceduralnym rozszerzeniem SQL'a firmy ORACLE. Programy napisane w języku PL/SQL mogą być uruchamiane nie tylko w środowisku SQL*Plus'a, ale również w wyspecjalizowanych narzędziach takich jak SQL*DBA, SQL*Forms, SQL*Menu oraz w OCI (Oracle Programmatic Interfaces).

PL/SQL jest językiem strukturalnym. Kod zapisywany jest blokami otoczonymi zdaniami BEGIN i END. Zmienne mogą być deklarowane zdaniem. Dla zmiennych PL/SQL'a używanych do kolumn bazy danych można podać atrybut %TYPE, co oznacza żądanie dynamicznego określenia typu w czasie wykonywania. Wszelkie zmiany w typie danych automatycznie spowodują zmianę typu zmiennej PL/SQL'a. Zbędne są jakiekolwiek zmiany w kodzie.

PL/SQL pozwala stosować wszystkie instrukcje SQL'a manipulowania danymi, włączając w to instrukcje INSERT, UPDATE, DELETE i SELECT, również operacje na kursorach i przetwarzanie transakcyjne. Bloki PL/SQL'a mogą zawierać dowolną liczbę instrukcji SQL'a.

2. Podstawy

2.1. Notacja syntaktyczna

Notacja używana w przykładach:

< > element syntaktyczny

-- rozpoczęcie wiersza komentarza

/* rozpoczęcie wielowierszowego komentarza

*/ zakończenie wielowierszowego komentarza

. separator występujący między nazwą obiektu i nazwą komponentu

.. separator pomiędzy dolną i górną wartością przedziału

... zaznaczenie kontynuacji kodu

Notacja używana w opisie składni instrukcji PL/SQL:

[ ] opcjonalny element

{ 'D jeden z elementów

| separator alternatywy wyboru

... oznaczenie powtarzalności elementu

2.2. Typy danych

Każda stała i zmienna jest określonego typu. PL/SQL udostępnia stosowanie typów skalarnych i złożonych

Typy skalarne Typy złożone

----------------------------------------------------- ------------------------------

BINARY_INTEGER CHAR RECORD TABLE

DEC CHARACTER

DECIMAL LONG

DOUBLE PRECISION LONG RAW

FLOAT RAW

INT ROWID

INTEGER STRING

NATURAL VARCHAR

NUMBER VARCHAR2

NUMERIC

POSITIVE DATE

REAL

SMALLINT BOOLEAN

2.3. Konwersja typów danych

Jawna konwersja typów:

| do

z | CHAR DATE NUMBER RAW ROWID

----------------------------------------------------------------------------------------------------------------------------------

CHAR | TO_DATE TO_NUMBER HEXTORAW CHARTOROWID

DATE | TO_CHAR

NUMBER | TO_CHAR TO_DATE

RAW | RAWTOHEX

ROWID | ROWIDTOCHAR

2.5. Bloki PL/SQL'a

PL/SQL jest blokowo zorientowanym językiem. Oznacza to, że programem PL/SQL są logiczne bloki, które mogą zawierać dowolną liczbę bloków zagnieżdżonych. Blok (podblok) pozwala grupować logicznie powiązane deklaracje i instrukcje. Deklaracje są lokalne dla bloku i przestają istnieć po zakończeniu wykonywania instrukcji bloku.

PL/SQL blok składa się z trzech części: opcjonalnej części deklaracji, wykonywalnej części i opcjonalnej części obsługi wyjątków.

[DECLARE

-- deklaracje]

BEGIN

-- instrukcje

[EXCEPTION

-- obsługa wyjątków]

END;

Bloki można zagnieżdżać w części wykonywalnej i części obsługi wyjątków bloku PL/SQL.

2.6. Zmienne i stałe

Zmienne i stałe należy deklarować w części deklaracji dowolnego bloku PL/SQL, podprogramu czy pakietu.

birthdate DATE;

emp_count SMALLINT := 0;

acct_id VARCHAR2(5) NOT NULL := 'AP001';

Użycie konstruktora NOT NULL wymaga zastosowania klauzuli inicjalizacji zmiennej lub stałej.

pi CONSTANT REAL := 3.14159;

radius REAL := 1;

area REAL := pi * radius**2;

Po słowie kluczowym CONSTANT należy podać typ stałej i zainicjalizować deklarowaną stałą. Deklarowane identyfikatory w bloku muszą być unikalne.

W deklaracjach nie można odwoływać się do niezadeklarowanych zmiennych i stałych. Również nie można wyliczać zmiennych i stałych po przecinku.

2.7. Atrybut %TYPE

Atrybut %TYPE dostarcza typ danych zmiennej, stałej lub kolumny tabeli.

credit REAL(7,2);

debit credit%TYPE;

Deklaracja przez %TYPE może zawierać klauzulę inicjalizacji.

balance NUMBER(7,2);

minimum_balance balance%TYPE := 10.00;

Użycie atrybutu %TYPE pozwala uwolnić kod od konieczności zmiany typu zmiennej, po zmianie typu bazowego. Jest on szczególnie użyteczny, gdy są deklarowane zmienne, których typ jest związany z typem kolumn tabel.

Konstruktora NOT NULL nie można używać w deklaracjach zmiennych z użyciem %TYPE.

2.8. Atrybut %ROWTYPE

Atrybut %ROWTYPE dostarcza typ reprezentujący wiersz tabeli lub perspektywy. Rekord przechowuje cały wiersz tabeli.

DECLARE

emp_rec emp%ROWTYPE;

CURSOR c1 IS SELECT deptno, dname, loc FROM dept;

dept_rec c1%ROWTYPE;

...

Kolumny wiersza odpowiadają polom rekordu przy zachowaniu zgodności nazw i typów. Dostęp do pól rekordu wymaga zastosowania notacji kropkowej.

IF emp_rec.deptno = 20 THEN ...

Deklaracja %ROWTYPE nie może zawierać klauzuli inicjalizacji. Mimo tego istnieją dwa sposoby przypisania wartości do wszystkich pól rekordu. Pierwszy polega na wykorzystaniu kursora a drugi na wykorzystaniu instrukcji SELECT ..INTO ...

2.9. Zakres identyfikatorów

Identyfikatory deklarowane w bloku PL/SQL są lokalne dla tego bloku a globalne dla bloków zagnieżdżonych. Jeżeli globalne identyfikatory zostaną powtórnie zadeklarowane w bloku zagnieżdżonym, to w celu odwołania się do identyfikatorów globalnych należy użyć kwalifikatora. Kwalifikatorem dla bloku jest etykieta umieszczana przed słowem kluczowym DECLARE.

<<outer>>

DECLARE

birthdate DATE;

BEGIN

...

DECLARE

birthdate DATE;

BEGIN

...

IF birthdate = outer.birthdate THEN

...

END IF;

END;

END outer;

3. Instrukcje kontroli sterowania

3.1. Instrukcje warunkowe

Dopuszcza się stosowanie trzech form instrukcji IF: IF-THEN, IF-THEN-ELSE, i IF-THEN-ELSIF.

Każda klauzula instrukcja IF musi zawierać przynajmniej jedną instrukcję wykonywalną. Może nią być instrukcja NULL dla spełnienia wymagań formalnych.

IF-THEN

IF warunek THEN

sekwencja_instrukcji;

END IF;

Sekwencja instrukcji jest wykonywana tylko wtedy, gdy warunek ma wartość TRUE. Jeżeli warunek ma wartość FALSE lub NULL, to jest wykonywana następna instrukcja po słowie kluczowym END IF.

IF-THEN-ELSE

IF warunek THEN

sekwencja_instrukcji1;

ELSE

sekwencja_instrukcji2;

END IF;

Sekwencja instrukcji w klauzuli ELSE jest wykonywana tylko wtedy, gdy warunek ma wartość FALSE lub NULL

Klauzule THEN i ELSE mogą zawierać konstrukcje IF.

IF-THEN-ELSIF

IF warunek1 THEN

sekwencja_instrukcji1;

ELSIF warunek2 THEN

sekwencja_instrukcji2;

ELSE

sekwencja_instrukcji3;

END IF;

Jeżeli pierwszy warunek ma wartość FALSE lub NULL, to klauzula ELSIF testuje kolejny warunek. Konstrukcja IF może zawierać dowolną liczbę klauzul ELSIF; końcowa klauzula ELSE jest opcjonalna. Jeżeli jakiś warunek ma wartość TRUE, to po wykonaniu sekwencji instrukcji tej klauzuli jest wykonywana następna instrukcja po słowie kluczowym END IF.

3.2. Pętle

Dopuszcza się stosowanie trzech form instrukcji LOOP : LOOP, WHILE-LOOP i FOR-LOOP.

LOOP

LOOP

sekwencja_instrukcji;

END LOOP;

Konstrukcja wymaga zastosowania instrukcji EXIT wewnątrz pętli.

WHILE-LOOP

WHILE warunek LOOP

sekwencja_instrukcji;

END LOOP;

Przed wykonaniem każdej iteracji jest wartościowany warunek. Jeżeli warunek ma wartość TRUE, to jest wykonywana sekwencja instrukcji. Jeżeli warunek ma wartość FALSE lub NULL, to jest wykonywana następna instrukcja po słowie kluczowym END LOOP.

FOR-LOOP

FOR counter IN [REVERSE] lower_bound..higher_bound LOOP

sekwencja_instrukcji;

END LOOP;

Zakres jest wartościowany tylko przy pierwszym wejściu do instrukcji FOR loop. Sekwencja instrukcji jest wykonywana dopóki licznik pętli będzie należał do zakresu. Po wykonaniu iteracji licznik pętli jest inkrementowany.

Jeżeli początek zakresu jest równy końcowi zakresu, to iteracja jest wykonywana jeden raz. Jeżeli początek zakresu jest większy od końca zakresu, to iteracja nie jest wykonywana i jest wykonywana następna instrukcja po słowie kluczowym END LOOP.

W celu ograniczenia zakresu pętli można użyć literałów, zmiennych lub wyrażeń pod warunkiem, że mogą zostać przeliczone.

j IN -5..5

k IN REVERSE first..last

step IN 0..TRUNC(high/low) * 2

code IN ASCII('A')..ASCII('J')

Licznik pętli może być wykorzystywany wewnątrz pętli.

3.3. Instrukcja pusta NULL

Instrukcja pusta przekazuje kontrolę do następnej instrukcji. Użycie instrukcji pustej zwiększa czytelność i przejrzystość.

...

EXCEPTION

WHEN ZERO_DIVIDE THEN

ROLLBACK;

WHEN VALUE_ERROR THEN

INSERT INTO errors VALUES ...

COMMIT;

WHEN OTHERS THEN

NULL;

END;

4. Interakcja z Oracle'm

PL/SQL pozwala używać wszystkich instrukcji SQL'a operujących na danych ( za wyjątkiem EXPLAIN PLAN), instrukcji kontroli transakcji, funkcji, pseudokolumn i operatorów.

4.1. SQL Funkcje

PL/SQL pozwala używać wszystkich funkcji SQL'a w tym również funkcji agregujących:

* AVG

* COUNT

* MAX

* MIN

* STDDEV

* SUM

* VARIANCE

Funkcji agregujących można używać tylko w instrukcjach SQL'owych, ale nie można używać w instrukcjach proceduralnych.

Postać odwołania do funkcji agregujących:

nazwa_funkcji([ALL | DISTINCT] wyr)

gdzie "wyr" jest wyrażeniem odwołującym się do jednej lub kilku kolumn. Jeżeli zostanie umieszczone słowo ALL (domyślnie), to funkcja agregująca obejmie wszystkie wartości w kolumnie. Jeżeli zostanie umieszczone słowo DISTINCT, to funkcja agregująca obejmie unikalne wartości w kolumnie

Funkcja COUNT pozwala wyspecyfikować znak '*', wynikiem czego będzie liczba wierszy tabeli.

4.2. SQL Pseudokolumny

PL/SQL rozpoznaje następujące pseudokolumny SQL'owe, które zwracają specyficzne wartości:

* CURRVAL - bieżący numer sekwencji

* LEVEL - poziom węzła w strukturze drzewa

* NEXTVAL - następny numer sekwencji

* ROWID - adres wiersza

* ROWNUM - liczba wierszy wybranych przez zapytanie

Do pseudokolumn można odwoływać się w instrukcjach SQL'owych, można wybierać wartości, ale nie można wstawiać, aktualizować i usuwać wartości.

4.3. SQL Operatory

PL/SQL pozwala używać wszystkich operatorów relacji, zbiorów i wierszy w instrukcjach SQL'owych.

Operatory relacji są używane w klauzuli WHERE wyliczając wartość wyrażenia, które może mieć wartość TRUE, FALSE lub NULL. Istnieje możliwość składania warunków używając w tym celu operatorów logicznych AND, OR lub NOT.

ALL - porównuje wartość do każdej wartości z listy lub podzapytania i ma wartość TRUE jeżeli wszystkie poszczególne porównania mają wartość TRUE.

ANY, SOME - porównują wartość do każdej wartości z listy lub podzapytania i ma wartość TRUE jeżeli jedno z porównań miało wartość TRUE.

BETWEEN - testuje czy wartość znajduje się wewnątrz przedziału.

EXISTS - zwraca TRUE jeżeli wynikiem podzapytania jest przynajmniej jeden wiersz

IN - testuje obecność w zbiorze

IS NULL - testuje nulls

LIKE - testuje czy łańcuch pasuje do wzorca

Operatory zbiorów składają wynik z dwóch zapytań

INTERSECT - zwraca wszystkie unikalne wiersze z obu zapytań

MINUS - zwraca wszystkie unikalne wiersze z pierwszego zapytania

UNION - zwraca wszystkie unikalne wiersze z drugiego zapytania

UNION ALL - zwraca wszystkie wiersze z drugiego zapytania ze wszystkimi powtórzeniami

Operatory wierszy odwołują się do poszczególnych wierszy.

ALL - wszystkie wiersze

DISTINCT - unikalne wiersze

PRIOR - odwołuje się do rodzica aktualnego wiersza.

4.4. Kursory

Oracle tworzy robocze obszary nazywane "private SQL areas" dla wykonywania instrukcji SQL'a i przechowywania informacji o procesach. Konstrukcja PL/SQL nazywana "kursorem" pozwala utworzyć i wykorzystać prywatny obszar SQL'a. Rozróżnia się dwa rodzaje kursorów: jawne i niejawne.

PL/SQL niejawnie deklaruje kursor dla wszystkich instrukcji manipulowania danymi(DMS), włączając zapytania zwracające tylko jeden wiersz. Dla zapytań zwracających więcej niż jeden wiersz można jawnie zadeklarować kursor przetwarzający indywidualnie wiersze.

Każdy kursor jawnie zdefiniowany ma cztery atrybuty: %NOTFOUND, %FOUND, %ROWCOUNT i %ISOPEN. Po dołączeniu do nazwy kursora nazwy atrybutu można uzyskać informację o wykonaniu wielowierszowego zapytania. Niejawny kursor SQL ma te same atrybuty co jawny kursor. Pozwalają one uzyskać informację o wykonaniu instrukcji INSERT, UPDATE, DELETE i SELECT INTO.

Atrybutów kursorów można używać w instrukcjach proceduralnych ale nie w instrukcjach SQL.

4.4.1. Jawne kursory

Zbiór wierszy zwróconych przez wielowierszowe zapytanie nazywa się "aktywnym zbiorem". Jego rozmiar odpowiada liczbie wierszy spełniających kryterium wyszukiwania. Jawny kursor wskazuje bieżący wiersz w aktywnym zbiorze. To pozwala programowi przetwarzać pojedynczy wiersz.

Kursor może być kontrolowany przez trzy rozkazy: OPEN, FETCH, i CLOSE. Najpierw należy zainicjować kursor rozkazem OPEN, który identyfikuje aktywny zbiór. Potem, można używając rozkazu FETCH pobierać kolejno wiersze ze zbioru. Po przetworzeniu ostatniego wiersza należy zwolnić kursor poleceniem CLOSE.

Jawne kursory mogą zawierać parametry.

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

SELECT ...

gdzie "parametr" wymaga następującej deklaracji

nazwa_zmiennej [IN] typ [{:= | DEFAULT wartość]

Zakres parametrów kursora jest lokalny co oznacza, że można odwoływać się do parametrów tylko w sekcji deklaracji kursora. Wartości parametrów kursora są kojarzone w zapytaniu w momencie otwarcia kursora.

4.4.2. Niejawne kursory

Oracle niejawnie otwiera kursor do przetwarzania każdej instrukcji SQL'a nie połączonej z jawnie zadeklarowanym kursorem. PL/SQL pozwala odwoływać się do ostatniego niejawnego kursora jak do kursora SQL. Tym samym można używać atrybutów kursora w celu pozyskania informacji o ostatnio wykonanej instrukcji SQL.

Przed otwarciem kursora SQL atrybuty kursora niejawnego są ustawiane na wartość NULL.

4.4.3. Instrukcja OPEN

Instrukcja OPEN wykonuje zapytanie skojarzone z jawnie zadeklarowanym kursorem. Otwarcie kursora wykonuje zapytanie i identyfikuje aktywny zbiór, który zawiera wiersze spełniające kryterium wyszukiwania.

Dla kursorów zadeklarowanych z użyciem klauzuli FOR UPDATE, instrukcja OPEN blokuje te wiersze.

OPEN c1;

Przekazywanie parametrów

Przykład

CURSOR c1 (my_ename CHAR, my_comm NUMBER) IS SELECT ...

instrukcje otwierające kursor:

OPEN c1('ATTLEY', 300);

OPEN c1(employee_name, 150);

OPEN c1('THURSTON', my_comm);

Parametrom formalnym zadeklarowanym z wartościami domyślnymi nie muszą odpowiadać parametry aktualne.

Każdy parametr aktualny musi mieć typ danych kompatybilny z typem odpowiadającego parametru formalnego.

4.4.4. Instrukcja FETCH

Instrukcja FETCH pobiera kolejno wiersze z aktywnego zbioru. Za każdym razem wykonywania instrukcji FETCH kursor jest przesuwany do następnego wiersza aktywnego zbioru.

Typowy sposób użycia instrukcji FETCH:

OPEN c1;

LOOP

FETCH c1 INTO my_record;

EXIT WHEN c1%NOTFOUND;

-- przetwarzanie danych

END LOOP;

4.4.5. Instrukcja CLOSE

Instrukcja CLOSE zwalnia kursor a aktywny zbiór przestaje być zdefiniowany.

CLOSE c1;

Zaraz po zamknięciu kursora można otworzyć go ponownie. Jakakolwiek operacja na zamkniętym kursorze zgłasza predefiniowany wyjątek INVALID_CURSOR.

4.4.6. Atrybut %NOTFOUND

Jawne kursory

Przed pierwszym pobraniem z aktywnego zbioru %NOTFOUND ma wartość NULL. Jeżeli ostatnie pobranie wiersza nie powiodło się, to %NOTFOUND ma wartość TRUE.

LOOP

FETCH c1 INTO my_ename, my_deptno;

EXIT WHEN c1%NOTFOUND;

...

END LOOP;

Jeżeli kursor nie jest otwarty odwołanie do atrybutu %NOTFOUND zgłosi predefiniowany wyjątek INVALID_CURSOR.

Niejawne kursory

%NOTFOUND ma wartość TRUE jeżeli INSERT, UPDATE, or DELETE nie zadziała na żadnym wierszu lub SELECT INTO nie zwróci żadnego wiersza. Poza tymi przypadkami %NOTFOUND ma wartość FALSE.

UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;

IF SQL%NOTFOUND THEN -- aktualizacji nie powiodła się

INSERT INTO temp VALUES (...);

END IF;

Jeżeli SELECT INTO nie zwróci wiersza, to predefiniowany wyjątek NO_DATA_FOUND jest zgłaszany bez względu na to czy jest sprawdzany %NOTFOUND w następnej linii, czy nie.

W takiej sytuacji %NOTFOUND jest użyteczny w programie obsługi wyjątków OTHERS. Zamiast kodować program obsługi NO_DATA_FOUND, można sprawdzić zgłoszenie wyjątku %NOTFOUND.

EXCEPTION

WHEN OTHERS THEN

IF SQL%NOTFOUND THEN -- sprawdzenie 'no data found'

...

END IF;

...

END;

4.4.7. Atrybut %FOUND

Jawne kursory

%FOUND jest logicznym przeciwieństwem %NOTFOUND. Po otwarciu jawnego kursora a przed pierwszym pobraniem %FOUND ma wartość NULL.

LOOP

FETCH c1 INTO my_ename, my_deptno;

IF c1%FOUND THEN -- pobranie udane

INSERT INTO temp VALUES (...);

ELSE -- pobranie nieudane

EXIT;

END IF;

...

END LOOP;

Niejawne kursory

%FOUND jest logicznym przeciwieństwem %NOTFOUND.

DELETE FROM temp_emp WHERE empno = my_empno;

IF SQL%FOUND THEN -- usunięcie udane

INSERT INTO emp VALUES (my_empno, my_ename, ...);

END IF;

4.4.8. Atrybut %ROWCOUNT

Jawne kursory

Otwarcie jawnego kursora zeruje atrybut %ROWCOUNT.

Przed pierwszym pobraniem wiersza %ROWCOUNT zwraca zero. Następnie zwraca liczbę wierszy dotąd pobranych. Licznik jest inkrementowany jeżeli ostatnie pobranie wiersza zakończyło się pomyślnie.

LOOP

FETCH c1 INTO my_ename, my_deptno;

IF c1%ROWCOUNT > 10 THEN

...

END IF;

...

END LOOP;

Niejawne kursory

%ROWCOUNT zwraca liczbę wierszy na których zadziałała instrukcja INSERT, UPDATE, DELETE lub zwróconych przez instrukcję SELECT INTO.

DELETE FROM emp WHERE ...

IF SQL%ROWCOUNT > 10 THEN

...

END IF;

Jeżeli SELECT INTO zwraca więcej niż jeden wiersz, to jest zgłaszany predefiniowany wyjątek TOO_MANY_ROWS i %ROWCOUNT jest ustawiany na 1, a nie na aktualną liczbę wierszy.

4.4.9. Atrybut %ISOPEN

Jawne kursory

%ISOPEN ma wartość TRUE jeżeli kursor jest otwarty, w przeciwnym razie ma wartość FALSE.

IF c1%ISOPEN THEN -- czy kursor otwarty

...

ELSE -- kursor zamknięty

OPEN c1;

END IF;

Niejawne kursory

Oracle automatycznie zamyka kursor SQL po wykonaniu instrukcji SQL. Wynikiem czego %ISOPEN zawsze ma wartość FALSE.

4.4.10. Upakowane kursory

Można oddzielić specyfikację kursora od ciała przez wykorzystanie pakietu z klauzulą RETURN.

CREATE PACKAGE emp_actions AS

/* Deklaracja specyfikacji kursora. */

CURSOR c1 RETURN emp%ROWTYPE;

...

END emp_actions;

CREATE PACKAGE BODY emp_actions AS

/* Definicja ciała kursora. */

CURSOR c1 RETURN emp%ROWTYPE

SELECT * FROM emp WHERE sal > 3000;

...

END emp_actions;

4.4.11. Kursor pętli FOR

Kursor FOR loop niejawnie deklaruje indeks pętli jako %ROWTYPE rekord, otwiera kursor, kolejno pobiera wiersze aktywnego zbioru podstawiając do rekordu, i zamyka kursor po zakończeniu przetwarzania.

DECLARE

salary_total REAL := 0.0;

CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp;

...

BEGIN

FOR emp_rec IN c1 LOOP

...

salary_total := salary_total + emp_rec.sal;

END LOOP;

...

END;

Dopuszcza się używanie aliasów dla komponentów instrukcji select.

CURSOR c1 IS

SELECT empno, sal+NVL(comm,0) wages, job FROM ...

4.4.12. Przekazywanie parametrów

DECLARE

CURSOR c1 (dnum NUMBER) IS

SELECT sal, comm FROM emp WHERE deptno = dnum;

...

BEGIN

FOR emp_rec IN c1(20) LOOP

...

END LOOP;

...

END;

4.4.13. Klauzula ORDER BY alias

DECLARE

CURSOR c1 IS

SELECT empno, ename, sal+comm wages

FROM emp

WHERE job = 'SALESPERSON'

ORDER BY wages;

...

4.5. Instrukcje kontroli transakcji

4.5.1. Zastosowanie instrukcji COMMIT

COMMIT potwierdza zmiany wykonane w bieżącej transakcji. Dopóki nie zostaną potwierdzone zmiany pozostali użytkownicy nie mają dostępu do zmienionych danych. Widzą dane przed zmianami.

BEGIN

...

UPDATE accounts SET bal = my_bal - debit

WHERE acctno = my_acctno;

...

UPDATE accounts SET bal = my_bal + credit

WHERE acctno = my_acctno;

COMMIT;

END;

Instrukcja COMMIT odblokowuje wszystkie wiersze. Również usuwa zaznaczone punkty od momentu ostatniego COMMIT czy ROLLBACK.

4.5.2. Zastosowanie instrukcji ROLLBACK

Instrukcja ROLLBACK jest przeciwieństwem instrukcji COMMIT. ROLLBACK kończy bieżącą transakcję i wycofuje zmiany powstałe od momentu rozpoczęcia transakcji. Instrukcja ROLLBACK jest przydatna z dwóch względów. Po pierwsze po skasowaniu wiersza z tabeli można przy użyciu instrukcji ROLLBACK odzyskać skasowany wiersz. Po drugie, ROLLBACK jest przydatny kiedy rozpoczęta transakcja nie może zostać zakończona z powodu zgłoszenia wyjątku czy błędu wykonania instrukcji SQL. W takiej sytuacji ROLLBACK pozwala wrócić do poprawnego stanu przed rozpoczęciem transakcji.

DECLARE

emp_id INTEGER;

...

BEGIN

SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ...

...

INSERT INTO emp VALUES (emp_id, ...);

INSERT INTO tax VALUES (emp_id, ...);

INSERT INTO pay VALUES (emp_id, ...);

...

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK;

...

END;

4.5.3. Zastosowanie instrukcji SAVEPOINT

SAVEPOINT nazywa i zaznacza bieżącą pozycję w przetwarzaniu transakcji. Użycie ROLLBACK TO powoduje wycofanie zmian do zaznaczonej pozycji.

DECLARE

emp_id emp.empno%TYPE;

BEGIN

...

UPDATE emp SET ... WHERE empno = emp_id;

DELETE FROM emp WHERE ...

...

SAVEPOINT do_insert;

INSERT INTO emp VALUES (emp_id, ...);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO do_insert;

END;

Po wycofaniu zmian wszystkie pozycje, które były zaznaczone później niż punkt, do którego zostały wycofane zmiany są usuwane. Punkt, do którego nastąpiło cofnięcie nie zostaje usunięty. W celu usunięcia takiego punktu należy wykonać instrukcję ROLLBACK lub COMMIT, która jednocześnie usuwa wszystkie punkty.

BEGIN

...

SAVEPOINT my_point;

UPDATE emp SET ... WHERE empno = emp_id;

...

SAVEPOINT my_point; -- przesunięcie punktu my_point

INSERT INTO emp VALUES (emp_id, ...);

...

EXCEPTION

WHEN OTHERS THEN

ROLLBACK TO my_point;

END;

Domyślnie, liczba aktywnych punktów w sesji jest ograniczona do 5.

4.5.4. Klauzula FOR UPDATE

DECLARE

CURSOR c1 IS SELECT empno, sal FROM emp

WHERE job = 'SALESMAN' AND comm > sal FOR UPDATE;

Klauzula FOR UPDATE wskazuje, że wiersze z aktywnego zbioru będą aktualizowane lub usuwane. Otwarcie kursora blokuje wszystkie wiersze aktywnego zbioru. Wiersze zostają odblokowane po użyciu instrukcji COMMIT .

DECLARE

CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE;

...

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO ...

...

UPDATE emp SET sal = new_sal WHERE CURRENT OF c1;

END LOOP;

...

4.6. Wyzwalacze

Wyzwalacze są zapamiętanymi programami PL/SQL skojarzonymi z wyspecyfikowanymi tabelami. Wyzwalacze są wykonywane automatycznie w przeciwieństwie do podprogramów, które wymagają jawnego uruchomienia.

Dopuszcza się stosowanie do 12 wyzwalaczy na tabelę. Aby utworzyć wyzwalacz należy posiadać przywilej CREATE TRIGGER i ALTER na skojarzonej tabeli lub ALTER ANY TABLE.

4.6.1. Tworzenie wyzwalacza

Wyzwalacz składa się z trzech części: zdarzenia, opcjonalnego konstruktora i akcji.

CREATE TRIGGER reorder

/* zdarzenie*/

AFTER UPDATE OF qty_on_hand ON inventory -- tabela

FOR EACH ROW

/* konstruktor */

WHEN (new.reorderable = 'T')

BEGIN

/* akcje */

IF :new.qty_on_hand < :new.reorder_point THEN

INSERT INTO pending_orders

VALUES (:new.part_no, :new.reorder_qty, SYSDATE);

END IF;

END;

Nazwa po klauzuli ON identyfikuje tabelę. Zdarzenie specyfikuje kiedy należy wykonać akcje. Słowo kluczowe AFTER specyfikuje kiedy należy wykonać akcje FOR EACH ROW oznacza, że wyzwalacz jest wykonywany dla każdego wiersza jeżeli zostanie spełniony warunek klauzuli WHEN. Przedrostek ":new" jest odwołaniem do nowej wartości kolumny. Można również używać przedrostka ":old".

CREATE TRIGGER check_salary

BEFORE INSERT OR UPDATE OF sal, job ON emp

FOR EACH ROW

WHEN (new.job != 'PRESIDENT')

DECLARE

minsal NUMBER;

maxsal NUMBER;

BEGIN

/* Get salary range for a given job from table sals. */

SELECT losal, hisal INTO minsal, maxsal FROM sals

WHERE job = :new.job;

/* If salary is out of range, increase is negative, *

* or increase exceeds 10%, raise an exception. */

IF (:new.sal < minsal OR :new.sal > maxsal) THEN

raise_application_error(-20225, 'Salary out of range');

ELSIF (:new.sal < :old.sal) THEN

raise_application_error(-20230, 'Negative increase');

ELSIF (:new.sal > 1.1 * :old.sal) THEN

raise_application_error(-20235, 'Increase exceeds 10%');

END IF;

END;

4.6.2. Rekompilacja

ALTER TRIGGER nazwa COMPILE;

4.6.3. Modyfikacja

CREATE OR REPLACE TRIGGER nazwa ... lub

DROP TRIGGER a następnie CREATE TRIGGER nazwa ...

4.6.4. Włączenie/Wyłączenie

ALTER TRIGGER nazwa ENABLE;

ALTER TRIGGER nazwa DISABLE;

Wyłączenie lub włączenie wszystkich wyzwalaczy związanych z tabelą:

ALTER TABLE nazwa_tabeli DISABLE ALL TRIGGERS;

ALTER TABLE nazwa_tabeli ENABLE ALL TRIGGERS;

4.6.5. Informacja o wyzwalaczach

USER_TRIGGERS, ALL_TRIGGERS, DBA_TRIGGERS

5. Obsługa błędów

5.1. Wyjątki

PL/SQL udostępnia prosty sposób obsługi wyjątków predefiniowanych i zdefiniowanych przez użytkownika. Kiedy zostanie zgłoszony wyjątek następuje przerwanie wykonywania programu i przejście do programu obsługi wyjątku. Predefiniowane wyjątki są zgłaszane niejawnie przez system a wyjątki zdefiniowane wymagają jawnego zgłoszenia przez klauzulę RAISE.

Wyjątki mogą być definiowane w części deklaracji bloku PL/SQL, podprogramu czy wyzwalacza. W części wykonywalnej należy testować pojawienie się wyjątku.

PROCEDURE calc_bonus (emp_id INTEGER, bonus OUT REAL) IS

my_sal NUMBER(7,2);

my_comm NUMBER(7,2);

comm_missing EXCEPTION; -- deklaracja wyjątku

BEGIN

SELECT sal, comm INTO my_sal, my_comm FROM emp

WHERE empno = emp_id;

IF my_comm IS NULL THEN

RAISE comm_missing; -- zgłoszenie wyjątku

ELSE

bonus := (my_sal * 0.05) + (my_comm * 0.15);

END IF;

EXCEPTION -- rozpoczęcie obsługi wyjątków

WHEN comm_missing THEN

-- obsługa błędu

WHEN OTHERS THEN -- obsługa pozostałych wyjątków

...

END calc_bonus;

Opcjonalna zmienna OTHERS obejmuje wszystkie niewyspecyfikowane wyjątki.

5.2. Deklaracja wyjątków

DECLARE

past_due EXCEPTION;

acct_num NUMBER(5);

BEGIN

...

5.3. Zakres działania wyjątków

Nie można zadeklarować wyjątku dwukrotnie w tym samym bloku, ale można w dwóch różnych blokach. Wyjątki deklarowane w bloku są lokalne, natomiast dla bloków zagnieżdżonych są globalne.

Zadeklarowanie wyjątku w bloku zagnieżdżonym przysłania deklarację globalnego wyjątku. Odwołanie do wyjątku z bloku nadrzędnego wymaga następującej konstrukcji:

etykieta_bloku.nazwa_wyjątku

DECLARE

past_due EXCEPTION;

acct_num NUMBER;

BEGIN

...

---------------- początek podbloku ----------------

DECLARE

past_due EXCEPTION; -- powtórzenie deklaracji

acct_num NUMBER;

BEGIN

...

IF ... THEN

RAISE past_due; -- zgłoszenie wyjątku

END IF;

...

END;

-------------------koniec podbloku -------------------

EXCEPTION

WHEN past_due THEN -- zgłoszony wyjątek nie jest obsługiwany

...

END;

5.4. Obsługa wyjątków

Po zgłoszeniu wyjątku następuje przejście do programu obsługi wyjątku.

...

EXCEPTION

WHEN ... THEN

-- obsługa błędu

WHEN ... THEN

-- obsługa błędu

WHEN OTHERS THEN

-- obsługa pozostałych błędów

END;

Po klauzuli WHEN należy podać nazwę wyjątku, następnie po słowie THEN umieścić instrukcje do wykonania.

5.5. Predefiniowane wyjątki

Nazwa wyjątku Błąd

--------------------------------------------------------------------------

CURSOR_ALREADY_OPEN ORA-06511

DUP_VAL_ON_INDEX ORA-00001

INVALID_CURSOR ORA-01001

INVALID_NUMBER ORA-01722

LOGIN_DENIED ORA-01017

NO_DATA_FOUND ORA-01403

NOT_LOGGED_ON ORA-01012

PROGRAM_ERROR ORA-06501

STORAGE_ERROR ORA-06500

TIMEOUT_ON_RESOURCE ORA-00051

TOO_MANY_ROWS ORA-01422

TRANSACTION_BACKED_OUT ORA-00061

VALUE_ERROR ORA-06502

ZERO_DIVIDE ORA-01476

Opis predefiniowanych wyjątków można znaleźć w dokumentacji PL/SQL'a.

5.6. Redeklaracja predefiniowanych wyjątków

W celu odwołania do wyjątku predefiniowanego, przesłoniętego przez użytkownika deklaracją tego wyjątku należy zastosować notację przedstawioną poniżej.

...

EXCEPTION

WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN

-- handle the error

...

END;

5.7. Pragma EXCEPTION_INIT

Do obsługi nienazwanych wewnętrznych błędów należy użyć sekcji OTHERS lub pragmy EXCEPTION_INIT.

Predefiniowana pragma EXCEPTION_INIT umożliwia skojarzenie kodu błędu z nazwą wyjątku, co z koleji umożliwia obsługę wyjątku w części obsługi wyjątków.

PRAGMA EXCEPTION_INIT(nazwa_wyjątku, Oracle_kod_błędu);

gdzie "nazwa_wyjątku" jest nazwą wcześniej zadeklarowanego wyjątku. Pragma musi zanleźć się w dowolnym miejscu części deklaracji po zadeklarowaniu wyjątku.

DECLARE

insufficient_privileges EXCEPTION;

PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);

BEGIN

...

EXCEPTION

WHEN insufficient_privileges THEN

-- obsługa błędu

...

END;

5.8. Instrukcja RAISE

PL/SQL bloki i podprogramy powinny zgłaszać wyjątek jeżeli powstały błąd uniemożliwia normalne zakończenie programu. Można również zgłaszać jawnie predefiniowane wyjątki.

DECLARE

out_of_stock EXCEPTION;

number_on_hand NUMBER(4);

BEGIN

...

IF number_on_hand < 1 THEN

RAISE out_of_stock;

END IF;

...

EXCEPTION

WHEN out_of_stock THEN

-- obsługa błędu

END;

5.9. Propagacja wyjątków

Po zgłoszeniu wyjątku, jeżeli PL/SQL nie może znaleźć sekcji obsługi w bieżącym bloku, to wyjątek jest propagowany na zewnątrz. Propagacja trwa do momentu znalezienia sekcji obsługi błędu lub wygenerowania błędu w przypadku braku obsługi wyjątku.

DECLARE

...

BEGIN

...

---------------- początek podbloku ----------------

DECLARE

past_due EXCEPTION;

BEGIN

...

IF ... THEN

RAISE past_due;

END IF;

...

END;

------------------- koniec podbloku ----------------

...

EXCEPTION

...

WHEN OTHERS THEN

ROLLBACK;

END;

5.10. Przekazanie zgłoszenia wyjątku

Można stosować przekazywanie zgłoszenia wyjątku w sposób jawny.

DECLARE

out_of_balance EXCEPTION;

BEGIN

...

---------------- początek podbloku ----------------

BEGIN

...

IF ... THEN

RAISE out_of_balance; -- zgłoszenie wyjątku

END IF;

...

EXCEPTION

WHEN out_of_balance THEN

-- obsługa wyjątku

RAISE; -- propagacja aktualnego wyjątku

...

END;

------------------- koniec podbloku -------------------

EXCEPTION

WHEN out_of_balance THEN

-- obsługa błędu

...

END;

5.11. Zastosowanie SQLCODE i SQLERRM

W programie obsługi wyjątków można używać funkcji SQLCODE i SQLERRM do sprawdzenia błędu i wysłania komunikatu o błędzie.

DECLARE

msg CHAR(100);

BEGIN

FOR num IN 1..9999 LOOP

msg := SQLERRM(num); -- może być SQLERRM(-num)

INSERT INTO errors VALUES (msg);

END LOOP;

END;

Nie można używać funkcji SQLCODE lub SQLERRM bezpośrednio w instrukcji SQL.

6. Podprogramy

6.1. Podprogramy

Podprogramy są nazwanymi blokami PL/SQL'a, które mogą pobierać parametry i być wykonywane. Rozróżnia się dwa typy podprogramów: procedury i funkcje. Budowa podprogramów jest taka jak bloków PL/SQL'a.

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS

old_balance REAL;

new_balance REAL;

overdrawn EXCEPTION;

BEGIN

SELECT bal INTO old_balance FROM accts

WHERE acctno = acct_id;

new_balance := old_balance - amount;

IF new_balance < 0 THEN

RAISE overdrawn;

ELSE

UPDATE accts SET bal = new_balance

WHERE acctno = acct_id;

END IF;

EXCEPTION

WHEN overdrawn THEN

...

END debit_account;

6.2. Procedury

Procedura jest podprogramem, który wykonuje określoną akcję.

PROCEDURE nazwa [ (parametr [, parametr , ...]) ] IS

[deklaracje lokalne]

BEGIN

instrukcje

[EXCEPTION

obsługa błędów]

END [nazwa ];

gdzie "parametr" ma następującą składnię:

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

Procedura składa się z części specyfikacji i ciała.

Specyfikacja procedury rozpoczyna się słowem kluczowym PROCEDURE i kończy się nazwą procedury lub listą parametrów.

Ciało procedury rozpoczyna się słowem kluczowym IS i kończy się słowem kluczowym END, po którym może pojawić się opcjonalna nazwa procedury.

PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS

current_salary REAL;

salary_missing EXCEPTION;

BEGIN

SELECT sal INTO current_salary FROM emp

WHERE empno = emp_id;

IF current_salary IS NULL THEN

RAISE salary_missing;

ELSE

UPDATE emp SET sal = sal + increase

WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO emp_audit VALUES (emp_id, 'No such number');

WHEN salary_missing THEN

INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');

END raise_salary;

6.3. Funkcje

Funkcja jest podprogramem, który wylicza wartość funkcji. Funkcje i procedury mają taką samą strukturę, z wyjątkiem tego, że funkcja zawiera klauzulę RETURN. Klauzula RETURN określa typ zwracanej wartości. We ciele funkcji należy umieścić instrukcję RETURN z wartością, która ma być zwrócona.

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

[deklaracje lokalne]

BEGIN

instrukcje

[EXCEPTION

obsługa błędów]

END [nazwa ];

gdzie "parametr" ma następującą składnię:

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

Wywołania funkcji w bloku PL/SQL'a lub podprogramie mogą pojawić się w proceduralnych instrukcjach, ale nie w instrukcjach SQL'a.

DECLARE

empnum INTEGER;

...

FUNCTION bonus (emp_id INTEGER) RETURN REAL IS

BEGIN ... END bonus;

BEGIN

...

INSERT INTO payroll

VALUES (empnum, ..., bonus(empnum)); -- nieprawidłowe wywołanie

END;

6.4. Instrukcja RETURN

Instrukcja RETURN natychmiast kończy wykonywanie podprogramu i zwraca sterowanie do miejsca wywołania. Nie należy mylić instrukcji RETURN z klauzulą RETURN. Podprogram może zawierać kilka Instrukcji RETURN. W procedurach instrukcja RETURN nie może zawierać wyrażenia. Wystąpienie instrukcji

RETURN zwraca sterowanie do miejsca wywołania przed normalnym zakończeniem wykonywania procedury. W funkcjach instrukcja RRETURN musi zawierać wyrażenie, które jest wyliczane w chwili wykonania instrukcji. Funkcja musi zawierać przynajmniej jedną instrukcję RETURN.

FUNCTION balance (acct_id INTEGER) RETURN REAL IS

acct_bal REAL;

BEGIN

SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;

RETURN acct_bal;

END balance;

6.5. Deklaracje zapowiadające

PL/SQL wymaga zadeklarowania identyfikatora przed jego użyciem. Nie wolno wywoływać procedury bez jej poprzedniej deklaracji. W celu umożliwienia PL/SQL dopuszcza stosowanie deklaracji zapowiadających, umożliwiając tym samym:

* definiowanie podprogramów w porządku alfabetycznym

* definiowanie rekursywnych podprogramów

* grup podprogramów w pakiecie.

Deklaracja zapowiadająca składa się ze specyfikacji podprogramu zakończonej średnikiem.

DECLARE

PROCEDURE calc_rating (...); -- deklaracja zapowiadająca

/* Definicje podprogramów w porządku alfabetycznym */

PROCEDURE award_bonus (...) IS

BEGIN

calc_rating(...);

...

END;

PROCEDURE calc_rating (...) IS

BEGIN

...

END;

...

Parametry formalne muszą znaleźć się deklaracji zapowiadającej jak i we właściwej definicji ciała podprogramu.

6.6. Upakowane podprogramy w pakietach

Deklaracje zapowiadające pozwalają grupować logicznie powiązane podprogramy w pakiecie. Specyfikacje podprogramów znajdują się w specyfikacji pakietu a definicji podprogramów w definicji pakietu, gdzie nie są widoczne w aplikacji.

PACKAGE emp_actions IS -- specyfikacja pakietu

PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);

PROCEDURE fire_employee (emp_id NUMBER);

END emp_actions;

PACKAGE BODY emp_actions IS -- definicja pakietu

PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS

BEGIN

INSERT INTO emp VALUES (empno, ename, ...);

END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS

BEGIN

DELETE FROM emp WHERE empno = emp_id;

END fire_employee;

END emp_actions;

Można zdefiniować podprogramy bez umieszczania specyfikacji programu w specyfikacji pakietu, ale takie programy nie są dostępne poza ciałem definicji pakietu.

6.7. Przekazywanie parametrów aktualnych

Wywoływanie podprogramu wymaga podania parametrów aktualnych. Parametry aktualne mogą być podane przez zachowanie zgodności z kolejnością parametrów formalnych lub przez skojarzenie nazw parametrów aktualnych z formalnymi.

DECLARE

acct INTEGER;

amt REAL;

PROCEDURE credit (acctno INTEGER, amount REAL) IS ...

...

BEGIN

credit(acct, amt); -- notacja pozycyjna

credit(amount => amt, acctno => acct); -- notacja nazywana

credit(acctno => acct, amount => amt); -- notacja nazywana

credit(acct, amount => amt); -- notacja mieszana

...

END;

6.8. Tryby przekazywania parametrów

Tryby przekazywania parametrów definiują zachowanie parametrów formalnych. Rozróżnia się trzy tryby: IN (domyślny), OUT i IN OUT. Należy unikać używania trybu OUT i IN OUT w funkcjach z uwagi na konstrukcję funkcji.

6.8.1. Tryb IN

Parametr w trybie IN pozwala przekazać wartość do wywoływanego podprogramu. Wewnątrz programu taki parametr zachowuje się jak stała.

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS

minimum_purchase CONSTANT REAL := 10.0;

service_charge CONSTANT REAL := 0.50;

BEGIN

IF amount < minimum_purchase THEN

amount := amount + service_charge; -- niepoprawne

END IF;

...

Aktualny parametr odpowiadający formalnemu parametrowi w trybie IN może być stałą, literałem, zainicjowaną zmienną lub wyrażeniem.

W przeciwieństwie do trybu OUT i IN OUT, parametry w trybie IN mogą być zainicjowane wartościami domyślnymi.

6.8.2. Tryb OUT

Parametr w trybie OUT pozwala przekazać wartość na zewnątrz wywoływanego podprogramu. Wewnątrz programu taki parametr zachowuje się jak niezainicjowana zmienna.

PROCEDURE calc_bonus (emp_id INTEGER, bonus OUT REAL) IS

hire_date DATE;

BEGIN

SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp

WHERE empno = emp_id;

IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN

bonus := bonus + 500; -- błąd syntaktyczny

END IF;

...

Aktualny parametr odpowiadający formalnemu parametrowi w trybie OUT musi być zmienną.

Jeżeli podprogram zakończy się pomyślnie, to PL/SQL przypisze wartości parametrom aktualnym. Jeżeli podprogram zakończy się wystąpieniem niezdefiniowanego wyjątku, to PL/SQL nie przypisze żadnych wartości parametrom aktualnym.

6.8.3. Tryb IN OUT

Parametr w trybie IN OUT pozwala przekazać wartość do wywoływanego podprogramu i zwrócić wartość na zewnątrz wywoływanego podprogramu. Wewnątrz programu taki parametr zachowuje się zainicjowana zmienna.

PROCEDURE calc_bonus (emp_id INTEGER, bonus IN OUT REAL) IS

hire_date DATE;

bonus_missing EXCEPTION;

BEGIN

SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp

WHERE empno = emp_id;

IF bonus IS NULL THEN

RAISE bonus_missing;

END IF;

IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN

bonus := bonus + 500;

END IF;

...

EXCEPTION

WHEN bonus_missing THEN

...

END calc_bonus;

Aktualny parametr odpowiadający formalnemu parametrowi w trybie OUT musi być zmienną.

6.9. Wartości domyślne parametrów

Wartości domyślne parametrów mogą być związane tylko z parametrami formalnymi w trybie IN. Jeżeli parametr aktualny zostanie pominięty w wywołaniu podprogramu to odpowiadający parametr formalny przybierze wartość domyślną.

6.10. Tworzenie upakowanych podprogramów

Podprogramy można oddzielnie kompilować i przechowywać w bazie danych. Umożliwia to instrukcja CREATE PROCEDURE i CREATE FUNCTION wywołana interakcyjnie z SQL*Plus lub SQL*DBA.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS

BEGIN

DELETE FROM emp WHERE empno = emp_id;

END;

Syntaktyka przechowywanego podprogramu jest identyczna z syntaktyką podprogramu z wyjątkiem pojawienia się słowa kluczowego AS w miejsce IS.

Raz skompilowany i zapamiętany w repozytorium bazy danych, podprogram staje się obiektem bazy danych, który może być wykorzystywany przez aplikacje połączone z bazą danych.

Przechowywane podprogramy mogą być wywoływane z wyzwalaczy, innych przechowywanych podprogramów, czy interakcyjnie z SQL*Plus lub SQL*DBA.

SQL> EXECUTE fire_employee(7499);

SQL> BEGIN create_dept('MARKETING', 'NEW YORK'); END;

6.11. Informacja o upakowanych podprogramach

Następujące perspektywy zawierają informację o podprogramach i pakietach:

ALL_SOURCE, USER_SOURCE, DBA_SOURCE

USER_OBJECT_SIZE, DBA_OBJECT_SIZE

7. Błędy kompilacji upakowanych wyzwalaczy i podprogramów

Błędy kompilacji w przypadku tworzenia upakowanych wyzwalaczy lub podprogramów moą zostać wyświetlone przez:

- użycie polecenia SHOW ERRORS lub

- sięgnięcie do perspektyw

ALL_ERRORS, USER_ERRORS, DBA_ERRORS

8. Pakiet DBMS_OUTPUT

Pakiet DBMS_OUTPUT jest szczególnie użyteczny dla wyświetlania informacji w trakcie śledzenia wykony-wania bloków PL/SQL'a. W tym celu należy ustawić zmienną środowiskową poleceniem:

SET SERVEROUTPUT ON.

8.1. PUT, PUT_LINE, NEW_LINE

Procedury PUT i PUT_LINE umieszczają podany jako parametr tekst w buforze, po którym w przypadku użycia procedury PUT_LINE jest umieszczany znacznik końca linii. Procedura NEW_LINE umieszcza w buforze znacznik końca linii.

DBMS_OUTPUT.PUT_LINE('Total wages = ' || TO_CHAR(total_wages));

Dodatki

A. Wybrane polecania SQL*Plus'a

@ (znak)

Wykonanie wyspecyfikowanego skryptu.

@ nazwa_skryptu.[rozszerzenie]

Przy wykonaniu polecenia SQL*Plus szuka wyspecyfikowanego skryptu w bieżącym katalogu. Jeżeli skrypt nie znajduje się w bieżącym katalogu to w linii polecenia należy zapisać ścieżkę dostępu przed nazwą skryptu.

Polecenie @ nie umożliwia przekazywania parametrów do skryptu w odróżnieniu od polecenia START.

/ (znak)

Wykonanie polecenia SQL lub bloku PL/SQL zapamiętanego w buforze

CONNECT

Polecenia zalogowania do bazy

CONNECT użytkownik[/hasło][@baza]

@baza - reprezentuje tekst połączenia z bazą zgodny z wymaganiami stawianymi przez SQL*Net.

DEFINE

Definiuje zmienne znakowe i przypisuje im wartości lub prezentuje zmienne i ich wartości

DEFINE [zmienna]|[zmienna=tekst]

Podanie DEFINE zmienna powoduje wyświetlenie wartości zmiennej a pominięcie zmiennej powoduje wyświetlenie wartości wszystkich zmiennych. Odwołanie w poleceniach SQL do zmiennej przez &zmienna wymaga podania wartości w przypadku, gdy zmiennej nie została wcześniej przypisana wartość a odwołanie przez &&zmienna nie wymaga kolejnego podawania wartości dla zmiennej. W celu odwołania definicji zmiennej należy użyć polecenia UNDEFINE.

DISCONNECT

Potwierdza zmiany i wylogowuje aktualnego użytkownika

EXIT

Potwierdza zmiany i oddaje sterowania do systemu operacyjnego

GET

Wprowadza wyspecyfikowany skrypt do bufora SQL

GET skrypt [LIST|NOT LIST]

Podanie opcji LIST powoduje wylistowanie zawartości wczytanego skryptu.

HOST

Wykonanie polecenia systemu operacyjnego

HOST [polecenie]

Podanie polecenia HOST bez parametrów powoduje przejście do systemu operacyjnego. Powrót następuje po napisaniu polecenia EXIT.

RUN

Listuje i wykonuje polecenie SQL lub blok PL/SQL znajdujący się w buforze SQL'a

RUN

SAVE

Zapisuje zawartość bufora SQL do skryptu

SAVE skrypt [CRE|REP|APP]

Domyślnym rozszerzeniem skryptu jest '.SQL'. Opcje zapisu oznaczają odpowiednio utworzenie nowego zbioru, zastąpienie zawartości zbioru i dopisanie do zbioru.

SET

Definiuje aspekt bieżącej sesji w środowisku SQL*Plus'a

SET zmienna_systemowa parametry

Zmienne systemowe:

SHOW

Listuje wartości zmiennych systemowych środowiska SQL*Plus'a

SHOW opcja

Opcje:

zmienna_systemowa - jedna z nazw zmiennych systemowych.

ALL - wszystkie zmienne.

REL - numer wersji ORACLE RDBMS'a

USER - nazwa użytkownika.

START

Wykonuje zawartość skryptu

START skrypt [par1 par2 ...]

Umożliwia wykonanie zawartości skryptu z możliwością przekazywania parametrów. Parametry przekazywane do skryptu są kolejno podstawiane po zmienne &1, &2 i itd. Jeżeli parametr ma reprezentować wartość typu CHAR, to należy odwołanie do tego parametru zapisać w apostrofach np. '&1'.

UNDEFINE

Usunięcie zdefiniowanej zmiennej

UNDEFINE zmienna

Usuwa zmienne zdefiniowane poleceniem DEFINE lub pośrednio przez parametry polecenia START.

Spis treści

1. Wstęp

---