-
8/3/2019 Les procdures stockes et les fonctions utilisateur
1/18
Les procdures stockes
et les fonctions utilisateur
Z
Grgory CASANOVA
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
2/18
Dotnet France Association CASANOVA Grgory
2 Les procdures stockes et les fonctions utilisateur [08/07/09]
Sommaire
1 Introduction ..................................................................................................................................... 3
2 Pr-requis ........................................................................................................................................ 4
3 Les procdures stockes.................................................................................................................. 5
3.1 Introduction aux procdures stockes .................................................................................... 5
3.2 Gestion des procdures stockes ............................................................................................ 5
3.2.1 Cration dune procdure stocke.................................................................................. 5
3.2.2 Modifier une procdure stocke ..................................................................................... 9
3.2.3 Suppression dune procdure stocke .......................................................................... 10
4 Les fonctions utilisateur ................................................................................................................ 12
4.1 Introduction aux fonctions utilisateur ................................................................................... 12
4.2 Cration dune fonction ........................................................................................................ 13
4.3 Modification dune fonction.................................................................................................. 16
4.4 Suppression dune fonction................................................................................................... 17
5 Conclusion ..................................................................................................................................... 18
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
3/18
Dotnet France Association CASANOVA Grgory
3 Les procdures stockes et les fonctions utilisateur [08/07/09]
1 IntroductionLes bases de donnes sont utilises partout. Comme simple conteneurs de donnes, comme
entits capables de communiquer sur un rseau ou encore avec une application cliente distante.
Cest cet aspect que nous allons valoriser dans ce chapitre : le fait que toute base de donnes puisse
tre exploitable depuis une application cliente. Dans cette dmarche, les procdures stockes et les
fonctions utilisateurs sont trs utiles. Ces objets de la base de donnes, puisquils sont rfrencs en
tant que tels, ont la particularit dtre directement appelable depuis une application cliente. Nous
allons dtailler tous les avantages de ce type dobjets dans ce chapitre.
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
4/18
Dotnet France Association CASANOVA Grgory
4 Les procdures stockes et les fonctions utilisateur [08/07/09]
2 Pr-requisPour comprendre au mieux ce chapitre, vous devrez au pralable avoir vu :
- Les gnralits sur les bases de donnes (Chapitre 2)- La cration des diffrents objets de la base vue auparavant (Chapitres 2, 3 et 5)-
Les gnralits sur le code T-SQL et la programmabilit du langage (Chapitre 4)
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
5/18
Dotnet France Association CASANOVA Grgory
5 Les procdures stockes et les fonctions utilisateur [08/07/09]
3 Les procdures stockes3.1 Introduction aux procdures stockes
Les procdures stockes sont des ensembles dinstructions du DML, pouvant tre excuts par
simple appel de leur nom ou par linstruction EXECUTE. Les procdures stockes sont de vritables
programmes qui peuvent recevoir des paramtres, tre excuts distance, renvoyer des valeurs etpossdant leurs propres droits daccs (EXECUTE). Celles-ci sont compiles une premire fois, puis
places en cache mmoire, ce qui rend leur excution plus performante du fait que le code soit
prcompil. Les procdures stockes sont contenues dans la base de donnes, et sont appelable par
leurs noms. Il existe une multitude de procdures stockes pr intgres dans SQL Server lors de
linstallation qui servent principalement la maintenance des bases de donnes utilisateur. Celle-ci
commence toujours par les trois caractres sp_ comme stored procedure. Pour rsumer les
avantages des procdures stockes, nous allons lister leurs utilisations :
- Accroissement des performances.- Scurit dexcution.- Possibilit de manipuler les donnes systme.- Implmente le traitement en cascade et lenchainement dinstructions.
3.2 Gestion des procdures stockes3.2.1 Cration dune procdure stocke
Pour crer une procdure stocke, nous somme oblig de passer par du code T-SQL, cest
pourquoi il est important de bien avoir lu le chapitre 4, traitant des gnralits du code T-SQL. En
revanche, il existe un assistant de gnration automatique de la structure dune procdure stocke.
Nous allons tout dabord tudier la structure gnrale dune procdure stocke avec cette
gnration automatique, puis nous donnerons un exemple, prsent dans le script de la base que
nous utilisons, pour bien comprendre les notions exposes sur les procdures stockes.
Tout dabord,pour gnrer le script automatiquement, tendez les nuds de lexplorateur dobjet
comme ceci :
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
6/18
Dotnet France Association CASANOVA Grgory
6 Les procdures stockes et les fonctions utilisateur [08/07/09]
On peut dj remarquer la prsence dune procdure stocke, Ajout_Client, que vous devez aussi
procder si vous avez tlcharg le script de la base que nous utilisons pour notre cours, la base dedonnes Entreprise. Maintenant, pour crer une nouvelle procdure stocke en gnrant le code
automatiquement, il vous suffit de faire un click droit sur le nud Procdure stockes et de
choisir loption Nouvelle procdure stocke. Une nouvelle fentre de requte souvre dans
SSMS, vous proposant le code pour crer une nouvelle procdure stocke. Le code est le suivant :
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
7/18
Dotnet France Association CASANOVA Grgory
7 Les procdures stockes et les fonctions utilisateur [08/07/09]
Nous allons maintenant dtailler le code.
Nous crons une procdure stocke avec linstruction DDL CREATEPROCEDURE suivie du nom
donner la procdure. Ce nom vous permettra de lappeler et de la reconnaitre dans la base.
Nous devons ensuite prciser les variables que prend en paramtre la procdure stocke, durant son
appel. Ces variables vont nous servir par la suite dans la dfinition des actions que la procdure
stocke fait. Nous pouvons initialiser ou non les variables, le plus important est bien entendu de
donner un nom conventionnel et un type de donne nos variables. Pour plus dinformations sur les
variables, accdez au chapitre 4 sur le T-SQL, la partie Variables.
-- Add the parameters for the stored procedure here=
,=
CREATEPROCEDURE
-- ================================================-- Template generated from Template Explorer using:-- Create Procedure (New Menu).SQL---- Use the Specify Values for Template Parameters-- command (Ctrl-Shift-M) to fill in the parameter-- values below.
---- This block of comments will not be included in-- the definition of the procedure.-- ================================================SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO-- =============================================-- Author: -- Create date: -- Description: -- =============================================
CREATEPROCEDURE-- Add the parameters for the stored procedure here=
,=
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;
-- Insert statements for procedure here
SELECT,ENDGO
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
8/18
Dotnet France Association CASANOVA Grgory
8 Les procdures stockes et les fonctions utilisateur [08/07/09]
Les instructions AS BEGIN et END sont les dlimiteurs du code utiliser par la procdure stocke.
Toutes les instructions comprises entre ces deux mots cls seront prises en compte et excutes par
la procdure stocke.
Maintenant que nous avons prsent la structure gnrale de cration dune procdure stocke,
prenons un exemple concret pour lillustrer. Nous alors nous appuyer sur la procdure stocke dj
cre dans le script de notre base de donnes Entreprise, la procdure stocke Ajout_Client.
Voici le code de cration dune procdure stocke dajout client :
On remarque parfaitement que lon dfinit les paramtres avec des variables en dbut de code,
aprs linstruction CREATEPROCEDURE. Ensuite, entre les blocs AS BEGIN et END, nous faisons un
simple dans la table INSERTINTO, Client et nous passons les paramtres de notre procdure
stocke en valeur, aprs linstruction VALUES. Il est alors possible dutiliser cette procdure stocke
de la faon suivante :
Linstruction EXEC ou EXECUTE permet dexcuter la procdure stocke. Il est alors ncessaire
dentrer des valeurs pour les paramtres indiqus dans la procdure stocke.
Remarque: Il est obligatoire de mettre les arguments de la procdure stocke dans lordre dont ellessont dcrites dans la procdure stocke. Si toute fois vous ne voulez pas les mettre dans lordre, il est
possible de prciser le nom de la variable et dy assigner une valeur de la faon suivante :
EXECdbo.Ajout_Client'NARBONNE','Christophe',33678764534,'17 alle des embrumes',NULL
CREATEPROCEDUREAjout_Client@Nomvarchar(50),@Prenomvarchar(50),@Numerovarchar(50),@Adressevarchar(50),@Mailvarchar(50)ASBEGIN
INSERTINTOClient(Nom_Client,Prenom_Client,Numero_Client,Adresse_Client,Mail_Client)VALUES(@Nom,@Prenom,@Numero,@Adresse,@Mail)
ENDGO
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;
-- Insert statements for procedure here
SELECT,ENDGO
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
9/18
Dotnet France Association CASANOVA Grgory
9 Les procdures stockes et les fonctions utilisateur [08/07/09]
Dans les deux cas, le rsultat est le mme puisque une ligne est ajoute la table Client. Voici le
rsultat :
Aprs la dclaration de cration de procdure, et la dclaration des paramtres, il est possible de
dfinir des options grce une clause WITH ou une clause FOR. Ces options sont les suivantes :
- WITH RECOMPILE : La procdure sera recompile chaque excution.- WITH ENCRYPTION : Permet de crypter le code dans la table systme.- FOR REPLICATION : Permet de prciser que la procdure sera utilise lors de la rplication.
3.2.2 Modifier une procdure stockeLa modification dune procdure stocke ne peut se faire que par linterface graphique en premier
lieu. Pour en modifier une, tendez tous les nuds qui mnent une procdure stocke en
particulier, comme ceci :
EXECdbo.Ajout_Client@Nom='NARBONNE',@Prenom='Christophe',@Numero= 33678764534,@Mail=NULL,@Adresse='17 alle des embrumes'
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
10/18
Dotnet France Association CASANOVA Grgory
10 Les procdures stockes et les fonctions utilisateur [08/07/09]
Pour modifier une procdure stocke en particulier, il vous suffit alors de procder un click droit sur
celle-ci, et de choisir loption Modifier . Une nouvelle fentre de requte apparait, avec le code
que vous aviez entr lors de la premire cration de votre procdure. Modifiez-le alors comme voulu
et r excutez le pour modifier la procdure.
3.2.3 Suppression dune procdure stockeLa suppression dune procdure peut se faire en revanche de deux manires, avec du code ou
linterface graphique. Nous allons prsenter les deux.
- Avec du code, il est trs simple de supprimer une procdure stocke. Regardons la syntaxegnrale :
Dans un premier temps, avec linstruction USE, placez vous dans la base contenant la
procdure stocke supprimer. Si vous ne le faites pas, la procdure stocke sera indiqus
comme un lment non existant dans la base par SQL Server. Utilisez ensuite les mots cl
DROPPROCEDUREsuivit du nom de la procdure stocke pour supprimer celle-ci. Excutez
le code. Vous venez de supprimer votre procdure stocke.
- Avec linterface graphique, nous allons procder comme pour une modification de laprocdure. Etendez dans un premier temps tous les nuds qui mnent la procdure
stocke supprimer, comme ceci :
USEEntrepriseDROPPROCEDUREAjout_Client
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
11/18
Dotnet France Association CASANOVA Grgory
11 Les procdures stockes et les fonctions utilisateur [08/07/09]
Oprez alors un simple click droit sur la procdure stocke choisie, et slectionnez loption
Supprimer . Une nouvelle fentre apparait. Il sagit simplement dune validation de votre choix,
aucune autre option nest disponible. Cliquez sur OK . Votre procdure stocke est supprime.
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
12/18
Dotnet France Association CASANOVA Grgory
12 Les procdures stockes et les fonctions utilisateur [08/07/09]
4 Les fonctions utilisateur4.1 Introduction aux fonctions utilisateur
Les fonctions utilisateurs sont de plusieurs types. Trois pour tre prcis. Il y a les fonctions
scalaires, les fonctions tables en ligne et les fonctions multi-instructions. Une fonction peu accepter
des arguments, et ne peu retourner que deux types de donnes : une valeur scalaire ou une table.
- Les fonctions scalaires retournent, grce au mot cl RETURN, une valeur scalaire. Tous lestypes de donnes peuvent tre retourns par une fonction scalaire hors mis timestamp,
table, cursor, text, ntext et image.
- Les fonctions table ne retourne comme rsultat, quune table, qui est le rsultat duneinstruction SELECT.
Le champ daction dune fonction est vraiment limit puisquil nest possible de modifier que des
objets locaux la fonction. Il nest pas possible de modifier des objets de la base, contenus
lextrieur de la fonction.La cration dune fonction se fait par linstruction DDL CREATE FUNCTION,
habituelle lors de la cration dobjets dans la base. De plus, il existe un multitude de fonctions
systme, utilisable par simple appel de celle-ci. Elles sont disponibles dans lexplorateur dobjets
ainsi.
Dtaillons maintenant tous les types de fonctions existants.
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
13/18
Dotnet France Association CASANOVA Grgory
13 Les procdures stockes et les fonctions utilisateur [08/07/09]
4.2 Cration dune fonctionLa cration dune fonction ce fais quasiment de la mme manire pour les trois types. Nous
allons bien sur prsenter les trois avec trois exemples, simplement nous allons le faire dans la mme
partie. Voici les deux syntaxes principales de cration de fonction (scalaire et table). Nous pouvons y
accder en dployant les nuds de lexplorateur dobjet jusquau nud Fonctions , appliquer un
click droit sur le type de fonction crer et choisir loption Nouvelle fonction .
- Fonction table :
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
14/18
Dotnet France Association CASANOVA Grgory
14 Les procdures stockes et les fonctions utilisateur [08/07/09]
Les fonctions table et table multi-instructions sont diffrentes dans le sens o le format de retour est
diffrent. En effet, la fonction table retourne la solution dun requte SELECT, alors que la fonction
table multi-instruction, retournera une variable de type table, contenant linstruction SELECT opre
par la fonction.
Prenons deux exemples pour mieux comprendre la syntaxe :
- Fonction table simple :
Dans ce cas la, aprs linstruction de cration de fonction, CREATE FUNCTION, on indique
simplement que la fonction retourne une donne de type table avec la clause RETURNSTABLE. Par
CREATEFUNCTIONrecommander_stock(@Idint,@seuilint)RETURNSTABLEASRETURN (SELECT*FROMStockWHEREId_Stock=@IdANDQuantite
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
15/18
Dotnet France Association CASANOVA Grgory
15 Les procdures stockes et les fonctions utilisateur [08/07/09]
la suite, aprs la clause AS, on prcise quelle instruction doit tre retourne dans la valeur de retour
de type table de la fonction.
- Fonction table multi-instruction :
Dans ce cas, en revanche, la valeur de retour est toujours une table, simplement, on donne une
variable ce type et on dfinit les colonnes quelle contient. Les valeurs dfinies par le SELECT y
seront alors contenues. Il faut faire attention, en contrepartie, ce que le nombre de colonnes dans
la variable retourne par la fonction ait le mme nombre de colonnes que le rsultat retourn par
linstruction SELECT.
- Fonction scalaire :
CREATEFUNCTIONtable_multi(@Idint)RETURNS@variableTABLE (Id_Stockint,Quantiteint,Nom_Entreposvarchar(25))ASBEGINSELECT@variable=(SELECTId_Stock,Quantite,Nom_EntreposFROMEntreposEINNERJOINStockSONE.Id_Entrepos=S.Id_EntreposWHERES.Id_Stock=@Id)RETURNEND
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
16/18
Dotnet France Association CASANOVA Grgory
16 Les procdures stockes et les fonctions utilisateur [08/07/09]
Ici, on cr une fonction scalaire que lon reconnait grce linstruction RETURNSint. On se sert
bien entendu de linstruction CREATE FUNCTION suivie du nom de la fonction utiliser et des
paramtres prendre en compte entre parenthses. Les clauses AS BEGIN et END dclarent dans
lordre, le dbut et la fin de la fonction. Cette fonction retourne une valeur qui dsigne le nombre
darticle prsents, pour un stock pass en paramtre.
Dans les fonctions, certaines options sont disponibles, comme dans les procdures stockes. Elles
vont vous permettre certaines actions sur cette fonction, et celle-ci sont appelables directement
aprs la dfinition du type de retour de la fonction :
- WITH SCHEMABINDING : Cette option permet de lier la fonction tous les objets de la baseauxquels elle fait rfrence. Dans ce cas, la suppression et la mise jour de nimporte quel
objet de la base, li la fonction est impossible.
- WITH ENCRYPTION : Permet de crypter le code dans la table systme.4.3 Modification dune fonction
La modification dune fonction nest possible que par une instruction T-SQL DDL, linstruction
ALTERFUNCTION. En effet, dans ce cas l, les commandes CREATEFUNCTIONet ALTERFUNTION
-- ================================================-- Template generated from Template Explorer using:-- Create Scalar Function (New Menu).SQL---- Use the Specify Values for Template Parameters-- command (Ctrl-Shift-M) to fill in the parameter-- values below.
---- This block of comments will not be included in-- the definition of the function.-- ================================================SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO-- =============================================-- Author: -- Create date: -- Description: -- =============================================
CREATEFUNCTION(
-- Add the parameters for the function here
)RETURNSASBEGIN
-- Declare the return variable hereDECLARE
-- Add the T-SQL statements to compute the return value hereSELECT=
-- Return the result of the functionRETURN
ENDGO
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
17/18
Dotnet France Association CASANOVA Grgory
17 Les procdures stockes et les fonctions utilisateur [08/07/09]
auront quasiment la mme fonction, puisque dans les deux cas, tout le corps de la fonction doit tre
rcrit et totalit. Il nest pas possible dajouter ou supprimer seulement une seule ligne dans celui-
ci. Leur seule diffrence rside donc dans le fait que la fonction soit cre ou modifie. Voyons donc
la syntaxe qui permet la modification dune fonction en Transact SQL :
On remarque donc aisment que la seule modification quil existe entre la cration et la modification
dune fonction, rside dans le remplacement du mot cl CREATEFUNCTIONpar le mot cl ALTER
FUNCTION.
4.4 Suppression dune fonctionLa suppression dune fonction peut, comme pour les procdures stockes ou tout autre objet
de la base, tre faite de deux manires. La premire est la mthode graphique. Nous ne dtaillerons
pas cette mthode, puisquelle est la mme pour tout objet de la base. Nous rappellerons juste que il
vous suffit dtendre, dans votre explorateur dobjet, les nuds correspondants au chemin de votre
fonction, de faire un click droit sur celle-ci, et de slectionner Supprimer .
Avec le langage Transact SQL, la mthode est aussi la mme que pour tout autre objet de la base.
Nous utiliserons linstruction DROP et nous ladapterons au cas dune fonction. Voici la mthode type
de suppression dune fonction par code T-SQL :
Linstruction DROPFUNCTION nous permet donc de supprimer une fonction, de nimporte quel type.
Il suffit juste de prciser le nom de la fonction aprs cette instruction.
DROPFUNCTIONnombre_element_stock
ALTERFUNCTIONnombre_element_stock(@Entreposint)RETURNSintASBEGINDECLARE@nbintSELECT@nb=COUNT(Id_Stock)FROMStockWHEREId_Entrepos=@EntreposRETURN@nbENDGO
-
8/3/2019 Les procdures stockes et les fonctions utilisateur
18/18