Qu'est-ce qu'une procédure stockée ? Comment fonctionnent-elles ? Quelle est la composition d'une procédure stockée (les choses que chacun doit avoir pour être une procédure stockée) ?
Les procédures stockées sont un lot d'instructions SQL qui peuvent être exécutées de plusieurs façons. La plupart des principaux SGBDs supportent les procédures stockées, mais pas tous. Vous devrez consulter la documentation d'aide de votre SGBD pour plus de détails. Comme je suis plus familier avec SQL Server, je l'utiliserai comme exemple.
Pour créer une procédure stockée, la syntaxe est assez simple :
CREATE PROCEDURE <owner>.<procedure name>
<param> <datatype>
AS
<Body>
Donc par exemple :
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
L'un des avantages des procédures stockées est qu'elles permettent de centraliser la logique d'accès aux données en un seul endroit qui est ensuite facile à optimiser pour les DBA. Les procédures stockées présentent également un avantage en matière de sécurité : vous pouvez accorder des droits d'exécution à une procédure stockée, mais l'utilisateur n'aura pas besoin d'avoir des droits de lecture/écriture sur les tables sous-jacentes. Il s'agit d'une bonne première étape contre l'injection SQL.
Les procédures stockées présentent toutefois des inconvénients, notamment la maintenance associée à l'opération de base [CRUD][CRUD]. Disons que pour chaque table, vous avez une insertion, une mise à jour, une suppression et au moins une sélection basée sur la clé primaire, ce qui signifie que chaque table aura 4 procédures. Maintenant, prenez une base de données de taille décente de 400 tables, et vous avez 1600 procédures ! Et cela en supposant que vous n’ayez pas de doublons, ce qui sera probablement le cas.
C'est là que l'utilisation d'un [ORM][ORM] ou d'une autre méthode pour générer automatiquement vos opérations CRUD de base a beaucoup de mérite.
[CRUD] : https://en.wikipedia.org/wiki/Create,_read,_update_and_delete [ORM] : https://stackoverflow.com/questions/1279613/what-is-an-orm-and-where-can-i-learn-more-about-it
Une procédure stockée est un ensemble d'instructions SQL précompilées qui sont utilisées pour effectuer une tâche particulière.
Exemple : Si j'ai une table Employé
, j'ai besoin d'une procédure stockée.
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
Je commence par récupérer la table Employee
:
Create Procedure Employee details
As
Begin
Select * from Employee
End
Pour exécuter la procédure sur SQL Server :
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
Ensuite, j'insère la valeur dans la table des employés.
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
Pour exécuter la procédure paramétrée sur SQL Server :
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
Exemple : @Nom Varchar(30)
Dans la table Employee
, la taille de la colonne Name
doit être varchar(30)
.
En règle générale, une procédure stockée est une "fonction SQL" :
-- 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
Il s'agit d'un exemple axé sur T-SQL. Les procédures stockées peuvent exécuter la plupart des instructions SQL, retourner des valeurs scalaires et basées sur des tables, et sont considérées comme plus sûres car elles empêchent les attaques par injection SQL.