6Operatory

background image

Operatory, funkcje

i PL/pgSQL

Tomasz Borzyszkowski

background image

Wstęp

2

Wielokrotnie już korzystaliśmy z operatorów w klauzulach

WHERE

,

czy

HAVING

instrukcji

SELECT

. Np. aby ograniczyć wybór wierszy z

tabeli towarów, do wierszy, które spełniają warunek: cena zakupu

towaru jest większa niż 4, można zastosować operator

>

w

następujący sposób:

SELECT * FROM item WHERE

cost_price > 4

;

Operatory można również składać ze sobą uzyskując bardziej

złożone warunki. Np. towary, których cena sprzedaży kończy się na

99/100, można uzyskać następująco:

SELECT * FROM item WHERE

(sell_price*100)%100 = 99

;

PostgreSQL pozwala także wykonywać skomplikowane operacje na

napisach. Przykładem może być zapytanie:

SELECT * FROM item WHERE

description ~* '^[PR].*E$

;

Wybierające towary, których opisy rozpoczynają się na

P

,

p

,

R

lub

r

i kończą się na

E

lub

e

.

background image

3

Priorytety i wartościowania

Operatory posiadają priorytety, które są na stałe zakodowane w

analizatorze składni. Priorytety te decydują o kolejności, w jakiej

są wykonywane działania w złożonych wyrażeniach. Jak w innych

językach programowania, kolejność ta może być zmieniana za

pomocą nawiasów. Np.:

SELECT

1+2*4

;

oraz

SELECT

(1+2)*4

;

Operatory w PostgreSQL charakteryzują się kierunkiem nadawania

wartości. Może on być prawy lub lewy. Określa on kolejność

nadawania wartości operatorom o tym samym priorytecie.

Operatory arytmetyczne, jak dodawanie lub odejmowanie, są

wartościowane od lewej do prawej, np.: 1+2-3 jest równoważne

(1+2)-3. Inne operatory, jak np. operator równości, są

wartościowane od prawej do lewej. Wartość wyrażenia x=y=z jest

równa wartości wyrażenia x=(y=z).

Opis najpopularniejszych operatorów wraz z przykładami ich użycia

można znaleźć w pliku:

operatory.sql

Zobacz także:

dziwne.sql

background image

4

PL/pgSQL

przegląd

Język PL/pgSQL jest ładowalnym językiem proceduralnym, który:

Umożliwia tworzenie funkcji i procedur wyzwalanych (triggerów)

Rozszerza SQL o struktury sterujące, podobne jak w językach

proceduralnych

PL/pgSQL daje możliwość definiowania własnych funkcji, które

można wykorzystywać wewnątrz bazy PostgreSQL. Instrukcja

CREATE FUNCTION

służy do definiowania nowych funkcji.

Postać ogólna:

CREATE FUNCTION nazwa (typ_arg1[,...])

RETURNS typ_wyniku
AS definicja_funkcji
LANGUAGE 'nazwa jezyka';

Przykład:

CREATE FUNCTION add_one (int4)
RETURNS int4 AS '

BEGIN RETURN $1 + 1 END

' LANGUAGE 'plpgsql';

Zobacz:

add_one.sql

background image

5

PL/pgSQL

instalacja

Po utworzeniu funkcji, jej definicja jest zapisywana w bazie danych.

Kiedy funkcja jest wywoływana po raz pierwszy, definicja funkcji

jest kompilowana przez program obsługi do postaci wykonywalnej, a

następnie wykonana. Oznacza to, że możemy nie wiedzieć o błędzie

funkcji do czasu, kiedy spróbujemy jej użyć.

Każda baza danych może posiadać zdefiniowane dla niej języki

programowania, niezależnie od pozostałych baz (bezpieczeństwo).

Domyślnie bazy danych PostgreSQL nie obsługują żadnych języków

proceduralnych. Aby korzystać z języka PL/pgSQL należy

samodzielnie zainstalować program jego obsługi, np. tak:

createlang plpgsql template1;

Administrator bazy danych może dodać języki do bazy danych

template1

. W takim przypadku, wszystkie nowe bazy danych będą

posiadały domyślną obsługę tych języków.

background image

6

PL/pgSQL

argumenty i deklaracje

PL/pgSQL to język blokowo-strukturalny, podobny do języka Pascal

lub C, z deklaracjami zmiennych i zakresami bloków. Każdy blok

składa się z opcjonalnej etykiety, deklaracji zmiennych i zamykają

go instrukcje otoczone słowami kluczowymi

BEGIN

i

END

. Składnia:

[etykieta]
[DECLARE deklaracje
]
BEGIN instrukcje
END;

Wielkość liter w tym języku nie ma znaczenia. Wszystkie słowa

kuczowe i nazwy zmiennych można zapisać z zastosowaniem liter

małych lub wielkich.

Funkcje PL/pgSQL mogą nie mieć argumentów lub mieć ich kilka.

Typy parametrów podaje się w nawiasach po nazwie funkcji. Każda

funkcja przechowywana w bazie musi zwracać wynik. Odwołania do

parametrów wewnątrz treści funkcji mają postać

$1

,

$2

,

...

, w

kolejności ich definiowania. W części deklaracji można parametrom

nadawać nazwy, np.:

nazwa ALIAS FOR $5;

Zobacz:

deklar.sql

background image

7

PL/pgSQL

zmienne

Prosta deklaracja zmiennej jest następująca:

nazwa [CONSTANT] typ [NOT NULL] [:= wartosc];

Występowanie

CONSTANT

oznacza, że wartości zmiennej nie wolno

zmieniać. Musi ona posiadać wartość początkową.

NOT NULL

powoduje zgłoszenie błędu, gdy zmiennej przypiszemy

NULL

.

PostgreSQL pozwala deklarować tzw. zmienne złożone,

odpowiadające całym wierszom w określonej tabeli. Składnia:

nazwa tabela%ROWTYPE;

ROWTYPE

jest słowem kluczowym,

tabela

jest nazwą tabeli.

Wynikiem tej deklaracji jest zmienna zawierająca pola, po jednym

dla każdej kolumny tabeli, na podstawie której powstała. Aby

skorzystać z pól, należy zastosować składnię:

zmienna.pole

.

Innym rodzajem typu złożonego jest

RECORD

. Jest on podobny do

ROWTYPE

ale nie opiera się on na konkretnej tabeli. Typ ten jest

wypełniany wartościami w czasie wykonania. Przydatny w

definicjach procedur wyzwalanych (więcej o tym, później).

Zobacz:

zmienne.sql

background image

8

PL/pgSQL

instrukcje przypisania

Podstawowa instrukcja przypisania w PL/pgSQLu ma następującą

postać:

referencja := wyrazenie;

referencja

jest nazwą zmiennej lub pola typu złożonego, np.

ROWTYPE

lub

RECORD

.

wyrazenie

jest zbudowane z dostępnych w

PosgreSQLu operacji, stałych, funkcji, zmiennych, konwersji typów.

W PL/pgSQLu istnieje możliwość przypisywania wartości całym

wierszom danych wartości. Służy do tego instrukcja:

SELECT wyrazenia INTO cel [FROM ... ];

W przypadku różnic typów, tam, gdzie to możliwe, PostgreSQL

zastosuje odpowiednie operacje konwersji. Wszystkie operacje

przypisania, nawet

:=

, są wykonywane przez serwer jako powyższa

instrukcja

SELECT

. Przez wykorzystanie klauzuli

FROM, ...

,

możemy zmiennym przypisać wartości obliczone na podstawie

zawartości bazy. Tylko pierwszy wiersz oddawany przez

SELECT

jest przypisywany zmiennej, reszta jest pomijana. Jeżeli

SELECT

nie zwraca żadnych wierszy, to zmienna

FOUND = false

.

Zobacz:

przypisania.sql

background image

9

PL/pgSQL

wyjątki i komunikaty

Funkcja w PL/pgSQLu, prócz instrukcji

RETURN

, może także

zakończyć się w przypadku powstania warunków, które

uniemożliwiają kontynuowanie jej działania. Zamiast wartości

funkcja zwraca wówczas wyjątek. Wyjątek spowoduje wykonanie

zapisu w dzienniku zdarzeń i może spowodować, że funkcja

zakończy się natychmiast:

RAISE poziom 'format' [zmienna, ...];

PostgreSQL definiuje następujące trzy poziomy ważności wyjątków:

DEBUG

zapisuje komunikat w dzienniku (zwykle wyłączone)

NOTICE

zapisuje komunikat w dzienniku i wysyła do aplikacji

EXCEPTION

zapisuje komunikat w dzienniku i kończy funkcję

Poziom

DEBUG

stosowany do uzyskiwania dodatkowych informacji

podczas normalnego działania funkcji.

NOTICE

umożliwia

generowanie ostrzeżeń dla błędów, które nie są błędami fatalnymi.

EXCEPTION

stosuje się dla błędów fatalnych. Więcej w

dokumentacji.

Zobacz:

wyjatki.sql

background image

10

PL/pgSQL

instrukcje warunkowe

W PL/pgSQLu istnieje kilka rodzajów instrukcji warunkowych. Są to

konstrukcje, które wykonują jeden lub kilka zbiorów instrukcji z w

zależności od wyniku testu. Najpopularniejsza jest

IF_THEN_ELSE

,

taka jak na językach programowania:

IF warunek THEN instrukcje
[ELSE instrukcje] END IF;

Kolejne to właściwie wyrażenia warunkowe, a nie instrukcje:

NULLIF(wejscie, wartosc);

Wynikiem jest

NULL

, jeżeli

wejscie=wartosc

,

wejscie

w

przeciwnym przypadku.
Następna, to dobrze znana instrukcja

CASE

:

CASE WHEN warunek THEN wyrazenie

[WHEN warunek THEN wyrazenie]

ELSE wyrazenie
END;

Zobacz:

warunki.sql

background image

11

PL/pgSQL

pętle

PL/pgSQL posiada bogaty zestaw pętli iteracyjnych. Najprostszą jest

pętla nieskończona, z której można wyjść tylko za pomocą instrukcji

EXIT

:

[<<etykieta>>]
LOOP instrukcje
END LOOP;

<<infinite>>
LOOP n:=n+1;

EXIT infinite WHEN n>=10;

END LOOP;

W powyższym przykładzie instrukcja

EXIT

zakończy pętlę o

etykiecie

infinite

, gdy

n>=10

. Program przejdzie do wykonania

następnej instrukcji po pętli.

EXIT

może występować tylko

wewnątrz pętli.

Prócz powyższej, PL/pgSQL udostępnia również pętle

WHILE

i

FOR

o

sładni:

[<<etykieta>>]
WHILE wyra?enie
LOOP

instrukcje

END LOOP;

[<<etykieta>>]
FOR nazwa
IN [REVERSE] od..do
LOOP

instrukcje

END LOOP;

Zobacz:

petle.sql

przyklad.sql

background image

12

PL/pgSQL

zapytania dynamiczne

Dotychczasowe funkcje używały tylko operacji będących napisami

stałymi. Czasami zachodzi potrzeba zdefiniowania funkcji, której

sposób wykonania, tj. odwołania do wartości, kolumn, czy funkcji

będą wyliczane dynamicznie za każdym razem, gdy funkcja jest

wywoływana. Służy do tego instrukcja:

EXECUTE ciag_znakow;

Ciąg znaków, będący wykonywanym zapytaniem, można tworzyć

dynamicznie wewnątrz funkcji, za pomocą operatorów operatorów

manipulowania ciągami znaków. Należy zwrócić szczególną uwagę

na odpowiednie użycie apostrofów dla nazw i literałów wewnątrz

ciągu znaków.

Wszystkie nazwy tabel i kolumn powinny być przetwarzane za

pomocą funkcji

quote_ident

, natomiast wartości należy

przetwarzać za pomocą funkcji

quote_literal

.

Zobacz:

dynamiczne.sql

background image

13

Funkcje SQL

Do tworzenia funkcji można używać także języka SQL. W tym celu

trzeba określić język procedury jako

'SQL'

i korzystać z instrukcji

języka SQL zamiast z instrukcji języka PL/pgSQL.
Instrukcje SQL przyjmują parametry, do kórych można się

odwoływać używając znaków

$1

,

$2

, ... . Nie są dostępne instrukcje

sterujące, dostępne w PL/pgSQLu. Można jedynie korzystać z

instrukcji SQL. Oznacza to, że o ile PL/pgSQL oferuje deklaracje

zmiennych, wartościowania warunkowe i pętle, to SQL oferuje

jedynie zastępowanie argumentów wywołania funkcji wartościami

wywołania. Wartością zwracaną przez funkcję SQL są dane zwracane

przez ostatnio wykonywaną instrukcję SQL, zwykle jest to

SELECT

.

Zaletą funkcji SQL jest to, że nie musimy ładować do bazy danych

obsługi języka PL/pgSQL. Dodatkowo funkcje SQL umożliwiają

zwracanie więcej niż jednego wiersza danych, jeżeli zadeklarujemy

typ zwracanej wartości jako

setof tabela

.

Zobacz:

fun_sql.sql

background image

14

Procedury wyzwalane

Dotychczas poznaliśmy dwa mechanizmy zapewniające sprawdzanie

i zachowanie logiki bazy danych. Są nimi klucze obce oraz warunki

logiczne nałożone na tabelę w trakcie jej tworzenia. Oba te warunki

mają charakter statyczny i nie pozwalają na wiązanie konkretnych

akcji ze zdarzeniami zachodzącymi w bazie, a nie z jej stanem.
Takie wiązanie akcji ze zdarzeniami jest możliwe dzięki

procedurom wyzwalanym (ang. triggers). Za pomocą procedury

wyzwalanej, w PostgreSQLu, można spowodować, że wykona się

procedura zapamiętana w bazie danych, jeżeli dla określonej tabeli

będą podjęte takie działania jak

INSERT

,

UPDATE

oraz

DELETE

.

Składnia polecenia tworzącego procedurę wyzwalaną:

CREATE TRIGGER nazwa {BEFORE | AFTER}
{zdarzenie
[OR ...]}
ON tabela
FOR EACH {ROW | STATEMENT}
EXECUTE PROCEDURE funkcja
(argumenty);

background image

15

Procedury wyzwalane

cd

Wyzwalacz zadziała, gdy zajdzie określone zdarzenie (

INSERT

,

DELETE

lub

UPDATE

). Możemy zażądać by zadziałał on po (

AFTER

)

zajściu zdarzenia. Wówczas wywołana procedura będzie miała

dostęp do danych pierwotnych (

UPDATE

i

DELETE

) oraz do nowych

danych (

UPDATE

i

INSERT

). Możemy również zażądać by zadziałał

przed (

BEFORE

) zajściem zdarzenia. W takim przypadku możemy

zabezpieczyć przed aktualizacją lub zmienić dane, które mają być

wprowadzane lub aktualizowane.

Dla zdarzeń dotyczących wielu wierszy, spowodowanych pojedynczą

instrukcją, możemy wybrać: czy wyzwalacz ma działać dla każdego

aktualizowanego wiersza (

ROW

), czy raczej raz dla całej operacji

aktualizacji (

STATEMENT

).

Kiedy spełnione są określone warunki, wyzwalacz wykonuje specjalny

rodzaj procedury przechowywanej w bazie danych, wzawnej

procedurą wyzwalaną. Są one podobne do zwykłych funkcji

przechowywanych w bazie, lecz podlegają pewnym ograniczeniom.

background image

16

Procedury wyzwalane

ograniczenia

Procedura wyzwalana jest składowana przez PostgreSQL jako

funkcja bez parametrów oraz o specjalnym typie wyniku

OPAQUE

.

Typ ten stosuje się dla funkcji zwracających wartości, których

PostgreSQL nie może bezpośrednio przetwarzać.
Procedura wywołana przez procedurę wyzwalaną, w reakcji na

zmiany w określonej tabeli, musi zwracać

NULL

lub wiersz

odpowiadający strukturze zmienianej tabeli.
Dla wyzwalaczy typu

AFTER

, które wykonuje się po operacji

UPDATE

, zaleca się, aby procedura wyzwalana zwracała wartość

NULL

.

Dla wyzwalaczy typ

BEFORE

, zwracany wynik wykorzystuje się do

sterowania aktualizacją, która ma być wykonana. Jeżeli procedura

wyzwalana zwraca

NULL

, operacja

UPDATE

nie jest wykonywana.

Jeżeli zwracany jest wiersz danych, jest on wykorzystany jako

źródło aktualizacji, dając procedurze okazję do zmiany danych

przed zatwierdzeniem ich w bazie danych.

Zobacz:

trigger1.sql


Wyszukiwarka

Podobne podstrony:
wyklady4 6Operatory

więcej podobnych podstron