1.Qu’est-ce qu’une procédure stockée ?
La procédure stockée est compilée une fois et peut être exécutée plusieurs fois (la procédure stockée est stockée sur le serveur). Il s'agit d'une collection précompilée et s'exécute rapidement.
2. Procédures stockées système couramment utilisées
code
-- Objectif : procédures stockées système couramment utilisées
EXEC sp_databases --Liste les bases de données du système actuel
EXEC sp_renamedb 'test', 'test1'--Modifier le nom de la base de données (accès utilisateur unique)
UTILISER studB
ALLER
EXEC sp_tables --Liste des objets interrogés dans la base de données actuelle
EXEC sp_columns stuInfo --renvoie des informations sur une colonne de table
EXEC sp_help stuInfo --Afficher les informations stuInfo de la table
EXEC sp_helpconstraint stuInfo --Afficher les contraintes de la table stuInfo
EXEC sp_helpindex stuMarks --Afficher l'index de la table stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks' --Afficher le texte de l'instruction de la vue
EXEC sp_stored_procedures --renvoie la liste des procédures stockées dans la base de données actuelle
utiliser le maître
aller
exec xp_cmdshell 'mkdir D:bank',no_output--Créer un dossier
3. Procédures stockées personnalisées
(1.) Procédure stockée sans paramètres
code
utiliser studb
aller
s'il existe (sélectionnez * dans les objets système où nom = 'proc_stu')
supprimer le proc proc_stu
aller
créer une procédure proc_stu
COMME
DECLARE @writeAvg float,@labAvg float --Variable de score moyen du test écrit et du test informatique
SELECT @writeAvg=AVG(writingExam), @labAvg=AVG(labExam) FROM stuMarks
print 'Score moyen au test écrit :'+convert(varchar(5),@writingAvg)
print 'Score moyen du test informatique :'+convert(varchar(5),@labAvg)
SI (@writeAvg>70 ET @labAvg>70)
print 'Résultats des examens de cette classe : Excellent'
AUTRE
print 'Résultats des examens de cette classe : médiocres'
imprimer '------------------------------------------------ --'
print 'Étudiants qui n'ont pas réussi cet examen :'
SELECT stuName, stuInfo.stuNo,writingExam,labExam FROM stuInfo
REJOINTION INTÉRIEURE stuMarks ON stuInfo.stuNo=stuMarks.stuNo
O ÉcritExam<60 OU labExam<60
ALLER
exec proc_stu --Exécuter des procédures stockées (2.) Procédures stockées avec paramètres d'entrée
code
UTILISER studB
ALLER
/*---Détecter si elle existe : la procédure stockée est stockée dans la table système sysobjects---*/
SI EXISTE (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
PROCÉDURE DE SUPPRESSION proc_stu
ALLER
/*---Créer une procédure stockée----*/
CRÉER UNE PROCÉDURE proc_stu
@writePass int,
@labPass int-- Vous pouvez ajouter une valeur par défaut pour que l'exécution puisse être comme celle-ci exec proc_stu sans spécifier de paramètres.
COMME
print 'Note de passage écrite :'+convert(varchar(5),@writingPass)
print 'Ligne de réussite du test informatique :'+convert(varchar(5),@labPass)
imprimer '------------------------------------------------ --'
print 'Étudiants qui n'ont pas réussi cet examen :'
SELECT stuName, stuInfo.stuNo,writingExam,labExam FROM stuInfo
REJOINTION INTÉRIEURE stuMarks ON stuInfo.stuNo=stuMarks.stuNo
OÙ writingExam<@writingPass OU labExam<@labPass
ALLER
exec proc_stu 60,55
exec proc_stu 60,@labPass=55
exec proc_stu @writingPass=60,@labPass=55
exec proc_stu @writingPass=60,55--Cette ligne signalera une erreur : le paramètre 2 doit être transmis et les paramètres suivants doivent être transmis sous la forme '@name = value'.
-- Une fois le formulaire '@name = value' utilisé, tous les paramètres suivants doivent être passés sous le formulaire '@name = value'
(3.) Procédure stockée avec paramètres de sortie
code
UTILISER studB
ALLER
/*---Détecter si elle existe : la procédure stockée est stockée dans la table système sysobjects---*/
SI EXISTE (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
PROCÉDURE DE SUPPRESSION proc_stu
ALLER
/*---Créer une procédure stockée----*/
CRÉER UNE PROCÉDURE proc_stu
@notpassSum int OUTPUT, mot-clé --OUTPUT, sinon il est considéré comme un paramètre d'entrée
@writePass int=60, --Les paramètres par défaut sont placés après
@labPass int=60 --Les paramètres par défaut sont placés après
COMME
print 'Note de passage écrite :'+convert(varchar(5),@writingPass)
+ 'Ligne de réussite au test informatique :'+convert(varchar(5),@labPass)
imprimer '------------------------------------------------ --'
print 'Étudiants qui n'ont pas réussi cet examen :'
SELECT stuName, stuInfo.stuNo,writingExam,labExam FROM stuInfo
REJOINTION INTÉRIEURE stuMarks ON stuInfo.stuNo=stuMarks.stuNo
OÙ writingExam<@writingPass OU labExam<@labPass
/*--Compter et renvoyer le nombre d'étudiants qui ont échoué à l'examen--*/
SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
OÙ writingExam<@writingPass OU labExam<@labPass
ALLER
/*---Appeler la procédure stockée----*/
DÉCLARER @sum int
EXEC proc_stu @sum OUTPUT ,64 --La note de passage pour le test informatique adopte la valeur par défaut : la note de passage pour le test écrit est de 64 points et la note de passage pour le test informatique est de 60 points.
imprimer '------------------------------------------------ --'
SI @somme>=3
print 'Nombre de personnes qui ont échoué :'+convert(varchar(5),@sum)+ 'Personnes, plus de 60 %, la note de passage devrait être abaissée'
AUTRE
print 'Nombre de personnes qui ont échoué :'+convert(varchar(5),@sum)+ 'Les personnes ont été contrôlées en dessous de 60 % et la note de passage est modérée'
ALLER