Na kolejnych dwóch wykładach zostanie przedstawione podstawowe narzędzie używane w Oracle przy tworzeniu oprogramowania aplikacji na serwerze, mianowicie rozszerzenie języka SQL o elementy języka programowania – język PL/SQL. Podobny język jest określony w nowym Standardzie SQL:1999. Nosi on tam nazwę SQL/PSM.
W tym wykładzie omówimy bloki PL/SQL, które stanowią podstawowe składniki
kodu używanego w procedurach, funkcjach, pakietach i wyzwalaczach (będące
tematami następnego
wykładu).
Język PL/SQL jest proceduralnym rozszerzeniem języka SQL i stanowi podstawę do pisania aplikacji Oracle – jest mianowicie dostępny w różnych programach narzędziowych ORACLE, jak np. SQL*Plus, Oracle*Forms czy Oracle*Reports, prekompilatory, procedury, wyzwalacze, aplikacje internetowe.
Odpowiednikiem konstrukcji bloku (i programu) występujących w językach programowania jest w PL/SQL
blok PL/SQL nazywany też blokiem anonimowym. Ma on następującą składnię:
| 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; |
Wprowadzając blok PL/SQL przy użyciu programu SQL*Plus, należy:
/ znajdującym się na osobnej linii - w celu wpisania go do bufora bieżącej instrukcji
i wykonania.Wykonanie instrukcji w buforze bieżącej instrukcji (instrukcji SQL lub
bloku PL/SQL) realizujemy poprzez podanie znaku / na osobnej linii.
Instrukcje samego SQL*Plus nie są wpisywane do bufora.
Przykład
W poniższym bloku jest oprogramowana transakcja sprzedaży samochodu marki Fiat. Gdy w magazynie jest brak informacji o takiej marce samochodu, podnoszony jest błąd, który jest następnie obsługiwany w sekcji wyjątków poprzez wpisanie odpowiedniej informacji do tablicy dziennika błędów.
DECLARE
ilość NUMBER(5);
BEGIN
SELECT m.Stan INTO ilość FROM Magazyn m
WHERE m.Produkt = 'Fiat';
/* Gdy w kolumnie Produkt tabeli Magazyn nie ma wartości 'Fiat' jest podnoszony wyjątek o nazwie
no_data_found. */
IF ilość> 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 -- Początek sekcji wyjątków
WHEN no_data_found THEN
INSERT INTO dziennik_błędów
VALUES ('Nie znaleziono produktu FIAT');
END;
/Deklaracja
zmiennej ma następującą postać:
identyfikator typ_danych [NOT NULL] [ := wyrażenie];
|
:= wyrażenie umożliwia inicjalizację wartości zmiennej – w przeciwnym razie zmiennej jest przypisywane
NULL.
Deklaracja stałej ma następującą postać:
identyfikator CONSTANT typ_danych [NOT NULL] [ := wyrażenie];
|
Oto przykłady:
zarobki NUMBER(7,2);
pi CONSTANT NUMBER(7,5) := 3.14159;
nazwisko VARCHAR2(25) := 'Kowalski';
data DATE := Sysdate;
żonaty BOOLEAN := False;
liczba_dzieci BINARY_INTEGER :=0;
Data DATE DEFAULT Sysdate;W PL/SQL są dostępne typy danych z języka SQL, a ponadto kilka innych, z których najważniejsze to typ BOOLEAN wartości logicznych i typ BINARY_INTEGER liczb całkowitych – niezależny od podtypów typu NUMBER i przez to wymagający przy zapisie mniej pamięci.
Nie należy nadawać tej samej nazwy zmiennej co kolumnie w tabeli. Zmienne i stałe PL/SQL mogą występować w instrukcjach SQL, tak jak stałe SQL i kolumny.
Oprócz zmiennych deklarowanych w bloku PL/SQL mogą występować jeszcze zmienne z aplikacji korzystającej z bloku PL/SQL - poprzedza się je dwukropkiem (:zmienna) i nazywa się zmiennymi wiązania. Mogą również występować zmienne podstawienia SQL*Plus (&zmienna) - ale tylko w wyrażeniach i nigdy po lewej stronie w instrukcji przypisania.
Przy testowaniu i uruchamianiu aplikacji składających się z kodu PL/SQL wygodnie jest pobierać dane do testowania z klawiatury i wypisywać informacje o przebiegu obliczeń na ekran. Realizuje się to w następujący sposób. Po ustawieniu w SQL*Plus:
SET SERVEROUTPUT ONDBMS_OUTPUT.Put_line(wyrażenie_napisowe);rocz_zarob, a następnie wypisywanie wyniku na ekran - za pomocą
procedury DBMS_OUTPUT.Put_line. Należy pamiętać, ze w celu uruchomienia skryptu należy go najpierw zapisać w pliku, a następnie wywołać go
w SQL*Plus przy użyciu instrukcji start.
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;
/Nie możemy użyć tego skryptu w iSQL*Plus, bo nie obsługuje on instrukcji ACCEPT. Na szczęście opuszczenie instrukci ACCEPT też prowadzi do satysfakcjonującego rezultatu. Dlaczego?
Zamiast zmiennych podstawienia można użyć zmiennych wiązania z SQL*Plus, np.
ACCEPT rocz_zarob PROMPT 'Podaj roczne zarobki: '
VARIABLE mies NUMBER
BEGIN
:mies := &rocz_zarob/12;
END;
/
PRINT Mies
Jest pewna liczba zmiennych zadeklarowanych w systemie, z których można korzystać w kodzie PL/SQL (ale nie w SQL) – ich wartości dotyczą ostatnio wykonanej instrukcji SQL.
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 nie znaleziono (przetworzono) żadnego wiersza;SQLERRM
tekstowa informacja o błędzie;SQLCODE
kod błędu.Obu zmiennych 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) przedstawia następujący blok:
DECLARE usunięte NUMBER;
BEGIN
DELETE FROM Dept WHERE Deptno = 50;
usunięte := SQL%ROWCOUNT;
INSERT INTO dziennik VALUES ('Dział', usunięte, Sysdate);
END;
/
Wewnątrz bloku PL/SQL instrukcja SELECT nie wypisuje wyników na ekran i w zasadzie nie można jej użyć jako takiej bez dodatkowych konstrukcji.
Instrukcja SELECT ma w PL/SQL swoją specjalną postać. Wynik zapytania nie jest wyświetlany na ekranie, tylko zostaje zapisany na zmiennych - zamieszczanych w klauzuli INTO, która jest wymaganą klauzulą instrukcji SELECT w PL/SQL. Oto składnia instrukcji SELECT w
PL/SQL:
| SELECT wyrażenie, wyrażenie,... INTO zmienna, zmienna,... FROM tabela, tabela,... [WHERE ...][GROUP BY ...][HAVING ...][FOR UPDATE OF ...]; |
Przykład
SELECT e.Ename
INTO nazwisko
FROM Emp e
WHERE e.Empno = 1030;nazwisko Emp.Ename%TYPEW PL/SQL jest możliwość użycia zmiennych "wierszowych", na których można zapamiętać cały wiersz pochodzący z tabeli. Dostęp do poszczególnych pól wiersza odbywa się tak, jak w przypadku rekordów, przez kropkę i podanie nazwy kolumny.
DECLARE
rek_osob Emp%ROWTYPE; /* Typ wierszowy */
BEGIN
SELECT * INTO rek_osob FROM Emp e WHERE e.Ename = 'SCOTT';
rek_osob.Sal := 1.1*rek_osob.Sal;
INSERT INTO Dziennik VALUES (rek_osob.Ename, rek_osob.Job, rek_osob.Sal, SYSDATE);
END;
/Rozszerzeniem typu wierszowego jest typ rekordowy – definiowany explicite ze swoimi polami w sekcji deklaracji. Ma on podobne własności i ograniczenia co typ wierszowy. Najpierw należy zdefiniować typ rekordowy, a następnie zadeklarować zmienne tego typu.
Przykład
Zdefiniujmy typ rekordu pracownika.
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;SELECT *
INTO rekord_prac
FROM Pracownicy p
WHERE p.Id_prac = 12;
Ważnym elementem każdej profesjonalnej aplikacji jest obsługa błędów (wyjątków).
W PL/SQL standardowe błędy (wyjątki) mają przyporządkowane nazwy. Oto najczęściej używane nazwy błędów (wyjątków):
|
Oto przykład, w którym po wykonaniu instrukcji SELECT, występuje przykładowa obsługa możliwych do wystąpienia błędów (wyjątków).
DECLARE
nazwisko Emp.Ename%TYPE;
stanowisko Emp.Job%TYPE;
komunikat VARCHAR2(100);
BEGIN
SELECT e.Ename, e.Job
INTO nazwisko, stanowisko
FROM Emp e
WHERE e.Hiredate BETWEEN '01-JAN-93' AND '01-JAN-94';
EXCEPTION
WHEN no_data_found THEN
INSERT INTO Dziennik
VALUES ('0 zatrudnionych w 93');
DBMS_OUTPUT.Put_line('0 zatrudnionych w 93');
WHEN too_many_rows THEN
INSERT INTO Dziennik VALUES (''Więcej niż 1 w 93');
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;
/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ą od razu propagowane 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 użyć licznika, zwiększającego się o jeden po wykonaniu każdej instrukcji SQL.
Wyjątki można deklarować samemu (w sekcji DECLARE) używając słowa kluczowego EXCEPTION
nazwa_wyjątku EXCEPTION; |
RAISE nazwa_wyjątku; |
WHEN nazwa_wyjątku THEN … |
DECLARE
brak_w_magazynie EXCEPTION;
stan_magazynu NUMBER(5);
BEGIN
SELECT m.Liczba
INTO stan_magazynu
FROM Magazyn m WHERE m.Marka = 'Fiat';
IF stan_magazynu < 1 THEN
RAISE
brak_w_magazynie;
END IF;
EXCEPTION
WHEN brak_w_magazynie THEN
INSERT INTO Zamówienia VALUES ('Fiaty');
RAISE wyjście;
END;
/BEGIN
.....
Raise_application_error(-20100,'Błąd');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -20100 THEN
DBMS_OUTPUT.Put_line('Błąd przechwycony!');
END IF;
END;
/Rozważmy obsługę błędu naruszenia więzów klucza obcego o numerze -2292.
DECLARE
bl_klucz_o EXCEPTION;
PRAGMA EXCEPTION_INIT (bl_klucz_o, -2292);
v_deptno Dept.Deptno%TYPE := &p_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;
/
Język PL/SQL posiada większość typowych instrukcji sterujących występujących w
typowych językach programowania:
| IF warunek THEN ciąg_instrukcji END IF;
IF warunek THEN ciąg_instrukcji |
Jest jeszcze jedna postać instrukcji IF.
| IF warunek THEN ciąg_instrukcji ELSIF warunek THEN ciąg_instrukcji END IF; |
| LOOP ciąg instrukcji (w tym EXIT lub EXIT WHEN warunek) END LOOP;
FOR zmienna IN wartość1 .. wartość2 LOOP
WHILE warunek LOOP |
Null – na przykład,
w sytuacji gdy obsługa wyjątku jest pusta.Używając dotychczas wprowadzonych konstrukcji języka PL/SQL nie było możliwe przeglądanie kolejno wszystkich wierszy będących wynikiem zapytania. Do tego celu jest potrzebny obiekt PL/SQL o nazwie kursor, który stanowi bufor, do którego są zapisywane, kolejno sprowadzane z bazy danych, wiersze wynikowe zapytania.
W sekcji deklaracji definiujemy kursor przyporządkowując mu instrukcję SELECT:
CURSOR nazwa_kursora |
Następnie otwieramy go za pomocą instrukcji OPEN:
OPEN nazwa_kursora; |
co oznacza wykonanie instrukcji SELECT przyporządkowanej kursorowi. Po czym możemy w pętli pobierać, przy użyciu instrukcji
FETCH nazwa_kursora INTO zmienna, …; |
EXIT WHEN nazwa_kursora%NOTFOUND; |
powodująca wyjście z pętli po sprowadzeniu wszystkich wierszy wynikowych. Na koniec, należy zamknąć kursor za pomocą instrukcji
CLOSE nazwa_kursora; |
Przykład
DECLARE
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 + NVL(rek_osoby.Sal,0);
END LOOP;
DBMS_OUTPUT.Put_line('W sumie zarobki = '||zarobki);
CLOSE kursor_osoba;
END;
/Na liście instrukcji SELECT mogą się znajdować dowolne wyrażenia, przy czym jeśli nie jest to nazwa kolumny, musi być użyty alias kolumny, np.
CURSOR kursor IS
SELECT Ename, Sal+NVL(Comm,0) AS Sumaryczne
FROM Emp;| FOR rek_osoby IN kursor_osoba LOOP ... END LOOP; |
| CURSOR nazwa_kursora(parametr typ_danych, ....) IS instrukcja-SELECT |
DECLARE
CURSOR emp_cursor(v_deptno NUMBER, v_job VARCHAR2) IS
SELECT e.Empno, e.Ename
FROM Emp e
WHERE e.Deptno = v_deptno AND e.Job = v_job;
BEGIN
OPEN emp_cursor(10, 'CLERK');
Analogicznie do standardowych zmiennych opisujących stan obliczeń ostatnio wykonanej instrukcji SQL, tak samo każdemu kursorowi są przyporządkowane zmienne, opisujące stan wykonania instrukcji SELECT z nim związanej. Oto te zmienne:
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.
Oto przykład użycia atrybutu %ISOPEN.
IF NOT prac_kursor%ISOPEN THEN
OPEN prac_kursor;
END IF;
LOOP
FETCH prac_kursor...
Przy wykonywaniu instrukcji SELECT można zakładać blokady na wiersze w celu ich modyfikacji. Klauzula
FOR UPDATE OF kolumna, kolumna, …
|
WHERE CURRENT OF kursor |
Przykład
Zastosujmy blokowanie wierszy poprzez kursor do zaprogramowania podwyższenia zarobków o 10% najmniej zarabiającym pracownikom oraz zmniejszenia zarobków o 10% najwięcej zarabiającym pracownikom.
DECLARE
CURSOR kursor_osoba IS
SELECT e.Ename, e.Sal FROM Emp e
FOR UPDATE OF e.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;
ELSIF rek_osoby.Sal > 100000 THEN
UPDATE Emp SET Sal = Sal * 0.9
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;
/
Są one potrzebne, aby usunąć ograniczenia związane z niemożliwością przekazywania kursora jako parametru procedury/funkcji.
DECLARE
TYPE Typ_produktów IS
REF CURSOR RETURN Produkty%ROWTYPE; /* silny typ */
kursor1 Typ_produktów;
TYPE Typ_kursorowy IS REF CURSOR; /* słaby typ */
kursor2 Typ_kursorowy;DECLARE
TYPE Typ_kursorowy IS REF CURSOR;
z_kurs_klienci Typ_kursorowy;
BEGIN
OPEN z_kurs_klienci FOR
SELECT k.Nazwisko, k.Imię, k.Telefon FROM Klienci k;
WHILE z_kurs_klienci%FOUND LOOP ... END LOOP;
CLOSE z_kurs_klienci;
END;
/
Przedstawiliśmy podstawowe elementy kodu PL/SQL wykonywanego przez serwer bazy danych Oracle. W następnym wykładzie zostanie pokazane jak używać tego kodu w jednostkach programistycznych zapisywanych w bazie danych razem z innymi obiektami.
blok PL/SQL (blok anonimowy) – podstawowa jednostka kodu w PL/SQL złożona z trzech sekcji: deklaracji, instrukcji i obsługi wyjątków.
SELECT INTO - instrukcja SELECT, w której dodatkowo występuje klauzula INTO wskazująca, na jakich zmiennych ma być zapisany wynik instrukcji SELECT (musi się on składać z dokładnie jednego wiersza).
obsługa wyjątków - jest realizowana w sekcji wyjątków bloku PL/SQL.
EXCEPTION - słowo kluczowe rozpoczynające sekcję wyjątków w PL/SQL.
kursor – struktura danych umożliwiająca sprowadzanie z bazy danych kolejnych wierszy wyniku zapytania.
FOR UPDATE OF - klauzula instrukcji SELECT w definicji kursora umożliwiająca aktualizację wierszy w bazie danych poprzez otworzony kursor.
Zadanie 1
Napisz blok PL/SQL służący do wprowadzania nowego pracownika (dane mają być wprowadzone na bieżąco). Najpierw sprawdź czy dane są poprawne w tym korzystając także z obsługi wyjątków. Po wykonaniu instrukcji INSERT spytaj użytkownika, czy chce zatwierdzić zmiany. Jeśli odpowiedź będzie NIE, wycofaj zmianę; jeśli odpowiedź będzie TAK, zatwierdź zmianę i wypisz wprowadzone wartości na ekran.
Zadanie 2
Utwórz tabelę Emps_in_Depts (Deptno NUMBER(2), Pracownicy VARCHAR2(4000)).
Napisz blok PL/SQL, w którym do tabeli Zestawienia są wprowadzone wiersze postaci
(deptno,ename1$ename2$....enamek$), gdzie ename1,ename2,...,enamek są wszystkimi pracownikami zatrudnionymi w dziale o numerze
deptno np. (10,CLARK$KING$MILLER$)