Wyk

ład

7

Język PL/SQL

Część I

Rozszerzenie języka SQL o

elementy języka programowania

• SQL/PSM - Standard SQL:1999
• Oracle PL/SQL

Język PL/SQL

Język

PL/SQL

jest proceduralnym

rozszerzeniem języka SQL stanowi podstawę do
pisania aplikacji Oracle – jest dostępny w
różnych programach narzędziowych ORACLE
np.

• SQL*Plus,
• Developer 2000 (Oracle*Forms i

Oracle*Reports),

• prekompilatory,
• procedury,
• wyzwalacze,
• aplikacje na stronach WWW.

DECLARE

deklaracje obiektów PL/SQL jak zmienne,

stałe, wyjątki, procedury, funkcje

BEGIN

ciąg instrukcji do wykonania

EXCEPTION

obsługa wyjątków (błędów)

END

;

• Deklaracje i obsługa wyjątków są opcjonalne.

Bloki mogą być zagnieżdżone.

• Instrukcje SQL w bloku PL/SQL: SELECT,

INSERT, UPDATE, DELETE, COMMIT i
ROLLBACK.

Blok anonimowy

CREATE TABLE Magazyn(Produkt

VARCHAR2(30), Stan NUMBER(4));

CREATE TABLE Zakupy(Info VARCHAR2(100),

Data DATE);

CREATE TABLE Dziennik(Rejestr

VARCHAR2(100));

INSERT INTO Magazyn VALUES('Fiat', 1);
INSERT INTO Magazyn VALUES(‘Honda', 3);
INSERT INTO Magazyn VALUES(‘Toyota', 2);

Przykładowe tabele

DECLARE
v_stan NUMBER(5);

BEGIN

SELECT Stan INTO v_stan FROM
Magazyn
WHERE Produkt = 'Fiat';

IF v_stan > 0 THEN
UPDATE Magazyn SET Stan = Stan
- 1
WHERE Produkt = 'Fiat';
INSERT INTO Zakupy
VALUES ('Kupiono Fiata',
Sysdate);
ELSE
INSERT INTO Zakupy
VALUES ('Brak Fiatów',
Sysdate);
END IF;
COMMIT;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO dziennik
VALUES('Nie znaleziono FIATa');
END;

Przykład

Tylko

jeden

rekord!!!

• między nawiasami /* i */ , albo

• od dwóch kresek -- do końca bieżącej linii.

Komentarze

v_zarobki NUMBER(7,2);

pi CONSTANT NUMBER(7,5) := 3.14159;

v_nazwisko VARCHAR2(25) := 'Kowalski';

v_data DATE := Sysdate;

żonaty BOOLEAN := False;

liczba_dzieci BINARY_INTEGER :=0;

Deklaracji zmiennych tych samych typów nie
można
łączyć razem jak w innych językach!

Deklaracje zmiennych i stałych

W PL/SQL są dostępne typy danych z języka SQL a

ponadto m.in.

• BOOLEAN

- wartości logiczne,

• BINARY_INTEGER

- typ liczb całkowitych –

niezależny od podtypów typu NUMBER i przez to
wymagający przy zapisie mniej miejsca w pamięci
RAM.

• Nie należy zmiennej nadawać tej samej nazwy co

kolumnie w tabeli.

• Wewnątrz bloku PL/SQL instrukcja SELECT nie

wypisuje wyników na ekran (ani do pliku).

• Zmienne i stałe PL/SQL mogą występować w

instrukcjach SQL.

Deklaracje zmiennych i stałych

Zmienne w SQL*Plus

• VARIABLE X NUMBER

(lub

CHAR(n)

lub

VARCHAR2(n)

, (n>0) – deklaracja zmiennej

wiązania;

• EXECUTE :X := wyrażenie;

– przypisz na zmienną

wiązania

:X

wartość wyrażenia. Po ustaleniu

wartości można zmienne wiązania poprzedzone
dwukropkiem używać w instrukcjach SQL i PL/SQL.

• PRINT X

– wypisz wartość zmiennej wiązania X;

• ACCEPT Zmienna PROMPT 'Podaj wartość

zmiennej: '

– utworzenie zmiennej podstawienia i

wczytanie na nią wartości. Następnie można
zmienne podstawienia poprzedzone znakiem &
używać w instrukcjach SQL i PL/SQL.

SET ServerOutput ON

ACCEPT rocz_zarob PROMPT 'Podaj roczne
zarobki: '

DECLARE

mies

NUMBER(9,2) := &rocz_zarob;

BEGIN

mies := mies/12;

DBMS_OUTPUT.PUT_LINE ('Miesięczne zarobki
= ' ||mies);

END

;

Przykład

ACCEPT rocz_zarob PROMPT 'Podaj roczne
zarobki: '

VARIABLE mies NUMBER

BEGIN

:mies := &rocz_zarob/12;

END

;

/

PRINT Mies

Przykład

Zmienne systemowe

• SQL%ROWCOUNT

- liczba wierszy

przetworzonych przez ostatnią instrukcję SQL;

• SQL%FOUND

= TRUE jeśli został znaleziony

(przetworzony) przynajmniej jeden wiersz;

• SQL%NOTFOUND

= TRUE jeśli żaden wiersz nie

został znaleziony (przetworzony);

• SQLERRM

- tekstowa informacja o błędzie;

• SQLCODE

- kod błędu.

Obie zmienne SQLERRM i SQLCODE można

używać tylko w sekcji EXCEPTION!

Przykład użycia zmiennej SQL%ROWCOUNT
do obliczenia liczby usuwanych działów o
numerze 50:

 

DECLARE

usunięte NUMBER;

BEGIN

DELETE FROM Dept WHERE Deptno
= 50;
usunięte := SQL%ROWCOUNT;
INSERT INTO DziennikDzialow
VALUES ('Dział 50', usunięte,
Sysdate);

END

;

Przykład

Instrukcje warunkowe

IF

warunek

THEN

ciąg_instrukcji

END

IF

;

IF

warunek

THEN

ciąg_instrukcji

ELSE

ciąg_instrukcji

END IF

;

 

IF

warunek

THEN

ciąg_instrukcji

ELSIF

warunek

THEN

ciąg_instrukcji

END IF

; 

• Instrukcje po THEN

są wykonywane
wtedy, gdy wartością
warunku jest TRUE.

• Instrukcje po ELSE

są wykonywane
wtedy, gdy wartością
warunku jest FALSE
lub NULL.

Instrukcje iteracji

LOOP

ciąg

instrukcji

(w tym EXIT

lub EXIT WHEN warunek)

END LOOP

;

 

FOR

zmienna

IN

wartość1 ..

wartość2

LOOP

…

END LOOP

;

 

WHILE

warunek

LOOP

…

END

LOOP

;

Instrukcja pusta

Null

– na przykład, gdy obsługa wyjątku

jest pusta.

Zmienne wierszowe

DECLARE

rek_osoby Emp%ROWTYPE; /* Typ

wierszowy */

BEGIN

SELECT * INTO rek_osoby
FROM Emp WHERE Ename = 'KOWALSKI';
rek_osoby.Sal := 1.1*rek_osoby.Sal;
INSERT INTO Dziennik
VALUES (rek_osoby.Ename, rek_osoby.Job,
rek_osoby.Sal, SYSDATE);

END

;

Zmiennej wierszowej nie można użyć

bezpośrednio po słowie kluczowym VALUES w
instrukcji INSERT INTO!

Rekordy PL/SQL, zmienne

rekordowe

TYPE

Typ_rek_prac

IS RECORD

( numer_prac NUMBER(4) NOT NULL,
nazwisko VARCHAR2(40) NOT NULL,
zarobki NUMBER(8,2),

num_działu NUMBER(4));

rekord_prac Typ_rek_prac;

Typy rekordowe mogą być zagnieżdżone. Dostęp

do pól rekordu jest przy pomocy notacji
kropkowej. Na zmienną rekordową można
przypisać wartość innej zmiennej rekordowej, ale
tylko tego samego typu rekordowego. Można ich
używać w klauzuli INTO (tak jak zmiennych
wierszowych):

SELECT * INTO rekord_prac
FROM Pracownicy
WHERE Id_prac = 12;

Instrukcja SELECT w PL/SQL

Aby instrukcja była poprawna, instrukcja SELECT
… INTO musi zwracać tylko jeden wiersz wyników!

SELECT Ename
INTO v_ename
FROM Emp
WHERE Empno = 1030;

Wartości w bloku PL/SQL, pochodzą na ogół z bazy
danych, gdzie został określony ich typ danych. W
związku z tym, wygodnie jest określać typ danych
jako ''typ danych wymienionej kolumny'' np. zamiast

DECLARE

v_ename VARCHAR2(30)

 

DECLARE

v_ename Emp.Ename%TYPE

Instrukcja SELECT w PL/SQL

Możemy natomiast odebrać cały wiersz
(jeden!), a nie tylko pojedynczą kolumnę:

SELECT *
INTO v_pracownik
FROM Emp
WHERE Empno = 1030;

Zmienną v_pracownik (która jest wierszem)
deklarujemy w ten sposób:

 

DECLARE

v_pracownik Emp

%ROWTYPE

Kursory: dostęp do obszarów

roboczych instrukcji SELECT

• Kursor – bufor, do którego są zapisywane,

kolejno sprowadzane z bazy danych, wiersze
z wynikami zapytania.

• Definicja kursora (przyporządkowanie

instrukcji SELECT)

CURSOR nazwa_kursora IS
instrukcja_SELECT;

– (bez INTO!)

• Otwarcie kursora (wykonanie instrukcji

SELECT)

OPEN nazwa_kursora;

Kursory

• Pobieranie kolejnych wierszy

FETCH nazwa_kursora INTO

zmienna, …;

• Wyjście z pętli po sprowadzeniu wszystkich

wierszy

EXIT WHEN nazwa_kursora
%NOTFOUND;

• Zamknięcie kursora

CLOSE nazwa_kursora;

CREATE OR REPLACE PROCEDURE

SumujZarobki

AS

zarobki REAL:=0;

CURSOR

kursor_osoba

IS

SELECT * FROM Emp; 
rek_osoby kursor_osoba%ROWTYPE;

BEGIN

OPEN

kursor_osoba;

LOOP

FETCH

kursor_osoba INTO rek_osoby;

EXIT WHEN kursor_osoba%NOTFOUND;

zarobki := zarobki + rek_osoby.Sal;

END LOOP;

Dbms_output.Put_line('W sumie zarobki = '||
zarobki);

CLOSE

kursor_osoba;

END

;

Przykład

Nasz bufor

Kursor – aliasy

kolumn

Na liście SELECT mogą się znajdować dowolne

wyrażenia, przy czym jeśli nie jest to nazwa
kolumny, musi być użyty alias np.,

 

CURSOR

kursor

IS

SELECT Ename, Sal+NVL(Comm,0) AS

Uposażenie

FROM Emp;

 Iteracja - wersja z kursorem:

FOR

rek_osoby

IN

kursor_osoba

LOOP

...

END LOOP

;

–

OPEN

,

FETCH

i

CLOSE

są tu

niejawne

!

Kursor z

parametrami

W instrukcji SELECT w kursorze mogą

występować parametry. Ten sam kursor może
być otwarty wielokrotnie – z różnymi
wartościami parametrów.

CURSOR nazwa_kursora(parametr

typ_danych, ....) IS

instrukcja-SELECT;

DECLARE

CURSOR

emp_cursor (v_deptno NUMBER,

v_job VARCHAR2)

IS

SELECT

Empno, Ename

FROM

Emp

WHERE Deptno = v_deptno AND Job =

v_job;

BEGIN

OPEN

emp_cursor(10, 'CLERK');

...

Atrybuty kursora

•   kursor%FOUND -- czy z bazy danych

sprowadzono kolejny wiersz,

• kursor%NOTFOUND -- czy koniec

sprowadzania wierszy,

• kursor%ROWCOUNT -- liczba

sprowadzonych dotąd wierszy,

• kursor%ISOPEN -- czy kursor jest otwarty.

 

IF

NOT prac_kursor%ISOPEN

THEN

OPEN

prac_kursor;

END IF

;

LOOP

FETCH

prac_kursor...

Aktualizacja wierszy za pomocą

kursora

• Przy wykonywaniu instrukcji SELECT można

zakładać blokady na wiersze w celu ich
modyfikacji.

FOR UPDATE [OF kolumna, kolumna,

… ]

Podane kolumny określają tabele, których

wiersze mają zostać zablokowane.

• Stowarzyszona z nią w instrukcji UPDATE lub

DELETE klauzula

WHERE CURRENT OF kursor

umożliwia modyfikację lub usunięcie

sprowadzonego przez kursor wiersza
odpowiedniej tabeli.

DECLARE

CURSOR

kursor_osoba

IS

SELECT Ename, Sal FROM Emp

FOR UPDATE OF

Sal;

rek_osoby kursor_osoba%ROWTYPE;

BEGIN

OPEN

kursor_osoba;

LOOP

FETCH

kursor_osoba

INTO

rek_osoby;

EXIT WHEN

kursor_osoba%NOTFOUND;

IF

rek_osoby.Sal < 10000

THEN

UPDATE Emp SET Sal = Sal * 1.1

WHERE CURRENT OF

kursor_osoba;

END IF

;

/* zamiast modyfikować możemy też
usunąć wiersz np.
DELETE Emp WHERE CURRENT OF
kursor_osoba; */

END LOOP

;

CLOSE

kursor_osoba;

COMMIT;

END

;

Przykład

Standardowe, nazwane wyjątki

• dup_val_on_index

(ta sama wartość w

indeksie jednoznacznym),

• no_data_found

(instrukcja SELECT nie

zwróciła wartości dla zmiennych w klauzuli
INTO),

• too_many_rows

(instrukcja SELECT zwróciła

więcej niż jeden wiersz wartości dla
zmiennych w klauzuli INTO),

• zero_divide

(dzielenie przez zero),

• timeout_on_resource

(zbyt długie

oczekiwanie na zasoby),

• invalid_cursor

(niepoprawna operacja na

kursorze),

• invalid_number

(niepoprawna konwersja na

liczbę).

Wyjątki

….. SELECT Ename, Job INTO v_ename, v_job …..
EXCEPTION

WHEN

no_data_found

THEN

INSERT INTO Dziennik VALUES ('Nikt nie zatrudniony
w 1993');
DBMS_OUTPUT.Put_line('Nikt nie zatrudniony w 93');

WHEN

too_many_rows

THEN

INSERT INTO Dziennik VALUES ('Więcej niż 1
zatrudniony w 1993');
DBMS_OUTPUT.Put_line('Więcej niż 1 w 93');

WHEN

OTHERS

THEN -- Obsługa pozostałych

błędów
komunikat := 'Błąd nr.= ' ||
SQLCODE|| ', komunikat= ' ||
Substr(SQLERRM,1,100);

-- SQLCODE i SQLERRM nie mogą wystąpić w

instrukcji SQL!

INSERT INTO Dziennik VALUES (komunikat);

DBMS_OUTPUT.Put_line(‘Wystąpił inny błąd ');

END;

Obsługa wyjątków

• Jeśli blok, w którym wystąpił błąd, zawiera

obsługę tego błędu, to po dokonaniu obsługi,
sterowanie jest w zwykły sposób
przekazywane do bloku go zawierającego
(nadrzędnego).

• Jeśli nie zawiera, następuje przekazanie błędu

do bloku zawierającego dany blok i albo tam
nastąpi jego obsługa albo błąd przechodzi do
środowiska zewnętrznego.

Obsługa wyjątków

• Błąd, który wystąpił w sekcji wykonawczej bloku

(między BEGIN i END) jest obsługiwany w sekcji
EXCEPTION tego samego bloku. Błędy, które
wystąpią w sekcji deklaracji lub w sekcji wyjątków
są przekazywane do bloku zawierającego dany
blok.

• Dobra praktyka programistyczna wymaga aby

każdy błąd został obsłużony – ewentualnie w
klauzuli WHEN OTHERS THEN najbardziej
zewnętrznego bloku.

• Aby móc stwierdzić, która instrukcja SQL

spowodowała błąd:

• można używać podbloków z własną obsługą
błędów, albo

• można używać licznika, zwiększającego się
o jeden po wykonaniu każdej instrukcji SQL.

Wyjątki definiowane przez

programistę

Wyjątki można deklarować samemu (w sekcji
DECLARE) używając słowa kluczowego
EXCEPTION

nazwa_wyjątku EXCEPTION;

powodować ich podniesienie (w sekcji
instrukcji)

RAISE nazwa_wyjątku;

a następnie je obsługiwać (w sekcji
EXCEPTION)

WHEN nazwa_wyjątku THEN ...

DECLARE

brak_w_magazynie

EXCEPTION

;

v_stan NUMBER(5);

BEGIN

SELECT Stan
INTO v_stan
FROM Magazyn WHERE Produkt =
'Fiat';

IF

v_stan < 1

THEN

RAISE brak_w_magazynie;

END IF

;

EXCEPTION

WHEN

brak_w_magazynie

THEN

INSERT INTO Zamówienia VALUES
('Fiaty');

RAISE

wyjście;

END

;

Przykład

Podnoszenie wyjątku za pomocą

Raise_Application_Error

• Przypisanie mu numeru między –20000 a –20999 i

tekstu.

• Wyjątek taki może zostać obsłużony albo w tym samym

bloku albo w aplikacji zewnętrznej, w której to
wywołanie zostanie wykonane.

DECLARE
numer INTEGER;
BEGIN .....

Raise_Application_Error(-20100,'Błąd');

EXCEPTION
WHEN OTHERS THEN
numer:=SQLCODE;
IF numer= -20100 THEN
Dbms_output.Put_line('Błąd

przechwycony!');

END IF;
END;

Obsługa błędów przechwytywanych

przez serwer bazy danych

Nadanie nazwy wyjątkowi systemowemu - w sekcji
deklaracji

nazwa_wyjątku EXCEPTION;

PRAGMA EXCEPTION_INIT (nazwa_wyjątku,

numer_błędu);

a następnie obsługa (w sekcji EXCEPTION)

WHEN nazwa_wyjątku THEN ...

Przykład obsługi błędu naruszenia więzów klucza obcego nr
-2292:

DECLARE

bl_klucz_o

EXCEPTION

;

PRAGMA EXCEPTION_INIT

(bl_klucz_o, -2292);

v_deptno Dept.Deptno%TYPE := :b_deptno;

BEGIN

DELETE FROM Dept
WHERE Deptno = v_deptno;
COMMIT;

EXCEPTION

WHEN

bl_klucz_o

THEN

Dbms_output.Put_line ('Nie można usunąć działu '
||
To_Char(v_deptno) || ', w którym są pracownicy. ');

END

;

Przykład