PL/SQL, część 1

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 …

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Witryna wykorzystuje Akismet, aby ograniczyć spam. Dowiedz się więcej jak przetwarzane są dane komentarzy.