Ćwiczenia > Procedury składowane w MS SQL Server 2000 > Tworzenie własnych procedur składowanych

8.4 Tworzenie własnych procedur składowanych



Użytkownik ma możliwość stworzenia w swoich bazach danych własnych procedur składowanych. Procedury te nazywamy procedurami lokalnymi lub procedurami użytkownika. Informacje o takiej procedurze są przechowywane w bazie danych, w której procedura została stworzona.

W tej lekcji dowiesz się, w jaki sposób możesz tworzyć własne procedury i jak należy je wywoływać.

Krok 1 - Utwórz własną procedurę składowaną

1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_8_4_1.sql.
5. Zaznacz i uruchom (klawisz F5) fragment kodu oznaczony w komentarzu jako (1).


-- (1) stworzmy procedure skladowana
-- ktora zwroci liste osob majacych wiecej lat
-- niz podano w parametrze @granica_dolna
-- i mniej lat niz w parametrze @granica_gorna
-- poza lista zwrocona zostanie ilosc tych osob
USE Northwind
GO

CREATE PROCEDURE pracownicy_zakres_wieku
	@granica_dolna int = 20,
	@granica_gorna int = 50,
	@znalezionych int OUTPUT
AS

DECLARE @komunikat varchar(100)

IF @granica_dolna < @granica_gorna
BEGIN
	SELECT LastName, FirstName, BirthDate
	FROM Employees
	WHERE DateDiff(year,BirthDate,GetDate()) >= @granica_dolna
	AND DateDiff(year,BirthDate,GetDate()) <= @granica_gorna

	SET @znalezionych = @@ROWCOUNT

	IF @znalezionych = 0
	BEGIN
		SET @komunikat = 'Nie znaleziono pracownikow.'
		PRINT @komunikat
	END 
	ELSE
	BEGIN
		SET @komunikat = 'Znaleziono pewna liczbe pracownikow.'
		PRINT  @komunikat
	END
END
ELSE
BEGIN
	SET @komunikat = 'Blad! Podano niewlasciwe granice wieku.'
	PRINT @komunikat
END

RETURN 
GO


Wynikiem wykonania powyższego kodu jest utworzenie w bazie danych Northwind procedury składowanej o nazwie pracownicy_zakres_wieku, której zadaniem jest wyświetlenie listy i podanie ilości pracowników, których wiek określony w latach zawiera się między dwiema liczbami.

Działanie procedury jest następujące:
- procedura pobiera dwa parametry typu liczba całkowita - granica_dolna (domyślna wartość - 20) i granica_gorna (domyślna wartość - 50),
- procedura ma także parametr wyjściowy znalezionych, dzięki któremu zwraca ilość znalezionych pracowników, których wiek zawiera się w wymaganym zakresie,
- jeśli górna granica wieku jest mniejsza od dolnej, procedura zwraca informację o błędzie,
- jeśli błąd nie wystąpi, to zostanie wykonane polecenie SELECT korzystające z parametrów wejściowych procedury (funkcja DateDiff służy do wyliczenia wieku pracownika w latach),
- do parametru znalezionych zapisywana jest liczba pracowników znalezionych przez polecenie SELECT,
- jeśli polecenie SELECT nie znajdzie żadnego pracownika, zwracana jest stosowna informacja.

Do tworzenia procedur składowanych służy polecenie CREATE PROCEDURE (lub wersja skrócona CREATE PROC), które nie może występować w jednym wsadzie z innymi poleceniami języka T-SQL.

Procedura może mieć maksymalnie 1024 parametry (zapewne nigdy nie wykorzystasz tej możliwości). Każdy parametr powinien, o ile to możliwe, posiadać wartość domyślną (zabezpiecz się na wypadek, gdyby w wywołaniu został pominięty jakiś parametr).

Krok 2 - Wykonaj procedurę składowaną z użyciem parametrów

1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (2).


-- (2) uzyjmy procedury skladowanej
DECLARE @wiek_od int, @wiek_do int
DECLARE @ilu_pracownikow int
SET @wiek_od = 50
SET @wiek_do = 70

EXEC pracownicy_zakres_wieku 
@granica_dolna = @wiek_od,
@granica_gorna = @wiek_do,
@znalezionych = @ilu_pracownikow OUTPUT

-- alternatywne wywolanie:
-- EXEC pracownicy_zakres_wieku @wiek_od, @wiek_do, @ilu_pracownikow

PRINT CAST(@ilu_pracownikow AS varchar(3))
GO


Wynikiem wykonania powyższego kodu jest wywołanie procedury składowanej pracownicy_zakres_wieku z parametrami wejściowymi o wartościach odpowiadających wartościom zmiennych wiek_od i wiek_do. Liczba znalezionych pracowników jest zapisywana pod zmienną ilu_pracownikow. W komentarzu pokazujemy też alternatywne wywołanie procedury (nie polecamy tej metody, ponieważ musisz znać kolejność parametrów w definicji procedury). Ostatnia linia wsadu drukuje w zakładce Messages ilość znalezionych pracowników (funkcja CAST jest jedną z dwóch używanych przez SQL Server funkcji rzutujących - drugą jest Convert).

Krok 3 - Zobacz właściwości procedury składowanej

1. Uruchom program Enterprise Manager.
2. Rozwijaj drzewo po lewej stronie okna programu i wejdź do folderu Databases.
3. W folderze bazy Northwind wybierz pozycję Stored Procredures.



Rys. 8.4.1 Enterprise Manager - lista procedur składowanych w bazie danych


4. Po prawej stronie kliknij dwukrotnie na nazwie nowo utworzonej procedury. Zobaczysz okno Stored Procedure Properties, które zawiera kod definicji procedury oraz przycisk Permissions... odsyłający do okna zarządzania uprawnieniami do wykonywania procedury.



Rys. 8.4.2 Enterprise Manager - okno właściwości procedury składowanej


Procedury składowane możesz też tworzyć przy użyciu kreatora w programie Enterprise Manager (w menu głównym wybierz Tools - Wizards..., a z listy kreatorów wybierz Create Stored Procedure Wizard w grupie Database). Jednak my zalecamy tworzenie procedur przy pomocy kodu T-SQL.


Krok 4 - Usuń procedurę z bazy danych

1. Wróć do programu Query Analyzer z otwartym skryptem. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) usunmy procedure z bazy danych
DROP PROC pracownicy_zakres_wieku
GO


Wynikiem wykonania powyższego kodu jest usunięcie z bieżącej bazy danych (Northwind) procedury pracownicy_zakres_wieku.

Procedury składowane użytkownika w aplikacjach spełniają ważną rolę - zapewniają jeszcze jeden poziom logiki biznesowej. To znaczy, umożliwiają sprawdzanie poprawności danych podawanych przez użytkowników aplikacji. Procedury składowane mogą używać wszystkich poleceń języka DML a dodatkowo mogą wykorzystywać inne procedury.


Do projektu Prace dyplomowe dołącz skrypt o nazwie proc.sql, w którym utwórz dwie procedury składowane i podaj kod je wywołujący (koniecznie użyj parametrów).


Przejdź dalej



Ćwiczenia > Procedury składowane w MS SQL Server 2000 > Tworzenie własnych procedur składowanych