Cos'è una stored procedure?

Le stored procedure sono un insieme di istruzioni SQL che possono essere eseguite in un paio di modi. La maggior parte dei principali DBM supportano le stored procedure; tuttavia, non tutti lo fanno. Dovrete verificare con la documentazione di aiuto del vostro particolare DBMS per le specifiche. Dato che ho più familiarità con SQL Server, lo userò come esempio.

Per creare una stored procedure la sintassi è abbastanza semplice:

CREATE PROCEDURE .

     <param> 

AS

Così, per esempio:

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

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

Un vantaggio delle stored procedure è che si può centralizzare la logica di accesso ai dati in un unico posto che è poi facile da ottimizzare per il DBA. Le stored procedure hanno anche un vantaggio in termini di sicurezza, in quanto è possibile concedere i diritti di esecuzione a una stored procedure, ma l'utente non dovrà avere i permessi di lettura/scrittura sulle tabelle sottostanti. Questo è un buon primo passo contro l'SQL injection.

Le stored procedure hanno dei lati negativi, fondamentalmente la manutenzione associata alla vostra operazione di base CRUD. Diciamo che per ogni tabella avete un Insert, Update, Delete e almeno una select basata sulla chiave primaria, ciò significa che ogni tabella avrà 4 procedure. Ora prendete un database di dimensioni decenti di 400 tabelle, e avrete 1600 procedure! E questo supponendo di non avere duplicati, cosa che probabilmente accadrà.

Questo è il punto in cui usare un ORM o qualche altro metodo per generare automaticamente le operazioni CRUD di base ha un sacco di merito.

Una stored procedure è un insieme di istruzioni SQL precompilate che vengono utilizzate per eseguire un compito speciale.

Esempio: Se ho una tabella `Employee

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

Prima recupero la tabella Employee:

Create Procedure Employee details
As
Begin
    Select * from Employee
End

Per eseguire la procedura su SQL Server:

Execute   Employee details

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

Poi secondo, inserisco il valore nella tabella dei dipendenti

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

Per eseguire la procedura parametrizzata su SQL Server:

Execute employee_insert 003,’xyz’,27,1234567890

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

Esempio: @Nome Varchar(30)

Nella tabella Employee la colonna Name deve essere varchar(30).

In generale, una stored procedure è una "Funzione SQL." Hanno:

-- 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

Questo è un esempio incentrato sul T-SQL. Le stored procedure possono eseguire la maggior parte delle istruzioni SQL, restituire valori scalari e basati su tabelle, e sono considerate più sicure perché impediscono gli attacchi SQL injection.