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

BEGIN
	polecenia programu
END;

Postać pełna

Sekcja deklaracji, wykonywalna bloku, obsługi błędów.

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.

DECLARE
	…
BEGIN
	DECLARE
		…
	BEGIN
		…
	END;
END;

Przykład

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.

<>
DECLARE
	…
BEGIN
	…
END;

Wskaźnik do zmiennej (notacja kropkowa): block_label.variable_name.

Przykład

<>
DECLARE
	n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1;
BEGIN
	<>
	DECLARE
		…
	BEGIN
		…
	END;
END;

• poprawia czytelność kodu,
• pozwala na lepszą kontrolę nad wykonaniem kodu (etykieta bloku może być celem instrukcji EXIT i CONTINUE)
• 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

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)

Przykład

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

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

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

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

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

DECLARE
	nazwa_zmiennej CONSTANT typ(długość) := wartość;

Musi zostać zainicjowana przy deklaracji:

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

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

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

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

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

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

LOOP
	-- sekwencja poleceń
END LOOP;

z EXIT WHEN

LOOP
	-- sekwencja poleceń
	EXIT WHEN warunek;
END LOOP;

pętla z EXIT

LOOP
	-- sekwencja poleceń
	IF warunek THEN
		EXIT;
	END IF;
END LOOP;

Iteracja - WHILE

WHILE warunek LOOP
	sekwencja poleceń
END LOOP;

Można przerwać poleceniem EXIT lub EXIT WHEN.

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

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

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

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);.

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,
• średnik umieszczamy na końcu całego polecenia.

Przykład

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.

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:

• Jawne - deklarowane przez programistę,
• Niejawne - tworzone automatycznie dla poleceń INSERT, UPDATE, DELETE i SELECT INTO.

Kursor jawny

Jest dedykowany zapytaniom, które mają zwrócić więcej niż jeden rekord. Jest jawnie deklarowany przez programistę.

Sposób użycia:

• 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

Składnia:

• Po CURSOR - nazwa kursora,
• jeśli ma być sparametryzowany, listę parametrów kursora umieszczamy w nawiasach po nazwie kursora, jako typ podajemy nazwę kursora

Dobra praktyka nazewnictwa: cur_.

DECLARE
	CURSOR nazwa_kursora[(lista_parametrow)] IS
		{ zapytanie | RETURN typ_rekordowy }
		[ FOR UPDATE [OF lista_atrybutów]];

Przykład

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

• %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

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

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

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

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.

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

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

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 …