Qu'est ce qu'une procédure stockée?
qu'est Ce qu'une procédure stockée? Comment fonctionnent-ils? Quelle est la composition d'une procédure stockée (choses chaque doit être une procédure stockée)?
15 réponses
procédures stockées sont un lot de déclarations SQL qui peuvent être exécutées de plusieurs façons. La plupart des SGBD majeurs supportent les procédures stockées; cependant, pas tous le font. Vous aurez besoin de vérifier avec votre documentation D'aide particulière de SGBD pour les détails. Comme je suis le plus familier avec SQL Server, je vais l'utiliser comme mes échantillons.
Pour créer une procédure stockée, la syntaxe est assez simple:
CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>
ainsi par exemple:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
un avantage des procédures stockées est que vous pouvez centraliser la logique d'accès aux données dans un seul endroit qui est ensuite facile pour DBA à optimiser. Procédures stockées ont également une prestation de sécurité que vous pouvez accorder des droits d'exécution d'une procédure stockée, mais l'utilisateur n'aura pas besoin d'avoir des autorisations de lecture/écriture sur les tables sous-jacentes. C'est une bonne première étape contre L'injection SQL.
procédures stockées ne viennent avec les inconvénients, essentiellement l'entretien associé à votre opération de base CRUD . Disons que pour chaque table vous avez un Insert, Update, Delete et au moins un select basé sur la touche 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 c'est en supposant que vous n'avez pas de duplicata ce que vous aurez probablement.
c'est ici que l'utilisation d'un ORM ou une autre méthode pour auto générer votre CRUD de base les opérations de a une tonne de mérite.
une procédure stockée est un ensemble d'instructions SQL précompilées qui sont utilisées pour effectuer une tâche spéciale.
exemple: si j'ai une Employee
table
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
tout d'abord, je récupère la table Employee
:
Create Procedure Employee details
As
Begin
Select * from Employee
End
pour exécuter la procédure sur le serveur SQL:
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 de L'employé
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 le serveur SQL:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
exemple: @Name Varchar(30)
dans le tableau Employee
la taille de la colonne Name
doit être varchar(30)
.
une procédure stockée est un groupe d'instructions SQL qui a été créé et stocké dans la base de données. Une procédure stockée acceptera les paramètres d'entrée de sorte qu'une procédure unique puisse être utilisée sur le réseau par plusieurs clients utilisant des données d'entrée différentes. Une procédure stockée réduira le trafic réseau et augmentera les performances. Si nous modifions une procédure stockée tous les clients obtiendront la procédure stockée mise à jour.
exemple de création d'une procédure stockée
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;
Avantages de l'utilisation de procédures stockées
-
une procédure stockée permet une programmation modulaire.
vous pouvez créer la procédure une fois, la stocker dans la base de données, et l'appeler n'importe quel nombre de fois dans votre programme.
-
Une procédure stockée permet une exécution plus rapide.
si l'opération nécessite une grande quantité de code SQL cela est effectué de façon répétitive, les procédures stockées peuvent être plus rapides. Ils sont analysés et optimisés lors de leur première exécution, et une version compilée de la procédure stockée reste dans une mémoire cache pour une utilisation ultérieure. Cela signifie que la procédure stockée n'a pas besoin d'être réparée et ré-optimisée à chaque utilisation, ce qui entraîne des délais d'exécution beaucoup plus rapides.
-
Une procédure stockée peut réduire le trafic réseau.
une opération nécessitant des centaines de lignes de code Transact-SQL peuvent être effectuées à travers une seule instruction qui exécute le code dans une procédure, plutôt que d'envoyer des centaines de lignes de code sur le réseau.
-
les procédures stockées assurent une meilleure sécurité de vos données
les Utilisateurs peuvent obtenir l'autorisation d'exécuter une procédure stockée, même si elles n'ont pas l'autorisation d'exécuter la procédure de déclarations directement.
Dans SQL Server nous avons différents types de procédures stockées:
- procédures stockées Système
- procédures stockées définies par L'utilisateur
- Procédures stockées Étendues
-
System - les procédures stockées sont stockées dans la base de données principale et celles-ci commencent par un préfixe
sp_
. Ces procédures peuvent être utilisées pour effectuer une variété de tâches pour soutenir les fonctions de SQL Server pour l'application externe d'appels dans les tables systèmeexemple: sp_helptext [StoredProcedure_Name]
-
défini par L'utilisateur les procédures stockées sont généralement stockées dans une base de données utilisateur et sont généralement conçues pour accomplir les tâches dans la base de données utilisateur. En codant ces procédures n'utilisez pas le préfixe
sp_
parce que si nous utilisons Préfixesp_
tout d'abord, il va vérifier la base de données principale, puis vient à la base de données définie par l'utilisateur. -
Extended les procédures stockées sont les procédures qui appellent des fonctions à partir de fichiers DLL. Aujourd'hui, les procédures stockées étendues sont déconseillés pour la raison qu'il serait préférable d'éviter d'utiliser des procédures stockées étendues.
généralement, une procédure stockée est une fonction " SQL."Ils ont:
-- 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 le T-SQL. Les procédures stockées peuvent exécuter la plupart des instructions SQL, retourner des valeurs scalaires et basées sur la table, et sont considérées comme plus sûres parce qu'elles empêchent les attaques par injection SQL.
pensez à une situation comme celle-ci,
- vous disposez d'une base de données contenant des données.
- il y a un certain nombre d'applications différentes nécessaires pour accéder à cette base de données centrale, et à l'avenir quelques nouvelles applications aussi.
- si vous allez insérer les requêtes de base de données en ligne pour accéder à la base de données centrale, à l'intérieur du code de chaque application individuellement, alors probablement vous devez dupliquer la même requête encore et encore dans le code de différentes applications.
- Dans ce genre de situation, vous pouvez utiliser des procédures stockées (SPs). Avec les procédures stockées, vous écrivez nombre de requêtes communes (procédures) et de les stocker avec la base de données centrale.
- maintenant la duplication du travail ne se produira jamais comme avant et l'accès aux données et la maintenance seront faites centralement.
NOTE:
- dans le texte ci-dessus situation, vous vous demandez peut-être " pourquoi ne pouvons-nous pas introduire un serveur central d'accès aux données pour interagir avec toutes les applications? Oui. Ce sera une alternative possible. Mais,
- le principal avantage du SPs par rapport à cette approche est que, contrairement à votre code d'accès aux données avec des requêtes en ligne, les SPs sont des instructions pré-compilées, de sorte qu'elles s'exécutent plus rapidement. Et les coûts de communication (via les réseaux) seront au minimum.
- par rapport à cela, SPs va ajouter un peu plus de charge à la serveur de base de données. Si cela pose un problème en fonction de la situation, un serveur centralisé d'accès aux données avec des requêtes en ligne sera un meilleur choix.
Une procédure stockée est principalement utilisé pour effectuer certaines tâches sur une base de données. Par exemple
- obtenir des ensembles de résultats de base de données à partir d'une certaine logique commerciale sur les données.
- exécute plusieurs opérations de base de données en un seul appel.
- sert à transférer des données d'une table à une autre.
- peut être appelé pour D'autres langages de programmation, comme Java.
une procédure stockée n'est rien d'autre qu'un groupe D'instructions SQL compilées dans un plan d'exécution unique.
- Créer une fois du temps et de l'appeler n fois
- il réduit le trafic réseau
exemple: création d'une procédure stockée
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
modifier une procédure stockée:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
déplacer ou supprimer une procédure stockée:
DROP PROCEDURE GetEmployee
une procédure stockée est utilisée pour récupérer des données, modifier des données, et supprimer des données dans la table de base de données. Vous n'avez pas besoin d'écrire une commande SQL complète chaque fois que vous voulez insérer, mettre à jour ou supprimer des données dans une base de données SQL.
-
une procédure stockée est un ensemble précompilé d'une ou plusieurs instructions SQL qui exécutent une tâche spécifique.
-
une procédure stockée doit être exécutée stand alone en utilisant
EXEC
-
une procédure stockée peut retourner plusieurs paramètres
-
une procédure stockée peut être utilisée pour mettre en œuvre la transaction
"qu'est Ce qu'une procédure stockée" est déjà répondu dans d'autres posts ici. Ce que je vais poster est un moyen moins connu d'utiliser la procédure stockée. C'est grouping stored procedures
ou numbering stored procedures
.
Référence Syntaxique
; number
selon ce
un entier optionnel qui est utilisé pour grouper les procédures du même nom. Ces procédures groupées peuvent être regroupées en utilisant un énoncé de procédure de largage
exemple
CREATE Procedure FirstTest
(
@InputA INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO
CREATE Procedure FirstTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
Utiliser
exec FirstTest 10
exec FirstTest;2 20,30
résultat
Une Autre Tentative
CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
résultat
Msg 2730, Niveau 11, État 1, Procédure SecondTest, Ligne 1 [Lot De La Ligne De Départ 3] Ne peut pas créer de procédure "SecondTest" avec un numéro de groupe de 2 parce qu'une procédure avec le même nom et un numéro de groupe de 1 n'existe pas actuellement dans la base de données. Must execute CREATE PROCEDURE 'SecondTest'; 1 first.
Références :
- "créer une procédure avec la syntaxe pour le numéro
- procédures stockées numérotées dans SQL Server - techie-friendly.blogspot.com
- Groupement des Procédures Stockées - sqlmag
ATTENTION
- procédures, vous ne pouvez pas vous déplacer individuellement.
- cette fonctionnalité peut être supprimée dans une version future de Microsoft SQL Server.
une procédure stockée est un ensemble nommé D'énoncés SQL et de logique procédurale I. e, compilé, vérifié et stocké dans la base de données du serveur. Une procédure stockée est généralement traitée comme d'autres objets de base de données et contrôlée par le mécanisme de sécurité du serveur.
dans un SGBD, une procédure stockée est un ensemble D'instructions SQL avec un nom assigné qui est stocké dans la base de données sous forme compilée afin qu'il puisse être partagé par un certain nombre de programmes.
L'utilisation d'une procédure stockée peut être utile dans le
-
fournir un accès contrôlé aux données (les utilisateurs finaux ne peuvent entrer ou modifier des données, mais ne peuvent pas écrire des procédures) "
-
garantir les données intégrité (les données seraient saisies de manière uniforme) et
-
améliore la productivité (les déclarations d'une procédure stockée doivent être écrites une seule fois)
pour simple,
procédure stockée sont programmes stockés , un programme/fonction stocké dans la base de données.
chaque programme stocké contient un corps qui se compose d'une instruction SQL. Cette instruction peut être une instruction composée composée de plusieurs instructions séparées par un point-virgule (;).
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
procédures stockées dans SQL Server peut accepter les paramètres d'entrée et retourner plusieurs valeurs de paramètres de sortie; dans SQL Server, les déclarations de programme de procédures stockées pour effectuer des opérations dans la base de données et retourner une valeur d'État à une procédure d'appel ou un lot.
les avantages d'utiliser des procédures stockées dans le serveur SQL
Ils permettent la programmation modulaire. Ils permettent une exécution plus rapide. Ils peuvent réduire le trafic réseau. Ils peuvent être utilisés comme un le mécanisme de sécurité.
Voici un exemple d'une procédure stockée qui prend un paramètre, exécute une requête et retourne le résultat. Plus précisément, la procédure stockée accepte le BusinessEntityID comme paramètre et l'utilise pour correspondre à la clé primaire des Ressources Humaines.Table de l'employé pour retourner l'employé demandé.
> create procedure HumanResources.uspFindEmployee `*<<<---Store procedure name`*
@businessEntityID `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId, <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId <<<---parameter used as criteria
end
j'ai appris cela d'essentiel.com...c'est très utile.
procédure stockée vous aidera à faire du code dans le serveur.Vous pouvez passer les paramètres et trouver la sortie.
create procedure_name (para1 int,para2 decimal)
as
select * from TableName