Artykuł
12/20/2011

Programowanie bazy danych – procedury i funkcje w bazie danych

Autor: Jacek Włodarski

Opublikowano: 2011-05-18

Wstęp
Transact-SQL
Funkcje użytkownika
Tworzenie funkcji
Usuwanie funkcji
Procedury
Procedury składowane
Tworzenie procedur składowanych
Usuwanie procedur składowanych
Wyzwalacze
Wyzwalacze a ograniczenia i procedury składowane
Tworzenie wyzwalaczy
Usuwanie wyzwalaczy

* *

1. Wstęp

Serwery bazy danych umożliwiają projektantom, programistom bądź administratorom tychże serwerów tworzenie rozszerzeń w postaci funkcji napisanych w którymś z języków programowania, np. c czy c#, a następnie załadowanie ich w momencie uruchamiania się serwera. A zatem oprócz tabel i widoków w bazach danych możemy tworzyć własne funkcje, procedury oraz specjalny typ procedur, które będą automatycznie wywoływane w momencie wstawiania, usuwania lub modyfikowania określonych danych – są to tzw. wyzwalacze. Funkcje rozszerzające napisane w taki sposób są wykonywane przez serwer, a nie przez aplikację kliencką, i są zapisywane wewnątrz samej bazy danych. Rozwiązanie takie daje możliwość zwiększenia funkcjonalności. Przykładem funkcji zaimplementowanej w ten sposób może być np. funkcja zamieniająca dowolny ciąg znaków na inny, licząca silnię czy dowolnie inne równanie. W takim zastosowaniu języki programowania – umożliwiając korzystanie ze zmiennych, wyrażeń warunkowych czy pętli – są nieocenionym narzędziem pracy każdego administratora baz danych. Z dalszej części tego artykułu dowiesz się, jak tworzyć i jak korzystać z tego typu obiektów w bazach danych.

2. Transact-SQL

Transact-SQL, czyli transakcyjny SQL, jest rozszerzeniem języka SQL umożliwiającym tworzenie konstrukcji takich jak pętle, instrukcje warunkowe czy zmienne. Ponadto właśnie T-SQL jest używany do tworzenia procedur, funkcji składowanych czy wyzwalaczy w bazie danych SQL Server. Więcej o tym języku dowiesz się w artykule zatytułowanym „Transact SQL”.

3. Funkcje użytkownika

Jedną z możliwości SQL Server jest możliwość tworzenia przez użytkowników własnych funkcji. Funkcjom, tak jak procedurom, można przekazać pewną liczbę parametrów, ale funkcja nie tylko wykonuje pewne operacje, ale także zwraca obliczony na podstawie przekazanych parametrów wynik.

4. Tworzenie funkcji

Wykonanie instrukcji CREATE FUNCTION spowoduje utworzenie funkcji użytkownika. Składnia wygląda tak jak poniżej:

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    { ,...n ]
    ]
  )
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
      Function_body
      RETURN scalar_expression
END
[ ; ]

schema_name to nazwa schematu, do którego należy funkcja zdefiniowana przez użytkownika.

function_name to nazwa funkcji zdefiniowanej przez użytkownika. Nazwa musi być unikatowa w ramach schematu.

Po słowie kluczowym „Return” umieszczamy typ zwracany przez funkcję. Między słowami kluczowymi „IS” i „BEGIN” umieszczamy deklaracje wszystkich zmiennych i kursorów lokalnych. Między słowami „BEGIN” i „END” umieszczamy kod SQL.

Przykład: funkcja oczekująca na parametr-datę, a następnie zwracająca odczytany z tej daty rok:

CREATE FUNCTION calendar.fn_data
(data DATE)
RETURNS CHAR(20)
RETURN YEAR(data);
Query OK, 0 rows affected (0.01 sec)
 
SELECT fn_data('2001-1-1');
+---------------------+
| fn_data('2001-1-1') |
+---------------------+
| 2001                |
+---------------------+

Podczas definiowania takiej funkcji trzeba zdefiniować nagłówek (nazwę, listę argumentów oraz typ zwracanej wartości) i ciało funkcji. Ostatnią instrukcją ciała funkcji powinna być instrukcja RETURN. Funkcja zwróci wartości wymienione po prawej stronie tej instrukcji.

Parametry funkcji i procedur

Na liście parametrów nie podaje się rozmiarów, a jedynie typ.

Typy parametrów:

IN – parametr przekazywany przez wartość:
o  jest przekazywany do programu przez referencję,
o  w programie zachowuje się jak stała,
o  musi być literałem, wyrażeniem, stałą lub zmienną.
OUT – parametr przekazywany z procedury do bloku wywołującego:
o  wartość zwracana do środowiska przez kopiowanie,
o  w programie zachowuje się jak niezainicjowana zmienna,
o  musi być zmienną.
IN OUT – parametr wejścia/wyjścia, domyślny:
o  wartość przekazywana do programu i zwracana do środowiska przez kopiowanie,
o  w programie zachowuje się jak zainicjowana zmienna,
o  musi być zmienną.

Aby funkcja mogła być wywoływana z poziomu polecenia SQL, musi posiadać odpowiedni poziom „czystości”:

Funkcja wywoływana z polecenia SELECT nie może modyfikować danych relacji bazy danych.
Funkcja wywoływana z poleceń INSERT, UPDATE, DELETE nie może odczytywać i modyfikować danych relacji, której dotyczy polecenie.
Funkcja wywoływana z poleceń SELECT, INSERT, UPDATE i DELETE nie może zawierać poleceń sterujących sesją i transakcjami (np. COMMIT, ALTER SESSION) oraz instrukcji DDL.

5.Usuwanie funkcji

Aby usunąć funkcję, należy użyć instrukcji DROP FUNCTION, co spowoduje usunięcie wybranych funkcji użytkownika, jak poniżej:

DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ]

Usunięcie funkcji z naszego przykładu:

DROP FUNCTION calendar.fn_data;
Query OK, 0 rows affected (0,00 sec)

6. Procedury

Procedura jest serią poleceń zapisaną w języku programowania baz danych, służącą do wykonywania obsługi na elementach bazy: tabelach, formularzach, raportach czy kwerendach.

7. Procedury składowane

Procedury składowane są zbiorami instrukcji języka SQL zapisanymi pod wspólną nazwą i wywoływanymi jak pojedyncza instrukcja. Umożliwiają one:

przekazywanie parametrów wywołania,
wykonywanie prawie wszystkich instrukcji języka SQL, w tym wywoływania innych procedur składowanych,
zwracanie dowolnej liczby wyników do programu, który wywołał procedurę,
zwracanie informacji o udanej lub niewykonanej procedurze.

Procedury składowane są powszechnie wykorzystywane do:

implementowania reguł logiki biznesowej,
zabezpieczenia obiektów bazy danych przed bezpośrednim dostępem użytkowników,
chronienia bazy danych przed atakami polegającymi na iniekcji kodu SQL,
poprawienia wydajności często wykonywanych instrukcji,
zminimalizowania obciążenia sieci (zamiast wysyłać całe instrukcje języka SQL użytkownik wywołuje jedynie procedurę, wysyłając jej nazwę i przekazując parametry jej wywołania).

8. Tworzenie procedur składowanych

Aby dodać nową procedurę składowaną, należy użyć instrukcji CREATE PROCEDURE, jak poniżej:

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [ schema_name. ] procedure_name [ ; number ]

( [ { @parameter [ type_schema_name ] [ OUT | OUTPUT ] [READONLY]
  ] [ ,...n]
[ WITH <procedure_option> [ ,...n ] ]
  [ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

schema_name – zmienna OUTPUT musi być zdefiniowana w trakcie tworzenia procedury.

procedure_name – jest to nazwa nowej procedury składowanej. Nazwa procedury musi być unikatowa w bazie danych i zgodna z regułami dotyczącymi identyfikatorów. Pełna nazwa procedury składowanej może składać się maksymalnie ze 116 znaków.

; number –to opcjonalna liczba całkowita używana do grupowania procedur o tej samej nazwie. Takie zgrupowane procedury można razem porzucać, używając pojedynczej instrukcji DROP PROCEDURE. Na przykład aplikacja orders może używać procedur o nazwach orderproc:1, orderproc:2 itd. Instrukcja DROP PROCEDUREorderproc porzuca całą grupę. Jeśli nazwa zawiera ograniczone identyfikatory, liczba nie powinna występować jako część ogranicznika, wtedy należy użyć odpowiednich ograniczników tylko wokół parametru procedure_name.

@ parameter – jest to parametr w procedurze. W instrukcji CREATE PROCEDURE można zadeklarować jeden lub więcej parametrów. Wartość każdego zadeklarowanego parametru musi być dostarczona przez użytkownika przy wywołaniu procedury, chyba że zdefiniowano wartość domyślną parametru lub ustawiono wartość równą innemu parametrowi. Procedura składowana może mieć maksymalnie 2100 parametrów. Jeśli procedura zawiera parametry zwracające tabele i brakuje parametru w komórce, to domyślnie przekazywana jest pusta tabela. Nazwę parametru należy zdefiniować, używając @ jako jej pierwszego znaku. Parametry są lokalne dla danej procedury, a zatem te same nazwy parametrów mogą być używane w różnych procedurach. Domyślnie parametry mogą zastępować tylko wyrażenia stałe. Nie mogą być używane zamiast nazw tabel, kolumn czy innych obiektów bazy danych. Podobnie jak funkcje, również przyjmują parametry typów: IN, OUT oraz IN OUT.

[type_schema_name.] data_type – jako parametr mogą być używane praktycznie wszystkie typy danych. Jeśli jednak chcemy zapewnić zgodność ze środowiskiem CLR, to nie możemy określić jako parametrów następujących typów danych: char, varchar, text, ntext, image, kursor, user-define table types oraz table.

VARYING –określa zestaw wyników obsługiwany jako parametr wyjściowy. Ten parametr jest konstruowany dynamicznie przez procedurę składowaną i jego zawartość może być różna, jednak ta właściwość jest stosowana tylko dla parametrów typu kursor.

OUTPUT (dane wyjściowe) – sama nazwa wskazuje, że jest to parametr wyjściowy. Użycie parametru OUTPUT zwraca wartości obiektowi wywołującemu procedurę. Typ tabeli zdefiniowanej przez użytkownika nie może być określony jako parametr OUTPUT procedury składowej.

Zmiana kontekstu zabezpieczeń

Będąc twórcą systemu często zastanawiamy się, w jaki sposób można uruchomić jakąś procedurę lub funkcję przez użytkownika, który standardowo nie ma do tego prawa. W tym celu została stworzona nowa klauzula EXECUTE AS, która pozwala na zmianę kontekstu użytkownika. Aby lepiej zrozumieć ideę działania tej klauzuli, prześledźmy następujący przykład. Użytkownik A ma uprawnienia do wykonania procedury ProceduraA. Procedura ta działa na obiektach ObiektA, Obiektb., ObiektC. Użytkownik A ma uprawnienia tylko do ObiektA. Natomiast ObiektB i ObiektC należą do użytkownika B. Jeśli użytkownik A wywoła procedurę, to zostanie ona przerwana w momencie odwołania się do obiektu, do którego A nie ma dostępu. Aby temu zapobiec, można zastosować klauzulę EXECUTE AS, która uruchomi procedurę w kontekście uprawnień użytkownika B.

Klauzula ta pozwala na stosowania jej przy procedurach składowanych oraz funkcjach użytkownika:

CREATE PROCEDURE ProceduraA
WITH EXECUTE AS ‘B’
AS

Przykład pokazuje stworzenie procedury, która każdemu umożliwi wywołanie procedury z uprawnieniami użytkownika B. Możliwe jest również użycie tej klauzuli w postaci EXECUTE AS SELF, co pozwala na wywołanie procedury w kontekście osoby, która ją wywołuje.

9. Usuwanie procedur składowanych

Aby usunąć procedurę składowaną, wystarczy użyć instrukcji DROP PROCEDURE, jak poniżej:

DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n]

10. Wyzwalacze

Wyzwalacze są specjalnym typem procedur składowanych, powiązanych z wybranymi tabelami i wywoływanych wykonaniem instrukcji języka SQL: INSERT, UPDATE albo DELETE.

Instrukcje wykonane w ramach ciała wyzwalacza traktowane są jako fragment transakcji jawnie lub niejawnie rozpoczętej przez użytkownika, który odwołał się do danych przechowywanych w powiązanej z wyzwalaczem tabeli. Wynika z tego, że wyzwalacz może zatwierdzić (wykonując instrukcję COMMIT TRANSACTION) lub wycofać (instrukcją ROLLBACK TRANSACTION) zmiany wprowadzone przez użytkownika.

Podstawowym zastosowaniem wyzwalaczy jest wymuszenie integralności danych, zwłaszcza ich zgodności z regułami logiki biznesowej. Wyzwalacze umożliwiają m.in.:

kaskadowe aktualizowanie danych w powiązanych tabelach,
sprawdzanie poprawności danych na podstawie wartości przechowywanych w dowolnych tabelach,
jednoczesne sprawdzanie danych zmodyfikowanych w dowolnej liczbie wierszy tabeli,
wywoływanie predefiniowanych lub zdefiniowanych przez użytkownika komunikatów błędu,
monitorowanie aktywności użytkowników,
modyfikacje danych w bazach niespełniających wymogów trzeciej postaci normalnej. W bazach tego typu prawdopodobnie przechowywane są informacje nadmiarowe (redundantne) i modyfikacja np. numeru telefonu w jednej tabeli może wiązać się z koniecznością zmiany tego numeru w innych tabelach.

11. Wyzwalacze a ograniczenia i procedury składowane

Obiekty obu tych typów w pewnym podstawowym zakresie mogą pełnić tę samą funkcję — wymuszenia integralności przechowywanych danych. Jednak zakres i typ przeprowadzanych przez nie akcji są różne:

Wyzwalacze, w przeciwieństwie do ograniczeń, wywoływane są w odpowiedzi na akcje użytkownika. Wynika z tego, że dopiero po wykonaniu instrukcji wyzwalany jest wyzwalacz, natomiast warunki ograniczeń sprawdzane są przed wykonaniem instrukcji języka SQL.
Konsekwencją poprzedniego punktu jest kolejność, w jakiej wywoływane są wyzwalacze i ograniczenia – najpierw sprawdzane są warunki zdefiniowane w ograniczeniach, a po ich pomyślnym sprawdzeniu wywoływany jest wyzwalacz.
Wyłącznie właściciel tabeli może utworzyć powiązany z nią wyzwalacz. Uprawnienie do tworzenia wyzwalaczy nie może zostać nikomu nadane czy przekazane.
Wyzwalacze nie mogą zostać powiązane z widokami oraz tabelami tymczasowymi.
Wyzwalacze mogą przetwarzać jednocześnie wiele wierszy tabeli. Możliwe jest również warunkowe przetwarzanie poszczególnych wierszy.
Tak jak możliwe jest zdefiniowanie dla tabeli dowolnej liczby zawężeń (wyjątkiem jest ograniczenie PRIMARY KEY), możliwe jest również utworzenie dowolnej liczby powiązanych z nią wyzwalaczy.
Natomiast tym, co odróżnia wyzwalacze od procedur składowanych (oprócz sposobu ich wywoływania) jest to, że wyzwalacze z reguły nie zwracają żadnych danych.

12. Tworzenie wyzwalaczy

Aby utworzyć wyzwalacz, należy wykonać instrukcję CREATE TRIGGER. W tym punkcie przyjrzymy się wykorzystaniu wyzwalaczy do monitorowania zmian nazw produktów. Wyzwalacz możemy dodać do istniejącej tabeli lub widoku, lub stworzyć wraz z tworzeniem tabeli lub widoku.

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name. ]trigger_name
ON { table | view}
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]
<method_specifier> ::=
    Assembly_name.class_name.method_name

13. Usuwanie wyzwalaczy

Aby usunąć wyzwalacz, należy wykonać instrukcję DROP TRIGGER, której struktura wygląda jak poniżej:

DROP TRIGGER [schema_name.]trigger_name [ ,...n ] [ ; ]

Podsumowanie

Z tego artykułu nauczyliśmy się, kiedy i jak właściwie stosować procedury i funkcje w bazie danych.