Tworzenie procedur przechowywanych (aparat bazy danych)
Tworzenie procedur przechowywanych, stosując procedurę tworzenia Transact-SQL instrukcja.
Przed utworzeniem procedura składowana, uznają, że:
Nie można łączyć instrukcji CREATE PROCEDURE z innych instrukcji SQL w jednej partia.
Aby utworzyć procedury, musi mieć uprawnienia CREATE PROCEDURE w bazie danych i ZMIEŃ na schemacie, w którym tworzona jest procedura.Procedury przechowywane CLR musi albo własnych wirtualny plik dziennika , do którego odwołuje się <method_specifier>, lub mieć uprawnienie odwołania do wirtualny plik dziennika.
Procedury przechowywane są obiekty o zakresie schematu, a ich nazwy muszą spełniać zasady dla identyfikatorów.
procedura składowana można utworzyć tylko w bieżącej bazie danych.
Podczas tworzenia procedura składowana, należy określić:
Danych wejściowych parametry i parametrów wyjściowych do wywołania procedury lub programu partia.
Instrukcje programowania, wykonujących operacje w bazie danych, łącznie z wywołaniem innych procedur.
Wartość stanu zwrócony do wywołania procedury lub partia oznacza sukces lub Niepowodzenie (i przyczynę awarii).
Instrukcje obsługa błędów potrzebne do połowu i obsługi potencjalnych błędów.
Błąd przekazywanie funkcji, takich jak ERROR_LINE i ERROR_PROCEDURE można określić w procedura składowana.Aby uzyskać więcej informacji, zobacz za pomocą SPRÓBOWAĆ...POŁÓW w języku Transact-SQL.
Nazewnictwo procedur przechowywanych
Firma Microsoft zaleca, aby nie utworzyć procedury przechowywane, przy użyciu sp_ jako prefiks.SQL Serverużywa sp_ prefiks do wyznaczenia procedury składowane w systemie.Wybrana nazwa może powodować konflikt z niektóre procedury przyszłego systemu.Jeśli aplikacja używa nonschema kwalifikowaną nazwę odwołania, nazwę procedury konflikt z nazwą procedury systemu aplikacji zostaną przerwane, ponieważ nazwa wiąże się z procedurą systemu nie własne.
Zdefiniowany przez użytkownika procedura składowana , która ma taką samą nazwę jak systemową procedura składowana i jest nonqualified lub w dbo Schemat nigdy nie będzie wykonywane; system procedura składowana będzie zawsze wykonać zamiast.Poniższy przykład ilustruje to zachowanie.
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO
Za pomocą schematu jawne kwalifikator zapewnia także korzyści nieznaczne wydajności.Rozpoznawanie nazw jest trochę szybszy Jeśli Aparat baz danych nie trzeba przeszukiwać wiele schematów, aby znaleźć procedurę.Aby uzyskać więcej informacji, zobacz wykonywanie procedury przechowywanej.
Tymczasowe procedury przechowywane
Prywatne i globalne tymczasowe procedury przechowywane, analogiczne do tabel tymczasowych mogą być tworzone z # i dodawany do nazwy procedury ## prefixes.# oznacza lokalny tymczasowe procedura składowana; ## oznacza globalne tymczasową procedura składowana.Te procedury nie istnieją po SQL Server jest shut niedziałający.
Tymczasowe procedury przechowywane są przydatne przy łączeniu się z wcześniejszych wersji programu SQL Server nie obsługują ponownego użycia planów wykonania dla Transact-SQL instrukcje lub partii.Łączenie się z aplikacjami SQL Server 2000 i wyższej powinny używać sp_executesql systemu procedura składowana zamiast tymczasowych procedur przechowywanych.Tylko połączenie lokalne tymczasową procedurę tworzenia można, wykonać i procedury jest automatycznie usuwany po zamknięciu połączenia.
Dowolne połączenie można wykonać globalnego tymczasową procedura składowana.Globalne tymczasową procedura składowana istnieje aż zamknięte połączenie używane przez użytkownika, który utworzył procedury i wykonywane są aktualnie wersjach procedury przez innych połączeń.Po zamknięciu połączenia, który został użyty do utworzenia procedury jest dozwolone nie dalszej realizacji globalnego tymczasową procedura składowana .Aby ukończyć są dozwolone tylko tych połączeń, które zostało już rozpoczęte, wykonywanie procedura składowana .
Jeśli procedura składowana nie prefiksem # lub ## jest tworzony bezpośrednio w programie tempdb bazy danych, procedura składowana jest automatycznie usuwany po SQL Server kończy pracę niedziałający ponieważ tempdb jest utworzony ponownie za każdym czas SQL Server jest uruchomiona.Procedury tworzone bezpośrednio w tempdb Istnieje nawet po tworzenia połączenie jest przerywane.
Ostrzeżenie
Użycie tymczasowych procedur przechowywanych, można utworzyć rywalizacja na tabele systemowe w tempdb i negatywnie wpływać na wydajność.Zalecane jest sp_executesql Zamiast używać.sp_executesql Nie przechowywać dane w tabele systemowe i w związku z tym pozwala uniknąć tego problemu.
Nie można utworzyć procedury przechowywane CLR jako tymczasowych procedur przechowywanych.
Przykłady
A.Prostą procedurę przy użyciu wybierz złożonych
Poniższa procedura składowana zwraca wszystkich pracowników (imion i nazwisk dostarczone), ich tytuły i ich nazwy działów z widoku.Ta procedura składowana nie używa żadnych parametrów.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, Department
FROM HumanResources.vEmployeeDepartmentHistory;
GO
uspGetEmployeesprocedura składowana mogą być wykonywane w następujący sposób:
EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;
B.Prostą procedurę przy użyciu parametrów
Poniższa procedura składowana zwraca tylko określonego pracownika (imię i nazwisko dostarczone), jej tytuł i jej nazwę działu z widoku.Ta procedura składowana akceptuje dokładne odpowiedniki parametry przekazywane.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName,Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName;
GO
uspGetEmployeesprocedura składowana mogą być wykonywane w następujący sposób:
EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
C.Prostą procedurę przy użyciu symboli wieloznacznych parametrów
Poniższa procedura składowana zwraca tylko określonych pracowników (imion i nazwisk dostarczone), ich tytuły i ich departamentów z widoku.Tego wzorca procedura składowana pasuje do parametrów przekazanych lub, jeżeli nie jest podany, wykorzystuje ustawienia domyślne (ostatnia nazwy zaczynające się od litery D).
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2
@LastName nvarchar(50) = N'D%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO
uspGetEmployees2 procedura składowana mogą być wykonywane w wielu kombinacji.Tylko kilka kombinacji przedstawiono poniżej:
EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';
D.Przy użyciu parametrów wyjściowych
Poniższy przykład tworzy uspGetList procedura składowana, która zwraca listę produktów, których ceny, które nie przekraczają określonej ilości.W przykładzie przy użyciu wielu instrukcji SELECT i wiele parametrów wyjściowych.Parametry wyjściowe umożliwiają procedury zewnętrznego, partialub więcej niż jedną Transact-SQL instrukcja dostępu wartość zestaw podczas wykonywania procedury.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SET NOCOUNT ON;
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
Wykonanie uspGetList , aby powrócić do listy Adventure Works produktów (Rowery), które kosztują mniej niż 700 $.Parametry wyjściowe @ Kosztów i @ ComparePrices są używane z język sterowania przepływem do zwracania wiadomości w wiadomości okna.
Ostrzeżenie
Zmienną produkcji musi być zdefiniowany podczas tworzenia procedury, jak również podczas użytkowania zmiennej.Parametr Nazwa i nazwa zmiennej nie muszą odpowiadać; Jednakże typ danych i pozycjonowania parametru musi odpowiadać (chyba że @ ListPrice= variablejest używana).
DECLARE @ComparePrice money, @Cost money
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
Poniżej przedstawiono częściową zestaw wyników:
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.
Zobacz także