Bardziej zaawansowane przetwarzanie danych.
- rozszerzenie SQL o elementy programowania proceduralnego i obiektowego (Procedural Language/Structured Query Language)
- Nastawiony na przetwarzanie danych
- Nieprzeznaczony do interakcji z użytkownikiem
- Pozwala na definiowanie:
- Anonimowych bloków programowych,
- Struktur trwale zapisywanych w bazie danych, tzw. programów składowych
- Można umieszczać polecenia DML
- Nie można umieszczać instrukcji DDL i DCL
- Indywidualne rozwiązanie firmy Oracle (nie jest elementem standardu). Inne SZBD mają swoje rozszerzenia, wszystkie one się mocno od siebie różnią.
Najważniejsze korzyści: większa łatwość wykonania niektórych zadań niż w SQL, zwiększenie wydajności (bloki PL/SQL), dostępność mechanizmów nieobecnych w SQL (zmienne, stałe, struktury sterujące, obsługa błędów), kod wykonywany po stronie serwera, możliwość wykorzystania predefiniowanych pakietów, pełna kompatybilność z SZBD Oracle
Bloki
Blok anonimowy
Podstawowa jednostka programowa: blok anonimowy. Nie posiada nazwy i jest wykonywany natychmiast po utworzeniu
Postać podstawowa
1 2 3 |
BEGIN polecenia programu END; |
Postać pełna
Sekcja deklaracji, wykonywalna bloku, obsługi błędów.
1 2 3 4 5 6 7 |
DECLARE deklaracje BEGIN polecenia programu EXCEPTION obsługa błędów END; |
Bloki zagnieżdżone
Można je dodać w sekcji wykonywania oraz w sekcji wyjątków bloku zewnętrznego.
1 2 3 4 5 6 7 8 9 |
DECLARE … BEGIN DECLARE … BEGIN … END; END; |
Przykład
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1; BEGIN DECLARE n_emp_id employees.employee_id%TYPE := &emp_id2; v_name employees.first_name%TYPE; BEGIN SELECT first_name INTO v_name FROM employees WHERE employee_id = n_emp_id; DBMS_OUTPUT.PUT_LINE('First name of employee ' || n_emp_id || ' is ' || v_name); EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found.'); END; END; |
Etykiety bloków
Wykorzystane do zmiany widoczności zmiennych, skoków.
1 2 3 4 5 6 |
<<block_label>> DECLARE … BEGIN … END; |
Wskaźnik do zmiennej (notacja kropkowa): block_label.variable_name
.
Przykład
1 2 3 4 5 6 7 8 9 10 11 |
<<parent>> DECLARE n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1; BEGIN <<child>> DECLARE … BEGIN … END; END; |
- poprawia czytelność kodu,
- pozwala na lepszą kontrolę nad wykonaniem kodu (etykieta bloku może być celem instrukcji
EXIT
iCONTINUE
) - Umożliwia odwołanie się (..?)
- …
Typy danych w PL/SQL
- Proste
- Liczbowe
- Znakowe
- Logiczny
- Czasowy
- Złożone
- Rekord
- Tablica
- Obiekt
- Wskaźnikowe
- LOB (Large Object)
Zmienne
- cel: krótkotrwałe przechowywanie danych wewnątrz bloku PL/SQL
- Definiowana w sekcji DECLARE - wymaga podania nazwy zmiennej i określenia jej typu (polecana konwencja:
v_
) - Zmienne proste (np. liczb, ciągów znaków, dat i wartości logicznych)
- Zmienne złożone (np. tablice, rekordy i obiekty)
Przykład
1 2 3 4 5 |
DECLARE v_i NUMBER(6); nazwa VARCHAR2(100); data_sprzedazy DATE; czy_w_magazynie BOOLEAN; |
Inicjowanie zmiennej
- nadanie początkowej wartości
- niezainicjowana zmienna posiada wartość
NULL
- sposoby inicjowania:
- przez przypisanie wartości - użycie operatora przypisania
:=
- przez określenie wartości domyślnej - słowo
DEFAULT
- dla zmiennej można wymusić obowiązkowość wartości (
NOT NULL
)
- przez przypisanie wartości - użycie operatora przypisania
Przykład
1 2 3 4 5 |
DECLARE v_i NUMBER(6) NOT NULL := 10; nazwa VARCHAR2(100) := 'ALGORYTMY'; data_sprzedazy DATE DEFAULT DATE '2018-03-26'; czy_w_magazynie BOOLEAN NOT NULL DEFAULT TRUE; |
Zmienna typu rekord
Grupa powiązanych danych składowanych w polach, z których każde ma własną nazwę i typ.
Przykład
1 2 3 4 5 6 7 8 |
DECLARE TYPE DanePracownika IS RECORD ( nazwisko VARCHAR2(100), imie VARCHAR2(100)); v_pracownik DanePracownika; BEGIN v_pracownik.nazwisko := 'Kowalski'; v_pracownik.imie := 'Jan'; |
Atrybuty %TYPE
Mechanizm ułatwiający deklarowanie zmiennej prostej na podstawie typu atrybutu relacji bazy danych lub typu innej zmiennej. Składnia: referencing_item referenced_item%TYPE
.
Atrybut ten jest szczególnie przydatny przy deklarowaniu zmiennych do przechowywania wartości z bazy danych. Składnia: variable_name table_name.column_name%TYPE
.
Element referencyjny dziedziczy następujące elementy z punktu odniesienia: typ i rozmiar danych oraz ograniczenia (chyba że odnośnik jest kolumną)
Przykład
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE name VARCHAR(25) NOT NULL := 'Smith'; surname name%TYPE := 'Jones'; BEGIN DBMS_OUTPUT.PUT_LINE('name=' || name); DBMS_OUTPUT.PUT_LINE('surname=' || surname); END; DECLARE surname employees.last_name%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('surname=' || surname); END; |
Atrybut %ROWTYPE
Mechanizm ułatwiający deklarowanie zmiennych. Służy do deklarowania zmiennej rekordowej w oparciu o schemat relacji, kursora lub typ innej zmiennej rekordowej. Używany, gdy potrzeba zmiennej rekordowej, która ma przechować cały rekord ze wskazanej relacji.
Inne zastosowania:
- tworzymy pojedynczą zmienną zamiast kilku(nastu, dziesięciu, set, …)
- nie musimy znać nazwy każdej kolumny
- …
- …
Składnia: record_name.field_name
.
Przykład
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE employees_temp ( empid NUMBER(6) NOT NULL PRIMARY KEY, deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid CHECK (deptid BETWEEN 100 AND 200), deptname VARCHAR2(30) DEFAULT ‚Sales’); DECLARE emprec employees_temp%ROWTYPE; BEGIN emprec.empid := NULL; — NULL constraint not inherited emprec.deptid := 50; — Check constraint not inherited … END; |
Przykład: deklarowanie rekordu, który reprezentuje wiersz ze złączenia i wybrany podzbiór kolumn
1 2 3 4 5 6 |
DECLARE CURSOR c2 IS SELECT employee_id, email, employees.manager_id, location_id FROM employees, departments … -- Niedokończone |
Stałe
Zmienne, których wartość nie zmienia się podczas wykonania procedury. Będą wywoływane tylko po prawej stronie operatorów. Próba zmiany wartości przerywa działanie procedury i zwraca komunikat o błędzie. Definiowana w DECLARE
, polecana notacja c_
.
Przykład
1 2 |
DECLARE nazwa_zmiennej CONSTANT typ(długość) := wartość; |
Musi zostać zainicjowana przy deklaracji:
1 2 |
DECLARE c_pi CONSTANT NUMBER(5,4) := 3.1415; |
Rodzaje struktur sterujących
Sekwencja
a->b->c->… -- ciąg poleceń wykonywanych w określonym porządku
1 2 3 4 5 6 7 8 |
DECLARE v_i NUMBER(3) := 0; v_nazwa VARCHAR2(10; BEGIN v_i := v_i + 1; v_nazwa := ‚ABC’; v_nazwa := ‚ABC’ || v_i; END; |
Selekcja
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
IF warunek THEN -- sekwencja poleceń END IF; IF warunek THEN -- sekwencja poleceń ELSE -- alternatywna sekwencja poleceń END IF; IF warunek_1 THEN -- sekwencja poleceń 1 ELSIF warunek_2 THEN -- sekwencja poleceń 2 ELSE -- sekwencja alternatywna END IF; |
Przykład
1 2 3 4 5 6 7 8 9 |
DECLARE v_prawda BOOLEAN := true; BEGIN IF v_prawda THEN dbms_output.put_line('prawda'); ELSE dbms_output.put_line('fałsz'); END IF; END; |
Iteracja
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CASE wyrażenie WHEN wartość_1 THEN sekwencja 1 WHEN wartość_2 THEN sekwencja 2 [ ELSE sekwencja alternatywna] END [CASE]; -- lub -- CASE WHEN warunek 1 THEN sekwencja 1 WHEN warunek 2 THEN sekwencja 2 [ELSE sekwencja alternatywna] END [CASE]; |
Przykłady
1 2 3 4 5 6 7 8 |
DECLARE v_vat number(2,2) := 0.23; v_proc varchar2(20); BEGIN v_proc := CASE v_var WHEN 0 THEN ‚0%’ WHEN 0.7 THEN ‚2…….. -- Niedokończone |
Pętle
Bezwarunkowa
1 2 3 |
LOOP -- sekwencja poleceń END LOOP; |
z EXIT WHEN
1 2 3 4 |
LOOP -- sekwencja poleceń EXIT WHEN warunek; END LOOP; |
pętla z EXIT
1 2 3 4 5 6 |
LOOP -- sekwencja poleceń IF warunek THEN EXIT; END IF; END LOOP; |
Iteracja - WHILE
1 2 3 |
WHILE warunek LOOP sekwencja poleceń END LOOP; |
Można przerwać poleceniem EXIT
lub EXIT WHEN
.
1 2 3 4 5 6 7 8 |
DECLARE v_licznik NUMBER(2) := 0; c_liczba_iteracji CONSTANT NUMBER(2) := 5; BEGIN WHILE v_licznik < v_liczba_iteracji LOOP … END LOOP; END; |
Iteracja - instrukcja FOR
1 2 3 |
FOR licznik IN [REVERSE] dolna_granica .. górna_granica LOOP sekwencja poleceń END LOOP; |
Zmienne licznikowe nie muszą być deklarowane. [REVERSE]
- określa porządek. Dodatkowo EXIT
i EXIT WHEN
przerywają pętlę.
Przykład
1 2 3 4 5 6 7 |
DECLARE c_liczba_iteracji CONSTANT NUMBER(2) := 5; BEGIN FOR v_licznik IN 1..c_liczba_iteracji LOOP … END LOOP; END; |
Instrukcja NULL
- nie wykonuje żadnej akcji,
- używana na etapie projektowania programu do testowania struktur sterujących,
- unikanie wygenerowania błędu niepoprawnej składni
IF … THEN
Przykład
1 2 3 4 5 6 7 8 9 |
DECLARE v_czy_zaplacono BOOLEAN := true; BEGIN IF NOT v_czy_zaplacono THEN NULL; ELSE dbms_output.put_line(‚Faktura opłacona!’); END IF; END; |
Interakcja z użytkownikiem
- przekazanie do programu informacji od użytkownika - zmienne podstawienia
- rozpoczynają się od znaku
&
:v_zmienna := &zmienna_podstawienia;
, - zmienne podstawienia są zamieniane na wartości przed wykonaniem procedury, a nie w jej trakcie. Nie można jej użyć do pytania użytkownika co do przebiegu programu (np. rozgałęziania).
Wypisanie informacji: DBMS_OUTPUT.PUT_LINE(ciag_tekstowy);
.
1 2 3 4 5 6 7 8 |
SET SERVEROUTPUT ON; DECLARE v_i NUMBER(3) := &liczba; v_nazwa VARCHAR2(50) := ‚&tekst’; — To ważne! BEGIN … END; |
DML w PL/SQL
Instrukcje modyfikujące obiekty w bloku PL/SQL są analogiczne jak w języku SQL, ale:
- zapytanie musi zwrócić dokładnie jeden wiersz,
- w zapytaniu dodatkowa klauzula
INTO
, a w niej:- lista zmiennych prostych; liczba zmiennych musi odpowiadać liczbie wyrażeń w klauzuli
SELECT
zapytania, typy muszą być zgodne; lub - zmienna rekordowa o strukturze zgodnej ze strukturą rekordu,
- lista zmiennych prostych; liczba zmiennych musi odpowiadać liczbie wyrażeń w klauzuli
- średnik umieszczamy na końcu całego polecenia.
Przykład
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE v_suma_plac NUMBER(6,2); v_ilu_pracownikow NUMBER(5); v_zespol zespoly%ROWTYPE; BEGIN SELECT * INTO v_zespol FROM zespoly WHERE nazwa = 'ADMINISTRACJA'; SELECT sum(placa_pod), count(*) INTO v_suma_plac, v_ilu_pracownikow FROM pracownicy WHERE id_zespolu = v_zespol.id; … END; |
Opcjonalna klauzula RETURNING … INTO
dla INSERT
lub UPDATE
pozwala na zapisanie we wskazanej zmiennej:
- Wartości atrybutów rekordu wstawionego przez
INSERT
, - Wartości atrybutów rekordu zmodyfikowanego przez
UPDATE
, - Wartości atrybutów rekordu usuniętego przez
DELETE
, - Tylko jednowierszowe zlecenia.
- Jawne - deklarowane przez programistę,
- Niejawne - tworzone automatycznie dla poleceń
INSERT
,UPDATE
,DELETE
iSELECT INTO
. - Zadeklarowanie w sekcji
DECLARE
- Otwarcie - wykonanie zapytania związanego z kursorem, odczytane rekordy trafiają do pamięci
- Pobieranie kolejnych rekordów
- Zamknięcie - zwolnienie obszaru pamięci kursora
- Po
CURSOR
- nazwa kursora, - jeśli ma być sparametryzowany, listę parametrów kursora umieszczamy w nawiasach po nazwie kursora, jako typ podajemy nazwę kursora
%ISOPEN
-TRUE
, jeśli otwarty;FALSE
wpp.%FOUND
-TRUE
, jeśli ostatnie pobranie rekordu z kursora zakończyło się powodzeniem,FALSE
wpp.,NULL
przed pierwszym pobraniem%NOTFOUND
- odwrotnie niż wyżej%ROWCOUNT
- liczba pobranych do tej pory rekordów
Kursor
Każde polecenie SQL w bloku PL/SQL w trakcie wykonania zostaje skojarzone z obszarem pamięci. Dostęp do tego obszaru pamięci - obszaru roboczego - jest możliwy za pomocą specjalnej struktury - kursora. Każdy kursor ma swoją własną nazwę.
Rodzaje kursorów:
Kursor jawny
Jest dedykowany zapytaniom, które mają zwrócić więcej niż jeden rekord. Jest jawnie deklarowany przez programistę.
Sposób użycia:
Składnia:
Dobra praktyka nazewnictwa: cur_
.
1 2 3 4 |
DECLARE CURSOR nazwa_kursora[(lista_parametrow)] IS { zapytanie | RETURN typ_rekordowy } [ FOR UPDATE [OF lista_atrybutów]]; |
Przykład
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE CURSOR cur_zespoly IS SELECT * FROM zespoly; CURSOR cur_pracownicy(p_id_zesp NUMBER) IS SELECT imie, nazwisko, placa_pod FROM pracownicy WHERE id_zesp = p_id_zesp; v_id zespoly.id_zesp%TYPE; v_nazwa zespoly.nazwa%TYPE; v_adres zespoly.adres%TYPE; v_zespol zespoly%ROWTYPE; BEGIN OPEN cur_zespoly; -- Po zadeklarowaniu kursora, ten musi zostać otwarty OPEN cur_pracownicy(10); FETCH cur_zespoly INTO v_id, v_nazwa, v_adres; -- Pobranie rekordów FETCH cur_zespoly INTO v_zespol; -- FETCH przesuwa kursor na następny rekord CLOSE cur_zespoly; -- Zamknięcie kursora CLOSE cur_pracownicy; END; |
Próba odczytania zamkniętego kursora skutkuje błędem INVALID_CURSOR
.
Atrybuty kursora
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE CURSOR cur_zespoly IS SELECT * FROM zespoly; v_zespol zespoly%ROWTYPE; BEGIN IF NOT cur_zespoly%ISOPEN THEN OPEN cur_zespoly; END IF; LOOP FETCH cur_zespoly INTO v_zespol; EXIT WHEN cur_zespoly%NOTFOUND; … END LOOP; CLOSE cur_zespoly; END; |
Pętla FOR
z kursorem
1 2 3 |
FOR licznik IN nazwa_kursora LOOP -- sekwencja poleceń END LOOP; |
Pętla wykona się tyle razy, ile rekordów odczyta kursor. Nie ma konieczności otwierania i zamykania kursora. Zmienna indeksująca pętli jest zmienną rekordową, nie należy jej deklarować. Odwołania do atrybutów relacji udostępnianych przez kursor - przy użyciu notacji kropkowej: kursor.nazwa_atrybutu
.
Przykład
1 2 3 4 5 6 7 8 9 |
DECLARE CURSOR cur_pracownicy(p_id_zesp NUMBER) IS SELECT imie, nazwisko, praca_pod FROM pracownicy WHERE id_zesp = p_id_zesp; BEGIN FOR v_cur IN cur_pracownicy(20) LOOP dbms_output.put_line(to_char(cur_pracownicy%ROWCOUNT)); END LOOP; END; |
Pętla FOR
z podzapytaniem
1 2 3 |
FOR licznik IN (zapytanie) LOOP -- sekwencja poleceń END LOOP; |
Klauzula WHERE CURRENT OF
Umożliwia modyfikację (poleceniem UPDATE
) bądź usunięcie (poleceniem DELETE
) bieżącego rekordu kursora. Kursor musi zostać zadeklarowany z klauzulą FOR UPDATE
. Otwarcie kursora zakłada blokady na rekordach otrzymanych w zapytaniu.
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE CURSOR cur_pracownicy(p_id_zesp NUMBER) IS SELECT … FROM … WHERE … FOR UPDATE; … BEGIN … UPDATE pracownicy SET placa_pod = placa_pod * v_podwyzka WHERE CURRENT OF cur_pracownicy; … END; |
Kursor niejawny
Tworzony automatycznie dla INSERT
, UPDATE
, DELETE
, SELECT INTO
. Nie wymaga deklarowania. Otwierany bezpośrednio przed wykonaniem polecenia, zamykany zaraz po wykonaniu polecenia. Nazwa kursora: SQL
. Atrybutów %FOUND
, %NOTFOUND
, %ROWCOUNT
, %ISOPEN
zwykle nie używamy.
Obsługa błędów PL/SQL
Źródłem błędów mogą być niepoprawne operacje, awarie SZBD, utrata połączenia z SZBD. Oracle ma zdefiniowany katalog błędów, w którym każdy błąd ma przypisany numer i odpowiedni komunikat. Wystąpienie błędu w trakcie działania procedury powoduje jej przerwanie i wyświetlenie na konsoli komunikatu o wystąpieniu błędu.
Obsługa błędów sprawia, że w razie wystąpienia błędu program nie zostanie przerwany, ale będzie wykonywał jakąś alternatywę. Wyjątek to błąd lub ostrzeżenie, które zostało wygenerowane w trakcie działania programu.
Struktura sekcji EXCEPTION
Wywołanie sekcji EXCEPTION
następuje, gdy pojawi się wyjątek. Sekcja obsługi wyjątków składa się z szeregu podsekcji WHEN … THEN
(minimum jednej). Jedna podsekcja może być związana z kilkoma wyjątkami (WHEN wyj_1 OR wyj_2
). Opcjonalna sekcja OTHERS
…
Propagacja wyjątków
Niech będzie blok zewnętrzny i wewnętrzny. Blok wewnętrzny może obsługiwać błędy powstałe w nim, jeśli nie obsłuży - obsługę przejmie blok zewnętrzny.
Przykład
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE v_nazwisko pracownicy.nazwisko%TYPE; BEGIN
SELECT nazwisko INTO v_nazwisko FROM pracownicy WHERE id_zesp = 10; dbms_output… EXCEPTION WHEN NO_DATA_FOUND THEN … WHEN TOO_MANY_ROWS THEN … END; |
Wyjątki użytkownika
1 2 |
DECLARE nazwa_wyjatku EXCEPTION; |
RAISE_APPLICATION_ERROR
Przerywa działanie programu z wypisaniem na konsoli komunikatu o wypisaniu błędu.
Użycie: RAISE_APPLICATION_ERROR(numer błędu, komunikat);
.
Zakres numerów błędów: od … do …