Pierwsze kroki z procedurami składowanymi w SQL Server

autor: Greg Robidoux | Zaktualizowano: 2020-07-30 | Komentarze (15) | powiązane: więcej> procedury składowane

Problem

używam SQL Server od jakiegoś czasu, ale cały kod, który jest wystawionyagainst baza danych jest osadzona w kodzie aplikacji. Wiem, że można tworzyć procedury magazynowane, ale nie jestem do końca pewien, od czego zacząć i czego potrzebuję, aby zaimplementować procedury magazynowane.,

rozwiązanie

procedury przechowywane to nic innego jak zbiór instrukcji T-SQL, które znajdują się w bazie danych. Zamiast wydawania wielu poleceń z Twojej aplikacji, możesz wydać jedno polecenie wywołania procedury składowanej w celu wykonania pracy zamiast tylko jednej instrukcji. Ponadto, ponieważ kod jest przechowywany w bazie danych, możesz wielokrotnie wydawać ten sam zestaw kodu nawet z różnych aplikacji lub okna zapytań. Aby zacząć, w dalszej części tej porady omówiono niektóre przykładowe procedury składowane oraz sposób, w jaki można zacząć i na nich budować.,

poniższe przykłady pokazują, jak proste jest tworzenie procedur składowanych. Wszystkie te przykłady wykorzystują bazę danych AdventureWorks, ale te przykłady powinny być dość proste, aby można było zastosować te pojęcia do własnych baz danych i aplikacji.

przykład 1 – prosta procedura składowana

Ten pierwszy przykład tworzy prostą procedurę składowaną, która pobiera pierwszy rekord od osoby.Tabela kontaktowa.

CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact 

Po utworzeniu powyższego polecenia użyj poniższego polecenia, aby wykonać ten proces przechowywania.,

EXEC uspGetContact

to są wyniki z tego pierwszego zapytania.

przykład 2 – procedura składowana z parametrem

Ten kolejny przykład jest modyfikacją pierwszego przykładu, ale tym razem dodaje parametr, który jest przekazywany do procedury w celu dynamicznego wyboru rekordów.Zamiast używać procedury CREATE używamy procedury ALTER aby zmodyfikować procedurę, którą stworzyliśmy w przykładzie 1, zamiast najpierw ją upuścić, a następnie odtworzyć.,

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName 

Poniżej przedstawiono dwa różne sposoby uruchomienia procedury składowanej. Pierwszy przykład przekazuje wartość parametru, którego chcemy użyć, a drugi przykład zawiera również nazwę parametru wraz z wartością. Procedurę składowaną można uruchomić za pomocą jednej z tych komend.

EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"

to są wyniki z tego pierwszego zapytania.,

przykład 3 – procedura składowana z parametrem i parametrem wyjściowym

w tym przykładzie mamy zarówno parametr wejściowy, jak i wyjściowy.Parametr wyjściowy zostanie użyty do przekazania ContactID, którego szukamy w procedurze składowanej. Ten parametr wyjściowy zostanie następnie użyty do wybrania osób kontakt, imię i nazwisko wraz z wszelkimi rekordami adresowymi dla tej osoby.,

ponownie zmieniamy procedurę składowaną uspGetContact, a następnie uruchamiamy kolejny zestaw kodu, który wykonuje procedurę uspGetContact, a następnie na podstawie otrzymanej wartości zwracanej zapytamy również o osoby name I addressinfo.

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName 

Po zmianie procedury składowanej Uruchom poniższy blok kodu. Spowoduje to wykonanie powyższej procedury składowanej, a jeśli ContactID ma wartość, zwróci również informacje o osobie i adresie.

DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName="Smith", @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END 

oto wyniki.,

przykład 4 – procedura składowana przy użyciu instrukcji RAISERROR

w tym przykładzie łączymy dwa kroki w przykładzie 3 w jedną procedurę składowaną.Pierwszym krokiem jest uzyskanie ContactID, a następnie druga część procedurewill wyszukuje informacje o nazwie i adresie osób. Dodaliśmy również w kodzie, aby użyć instrukcji theRAISERROR, aby zwrócić błąd, jeśli nie znaleziono rekordów.

jest to uruchamiane dwa razy, aby pokazać, jak wygląda, gdy dane zostaną znalezione, A gdy nie zostaną znalezione żadne dane., Instrukcja RAISERROR może być używana do kontrolowania, w jaki sposób aplikacja nie obsługuje żadnych danych lub innych błędów, które mogą wystąpić.

EXEC uspGetContact @LastName="Walters"

oto wyniki.

EXEC uspGetContact @LastName="Job"

jest to wynik, gdy nie znaleziono żadnych danych.

przykład 5 – procedura składowana z oddzielnym wywołaniem procedury składowanej

oto kolejny przykład, w którym mamy dwie procedury składowane., Pierwszy proces przechowywany uspFindContact wyszukuje pierwszy rekord, który ma rekord adresu i następnie zwraca ContactID do wywołującej procedury składowanej, aby ponownie wyświetlić informacje o osobie i adresie.

CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName 

poniższy kod zmienia procedurę składowaną uspGetContact, która wywołuje suspfindcontact i zwraca zestawy rekordów.

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @, @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END 

EXEC uspGetContact @LastName="Walters"

oto wyniki.

EXEC uspGetContact @LastName="Job"

oto wyniki.,

przykład 6 – procedura składowana z komentarzami

ten ostatni przykład pobiera procedurę składowaną uspgetkontakt i dodaje komentarze do kodu, dzięki czemu można zobaczyć, jak komentarze działają w ramach procedury składowanej.

komentarze można tworzyć na dwa sposoby

1. używając —
2. używając/*, aby rozpocząć blok komentarzy i*/, aby zakończyć blok komentarzy.

poza tym nic innego nie uległo zmianie w procedurze składowanej.,

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS /* This is a sample stored procedure to show how comments work within a stored procedure */ -- declare variable DECLARE @ContactID INT -- set variable value SET @ContactID = 0 -- execute stored proc and return ContactID value EXEC uspFindContact @, @ OUTPUT -- if ContactID does not equal 0 then return data else return error IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END 

są to dość proste przykłady, ale mam nadzieję, że to daje wyobrażenie o tym, jak łatwe jest tworzenie procedur składowanych dla SQL Server. Jeśli możesz uruchomić polecenie SELECT z okna zapytania lub z aplikacji, możesz równie łatwo uruchomić procedurę astored, jak pokazano powyżej.,

Następne kroki

• Jeśli jeszcze nie korzystasz z procedur składowanych, mam nadzieję, że to da ci pewien wgląd w to, co musisz zrobić, aby zacząć z nich korzystać
• jak wspomniano, są to dość proste przykłady, ale prawie wszystko, co możesz zrobić z partią instrukcji, może być połączone w procedurę składowaną i używane w kółko dla Twoich aplikacji.,
• zapoznaj się z samouczkiem procedury, aby uzyskać więcej przykładów

Ostatnia aktualizacja: 2020-07-30

o autorze

Greg Robidoux jest prezesem Edgewood Solutions i współzałożycielem MSSQLTips.com.
View all my tips

Related Resources

• More Database Developer Tips…