Procedury

składowane

Przegląd zagadnień

Czym są procedury składowane
Praca z procedurami składowanymi
Zalety i wady procedur składowanych
Podsumowanie
Laboratorium

Czym są procedury składowane

Procedura składowana (ang. stored procedure) jest

nazwanym zbiorem zapytań w języku SQL, który jest

przechowywany na serwerze (w SZBD) i jest kompilowany

przy pierwszym wykonaniu.
Procedury wnoszą do środowiska serwera baz danych

przetwarzanie warunkowe i możliwości programistyczne.
W SZBD wykonanie dowolnego fragmentu kodu języka SQL

wiąże się z pewnym ciągiem procesów - począwszy od

sprawdzenia składni aż do kompilacji i wykonania.

Czym są procedury składowane

Proces wykonania zapytania SQL
Wykonywanie procedur składowanych
Rekompilacja procedur składowanych

Czym są procedury składowane

Jak odbywa się wykonanie pojedynczego zapytania w języku Transact-SQL w MS SQL Server:

Kod musi zostać sprawdzony pod względem poprawności składni (kontrola poprawności

semantycznej - czyli czy kod nie odwołuje się do nieistniejących obiektów lub używa

nieistniejących poleceń oraz kontrola poprawności syntaktycznej - czy użyta składnia jest

poprawna).
Następnie kod jest rozdzielany na fragmenty (często nazywane znacznikami)

interpretowane przez ZSBD. Proces ten nazywamy parsowaniem (ang. parsing).
Następnie SZBD standaryzuje wyodrębnione części kodu, tzn. zapisuje je w jednoznacznej

postaci (usuwając niepotrzebne znaczniki).
Kolejnym etapem jest optymalizacja - każde zapytanie może posiadać wiele

przygotowanych tzw. planów wykonania (ang. execution plan). MS SQL Server posiada

wewnętrzny proces (Query Optimizer), który wybiera optymalny sposób dostępu do danych

- tzn. taki plan wykonania zapytania, w którym serwer będzie skanował (przeszukiwał)

najmniejszą ilość stron danych. Na optymalizację szczególny wpływ mają struktura

indeksów oraz sposób łączenia tabel.
Następuje kompilacja zapytania wg optymalnego planu wykonania i wykonanie

skompilowanego zapytania.
Wyniki działania zapytania są zwracane do klienta.

Wykonywanie procedur składowanych

Wykonywanie procedur składowanych odbywa się inaczej niż wykonywanie

pojedynczych zapytań SQL.

Schemat utworzenia i pierwszego wykonania procedury:

Programista bazy danych tworzy definicję procedury składowanej - tzn.

wykonuje polecenie CREATE PROCEDURE.
Kod procedury jest sprawdzany pod względem syntaktyki.
Nazwa procedury i jej kod (tzw. ciało) są zapisywane do odpowiednich

tabel systemowych bazy danych (sysobjects oraz syscomments).
Użytkownik wywołuje procedurę z odpowiednimi parametrami używając

polecenia EXEC.
Dalej następuje właściwe wykonanie procedury - optymalizacja planu

wykonania i kompilacja.
Skompilowany optymalny plan wykonania jest zapisywany w tzw. cache'u

procedur.

Wykonywanie procedur składowanych

W MS SQL Server przy kontroli poprawności kodu procedury

w trakcie jej tworzenia serwer nie sprawdza, czy istnieją

obiekty (tabele, widoki), do których procedura się odwołuje.
Sprawdzenie to następuje dopiero przy wykonaniu procedury

(w przypadku odwołania do nieistniejącego obiektu

procedura zgłosi błąd).

Wykonywanie procedur składowanych

Praca z procedurami składowanymi

Tworzenie procedur składowanych
Wywoływanie procedur składowanych
Parametry procedur składowanych
Kolejność parametrów

Tworzenie procedur składowanych

Do tworzenia procedur składowanych używamy polecenia

języka SQL

CREATE PROCEDURE (lub CREATE PROC).
W definicji procedury składowanej określamy: nazwę

procedury; nazwy, typy danych oraz kierunek działania

parametrów procedury; ciało procedury - czyli kod

wykonywany przez procedurę; opcjonalnie deklarujemy, czy

procedura ma być przy każdym wykonaniu rekompilowana.

Parametry procedur składowanych

Procedury składowane mogą przyjmować parametry

wywołania. Ilość i typ danych, które należy podać przy

wywołaniu procedury składowanej określamy w trakcie

tworzenia procedury (używając polecenia CREATE

PROCEDURE). W zależności od tego, czy parametry będą

potrzebne do wykonania procedury, czy też mają być one

przez procedurę zwrócone, wyróżniamy dwa rodzaje

parametrów: wejściowe (INPUT) oraz wyjściowe (OUTPUT).
Możliwe jest też zdefiniowanie w procedurze parametru

przejściowego (będącego jednocześnie wejściowym i

wyjściowym), czyli parametru, którego wartość podajemy

przy wywołaniu procedury, a procedura podczas działania

może zmienić wartość parametru i zwrócić nową wartość.

Parametry procedur składowanych –

Kolejność parametrów

Użytkownik ma dwie możliwości wywoływania procedury

składowanej z wieloma parametrami:

podać wartości parametrów w takiej kolejności, w jakiej

parametry zostały zdeklarowane w definicji procedury

składowanej,
przyporządkowywać wartości parametrom o konkretnych

nazwach (kiedy nadajemy jawnie wartości parametrom,

kolejność parametrów w wywołaniu jest dowolna).

Praca z procedurami składowanymi

Tworzenie procedur składowanych
Wywoływanie procedur składowanych
Parametry procedur składowanych
Kolejność parametrów

Zalety procedur składowanych

zwiększają wydajność bazy danych,
ograniczają ruch w sieci (przesyłane są tylko nazwy

procedur wartości parametrów).
zapewniają jedną logikę biznesową dla wszystkich

aplikacji klienckich,
przesłaniają szczegóły tabel w bazie danych

(przezroczystosc struktury dla zwykłego użytkownika

aplikacji),
umożliwiają modyfikację danych bez bezpośredniego

dostępu do tabel bazy danych,
dostarczają mechanizmów bezpieczeństwa (można

nadawać uprawnienia do wykonywania procedur

poszczególnym użytkownikom bazy danych).

Wady procedur składowanych

rekompilacja czasem jest następstwem zmniejszenia wydajności

procedury (czyli administrator baz danych musi wiedzieć, kiedy

przeprowadzić rekompilację),
w procedurach składowanych podobnie jak w perspektywach

występuje problem zerwanego łańcucha własności (patrz moduł 8),
w przypadku zagnieżdżania procedur składowanych należy

pamiętać o tym, że zmienia się kontekst wykonania (procedura

zagnieżdżana wykonuje się z uprawnieniami innej procedury),
wreszcie, aby tworzyć dobre (tzn. poprawnie działające) procedury

składowane niezbędne jest poznanie zaawansowanych

mechanizmów języka SZBD (np. T-SQL) takich jak: operowanie

zmiennymi, funkcje i procedury systemowe, obsługa błędów.

Podsumowanie

Czym są procedury składowane
Praca z procedurami składowanymi
Zalety i wady procedur składowanych

Laboratorium

Procedury systemowe

Tworzenie procedur składowanych