Bezpłatny kurs PL/pgSQL by Andrzej Klusiewicz

Cześć  :)

Oddaję w Wasze ręce wersję 1.0.0.0.0.(0) swojego tutoriala dotyczącego języka PL/pgSQL - czyli proceduralnego języka przetwarzania danych dostępnego w bazach PostgreSQL.  Gdybyście mieli jakieś uwagi, zapraszam do ich zgłaszania w komentarzach pod tym postem.
Andrzej Klusiewicz

1. Podstawy
2. Używanie zmiennych i wyświetlanie komunikatów
3. Instrukcję warunkowe
4. Wyjątki
5. Pętle
6. Instrukcje DML
7. Odczyt danych z tabel i widoków
8. Funkcje
9. Wyzwalacze

Wyzwalacze

Wyzwalacze to specjalne funkcje które uruchamiane są w reakcji na jakieś zdarzenie na obiektach bazodanowych. Możesz stworzyć trigger który będzie np. zapisywał informacje o tym to i co zmienił w tabeli ludziki, albo zablokować jakiś rodzaj zmian.
Wyzwalacze mogą być uruchamiane przed lub po wydarzeniu które je wyzwala. Mogą działać na poziomie obiektu, albo na poziomie wiersza. W pierwszym przypadku wyzwalacz (trigger) uruchomi się tylko raz w związku ze zdarzeniem, niezależnie od ilości zmienianych wierszy. W drugim przypadku uruchomi się dla każdego zmienianego wiersza. Kiedy i jakie wyzwalacze stosować omówimy sobie nieco dalej. Na ten moment rozpoczniemy od najprostszego wyzwalacz.
Na wyzwalacz w PostgreSQL składają się dwie rzeczy. Wyzwalacz sam w sobie, oraz funkcja która przez ten wyzwalacz jest uruchamiana. To właśnie w tej funkcji opisywana jest cała mechanika jaka ma zostać wykonana. W samym wyzwalaczu opisane jest tylko kiedy owa funkcja ma zostać uruchomiona.

Wyzwalacze obiektowe

Nasz pierwszy wyzwalacz będzie wypisywał na konsolę komunikat za każdym razem gdy ktoś zmieni zawartość tabeli ludziki.
Zaczynamy od stworzenia funkcji która będzie zawierała całe "mięcho". Kod funkcji znajduje się poniżej:



create or replace function obserwator_funkcja() returns trigger as $$
begin
raise notice 'ktoś coś zmienił w ludzikach!';
end $$ language plpgsql;



Zwróć uwagę na typ zwracany przez tę funkcję - returns trigger - jeśli funkcja jest dedykowana pod wyzwalacz, musi mieć właśnie taką konstrukcję.

Po skompilowaniu funkcji musimy założyć wyzwalacz na tabelkę "ludziki":




create trigger obserwator_trigger
after update on ludziki
execute procedure obserwator_funkcja();




Wyzwalacz jako taki określa tylko moment uruchomienia funkcji. Przyjrzyj się linii 2 wyzwalacza. Funkcja zostanie uruchomiona po zdarzeniu update na tabelce ludziki. W ramach wyzwalacza uruchamiana jest nasza funkcja. Aby przetestować wyzwalacz dokonuję zmiany na wszystkich wierszach tabeli ludziki:

Jak widać choć aktualizacja dotyczyła kilku wieszy, wyzwalacz został uruchomiony tylko raz. Związane jest to z tym, że domyślnie wyzwalacz zakładany jest na poziomie obiektu a nie wiersza.

Wyzwalacze wierszowe

Jeśli chcemy by wyzwalacz uruchamiał się dla każdego zmienianego wiersza, musi to być wyzwalacz wierszowy. Poniżej znajduje się wierszowy wariant wyzwalacza z poprzedniego podrozdziału:



create trigger obserwator_trigger_wiersz
after update on ludziki
for each row
execute procedure obserwator_funkcja_wiersz();



Od poprzedniego odróżnia go klauzula "for each row". To za jej sprawą właśnie wyzwalacz będzie się uruchamiał dla każdego zmienianego wiersza. W związku ze zmianą wyzwalacza musimy zmienić też podpiętą funkcję:



create or replace function obserwator_funkcja_wiersz() returns trigger as $$
begin
raise notice 'ktoś coś zmienił w ludzikach!';
return new;
end $$ language plpgsql;



Różnica sprowadza się do klauzuli "return new". Co ona właściwie oznacza? Zwraca modyfikowany wiersz. Skąd on jednak tutaj się wziął i po co właściwie uruchamiać wyzwalacz dla każdego modyfikowanego wiersza? Wyzwalacze wierszowe stosuje się gdy zechcemy uzyskać dostęp do zmienianych wierszy, odczytać jego wartości przed zmianą i po. Możemy go również zmodyfikować w locie. Do nowej i starej postaci wiersza odnosimy się poprzez "new" i "old". Jeśli wyzwalacz jest założony nie "after" a "before" - czyli jest uruchamiany przed np. modyfikacją wiersza, to taki aktualizowany wiersz "przelatuje" przez nasz wyzwalacz, i możemy dokonać w locie jakichś zmian na nim. Na przykład uzupełnić jakieś kolumny. W związku z tym musimy w wyzwalaczu wierszowym oddać ten przelatujący wiersz.


Po modyfikacjach uruchamiam aktualizację danych po raz kolejny. Widzimy teraz że wyzwalacz uruchomił się dla wszystkich wierszy osobno.

Wykorzystajmy teraz predykaty "new" i "old". Do pól nowego i starego wiersza odnoszę się podając nazwę kolumny po new lub old:

create or replace function obserwator_funkcja_wiersz() returns trigger as $$

begin

raise notice 'Zmiana dla % %. old.wiek=% new.wiek=%', new.imie,new.nazwisko, old.wiek, new.wiek;

return new;

end $$ language plpgsql;

Sam wyzwalacz pozostaje bez zmian. Efekt działania:

Predefiniowane zmienne

Poza new i old PostgreSQL dostarcza również inne zmienne niosące ze sobą cenne informacje.

NEW	Nowa postać wiersza
OLD	Stara postać wiersza
TG_NAME	Nazwa wyzwalacza
TG_WHEN	Zwraca "BEFORE" lub "AFTER" w zależności od momentu uruchomienia wyzwalacza
TG_LEVEL	Zwraca "ROW" lub "STATEMENT" zależności czy jest to wyzwalacz na poziomie wiersza czy obiektu
TG_OP	Zwraca "INSERT" "UPDATE" "DELETE" "TRUNCATE" w zależności od instrukcji która uruchomiła wyzwalacz
TG_TABLE_NAME	Nazwa tabeli dla której wyzwalacz został wywołany

Funkcje

Funkcje to nazwane bloki kodu. Mogą ale nie muszą przyjmować parametry. Mogą ale nie muszą niczego zwracać. Może to być pewnym zaskoczeniem dla programistów Oracle - tu nie ma podziału na funkcje i procedury. Odpowiednikiem procedury jest funkcja która nic nie zwraca. Funkcje będą składowane w bazie danych.

Deklaracja i wywoływanie funkcji

Najprostsza funkcja której jedynym zadaniem będzie wyświetlenie komunikatu mogłaby wyglądać tak:

create or replace function sayhello() returns void as $$
begin
raise notice 'Hello my friend!';
end $$ language plpgsql;



Jak widzisz po słówku "returns" następuje deklaracja zwracanego przez funkcję typu danych, jeśli funkcja nic nie zwraca (czyli jest odpowiednikiem procedury w bazach Oracle) to piszemy "returns void". Zauważ że w samym kodzie funkcji nigdzie nie ma też zwrotu wartości z funkcji z użyciem klauzuli "return".



Wywołać tę funkcję możemy na jeden z dwóch sposobów (podobnie jak i każdą natywną funkcję w PostgreSQL). Albo z użyciem SELECT, albo wewnątrz kodu plpgsql z użyciem klauzuli "perform":



select sayhello();



do
$$
begin
perform sayhello();
end $$;

Parametry funkcji i zwracanie wartości

Zaczniemy od zwracania wartości przez funkcję. Jeśli chcemy by funkcja nam coś zwracała, po słówku returns w nagłówku podajemy zwracany typ danych.



create or replace function dawajliczbe() returns numeric as $$
begin
return 12;
end $$ language plpgsql;

Skoro zobligowałem się do zwrotu wartości typu numeric, to zobowiązanie muszę spełnić umieszczając w kodzie funkcji klauzulę "return" zwracającą zadeklarowaną wartość. Działanie tej funkcji sprowadza się do oddania nam liczby 12. Możemy tę funkcję wywołać selectem:

lub odbierając wartość zwracaną przez funkcję w innej funkcji lub bloku anonimowym:

do
$$
declare
x numeric;
begin
x:=dawajliczbe();
raise notice 'Dostałem %',x;
end $$;

Funkcje mogą przyjmować parametry. Nie wiąże się to w żaden sposób ze zwracaniem przez nie wyników - to są dwie niezależne sprawy. Jeśli chcemy by funkcja coś przyjęła, deklarujemy parametry w nawiasach po nazwie funkcji deklarując jednocześnie typ parametrów. W typ przypadku będą to dwa parametry liczbowe:

create or replace function dodaj(x numeric,y numeric) returns numeric as $$
declare
suma numeric;
begin
suma:=x+y;
return suma;
end $$ language plpgsql;

Przy wywołaniu funkcji musimy teraz podać wartości które trafią do parametrów:

Nasze funkcje podobnie jak funkcje natywne, możemy używać następnie w SQL. Poniżej zdeklarowałem funkcję która do wartości podanej przez parametr dolicza 23% podatek VAT:

create or replace function vat(netto numeric) returns numeric as $$
declare
brutto numeric;
begin
brutto:=netto*1.23;
return brutto;
end $$ language plpgsql;

Możemy podać wartość "z palca" jak poniżej...

... lub użyć do przetwarzania danych z tabeli:

Odczyt danych z tabel i widoków w PL/pgSQL

W zależności od tego czy chcemy pobierać jeden czy więcej wierszy, stosujemy albo konstrukcję select into, albo kursor. Jeśli mamy pewność że zostanie odczytany dokładnie jeden wiersz, możemy użyć takiej konstrukcji:


do
$$
declare
ilu integer;
begin
select count(*) into ilu from ludziki;
raise notice 'mamy % ludzików',ilu;
end $$;


Wartości odczytywane są przekazywane do zmiennych pozycyjnie. Tj w tym przypadku wynik count(*) wyląduje w zmiennej ile. Gdybyśmy zechcieli, możemy też odczytać więcej wartości:


do
$$
declare
ilu integer;
najstarszy integer;
begin
select count(*),max(wiek) into ilu,najstarszy from ludziki;
raise notice 'mamy % ludzików',ilu;
raise notice 'najstarszy ma % lat',najstarszy;
end $$;

W takim przypadku wynik count(*) wyląduje w zmiennej "ile"a wynik max(wiek) wyląduje w zmiennej "najstarszy" - czyli zgodnie z kolejnością. Ilość podstawianych zmiennych musi zawsze odpowiadać ilości zwracanych wartości.

Jeśli chcemy odczytać więcej niż jeden wiersz, niezbędny nam będzie kursor. Ważne - kursor nie przechowuje danych, a jedynie pozwala na ich odczyt. Odczyt będzie przebiegał wiersz po wierszu, nie musisz się więc martwić o wycieki pamięci. Na potrzeby przetwarzania kursorów powstała też specjalna pętla, która niejawnie kursor otwiera, zamyka, a także powoduje odczyt kolejnych wierszy. Pętla obróci się więc tyle razy, ile będzie wierszy w zapytaniu na podstawie którego powstał kursor. Wewnątrz tej pętli mam dostęp do aktualnie odczytanego wiersza, mogę więc go np. wypisać na konsolę. Tutaj są dwa warianty. Albo robimy to tak jak w bazach Oracle i wymieniamy wszystkie kolumny po kolei:


do
$$
declare
k cursor for select * from ludziki;
begin
for w in k loop
raise notice '% % % %',w.id,w.imie,w.nazwisko,w.wiek;
end loop;
end $$;

Albo... odnosząc się do całego wiersza (i tutaj programiści Oracle PL/SQL padają z wrażenia):


do
$$
declare
k cursor for select * from ludziki;
begin
for w in k loop
raise notice '%',w;
end loop;

end $$;

Ćwiczenia

1. Napisz program który odczyta i wyświetli na konsoli średni, najwyższy i najniższy wiek z tabeli ludziki.
   
2. Napisz program który wyświetli na konsoli osoby starsze niż średnia wieku wśród wszystkich osób z tabeli ludziki. Osoby mają być wyświetlane w ten sposób, by podane było ich imię, nazwisko, wiek, oraz różnica w stosunku do średniego wieku - przycięta do jednostek. Osoby mają być wyświetlone od najstarszej do najmłodszej.
   

Instrukcje DML w PL/pgSQL

Abyśmy mogli rozpocząć przykłady z tego rozdziału, niezbędne będzie stworzenie potrzebnej nam tabeli i umieszczenie w niej danych:


create table ludziki (
id serial primary key,
imie text,
nazwisko text,
wiek integer
);


insert into ludziki(imie,nazwisko,wiek) values ('Marian','Paździoch',75);
insert into ludziki(imie,nazwisko,wiek) values ('Waldemar','Kiepski',24);
insert into ludziki(imie,nazwisko,wiek) values ('Ferdynand','Kiepski',56);
insert into ludziki(imie,nazwisko,wiek) values ('Marian','Boczek',34);
insert into ludziki(imie,nazwisko,wiek) values ('Rozalia','Kiepska',120);


Po tej operacji powinniśmy mieć tabelę z 5 wierszami.

Insert

Jeśli zechcemy wstawić wiersz do tabeli z użyciem PL/pgSQL, wystarczy umieścić instrukcję Insert wewnątrz kodu:


do
$$
begin
insert into ludziki(imie,nazwisko,wiek) values ('Zenek','Martyniuk',45);
end $$;


Kolumna id jest uzupełniana automatycznie, w związku z tym że jest to kolumna typu serial. PL/pgSQL pozwala nam dodatkowo na użycie zmiennych:


do
$$
declare
im text:='Koziołek';
na text:='Matołek';
wi integer:=17;
begin
insert into ludziki(imie,nazwisko,wiek) values (im,na,wi);
end $$;

Po uruchomieniu kodu i sprawdzeniu zawartości tabeli ludziki, zaobserwujemy pojawienie się nowych wierszy w tabeli:

 Update i delete

Instrukcja UPDATE czy DELETE może być wykonywana tak jak w SQL, z tym że w PL/pgSQL możemy używać zmiennych, podobnie jak przy INSERT:


do
$$
declare
postarzej_o integer:=5;
begin
update ludziki set wiek=wiek+postarzej_o;
end $$;


Po wykonaniu powyższego kodu wszystkie ludziki są starsze o 5 lat :) Poniżej usuwamy jeszcze Zenka Martyniuka (i tak jest go już wszędzie pełno).


do
$$
declare
kogo integer:=6;
begin
delete from ludziki where id=kogo;
end $$;

Pętle

Pętle służą do wielokrotnego wykonywania jakiejś czynności. Pętle zasadniczo można podzielić na te z "FOR", te z "WHILE" i te z "EXIT WHEN". Różnica pomiędzy nimi jest taka, że przy pętlach for musisz znać konkretną ilość powtórzeń jaką ma wykonać pętla, a przy pętli z while czy exit when nie musisz. Pętla while jest wykonywana tak długo jak długo określony w niej warunek jest prawdziwy - możesz np. wykonywać pętle tak długo, jak długo prawdą jest że dziś jest środa. Pętle z exit when mają określony warunek wyjścia - tj. będziesz deklarował np. wykonuj w pętli coś, wyjdź kiedy okaże się że dziś czwartek.

Pętle FOR

Zaczniemy od pętli typu for, jako tej prostszej postaci. Poniżej przykład takiej pętli:


do
$$
begin
for x in 1..10 loop
raise notice '%' , x;
end loop;
end $$;

Pętla ta wypisuje kolejne wartości w zakresie od 1 do 10. Jak ta pętla działa? Przyjrzyjmy się nagłówkowi:


for x in 1..10 loop


X jest zmienną widoczną tylko wewnątrz pętli. Jako pierwszą wartość przypisujemy jej 1, a przy każdym obrocie pętli wartość ta będzie zwiększana o 1 aż do osiągnięcia wartości 10. Krótko mówiąc, zawartość pętli (czyli to co pomiędzy "loop" a "end loop") zostanie wykonane 10 razy. Zmiennej x nie musimy deklarować osobno w sekcji declare.

Wartości skrajne podałem bezpośrednio, ale mogą to być również zmienne. Nie muszą też być to wartości większe od zera. Poniżej przykład użycia dodatkowych zmiennych, zakres zaczyna się od wartości -10.



do
$$
declare
poczatek integer:=-10;
koniec integer:=10;
begin
for x in poczatek..koniec loop
raise notice '%' , x;
end loop;

end $$;

Możesz również określić skok o określoną ilość wartości X. W poniższym przykładzie x będzie się zwiększał nie o 1 a o 2:

do
$$
begin
for x in 1..10 by 2 loop
raise notice '%' , x;
end loop;
end $$;

 Pętle WHILE

Kolejnym rodzajem pętli są pętle while. Tu będziemy określać że dana pętla ma się wykonywać tak długo, jak długo określony warunek jest prawdziwy. Poniżej adaptacja poprzednich przykładów, ale z użyciem pętli while:


do
$$
declare
x integer:=1;
begin
while(x<=10) loop
raise notice '%',x;
x:=x+1;
end loop;

end $$;

Kluczowy jest tu warunek "while(x<=10)". Określa on kiedy pętla ma być wykonywana. Powyższa pętla będzie wykonywana tak długo, jak długo jak długo prawdą jest że x jest mniejsze bądź równe 10. Nie zapomnij o zwiększaniu iteratora "x:=x+1", ponieważ jeśli ten element pominiesz, pętla będzie wykonywała się w nieskończoność, bo warunek w while zawsze będzie prawdziwy. Jeśli warunek określony w while nie był prawdziwy od początku, pętla nie wykona się ani razu.

 Pętle z EXIT WHEN

Pętlę możesz też zdeklarować w ten sposób:


do
$$
declare
x integer:=1;
begin
loop
raise notice '%',x ;
x:=x+1;
end loop;
end $$;



Z tym że w takim przypadku pętla ta nie skończyłaby się nigdy (w zasadzie po przekroczeniu zakresu typu danych integer). Brakuje nam tutaj określenia jak długo pętla ma być wykonywana, lub kiedy ma zostać przerwana. Poniżej adaptacja przykładu z pętli while, tyle że z użyciem klauzuli exit when.


do

$$
declare
x integer:=1;
begin
loop
exit when x=10;
raise notice '%',x ;
x:=x+1;
end loop;

end $$;


Jak widzisz warunek jest sprawdzany na początku pętli. Gdyby okazało się że x ma wartość 10, pętla zostałaby przerwana. Tak też się dzieje. Troszeczkę inne jest podejście - w While mamy warunek przy spełnieniu którego pętla ma być wykonywana nadal, tutaj warunek określa kiedy ma zostać przerwana.

Ćwiczenia

1. Stwórz program który wyświetli wartości od 1 do 100, wypisując obok czy dana wartość jest parzysta czy nie.
   
2. Stwórz program który wyświetli wartości od -10 do 10, a obok każdej z nich wynik dzielenia 1 przez daną wartość. Ewentualny wyjątek powinien być obsłużony w taki sposób, by nie przerywać działania programu, a zamiast tego wyświetlić odpowiedni komunikat.
   
3. Napisz symulator lokaty - program powinien przyjmować poprzez zmienne kapitał startowy, oprocentowanie w skali roku i czas trwania lokaty wyrażony w miesiącach. Program ma wyświetlić kolejne miesiące, oraz aktualny kapitał powiększony o odsetki. Kapitalizacja odsetek następuje co miesiąc. Na koniec program powinien jeszcze wyświetlić sumę zarobku z odsetek.
   

Wyjątki

Niekiedy podczas wykonania pojawiają się sytuacje wyjątkowe, takie jak np. próba dzielenia przez zero, brak dostępu do pliku, próba odczytania nieistniejących danych etc.

do
$$
begin
raise notice '%',10/0;
end $$;


Próba wykonania powyższego kodu skończy się wyjątkiem - próbowałem wyświetlić wynik dzielenia 10 przez 0.

Mamy tu do czynienia z pojawieniem się wyjątku. W takiej sytuacji blok kodu jest przerywany, a na konsoli pojawia się komunikat błędu. Gdyby po linijce w której wyjątek następuje były kolejne działania, nie zostałyby one wykonane.

Możemy zareagować na pojawienie się takiego wyjątku dodając sekcję obsługi wyjątków:

do
$$
begin
raise notice '%',10/0;
raise notice 'Coś na koniec...';
exception
when division_by_zero then raise notice 'ej, nie można dzielić przez 0!';
end $$;

Sprowadza się to do dodania słówka exception na końcu kodu i dodania obsługi wyjątku. Jeśli nastąpi wyjątek dzielenia przez zero, zostanie on na poziomie tej sekcji obsłużony. Możemy w tym miejscu umieścić jakiś kod który np. wyświetli użytkownikowi stosowny komunikat zamiast kończyć program z błędem. Jak widać na powyższym screenie, linia z wyświetleniem tekstu "Coś na koniec" nadal się nie wykonała, ale wykonanie przesło do sekcji obsługi wyjątków i zostało wyświetlone przewidziane przez nas ostrzeżenie.
Może zaistnieć potrzeba obsłużenia wyjątku ale bez kończenia programu. Przykładowo chcielibyśmy wyświetlić użytkownikowi ostrzeżenie o braku możliwości dzielenia przez zero, ale kontynuować wykonanie programu. W takiej sytuacji możemy posłużyć się blokami zagnieżdżonymi:


do
$$
begin
begin
raise notice '%',10/0;
exception
when division_by_zero then raise notice 'ej, nie można dzielić przez 0!';
end;
raise notice 'Coś na koniec...';

end $$;

Blok zagnieżdżony to po prostu blok wbudowany w inny blok. Jak widzisz, blok zagnieżdżony może posiadać własną sekcję obsługi wyjątków. W tym przypadku objąłem problematyczną linijkę blokiem zagnieżdżonym z własną sekcją obsługi wyjątków. Dzięki takiemu zabiegowi, wyjątek został obsłużony na poziomie bloku zagnieżdżonego. Sam blok zagnieżdżony jako "podjednostka" zakończył się poprawnie, dzięki czemu nasz nadrzędny blok kodu jest wykonywany dalej. Możemy to zaobserwować dzięki wyświetleniu się "coś na koniec..." w konsoli.
Nie musimy wskazywać żadnego konkretnego wyjątku. Możemy posłużyć się klauzulą "when others" aby obsłużyć każdy pojawiający się wyjątek:

do
$$
begin
raise notice '%',10/0;
exception
when others then raise notice 'KLOPS!';

end $$;

Możemy również połączyć jedno z drugim w taki sposób, że dzielenie przez zero będzie obsługiwane w dedykowany sposób, a każdy inny wyjątek w sposób ogólny:


do
$$
begin
raise notice '%',10/0;
exception
when division_by_zero then raise notice 'nie dziel przez zero!';
when others then raise notice 'KLOPS!';
end $$;

Gdybyś potrzebował dobrać się do komunikatu lub kodu błędu (na przykład na potrzeby logowania pojawiających się wyjątków do osobnej tabeli) , możesz wykorzystać SQLERRM i SQLSTATE:


do
$$
begin
raise notice '%', 10/0;
exception
when others then
raise notice 'SQLERRM: %',SQLERRM;
raise notice 'SQLSTATE: %',SQLSTATE;
end $$;