Wykład 12

Transact-SQL – procedury składowane

1. Wstęp

Dotychczas omawiane przykłady były fragmentami kodu uruchamianymi przez użytkownika słowem GO. Nie były one jednak zapisywane trwale w bazie danych. Można zachować na stałe kod napisany w Transact-SQL pod postacią tzw. procedury składowanej (stored procedure). Procedurę taką będą mogli w przyszłości uruchamiać użytkownicy, o ile oczywiście posiadają do niej uprawnienia.

Procedury składowane są jednym z podstawowych i najczęściej stosowanych mechanizmów przy oprogramowaniu strony serwera aplikacji bazodanowej. Typową sytuacją w aplikacjach jest taka, w której większość lub czasem wszystkie operacje na danych są wykonywane poprzez procedury składowane.

Stosowanie procedur składowanych ma bardzo istotne zalety:

• Procedury są kompilowane, więc kod w nich zawarty wykona się szybciej, niż w przypadku wysłania do serwera wielu pojedynczych instrukcji SQL.
• Aplikacja oparta o procedury składowane może być lepiej zabezpieczona, gdyż nie nadajemy użytkownikom uprawnień do wykonywania instrukcji SELECT, INSERT, UPDATE, DELETE bezpośrednio do tabel, lecz nadajemy uprawnienia wyłącznie do wykonania odpowiednich procedur.
• Procedury składowane pozwalają wyraźnie oddzielić warstwę bazy danych od pozostałych warstw aplikacji (warstwy interfejsu, a czasami też innych, np. logiki biznesowej). Pozwala to zachować porządek w kodzie oraz zapewnia lepszą pielęgnowalność aplikacji. Kod implementujący pozostałe warstwy nie zawiera wyrażeń SQL, lecz jedynie wywołania odpowiednich procedur. Całość przetwarzania danych jest natomiast zaimplementowana w procedurach.

2. Składnia

Aby utworzyć procedurę piszemy:

CREATE PROCEDURE nazwa_procedury 
   lista parametrów 
AS BEGIN
   instrukcje Transact-SQL
END

Kod istniejącej procedury możemy podmienić w podobny sposób, zamieniając słowo CREATE na ALTER. Procedury usuwamy poleceniem DROP PROCEDURE.

Listę istniejących procedur możemy zaobserwować w narzędziu Management Studio rozwijając gałąź Programability, a następnie Stored procedures. Z tego poziomu możemy rozpocząć modyfikację istniejącej procedury lub ją usunąć. Istnieje tutaj też możliwość utworzenia nowej procedury, lecz graficzny interfejs niewiele nam w tym pomoże. Otworzy się jedynie nowa zakładka z nagłówkiem procedury.

Przykład:

Procedura wprowadzająca nowego pracownika z numerem empno większym o jeden od maksymalnego numeru istniejącego w tabeli:

CREATE PROCEDURE wstaw_emp 
   @ename VARCHAR(10),
   @sal DECIMAL(8,2) 
AS BEGIN 
   DECLARE @empno INT
   SELECT @empno = ISNULL(MAX(empno), 0) + 1
   FROM emp
   INSERT INTO emp (empno, ename, sal)
   VALUES (@empno, @ename, @sal)
END

Aby wywołać powyższą procedurę, napiszemy na przykład:

EXECUTE wstaw_emp 'Kowalski', 1000

Lub w skrócie:

EXEC wstaw_emp 'Kowalski', 1000

Istnieje też składnia rozszerzona, pozwalająca podać wartości parametrów razem z ich nazwami:

EXECUTE wstaw_emp @ename = 'Kowalski', @sal = 1000

Kolejny przykład będzie nieco bardziej skomplikowany. Procedura będzie służyła do wstawiania działów do tabeli „dept”. Przed wstawieniem sprawdzimy, czy dział o podanej nazwie lub lokalizacji już istnieje. Jeśli tak by się stało, nie dodamy nowego rekordu:

CREATE PROCEDURE wstaw_dzial
   @deptno INT,
   @dname VARCHAR(14),
   @loc VARCHAR(13)
AS
BEGIN
   DECLARE @ile INT
   SELECT @ile = COUNT(*) FROM dept WHERE dname = @dname OR loc = @loc
   IF @ile = 0
      INSERT INTO dept (deptno, dname, loc)
      VALUES (@deptno, @dname, @loc)
   ELSE
      PRINT 'Taki dzial juz istnieje'
END

Sprawdzenie czy rekord istnieje zostało zrealizowane poprzez deklarację zmiennej i przypisanie na nią wartości funkcji COUNT. To samo zadanie możemy rozwiązać nieco prościej:

CREATE PROCEDURE wstaw_dzial
   @deptno INT,
   @dname VARCHAR(14),
   @loc VARCHAR(13)
AS
BEGIN
   IF NOT EXISTS (SELECT 'X' FROM dept WHERE dname = @dname OR loc = @loc)
      INSERT INTO dept (deptno, dname, loc)
      VALUES (@deptno, @dname, @loc)
   ELSE
      PRINT 'Taki dzial juz istnieje'
END

3. Parametry

Jak widzieliśmy w powyższych przykładach, procedury zwykle posiadają parametry, dzięki którym osoba je wywołująca może przekazać wartości do wnętrza procedury. Nazwa parametru, podobnie jak zmiennej, musi zaczynać się od znaku @. Musi mieć też określony typ danych. Parametr może mieć przypisaną wartość domyślną czyli taką, która zostanie przyjęta, gdy w trakcie wywołania nie zostanie podana wartość. Możemy też zadeklarować parametr jako wyjściowy.

@nazwa_parametru TYP [=wartość_domyślna] [OUTPUT]

Poprzez parametr wyjściowy procedura może zwrócić wartość użytkownikowi.

W poniższym przykładzie procedura dla podanego empno modyfikuje zarobki o zadany procent (domyślnie 20) oraz zwraca przez parametr wyjściowy nową wartość.

CREATE PROCEDURE pensja 
   @Empno INT,
   @Nowe_zarobki DECIMAL(6,2) OUTPUT,
   @Procent INT = 20
AS 
BEGIN 
   SELECT @Nowe_zarobki = Sal + Sal * @Procent / 100 
   FROM Emp 
   WHERE Empno = @Empno
   UPDATE Emp SET Sal = @Nowe_zarobki 
   WHERE Empno = @Empno
END

Wywołanie procedury zwracającej dane przez parametr wyjściowy odbywa się w sposób analogiczny do zwykłej procedury. Jeśli jednak chcielibyśmy odebrać nową wartość zarobków, piszemy:

DECLARE @New_sal DECIMAL(6,2)
EXECUTE Pensja 1234, @New_sal OUTPUT 
PRINT @New_sal

Ten fragment kodu piszemy jedynie, gdy chcemy przetestować procedurę z OUTPUT lub chcemy, aby zwrócona wartość była użyta w dalszych obliczeniach. Pamiętajmy, że w praktyce procedury są uruchamiane zwykle przez aplikacje je wykorzystujące, a więc sterownik bazy danych narzędzia, w którym napisana była aplikacja, będzie obsługiwał pobranie wartości z parametru wyjściowego.

4. RETURN

Innym sposobem, w jaki procedura może zwracać wartość, jest instrukcja RETURN. Powoduje ona natychmiastowe wyjście z procedury. Może przekazywać wyłącznie pojedynczą wartość typu INT w przeciwieństwie do parametrów typu OUTPUT, których może być wiele i mogą być różnych typów.

CREATE PROCEDURE Ile_pracownikow 
AS 
BEGIN 
   DECLARE @ile INT
   SELECT @ile = COUNT(*) FROM Emp
   RETURN @ile 
END

Aby wywołać procedurę i pobrać wartość piszemy:

DECLARE @Ilu_prac Int 
EXECUTE @Ilu_prac = Ile_pracownikow 
PRINT @Ilu_prac

5. Resultset

Istnieje jeszcze jeden sposób, w jaki procedura może zwracać dane na zewnątrz. Jest nim tzw. resultset, czyli zestaw rekordów będących wynikiem działania instrukcji SELECT. Należy pamiętać, że przekazywany jest wynik ostatniej instrukcji SELECT w procedurze.

Poniższa procedura zwróci w postaci tabeli wszystkich pracowników z zadanego parametrem działu.

CREATE PROCEDURE pracownicy_dzialu 
   @dzial INT 
AS BEGIN 
   SELECT * FROM emp WHERE deptno = @dzial
END

Aby wywołać procedurę, piszemy:

EXECUTE pracownicy_dzialu 20

Resultset jest jak widzimy, bardzo łatwą w użyciu i elastyczną metodą zwracania danych przez procedury składowane. Z tego względu jest metodą najczęściej wykorzystywaną.

6. Porównanie metod zwracania danych przez procedury

Z punktów 3, 4 i 5 dowiedzieliśmy się, że istnieją trzy sposoby, w jakie procedura może zwracać wartości. Wybór odpowiedniej metody będzie zależał od tego, jakie dane mają być zwracane oraz tego, które metody są dostępne w sterowniku bazy danych języka wykorzystywanego do tworzenia aplikacji, która ma z procedury korzystać.

• OUTPUT użyjemy wtedy, gdy procedura ma zwrócić kilka pojedynczych wartości określonych typów.
• RETURN użyjemy, gdy procedura ma zwrócić jedną wartość typu INT.
• Resultset użyjemy, gdy procedura ma zwrócić wynik w postaci tabeli.

Poniżej przedstawione zostało rozwiązanie tego samego zadania w trzech wersjach. Każda z procedur zwraca liczbę rekordów w tabeli „emp”.

Wersja z OUTPUT:

CREATE PROCEDURE IleOutput
   @Ile INT OUTPUT
AS BEGIN
   SELECT @Ile = COUNT(*) FROM emp
END

Wywołanie:

DECLARE @a INT
EXEC IleOutput @a OUTPUT
PRINT @a

Wersja z RETURN:

CREATE PROCEDURE IleReturn
AS BEGIN
   DECLARE @Ile INT
   SELECT @Ile = COUNT(*) FROM emp
   RETURN @Ile
END

Wywołanie:

DECLARE @a INT
EXEC @a = IleReturn
PRINT @a

Wersja zwracająca przez resultset:

CREATE PROCEDURE IleResultset
AS BEGIN
   SELECT COUNT(*) FROM emp
END

Wywołanie:

EXEC IleResultset

7. Procedury składowane w PL/SQL

Tworzenie procedury składowanej w języku PL/SQL wygląda następująco:

CREATE [OR REPLACE] PROCEDURE nazwa (par [IN/OUT/INOUT] TYP, ...) AS
   deklaracje
BEGIN
   kod procedury
END;

Podstawowe różnice w stosunku do Transact-SQL:

• Procedurę tworzymy poleceniem CREATE lecz możemy napisać również CREATE OR REPLACE dzięki czemu procedura zostanie utworzona, lub jeśli już istnieje, zamieniona.
• Przy definicji parametru możemy dopisać IN, OUT lub INOUT określając, czy jest to parametr wejściowy, wyjściowy lub dwukierunkowy. Jeśli nie określimy, domyślnie będzie to parametr wejściowy.
• Typy danych podajemy bez rozmiaru, a więc na przykład VARCHAR, a nie VARCHAR(10).
• Podobnie jak w bloku anonimowym, deklaracje muszą znaleźć się w odpowiedniej sekcji kodu, a nie w dowolnym miejscu, jak w przypadku Transact-SQL.
• Nie ma możliwości zwracania danych przez resultset.

Oto rozwiązanie przykładu z punktu 2, dotyczącego procedury wstawiającej nowy dział:

CREATE OR REPLACE PROCEDURE wstaw (nr NUMBER, nazwa VARCHAR2, lokalizacja VARCHAR2) AS
   ile INTEGER;
BEGIN
   SELECT COUNT(*) INTO ile FROM dept WHERE dname = nazwa or loc = lokalizacja;
   IF ile = 1 THEN
      raise_application_error(-20500, 'Dzial juz istnieje');
   ELSE
      INSERT INTO dept (deptno, dname, loc)
      VALUES (nr, nazwa, lokalizacja);
   END IF;
END;