Noțiuni de bază cu Proceduri Stocate în SQL Server
De: Greg Robidoux | Actualizat: 2020-07-30 | Comentarii (15) | Conexe: Mai > Proceduri Stocate
Probleme
am fost folosind SQL Server de ceva timp, dar tot codul care este issuedagainst baza de date este încorporat în codul de aplicare. Știu că puteți creaproceduri stocate, dar nu sunt exact sigur de unde să încep sau de ce trebuie să facpentru a implementa proceduri stocate.,
soluție
procedurile stocate nu sunt altceva decât un lot de declarații T-SQL care suntstocate în baza de date. În loc de a avea pentru probleme mai multe declarații decât aplicație puteți emite o comandă pentru a apela procedura stocată pentru a face abatch de muncă în loc de doar o declarație. În plus, deoarece codul isstored în baza de date puteți emite același set de codul de peste si peste againeven din diferite aplicații sau o fereastră de interogare. Pentru a începe, restul acestui sfat se uită la uneleproceduri de exemple stocate și cum puteți începe și construi pe ele.,exemplele de mai jos vă arată cât de simplu este să creați proceduri stocate. Toate aceste exemple folosesc baza de date AdventureWorks, dar aceste exemple ar trebui să fie destul de simplecă puteți aplica aceste concepte la propriile baze de date și aplicații.
Exemplul 1-procedură simplă stocată
Acest prim exemplu creează o procedură simplă stocată care obține înregistrarea de TOP 1de la persoană.Masa de Contact.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
După cele de mai sus a fost creat utilizați comanda de mai jos pentru a executa acest storedprocedure.,
EXEC uspGetContact
acestea sunt rezultatele acestei prime interogări.
Exemplul 2 – proceduri stocate cu un parametru
următorul exemplu este o modificare de primul exemplu, dar de data asta addinga parametru care este trecut în procedura de dinamic selectați înregistrări.În loc să folosim procedura de creare, folosim procedura de modificare pentru a modifica procedurape care am creat-o în exemplul 1, în loc să o lăsăm mai întâi și apoi să o recreăm.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
de mai jos arată două moduri diferite de procedura stocată poate fi rulat. Primul exempludoar trece valoarea parametrului pe care dorim să o utilizăm, iar al doilea exemplu include și numele parametrului împreună cu valoarea. Puteți rula procedura stocată cu fieuna dintre aceste comenzi.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
acestea sunt rezultatele acestei prime interogări.,
Exemplul 3 – proceduri stocate cu un parametru și parametru de ieșire
În acest exemplu avem atât un parametru de intrare, precum și un parametru de IEȘIRE.Parametrul de ieșire va fi utilizat pentru a trece înapoi ContactID că suntem lookingup în procedura stocată. Acest parametru de ieșire va fi apoi folosite pentru a selecta thepersons ContactID, FirstName și LastName, împreună cu orice adresa de înregistrări pentru thisperson.,
din Nou, suntem modificarea procedură stocată uspGetContact și apoi, în al doilea rând weare execută următorul set de cod care se execută procedura uspGetContact și thenbased pe valoarea returnata se va interoga, de asemenea, pentru nume de persoane ș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
după ce procedura stocată a fost modificată executați blocul de cod de mai jos. Acest lucru va executa procedura stocată mai sus și dacă ContactID are o valoare, va returna și informațiile despre persoană și adresă.
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
acestea sunt rezultatele.,
Exemplul 4 – proceduri stocate folosind RAISERROR declarație
În acest exemplu vom combina cele două etape în Exemplul 3 într-o procedură stocată.Primul pas este de a obține ContactID și apoi a doua parte a proceduriiva căuta numele persoanelor și adresa info. De asemenea, am adăugat în cod pentru a utiliza declarația theRAISERROR pentru a returna o eroare dacă nu se găsesc înregistrări.
aceasta este apoi rulată de două ori pentru a arăta cum arată când se găsesc date și când nu se găsesc date., Declarația RAISERROR poate fi folosit pentru a controla modul în care applicationhandles dvs. nu există date sau orice altă eroare care pot apărea.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT 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 IF @@ROWCOUNT > 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"
Acesta este rezultatul.
EXEC uspGetContact @LastName="Job"
Acesta este rezultatul atunci când datele nu este de găsit.
Exemplul 5 – proceduri stocate separat de asteptare procedură stocată
Aici este un alt exemplu în care avem două proceduri stocate., Prima storedprocedure uspFindContact căutări prima înregistrare care are o adresa de înregistrare și apoi se întoarce la ContactID la chemarea procedură stocată pentru a afișa din nou theperson și adresa de informații.
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
codul De mai jos are un alter a uspGetContact procedură stocată care callsuspFindContact și returnează recordsets.
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"
Acesta este rezultatul.
EXEC uspGetContact @LastName="Job"
Acesta este rezultatul.,
Exemplul 6 – proceduri stocate cu comentarii
Acest ultim exemplu ia uspGetContact procedură stocată și adaugă commentsto codul, astfel încât să puteți vedea cât de comentarii de lucru în cadrul unei proceduri stocate.
comentariile pot fi făcute în două moduri
- folosind —
- folosind / * pentru a începe blocul de comentarii și * / pentru a termina blocul de comentarii.
altele decât că nimic altceva nu sa schimbat în procedura stocată.,
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
Acestea sunt destul de simple exemple, dar sperăm că acest lucru vă oferă o idee de howeasy este de a crea proceduri stocate pentru SQL Server. Dacă puteți rula o Select statementfrom fie o fereastră de interogare sau din aplicația dvs., puteți rula la fel de ușor procedura astored ca arată mai sus.,
pașii următori
- dacă nu utilizați deja proceduri stocate sperăm că acest lucru vă oferă o perspectivă asupra a ceea ce trebuie să faceți pentru a începe să le utilizați
- așa cum am menționat, acestea sunt exemple destul de simple, dar aproape orice puteți face cu un lot de declarații poate fi combinat într-o procedură stocată și apoi folosit din nou și din nou pentru aplicațiile dvs.,
- Check out thestored procedura tutorial pentru mai multe exemple
Ultima Actualizare: 2020-07-30
Despre autor
Greg Robidoux este Președintele Edgewood Soluții și co-fondator al MSSQLTips.com.Vezi toate sfaturile mele
- Mai mult Dezvoltator de baze de Date Sfaturi…