Wat is een opgeslagen procedure?

Opgeslagen procedures zijn een reeks SQL statements die op een aantal manieren kunnen worden uitgevoerd. De meeste grote DBM's ondersteunen stored procedures; maar niet allemaal. U zult de help-documentatie van uw DBMS moeten raadplegen voor meer details. Aangezien ik het meest vertrouwd ben met SQL Server zal ik dat als voorbeeld gebruiken.

Om een opgeslagen procedure te maken is de syntax vrij eenvoudig:

CREATE PROCEDURE .

     <param> 

AS

Dus bijvoorbeeld:

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

Een voordeel van opgeslagen procedures is dat u de logica van de gegevenstoegang kunt centraliseren op één enkele plaats die dan gemakkelijk door DBA's kan worden geoptimaliseerd. Opgeslagen procedures hebben ook een veiligheidsvoordeel: je kunt uitvoeringsrechten toekennen aan een opgeslagen procedure, maar de gebruiker hoeft geen lees-/schrijfrechten te hebben op de onderliggende tabellen. Dit is een goede eerste stap tegen SQL injectie.

Opgeslagen procedures komen met nadelen, in principe het onderhoud geassocieerd met uw basis CRUD operatie. Laten we zeggen dat je voor elke tabel een Insert, Update, Delete en minstens één select hebt gebaseerd op de primaire sleutel, dat betekent dat elke tabel 4 procedures zal hebben. Neem nu een behoorlijke grootte database van 400 tabellen, en je hebt 1600 procedures! En dat's in de veronderstelling dat je geen duplicaten hebt, wat waarschijnlijk wel zo zal zijn.

Dit is waar het gebruik van een ORM of een andere methode om automatisch genereren van uw basis CRUD operaties heeft een ton van verdienste.

Een opgeslagen procedure is een reeks vooraf samengestelde SQL-statements die worden gebruikt om een speciale taak uit te voeren.

Voorbeeld: Als ik een Employee tabel heb

Employee ID  Name       Age  Mobile
---------------------------------------
001          Sidheswar  25   9938885469
002          Pritish    32   9178542436

Eerst haal ik de Employee tabel op:

Create Procedure Employee details
As
Begin
    Select * from Employee
End

Om de procedure op SQL Server uit te voeren:

Execute   Employee details

--- (Employee details is a user defined name, give a name as you want)

Ten tweede, ik voeg de waarde in de werknemer tabel

Create Procedure employee_insert
    (@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
    Insert Into Employee
    Values (@EmployeeID, @Name, @Age, @Mobile)
End

Om de parametrische procedure op SQL Server uit te voeren:

Execute employee_insert 003,’xyz’,27,1234567890

  --(Parameter size must be same as declared column size)

Voorbeeld: @Name Varchar(30)

In de Employee tabel moet de Name kolom varchar(30) zijn.

In het algemeen is een opgeslagen procedure een "SQL Functie." Ze hebben:

-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID

Dit is een T-SQL gericht voorbeeld. Opgeslagen procedures kunnen de meeste SQL statements uitvoeren, geven scalaire en tabelgebaseerde waarden terug, en worden als veiliger beschouwd omdat ze SQL injectie aanvallen voorkomen.