Programowanie w bazie danych — PL/pgSQL

Od struktury danych do sposobu wykonania operacji

Poprzedni wykład dotyczył organizacji informacji: zależności między atrybutami, kluczy i podziału tabel zgodnego z postaciami normalnymi. Poprawny model odpowiada na pytanie co baza pamięta i jakie fakty można z niej odtworzyć. Sam podział tabel nie opisuje jednak przebiegu operacji: co zrobić, gdy brakuje danych, jak wybrać jedną z kilku odpowiedzi albo jak zareagować na naruszenie reguły.

Ten wykład wprowadza program wykonywany przez serwer bazy danych. Punktem wyjścia pozostaje SQL (Structured Query Language — strukturalny język zapytań). Do SQL dodamy zmienne, instrukcje warunkowe, pętle i obsługę błędów. Celem jest rozumienie działania krótkiego programu, a nie budowa kompletnego systemu magazynowego podczas jednego spotkania.

Po wykładzie istotne jest rozróżnianie:

opisu zbioru danych w SQL i kolejności czynności w programie proceduralnym;
zmiennej lokalnej i wartości zapisanej w tabeli;
jednorazowego bloku i funkcji, którą można wielokrotnie wywołać;
komunikatu informacyjnego i błędu przerywającego wykonanie;
bloku programu i transakcji bazodanowej.

SQL a rozszerzenie proceduralne

Opis wyniku i opis przebiegu

SQL jest w znacznej części językiem deklaratywnym. Zapytanie określa wymagany wynik, na przykład wszystkie pozycje z dodatnim stanem. Nie wyznacza kolejności sprawdzania wierszy ani sposobu odczytu danych z dysku. Wybór planu wykonania należy do systemu zarządzania bazą danych — SZBD, po angielsku Database Management System (DBMS).

SELECT item_name, quantity
FROM rsod4_demo.stock
WHERE quantity > 0;

W programie proceduralnym ważna jest również kolejność: najpierw przypisanie wartości, potem sprawdzenie warunku, następnie wybrana operacja. Taki program może wykonywać polecenia SQL. Nie zastępuje relacyjnego modelu danych i nie unieważnia ograniczeń tabel.

SQL	Konstrukcja proceduralna
Wybierz wiersze spełniające warunek.	Jeśli warunek jest prawdziwy, wykonaj wskazany blok.
Zmień wszystkie pasujące wiersze jednym `UPDATE`.	Wykonaj kolejne instrukcje w określonej kolejności.
Zwróć zbiór wyników.	Zapamiętaj wartość w zmiennej, podejmij decyzję, zwróć wynik.

Rozszerzenie proceduralne jest potrzebne wtedy, gdy operacja ma wyraźny przebieg i reakcje na różne sytuacje. Nie jest wymagane do każdego raportu. Filtrowanie, złączenia i agregacje zwykle nadal najlepiej wyrażać bezpośrednio w SQL.

Dlaczego PL/pgSQL, a nie PL/SQL?

PL/pgSQL to proceduralny język systemu PostgreSQL. Skrót PL oznacza Procedural Language — język proceduralny; część pgSQL wskazuje jego związek z PostgreSQL i SQL. Dokumentacja określa go jako SQL Procedural Language. PL/SQL, czyli Procedural Language/SQL, jest odrębnym językiem używanym w Oracle. Podobieństwo nazw i konstrukcji nie oznacza zgodności kodu.

W programie przedmiotu występuje nazwa PL/SQL. Omawiane idee — bloki, zmienne, warunki, pętle i wyjątki — realizujemy tutaj w jednej wersji: PostgreSQL z PL/pgSQL. Pokazane pliki uruchamia się na serwerze PostgreSQL. SQLite z poprzednich demonstracji nie wykona tych bloków, a pgAdmin jest klientem serwera, nie osobnym silnikiem bazodanowym.

Gdzie działa program i jakie ma granice?

Aplikacja wysyła do serwera zapytanie lub wywołanie funkcji. Serwer wykonuje kod z dostępem do danych i zwraca wynik albo błąd. Funkcja może więc stanowić wspólne miejsce obliczenia używanego przez kilka aplikacji. Ma to sens, gdy reguła jest związana z danymi, a jej powielanie u każdego klienta utrudniałoby utrzymanie zgodności.

Nie wynika z tego, że cała aplikacja powinna znaleźć się w bazie. Wyświetlanie formularza, komunikacja z użytkownikiem i obsługa interfejsu pozostają zadaniami aplikacji. Prosta reguła integralności, taka jak zakaz ujemnej ilości, powinna być zapisana jako ograniczenie tabeli. Wtedy chroni dane także przed klientem, który ominie przygotowaną funkcję i wykona bezpośredni UPDATE.

Przydatne są zatem trzy poziomy odpowiedzialności:

Tabela i ograniczenia określają dopuszczalny stan danych.
SQL i funkcje odczytują dane, obliczają wyniki lub wykonują operacje.
Aplikacja organizuje kontakt z użytkownikiem i interpretuje odpowiedź.

Procedura składowana i funkcja nie są w PostgreSQL synonimami. Funkcję wywołamy w wyrażeniu, na przykład przez SELECT; procedurę wywołuje się poleceniem CALL. Na tym wykładzie wystarczy funkcja zwracająca jedną wartość. Procedury, wyzwalacze i zarządzanie uprawnieniami nie są potrzebne do pierwszego zrozumienia języka.

Baza demonstracyjna i uruchamianie przykładów

Pobierz komplet plików SQL. Zestaw jest przeznaczony do osobnej bazy dydaktycznej PostgreSQL. Nie wymaga bazy SQLite ani danych z poprzedniego wykładu. W pgAdmin należy wybrać bazę, otworzyć Query Tool i uruchomić plik 00_setup.sql jeden raz. Tworzy on schemat rsod4_demo, czyli nazwany obszar zawierający obiekty pokazu. Jeśli schemat już istnieje, skrypt zatrzyma się, zamiast nadpisywać wcześniejsze dane.

CREATE TABLE rsod4_demo.stock (
    item_id integer PRIMARY KEY,
    item_name text NOT NULL,
    quantity integer NOT NULL CHECK (quantity >= 0)
);

item_id	item_name	quantity
1	Radio	5
2	Tent	3
3	Battery	0

To uproszczony stan ilościowy asortymentu, a nie ewidencja pojedynczych numerowanych egzemplarzy z wcześniejszego pokazu normalizacji. Jedna radiostacja w tej tabeli nie ma osobnego numeru seryjnego. Zmiana poziomu szczegółowości jest celowa: trzy pozycje wystarczają do pokazania przepływu programu. Nie prowadzimy tutaj dokumentów wydań ani historii zmian.

Wyniki SELECT pojawiają się w Data Output. Komunikaty RAISE NOTICE znajdują się w Messages. Cały blok DO $$ ... $$; należy wykonywać razem, łącznie z końcowym średnikiem. Zaznaczenie samej linii ze środka bloku nie jest wywołaniem kompletnego programu.

Blok: wspólny zakres instrukcji i zmiennych

Blok porządkuje program. Opcjonalna część DECLARE deklaruje zmienne. Część między BEGIN a END zawiera wykonywane instrukcje. Sekcja EXCEPTION, jeżeli występuje, określa reakcję na wybrane błędy.

DO $$
DECLARE
    v_available integer := 5;
    v_requested integer := 2;
    v_remaining integer;
BEGIN
    v_remaining := v_available - v_requested;
    RAISE NOTICE 'Available: %, requested: %, remaining: %',
        v_available, v_requested, v_remaining;
END;
$$;

DO uruchamia jednorazowy, anonimowy blok: nie nadajemy mu nazwy do późniejszego wywoływania. Znaki $$ wyznaczają granice tekstu programu przekazanego do wykonania. Dzięki nim zwykłe apostrofy wewnątrz komunikatu nie wymagają podwajania. Znak % w komunikacie oznacza miejsce wstawienia kolejnego argumentu.

Wynik to informacja o dostępnych 5 sztukach, żądanych 2 i pozostałych 3. Tabela nie została zmieniona. W tym programie wykonano obliczenie wyłącznie na zmiennych. Odczyt SELECT * FROM rsod4_demo.stock nadal pokaże ilość 5. To ważne rozróżnienie między obliczeniem możliwego wyniku a zapisaniem operacji.

BEGIN w PL/pgSQL oznacza początek części wykonywalnej bloku. Nie jest tym samym co polecenie SQL BEGIN rozpoczynające transakcję. Blok może działać wewnątrz transakcji; te dwa poziomy nie opisują tej samej rzeczy.

Zmienne, typy i wartość NULL

Zmienna przechowuje wartość na czas wykonania bloku. Ma nazwę oraz typ, na przykład integer, numeric, text albo boolean. Typ określa, jakie wartości i operacje są dozwolone. Przypisanie := zmienia wartość zmiennej; porównanie = sprawdza równość. Prefiks v_ jest tutaj konwencją nazw zmiennych, a nie obowiązkowym elementem języka.

Zmienna bez wartości początkowej ma domyślnie NULL. To brak znanej wartości, a nie liczba zero ani pusty tekst. Wyrażenie 5 - NULL ma wynik NULL. Również porównanie NULL = 0 nie daje prawdy ani fałszu, lecz wartość nieznaną. Dlatego obecność wartości bada się przez IS NULL lub IS NOT NULL.

Przykładowo brak liczby zamawianych sztuk nie może zostać potraktowany jak poprawne zamówienie. Program powinien zdecydować, czy odrzuci brak danych, czy zastosuje jawnie ustaloną wartość domyślną. Milczące zastępowanie braku zerem mogłoby ukryć błąd formularza.

Warunki: wybór jednej ścieżki

Instrukcja IF wykonuje gałąź tylko wtedy, gdy warunek ma wartość prawda. ELSIF pozwala sprawdzić kolejny przypadek, a ELSE obsługuje pozostałe sytuacje. Kolejność ma znaczenie: wykonana zostanie pierwsza pasująca gałąź.

DO $$
DECLARE
    v_available integer := 5;
    v_requested integer := 2;
BEGIN
    IF v_requested IS NULL OR v_requested <= 0 THEN
        RAISE NOTICE 'Invalid request';
    ELSIF v_requested > v_available THEN
        RAISE NOTICE 'Insufficient stock';
    ELSE
        RAISE NOTICE 'Request accepted; remaining: %', v_available - v_requested;
    END IF;
END;
$$;

Najpierw sprawdzamy poprawność żądania. Dopiero dla poprawnej liczby ma sens porównanie jej ze stanem magazynu. Tak uporządkowane warunki oddzielają błąd danych wejściowych od poprawnego żądania, którego nie można zrealizować.

Żądana ilość	Interpretacja	Gałąź
2	Poprawne żądanie mieszczące się w stanie 5.	`ELSE`
7	Poprawna liczba, ale niewystarczający zapas.	`ELSIF`
0 lub −1	Żądanie nie opisuje dodatniej liczby sztuk.	Pierwszy `IF`
`NULL`	Nie przekazano znanej liczby sztuk.	Pierwszy `IF`

Wszystkie cztery przypadki można sprawdzić, zmieniając wyłącznie deklarację v_requested w pliku 02_conditions.sql. Program nadal tylko oblicza odpowiedź; nie wykonuje wydania z magazynu.

Pętle: powtarzanie instrukcji

Pętla jest potrzebna, gdy określone instrukcje mają zostać wykonane wielokrotnie. Pętla liczbowa FOR jest wygodna dla znanego zakresu, natomiast WHILE powtarza blok tak długo, jak długo warunek pozostaje prawdziwy. W obu przypadkach należy rozumieć, kiedy program zakończy powtarzanie.

DO $$
BEGIN
    FOR v_number IN 1..3 LOOP
        RAISE NOTICE 'FOR label: %', v_number;
    END LOOP;
END;
$$;

Zmienna sterująca liczbowej pętli FOR jest deklarowana automatycznie. Pętla wykona trzy iteracje z wartościami 1, 2 i 3. Po ostatniej iteracji program przejdzie do instrukcji po END LOOP.

DO $$
DECLARE
    v_number integer := 1;
BEGIN
    WHILE v_number <= 3 LOOP
        RAISE NOTICE 'WHILE label: %', v_number;
        v_number := v_number + 1;
    END LOOP;
END;
$$;

W WHILE to program zmienia stan wpływający na warunek. Pominięcie zwiększenia licznika pozostawiłoby warunek prawdziwy i powodowałoby niekończące się powtarzanie. Taki błąd należy omówić na kodzie, bez uruchamiania nieskończonej pętli na serwerze.

Pętla nie jest domyślnym sposobem przetwarzania tabeli. Jeśli wszystkie pozycje mają zostać zmienione według tego samego wzoru, pojedyncze UPDATE zwykle wyraża zamiar lepiej niż odczyt każdego wiersza i osobna zmiana. SQL operuje na zbiorach; procedura jest uzupełnieniem tego modelu, a nie powodem, by go porzucać.

Odczyt z tabeli do zmiennej

Dotychczas wartości były zapisane w programie. Teraz źródłem informacji jest tabela. Wewnątrz PL/pgSQL konstrukcja SELECT ... INTO umieszcza wynik zapytania w zmiennej.

DO $$
DECLARE
    v_item_id integer := 1;
    v_quantity integer;
BEGIN
    SELECT quantity INTO v_quantity
    FROM rsod4_demo.stock
    WHERE item_id = v_item_id;

    IF NOT FOUND THEN
        RAISE NOTICE 'Unknown item: %', v_item_id;
    ELSE
        RAISE NOTICE 'Item %, quantity %', v_item_id, v_quantity;
    END IF;
END;
$$;

FOUND jest zmienną logiczną dostarczaną przez PL/pgSQL. W tym miejscu informuje, czy poprzedni SELECT INTO znalazł wiersz. Sprawdzamy ją od razu, ponieważ kolejne instrukcje obsługujące dane mogą zmienić jej wartość. Dla identyfikatora 999 zapytanie niczego nie znajdzie. Taki przypadek różni się od istniejącej pozycji Battery z ilością 0.

Klucz główny item_id gwarantuje najwyżej jeden pasujący wiersz. W ogólnym przypadku SELECT INTO bez STRICT przy braku wiersza przypisuje NULL, a przy wielu wierszach wybiera pierwszy; bez porządku nie należy zakładać, który to będzie. Wariant INTO STRICT wymaga dokładnie jednego wiersza i zgłasza błąd przy zeru lub wielu wynikach. To wybór kontraktu operacji, a nie tylko dodatkowy zapis składni.

Poza PL/pgSQL polecenie SQL SELECT INTO w PostgreSQL może służyć do tworzenia nowej tabeli. W przedstawionym bloku INTO v_quantity oznacza przypisanie do zmiennej. Kontekst użycia ma znaczenie.

Funkcja: nazwana operacja z wejściem i wynikiem

Funkcja pozwala zapisać fragment programu raz i wywoływać go z różnymi danymi. Jej kontrakt obejmuje nazwę, parametry, typ wyniku oraz znaczenie odpowiedzi. Poniższa funkcja klasyfikuje stan ilościowy. Otrzymuje liczbę, zwraca tekst i nie zmienia żadnej tabeli.

CREATE OR REPLACE FUNCTION rsod4_demo.stock_status(p_quantity integer)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_quantity IS NULL OR p_quantity < 0 THEN
        RETURN 'invalid';
    ELSIF p_quantity = 0 THEN
        RETURN 'unavailable';
    ELSE
        RETURN 'available';
    END IF;
END;
$$;

CREATE OR REPLACE zapisuje definicję funkcji lub zastępuje zgodną istniejącą definicję. Nie jest jej wywołaniem. RETURNS text określa typ wyniku, a RETURN kończy bieżące wywołanie i przekazuje wartość wywołującemu.

SELECT quantity, rsod4_demo.stock_status(quantity) AS status
FROM (VALUES (5), (0), (-1), (NULL::integer)) AS samples(quantity);

Oczekiwane odpowiedzi to kolejno available, unavailable, invalid, invalid. Zapis NULL::integer jawnie nadaje brakującej wartości typ całkowity. Parametr p_quantity jest wejściem funkcji, a nie wskazaniem kolumny, którą funkcja miałaby zmodyfikować. Aby zmienić dane, potrzebna byłaby instrukcja modyfikująca, na przykład UPDATE.

Taką prostą klasyfikację można również zapisać wyrażeniem SQL CASE. Przykład służy poznaniu kontraktu funkcji; nie dowodzi, że dla każdej decyzji należy tworzyć funkcję składowaną.

Zmiana danych i informacja, czy operacja się udała

Odczyt stanu i późniejsze niezależne zmniejszenie ilości nie stanowią jeszcze pełnej ochrony danych przy wielu klientach. Dobrą podstawą jest zapisanie warunku dostępności w samej instrukcji modyfikującej.

UPDATE rsod4_demo.stock
SET quantity = quantity - 2
WHERE item_id = 1 AND quantity >= 2
RETURNING quantity;

Operacja zmniejsza stan tylko wtedy, gdy dany wiersz istnieje i ma co najmniej dwie sztuki. RETURNING zwraca ilość po zmianie. Brak wiersza w wyniku oznacza, że nic nie zmieniono. Sam ten brak nie rozstrzyga, czy nie było pozycji, czy zabrakło sztuk.

Plik 06_update.sql umieszcza tę operację w bloku z parametrami lokalnymi, sprawdzeniem dodatniej liczby i IF FOUND. Przy stanie początkowym 5 i żądaniu 2 pozostaną 3 sztuki. Kolejna udana próba ponownie zmniejsza stan — uruchamianie tego pliku nie jest neutralnym podglądem. Plik 08_restore_data.sql przywraca trzy demonstracyjne ilości do 5, 3 i 0.

Nie jest to kompletny proces wydania: brak tu dokumentu, odbiorcy i historii. Jeżeli operacja wymaga zmiany kilku tabel jako jednej całości, potrzebne jest przetwarzanie transakcyjne. To temat następnego wykładu.

Wyjątek: przerwanie zwykłej ścieżki programu

Błąd może pochodzić z programu, ograniczenia tabeli lub systemu. Na przykład RAISE EXCEPTION jawnie zgłasza błąd, a naruszenie CHECK powoduje błąd integralności. RAISE NOTICE jest tylko komunikatem i nie przerywa wykonania.

Obsługa wyjątku powinna wskazywać konkretny problem, na który program umie zareagować. Przechwycenie wszystkich błędów i zgłoszenie sukcesu ukrywałoby awarię zamiast ją obsługiwać.

DO $$
BEGIN
    BEGIN
        UPDATE rsod4_demo.stock SET quantity = quantity + 1 WHERE item_id = 2;
        UPDATE rsod4_demo.stock SET quantity = -1 WHERE item_id = 1;
    EXCEPTION
        WHEN check_violation THEN
            RAISE NOTICE 'CHECK rejected negative stock; both inner updates were undone';
    END;
    RAISE NOTICE 'Execution continues after the handled error';
END;
$$;

Pierwsza zmiana w wewnętrznym bloku jest poprawna, ale druga narusza CHECK (quantity >= 0). Obsługa check_violation przechwytuje ten konkretny błąd. Zmiany danych wykonane wewnątrz tego bloku zostają wycofane, także pierwsza, która sama była poprawna. Po obsłużeniu błędu program przechodzi dalej. Odczyt tabeli pokaże taki stan jak przed uruchomieniem bloku.

Nie oznacza to, że dowolny wcześniej wykonany kod w całej sesji zostanie cofnięty. Znaczenie ma zakres bloku z obsługą wyjątku. Nie oznacza to również cofnięcia lokalnych wartości zmiennych w taki sam sposób jak zmian danych. Na tym etapie najważniejszy jest obserwowalny skutek: błąd nie pozostawia częściowo wykonanej zmiany danych z tego wewnętrznego bloku.

Jak sprawdzać krótki program bazodanowy?

Poprawna kompilacja nie dowodzi poprawnego działania. Weryfikacja musi obejmować typowy przypadek, granice oraz brak danych.

Co sprawdzamy?	Przykład	Oczekiwany sens wyniku
Zwykłe działanie	Dostępne 5, żądane 2.	Pozostają 3 sztuki, jeśli wykonano zapis.
Granicę	Dostępne 5, żądane 5.	Stan 0 jest legalny.
Brak zasobu	Dostępne 5, żądane 7.	Brak wydania, bez ujemnego stanu.
Niepoprawne wejście	Żądane 0, −1 lub `NULL`.	Jawne odrzucenie żądania.
Nieistniejący obiekt	Identyfikator 999.	Nie mylimy braku pozycji ze stanem 0.
Ochronę tabeli	Bezpośredni zapis ilości −1.	`CHECK` odrzuca zmianę.

Kontrola wyniku obejmuje zarówno odpowiedź programu, jak i odczyt tabeli. Komunikat „accepted” w bloku operującym wyłącznie na zmiennych nie jest dowodem zapisu do bazy. Z kolei brak wyjątku przy UPDATE, który nie zmienił żadnego wiersza, nie oznacza zrealizowania żądania.

Pytania do utrwalenia

Dlaczego zmiana v_available nie zmienia kolumny quantity?
Czym różni się brak wiersza od istniejącego wiersza z ilością zero?
Dlaczego NULL trzeba uwzględnić przed porównaniem żądania ze stanem?
Kiedy pojedyncze UPDATE jest lepszym opisem operacji niż pętla?
Co pozostanie w tabeli, gdy druga zmiana wewnętrznego bloku naruszy CHECK?
Dlaczego warunek w funkcji nie zastępuje ograniczenia tabeli?

Nie ma tutaj testu początkowego. Pytania służą omówieniu materiału; sprawdzenie końcowe kursu pozostaje osobnym wydarzeniem.