Język SQL – definiowanie obiektów bazy danych
4.3. Procedury i funkcje
Procedury są obiektami składającymi się ze zdań języka SQL, zapisywanymi
w bazie danych tak jak inne obiekty. Mogą być wykorzystywane przez wszystkie aplikacje i użytkowników, oczywiście pod warunkiem posiadania przez nich odpo-wiednich uprawnień. Procedury można podzielić na procedury pamiętane i procedury
zdarzeń (triggery). Większość środowisk bazodanowych, w tym również Sybase,
udo-stępnia możliwość tworzenia procedur zwracających wartości, podobnie jak funkcje w tradycyjnych językach programowania. Takie procedury w systemach baz danych
nazywane są również funkcjami. Ponieważ procedury i funkcje są obiektami bazy danych, a więc niezależnymi od aplikacji, sposób działania poprzez wykorzystywanie tych obiektów ma wiele zalet [9, 15, 23]:
• Standaryzacja – używanie tych samych procedur przez różnych użytkowników lub aplikacje powoduje wykonywanie pewnych działań w jednakowy, standardowy sposób.
• Efektywność – w sytuacji, gdy aplikacje korzystają z bazy danych zaimplemen-towanej na serwerze sieciowym, procedury i funkcje są wykonywane na komputerze serwera, a więc dla zrealizowania wymaganego dostępu do danych nie jest potrzebna komunikacja przez sieć. Taki sposób pracy jest szybszy, nieobciążony wpływem czynnika komunikacji sieciowej, w przeciwieństwie do wykonywania operacji po stronie klientów.
• Bezpieczeństwo – możliwość korzystania z procedur lub funkcji mają tylko użytkownicy, którym administrator bazy danych nadał odpowiednie uprawnienia, co pozwala na sterowanie dostępem do danych. Procedury zdarzeń, które zostaną do-kładnie omówione w dalszej części rozdziału, są dodatkowym mechanizmem pozwa-lającym utrzymywać bazę w stanie spójnym, wymuszając niejako jednakowy sposób wykonywania operacji aktualizacyjnych.
Tworzenie procedury odbywa się poprzez zdanie CREATE PROCEDURE
na-zwa_procedury, po którym definiowane są parametry wejściowe (IN), wyjściowe
(OUT), lub wejściowo/wyjściowe (INOUT). Tekst procedury rozpoczyna się od słowa BEGIN i kończy słowem END. Pomiędzy takimi „ramkami” umieszczany jest dowolny zestaw (ciąg) zdań języka SQL, gdzie poszczególne zdania muszą być oddzielane średnikami. Taki ciąg zdań traktowany jest jak zdanie złożone. Pomię-dzy słowami BEGIN i END oprócz zdań SQL dopuszczalne są zdania sterujące, umożliwiające organizację przepływów logicznych i podejmowanie decyzji oraz lokalne deklaracje zmiennych, kursorów, tabel tymczasowych i identyfikacji błędów (wyjątki, exception) definiowanych przez użytkownika. Deklaracji dokonuje się przez zdanie DECLARE.
Składnia zdania deklaracji zmiennych
DECLARE nazwa zmiennej typ danych
W środowisku Sybase, deklarując obsługę sytuacji błędnych, korzysta się z wyjąt-ków systemowych, których kody są przekazywane jako specjalne parametry OUT o nazwie SQLSTATE w następującym zdaniu:
DECLARE nazwa wyjątku EXCEPTION FOR SQLSTATE [VALUE] łańcuch znaków
00000 (no message) 02000 Row not found
01000 Warning 02W01 No data
Pełen wykaz kodów błędów znajduje się w dokumentacji technicznej środowiska Adapive Server Anywhere [23].
Wykaz zdań sterujących dopuszczalnych w procedurach lub funkcjach:
Zdania sterujące Składnia
Wykonanie warunkowe
CASE
Powtórzenia Wyjście z pętli Pętla kursora
IF warunek THEN lista zdań ELSEIF warunek THEN
Lista zdań
ELSE
Lista zdań
END IF
CASE wyrażenie WHEN wartość THEN
Lista zdań
WHILE warunek LOOP
Lista zdań
END LOOP LEAVE etykieta FOR lista zdań END FOR
Przykładowa procedura z zastosowaniem zdań sterujących; w zależności od para-metru wejściowego w wyniku działania procedury powstaje lista firm klientów z adre-sami lub lista firm klientów wraz z numerami telefonów:
CREATE PROCEDURE firmy (IN parametr CHAR(1)) BEGIN
IF parametr = ‘n’ THEN
SELECT nazwa_firmy, adres, miasto FROM KLIENCI
ELSE
SELECT nazwa_firmy, telefon
FROM KLIENCI
END IF END
Wywołanie procedury (zarówno w przypadku pracy interaktywnej, jak i w aplika-cjach i innych procedurach) odbywa się poprzez zdanie CALL nazwa_procedury (wartości parametrów). W powyższym przykładzie efektem wywołania CALL firmy (‘n’) będzie wykaz firm klientów z adresami, natomiast efektem wywołania procedury z jakimkolwiek innym parametrem będzie wykaz firm klientów z numerami telefonów.
Inny przykład procedury umożliwiającej wprowadzanie danych do tabeli
KWALI-FIKACJE:
CREATE PROCEDURE wpis_kwalifikacji
(IN id INTEGER,
IN nazwa CHAR(30),
IN organizator VARCHAR(50) IN kategoria INTEGER) BEGIN
INSERT INTO „DBA“.KWALIFIKACJE (id_kwal, nazwa_kwal, opis, Id_kat
VALUES (id, nazwa, organizator, kategoria) END
W podanym przykładzie przykładowe wywołanie procedury: CALL wpis_kwalifikacji (60, ‘kurs rachunkowości’, ‘TNOiK, podstawy’, 1) spowoduje zapisanie nowego wiersza do tabeli KWALIFIKACJE, z takimi wartościami jak parametry wejściowe). Utworzona procedura jest obiektem bazy danych dopóty, dopóki nie zostanie jawnie usunięta zda-niem DROP PROCEDURE nazwa_procedury.
Przedstawione przykłady ilustrowały procedury z parametrami wejściowymi. W przypadku procedur z parametrami wyjściowymi odbiór wyników zwracanych przez procedurę do środowiska wywołującego jest możliwy w dwojaki sposób:
• wartości pojedyncze są zwracane jako parametry OUT lub INOUT procedury, • tworzone są zbiory wyników.
Przykładowa procedura obliczająca średnią zarobków pracowników i zwracająca wynik obliczenia jako parametr wyjściowy OUT:
CREATE PROCEDURE Średnia_płaca (OUT średnia DECIMAL (20,2)) BEGIN
SELECT AVG (uposażenie) INTO średnia FROM PRACOWNICY END
Jeżeli środowiskiem wywołującym jest aplikacja ISQL (interaktywny tryb pracy), należy przed jej wywołaniem utworzyć zmienną, która będzie przechowywała wartość parametru OUT:
Następnie należy wywołać procedurę: CALL Średnia_płaca (średnia)
Obliczoną wartość średnich zarobków otrzymamy w oknie danych po wykonaniu polecenia:
SELECT średnia
Podany przykład ilustruje działanie procedury zwracającej pojedynczą wartość po-przez parametr OUT. Inną możliwością są procedury zwracające zbiory wyników, podobnie jak zapytania.
Przykładowa procedura podająca listy pracowników wraz z zarobkami, w rozbiciu na wydziały:
CREATE PROCEDURE lista_płac (IN nr_wydziału INTEGER)
RESULT („Identyfikator_pracownika” INTEGER, „pensja” DECIMAL (20,2)) BEGIN
SELECT Id_pracownika, uposażenie FROM PRACOWNICY WHERE PRACOWNICY.Id_wydz = nr_wydzialu
END
Po wywołaniu procedury: CALL lista_płac (3) z podaniem jako parametru wejścio-wego numeru wydziału, dla którego ma być sporządzone zestawienie, w aplikacji ISQL w oknie danych pojawi się wynik opisany komentarzami podanymi w klauzuli RESULT.
Przy omawianiu zdań sterujących dopuszczalnych w ciele procedur pojawiło się pojęcie kursora, które jest związane z przetwarzaniem wierszowym. Używając in-strukcji języka SQL, nie ma możliwości przeglądania kolejnych wierszy będących wynikiem zapytania. Zgodnie z zasadami algebry relacyjnej zapytania działają na tabelach i wynikiem ich działania są również tabele – możliwość taką stwarza mecha-nizm kursora. Kursor jest buforem (obszarem roboczym), do którego są zapisywane kolejno wiersze będące wynikiem zapytania, z którego mogą one być pobierane w celu np. modyfikacji danych. Obsługa kursorów wymaga podobnych działań jak obsługa plików w tradycyjnym programowaniu:
• Deklarowanie kursora z przyporządkowaniem instrukcji SELECT DECLARE nazwa_kursora
CURSOR FOR zdanie SELECT [ FOR {READ ONLY | UPDATE}]
• Otwarcie kursora za pomocą instrukcji OPEN (czyli uaktywnienie zdania SELECT przypisanego kursorowi)
OPEN nazwa_kursora
• Pobieranie kolejnych wierszy wyniku zapytania i przypisywanie ich do zmien-nych za pomocą instrukcji FETCH, w celu przetworzenia dazmien-nych
FETCH nazwa_kursora INTO nazwa_zmiennej
Standardowo instrukcja FETCH umieszczana jest w pętli, której działanie kończy się z chwilą sprowadzenia do kursora wszystkich wierszy wyniku.
• Zamknięcie kursora za pomocą instrukcji CLOSE nazwa_kursora Przykład użycia kursora w procedurze pamiętanej:
BEGIN
DECLARE kurs_prac CURSOR FOR SELECT nazwisko FROM PRACOWNICY; DECLARE nazwisko_pracownika CHAR(40);
OPEN kurs_prac; LOOP
FETCH NEXT kurs_prac INTO nazwisko_pracownika;
... END LOOP CLOSE kurs_prac END
Oczywiście kursory używane są nie tylko w procedurach pamiętanych, czy oma-wianych w dalszej części rozdziału procedurach zdarzeń. Deklaracje kursorów mogą wystąpić w dowolnym miejscu każdej aplikacji bazodanowej, niezależnie od tego, czy została skonstruowana za pomocą narzędzia typu RAD, czy instrukcje języka SQL zostały osadzone w języku programowania 3GL.
Funkcje są w zasadzie pewną klasą procedur, które zwracają pojedyncze wartości
do środowiska wywołującego. Tworzy się je za pomocą zdania CREATE FUNCTION. Syntaktyka zdania tworzącego funkcję różni się bardzo niewiele od zdania tworzącego procedurę. Zasadnicze różnice polegają na tym, że:
• W zdaniu tworzącym funkcję nie występują słowa IN, OUT, INOUT, ponieważ wszystkie parametry są parametrami wejściowymi.
• W przypadku, kiedy funkcja zwraca do środowiska wywołującego dane, wyma-gane jest użycie słowa kluczowego RETURNS, po którym precyzuje się typy zwraca-nych dazwraca-nych.
• W przypadku, kiedy funkcja zwraca pojedynczą wartość używa się słowa klu-czowego RETURN.
Przykładowa funkcja, która w efekcie swojego działania zwraca dane w postaci łańcucha znaków zawierającego nazwisko i imię rozdzielone spacją:
CREATE FUNCTION dane_osób (imię (CHAR 20), nazwisko (CHAR (30)) RETURNS (CHAR 51);
BEGIN
SET dane_os = imię || ‘ ‘ || nazwisko; RETURN dane_os;
END
Wywołanie funkcji w aplikacji ISQL w celu uzyskania listy imiennej pracowni-ków hurtowni Fitness2:
SELECT dane_osób (imię, nazwisko) AS dane_os FROM PRACOWNICY Efekt takiego wywołania:
Gdyby potrzebna była lista imienna klientów hurtowni, wówczas wywołanie funk-cji musiałoby się odnosić do tabeli z danymi klientów, czyli:
SELECT dane_osób (imię_k, nazwisko_k) AS dane_os FROM KLIENCI
Procedury zdarzeń (wyzwalacze, triggery) są to procedury definiujące akcje, które
system powinien podjąć po wystąpieniu określonych zdarzeń dotyczących tabel bazy danych. Konstruowane są w celu zdefiniowania dodatkowych więzów integralności lub audytowania zmian bazy danych. Zdarzenia, z którymi związane są procedury zdarzeń dotyczą wstawiania, aktualizacji i usuwania wierszy z tabel. Składnia instruk-cji umożliwiającej utworzenie triggerów (procedur zdarzeń):
CREATE TRIGGER nazwa triggera czas zadziałania zdarzenia [, zdarzenie, ...] [ORDER integer] ON nazwa tabeli
[REFERENCING [OLD AS stara nazwa] [NEW As nowa nazwa]] [FOR EACH ROW {ROW | STATEMENT}] [WHEN (warunek wyboru)]
[IF UPDATE (nazwa kolumny) THEN
[{AND | OR} UPDATE (nazwa kolumny)] …]
ciąg zdań (zdanie złożone)
[ELSEIF UPDATE (nazwa kolumny) THEN [{AND | OR} UPDATE (nazwa kolumny)]…
ciąg zdań (zdanie złożone)
END IF]]
Czas zadziałania procedury określa się poprzez podanie jednego z parametrów: BEFORE | AFTER.
Zdarzenia, z którymi związana ma być procedura określa się poprzez wybranie od-powiedniej akcji: DELETE | INSERT | UPDATE | UPDATE OF lista kolumn.
Procedury zdarzeń uruchamiane są automatycznie przez DBMS (odpalane) przed (opcja BEFORE) lub po (opcja AFTER) wykonaniu operacji aktualizacji, wstawiania lub kasowania, wykonywanych na pojedynczym wierszu (opcja FOR EACH ROW) lub po wykonaniu całej instrukcji (opcja FOR EACH STATEMENT). Klauzula WHEN jest stosowana tylko dla triggerów wierszowych. Dla rozróżnienia starych i nowych wartości w wierszu stosuje się oznaczenia REFERENCING OLD i REFE-RENCING NEW. W przypadku triggerów zdaniowych należy podać nazwy tworzo-nych tymczasowych tabel przechowujących stare i nowe wartości wierszy.
Przykładowy trigger umożliwiający sprawdzenie, czy przyjmowany pracownik jest pełnoletni:
CREATE TRIGGER kontrola_daty_ur BEFORE INSERT ON PRACOWNICY
REFERENCING NEW AS NOWI_PRACOWNICY FOR EACH ROW
BEGIN
DECLARE skontroluj_datę_urodzenia EXCEPTION FOR SQLSTATE '99999'; IF NOWI_PRACOWNICY.data_ur > ‘1985-01-01’ THEN
SIGNAL skontroluj_datę_urodzenia;
END IF;
END
Procedury zdarzeń są, podobnie jak procedury pamiętane i funkcje, obiektami bazy danych, których usunięcia dokonuje się za pomocą instrukcji DROP TRIGGER nazwa.
Podsumowując tę partię materiału warto zwrócić uwagę na możliwość umieszcza-nia tekstów procedur i funkcji w skryptach SQL, co znacznie ułatwia zmiany i mody-fikacje. W przypadku korzystania z aplikacji ISQL skrypty tworzy się poprzez polecenie zapamiętania tekstu wpisywanej procedury wybierane z menu (opcja SAVE
US), co powoduje utworzenie pliku typu Nazwa.sql, który w dowolnej chwili może
być otwierany (opcja OPEN) i wykonywany (klawisz EXECUTE).
4.4. Sterowanie dostępem do danych
Każdy System Zarządzania Bazą Danych jest wyposażony w mechanizmy umoż-liwiające kontrolę i sterowanie dostępem do danych. Mechanizmy zabezpieczeń bazu-ją na idei autoryzacji użytkowników, praw własności do obiektów oraz przydzielonych uprawnień. Autoryzacja użytkowników jest dokonywana na podstawie identyfikatorów oraz haseł przydzielanych każdemu użytkownikowi przez administra-tora bazy danych. Z każdym identyfikatorem związany jest przydzielony zakres uprawnień, sprawdzany każdorazowo przez DBMS z chwilą podejmowania działań przez użytkownika. Każdy z obiektów bazy danych ma swojego właściciela
(użyt-kownik, który obiekt utworzył) i początkowo jest to jedyna osoba, która może wyko-nywać operacje na obiekcie. W języku SQL istnieją dwie instrukcje umożliwiające przydzielanie i odwoływanie uprawnień do działania na obiektach bazy danych: GRANT (przydziel) i REVOKE (odwołaj).
Składnie zdania GRANT zaimplementowane w środowisku Sybase:
1. GRANT CONNECT TO identyfikator_użytkownika, ... IDENTIFIED BY hasło
Zdanie o takiej składni umożliwia utworzenie nowego użytkownika bazy danych lub zmianę hasła istniejącego użytkownika.
2. GRANT
DBA | GROUP
| MEMBERSHIP IN GROUP identyfikator_użytkownika, … | [RESOURCE]
…TO identyfikator_użytkownika
W powyższym zdaniu identyfikator DBA oznacza administratora bazy danych z pełnym zakresem uprawnień. Klauzula GROUP umożliwia tworzenie grup użyt-kowników z prawem posiadania członków, a klauzula MEMBERSHIP IN GROUP przypisanie poszczególnych użytkowników do grup. Poprzez słowo RESOURCE udzielane jest uprawnienie do zakładania tabel i perspektyw. Przykładowo, założenie grupy księgowi i przypisanie konkretnej osoby do tej grupy wymaga następujących sekwencji zdań:
• Utworzenie użytkownika księgowi
GRANT CONNECT TO księgowi IDENTIFIED BY bilans • Nadanie użytkownikowi statusu grupy
GRANT GROUP TO księgowi • Przypisanie członka do grupy
GRANT MEMBERSHIP IN GROUP księgowi TO J_Bielska
Użytkownik przypisany do grupy dziedziczy wszystkie uprawnienia przydzielone grupie.