4. Procédures stockées [3]

PLAN DU COUR
A. Les scripts SQL.
-
Les variables ;
-
les structures de contrôle ;
-
les conditions ;
B. Les curseurs.
C. Les procédures et les fonctions.
-
Programmation des procédures stockées sur le SGBD.
-
Appel des fonctions à partir de requêtes SQL.
-
Les EXCEPTIONS : prédéfinies et utilisateur.
D. Créer des packages sur le SGBD
-
Description du formalisme à respecter.
-
Présentation des packages standard.
-
Programmation des packages.
E. Programmer des déclencheurs
-
Événements qui déclenchent les triggers.
-
Formalisme à respecter.
-
Programmation des Triggers.
-
Test du trigger et correction des erreurs.
Introduction
Pourquoi programmer côté serveur de bases de données ? [1]
Cette programmation a de multiples intérêts :



La performance
Le non redondance de code
Le partage du code avec des applications de nature différente (Web, PDA,
Client/serveur, Reporting, etc.)
La performance
Des traitements qui manipulent les données sont naturellement plus rapides quand ils
s'exécutent dans la base de données.
En effet les gestionnaires de cache et de verrouillage peuvent alors donner tout leur potentiel,
par ailleurs, les aspects réseau sont bien sûr ignorés dans ce cas.
Les temps de réponse a été divisé de manière drastique : de 2 heures à 50 secondes par
exemple.
Le non redondance de code
C’est, au même titre que la redondance des données, un des ennemis du bon développeur.
La centralisation dans le moteur de bases de données de vues et procédures stockées permet
de diminuer cette redondance au minimum et d'augmenter ainsi la vitesse de développement
et la rapidité de maintenance.
A noter que SQLServer2005 avec son intégration du CLR permet d'aller encore plus loin dans
cette chasse à la redondance.
Le partage du code
Il est quasiment obligatoire de travailler avec des logiciels multiples et donc des appels aux
bases de données issus de différentes sources : Un générateur d'états, un serveur Web, une
application client léger, une application client lourd,...
Cette centralisation va là encore, permettre le partage de tout ce code entre différentes
applications.
Que voit-on dans ce cours :
C'est un cours orienté développement et il couvre donc tous les aspects liés à la
programmation du moteur de bases de données; Le langage TRANSACT SQL est décrit dans
tous ces aspects. Tous les types de requêtes sont également étudiés.
De plus un des apports majeurs de 2005 est SQL server a trop avancé: la gestion des erreurs
par try ... catch ; Les développeurs sérieux vont enfin pouvoir écrire du code robuste, avec,
par exemple, la mise en place de pré-conditions dans les procédures stockées.
Les fonctions étendues sont complètement décrites, avec en particulier, l'étude des fonctions
de types table qui sont très utile pour encapsuler des fonctions avancées.
Les triggers sont également étudiés : une nouveauté intéressante de 2005 concerne les triggers
DDL qui sont capables de surveiller les mises à jour du dictionnaire (create table, create
proc,...) je les utilise par exemple pour gérer de manière efficace et automatique la sécurité
des objets dans mes bases de données.
En conclusion
Je lis sur certains forums que le développement côté serveur serait obsolète et que les
nouvelles technologies amèneraient des solutions de remplacement.
Je pense qu'il n'en est rien car de plus en plus d'applications utiliseront des machines à très
faibles capacités de traitement et de stockage : les PDA, les téléphones numériques.
Par ailleurs le partage des données entre application multiples et hétérogènes devient une
évidence : serveur Intranet + Internet + client serveur 2 tiers, + PDA +....
A l'inverse, l'époque où les applications Internet étaient un monde à part me parait révolue et
on va donc bien devoir développer des applications rapides, robustes et à haute disponibilité.
D'ailleurs, la question que je me pose maintenant de plus en plus pour un logiciel est :
où vais-je développer cette partie ? Avec quel composant ?
Et il est clair que, pour une partie manipulant beaucoup de données, la réponse est souvent
dans la base de données.
A l'inverse des interactions homme-machine seront bien évidemment à l'aise côté client
(JavaScript, VBScript,...)
Donc un bon développeur doit utiliser : le bon outil au bon moment.
Bref historique : [2]
En juin 1970 Edgar Frank Codd publia l'article « A Relational Model of Data for Large
Shared Data Banks » ("Un modèle de données relationnel pour de grandes banques de
données partagées") dans la revue Communications of the ACM (Association for Computing
Machinery). Ce [modèle relationnel] basé sur la logique des prédicats du premier ordre a été
rapidement reconnu comme un modèle théorique intéressant pour l'interrogation des bases de
données, et a inspiré le développement du langage Structured English Query Language
("SEQUEL") (langage d'interrogation structuré en anglais), renommé ultérieurement SQL.
Développée chez IBM en 1970 par Donald Chamberlain et Raymond Boyce, cette première
version a été conçue pour manipuler et éditer des données stockées dans la base de données
relationnelle à l'aide du système de gestion de base de données IBM System R. Le nom
SEQUEL, qui était déposé commercialement par l'avionneur Hawker Siddeley pour un
système d'acquisition de données, a été abandonné et contracté en SQL en 1975. SQL était
censé alors devenir un élément clé du futur projet FS.
En 1979, Relational Software, Inc. (actuellement Oracle Corporation) présenta la première
version commercialement disponible de SQL, rapidement imité par d'autres fournisseurs.
SQL a été adopté comme recommandation par l'Institut de normalisation américaine (ANSI)
en 1986, puis comme norme internationale par l'ISO en 1987 sous le nom de ISO/CEI 9075 Technologies de l'information - Langages de base de données - SQL.
La norme internationale SQL est passée par un certain nombre de révisions :
Année Nom
1986
1989
1992
1999
2003
2008
Appellation Commentaires
SQL-86 ou
ISO/CEI 9075:1986
Édité par l'ANSI puis adopté par l'ISO en 1987.
SQL-87
SQL-89 ou
ISO/CEI 9075:1989
Révision mineure.
SQL-1
SQL-92 ou
ISO/CEI 9075:1992
Révision majeure.
SQL2
Expressions rationnelles, requêtes récursives,
déclencheurs, types non-scalaires et quelques
SQL-99 ou
ISO/CEI 9075:1999
fonctions orientées objet (les deux derniers points
SQL3
sont quelque peu controversés et pas encore
largement implémentés).
Introduction de fonctions pour la manipulation
XML,« window functions », ordres standardisés et
ISO/CEI 9075:2003 SQL:2003
colonnes avec valeurs auto-produites (y compris
colonnes d'identité).
Ajout de quelques fonctions de fenêtrage (ntile,
lead, lag, first value, last value, nth value),
ISO/CEI 9075:2008 SQL:2008
limitation du nombre de ligne (OFFSET / FETCH),
amélioration mineure sur les types distincts,
curseurs et mécanismes d'auto incréments.
Comme toute norme internationale publié par l'ISO, ISO/CEI 9075 est disponible à l'achat sur
le site de cette organisation : http://www.iso.org. Le dernier brouillon (working draft) de la
norme est disponible à cette adresse http://www.wiscorp.com/sql_2003_standard.zip sur le
site de http://www.wiscorp.com/about_wiscorp.html.
Rappel sur des définitions : [2]
SQL se décompose en 5 parties, à savoir :

Ordres LDD (langage de définition des données, ou DDL, Data Definition Language) :
permet de modifier la structure de la base de données ;
Exemples :
- Création d'une table :
CREATE TABLE table1
( colonne1 INTEGER,
colonne2 INTEGER,
colonne3 DATE,
colonne4 DATE);
- Modification d'une table :
ALTER TABLE table1 ADD COLUMN colonne5 INTEGER NULL;
ALTER TABLE table1 DROP COLUMN colonne5;
- Suppression d'une table :
DROP TABLE table1;
- Ajout d'une contrainte sur une table :
ALTER TABLE table1
ADD CONSTRAINT ck_jour
CHECK (colonneJour IN ('Lundi',
'Vendredi', 'Samedi', 'Dimanche'));

'Mardi',
'Mercredi',
'Jeudi',
Ordres LMD (langage de manipulation des données, ou DML, Data Manipulation
Language) : permet de consulter / modifier le contenu de la base de données ;
Exemples :
- Requête de base :
SELECT prenom, telephone
FROM
entrants
CROSS JOIN sortants
WHERE nom = 'Dupont';
- Requête plus générique :
SELECT
FROM
WHERE
ORDER
name, service
employees
statut='stagiaire'
BY name;
Exemples de requêtes pour afficher les lignes d'une table TABLE1 de clé primaire colonne1
non présents dans une seconde table TABLE2 :
-- Logique d'exclusion : NOT + enumeration IN
-à eviter pour de bonnes performances
SELECT *
FROM
TABLE1
WHERE TABLE1.colonne1 NOT IN (SELECT colonne2 FROM TABLE2)
-- Logique ensembliste de différence + enumeration IN
SELECT *
FROM
TABLE1
WHERE TABLE1.colonne1 IN (SELECT colonne1
FROM
TABLE2
EXCEPT
SELECT colonne2
FROM
TABLE2)
-- Logique ensembliste de différence + jointure
SELECT *
FROM
TABLE1,
INNER JOIN (SELECT colonne1
FROM
TABLE2
EXCEPT
SELECT colonne2
FROM
TABLE2) TMP
ON TABLE1.colonne1 = TMP.colonne1

Ordres LCD (langage de contrôle des données, ou DCL, Data Control Language) : permet
de gérer les privilèges, c'est-à-dire les utilisateurs et les actions qu'ils peuvent
entreprendre ;

Ordres LCT (langage de contrôle des transactions, ou TCL, Transaction Control
Language, ) : permet de gérer les transactions, c'est-à-dire rendre atomique divers ordres
enchaînés en séquence ;
Exemples :
- Gestion des transactions :
-- lancement d'une transaction avec annulation
START TRANSACTION ISOLATION LEVEL REPEATABLE READ
...
ROLLBACK WORK AND NO CHAIN

SQL procedural : PSM (Persistent Stored Module), CLI (Call Level Interface), Embedded
SQL, … qui est un ensemble d'outils pour développer des procédures, déclencheurs
(triggers) et fonctions utilisateurs (UDF : User Define Function) et pour que SQL
s'interface avec des langages hôtes.
Exemples :
Exemple d'utilisation d'un curseur dans le cadre d'une procédure stockée (PSM) :
DECLARE N INTEGER;
SET N = 1;
FOR C
AS C_USR_MISE_A_JOUR
CURSOR FOR
SELECT USR_ID, USR_NOM
FROM
T_UTILISATEUR_USR
ORDER BY USR_ID
FOR UPDATE OF USR_NOM
DO
IF MOD(N, 2) = 0
THEN
UPDATE T_UTILISATEUR_USR
SET USR_NOM = UPPER(USR_NOM)
WHERE CURRENT OF C_USR_MISE_A_JOUR;
ELSE
UPDATE T_UTILISATEUR_USR
SET USR_NOM = LOWER(USR_NOM)
WHERE CURRENT OF C_USR_MISE_A_JOUR;
END IF
END FOR
Dans cet exemple, la colonne USR_NOM de la table T_UTILISATEUR_USR est mise à jour
en majuscule pour les lignes de position ordinale paires et en minuscule pour les autres.
A. Écrire des scripts dans le langage procédural du SGBD [3]
I. Les variables
1. Identifiant
Les identifiants (nom des objets) SQL :





ne peuvent dépasser 128 caractères.
Ils doivent commencer par une lettre ou un "underscore".
Les caractères spéciaux et le blanc ne sont pas admis.
On se contentera d'utiliser les 37 caractères de base : ['A'..'Z', '0'..'9', '_']
La casse n'a pas d'importance.
Le symbole @ commence le nom de toute variable.
Le symbole dédoublé @@ commence le nom des variables globales du SGBDR.
Le symbole # commence le nom de toute table temporaire (utilisateur)
Exemple:
SELECT CURRENT_TIMESTAMP as DateTime
INTO #tempT
SELECT *
FROM #tempT
Le symbole dédoublé ## commence le nom de toute table temporaire globale.
Conséquence : la libération des tables temporaires est consécutive à la libération des
utilisateurs.
Nota : SQL Server utilise une base particulière "tempDB" pour stocker la définition des
objets temporaires.
Attention : pour des raisons de performances il est fortement déconseillé d'utiliser le
SELECT … INTO … qui n'est d'ailleurs pas normalisé.
Remarque :
- Un identifiant est dit "qualifié" s'il fait l'objet d'une notation pointée définissant le serveur, la
base cible et l'utilisateur :
serveur.base_cible.utilisateur.objet
Exemple :
SELECT * FROM MonServeur.MaBase.MonUser.MaTable
Attention :
- Ceci suppose que les serveurs soient connus les uns des autres (procédure de
"linkage"). On peut ignorer le serveur, comme l'utilisateur, dans ce cas c'est
l'utilisateur courant qui est pris en compte :
base_cible..objet
Et au sein de la base :
Objet
-
Un identifiant ne doit pas être un mot clef de SQL, ni un mot clef de SQL Server
ni même un mot réservé. Dans le cas contraire il faut utiliser les guillemets comme
délimiteur.
2. Variables
-
Les types disponibles sont ceux de SQL :
Bit, int, smallint, tinyint, decimal, numeric, money,
smallmoney,
float,
real,
datetime,
smalldatetime,
timestamp,
uniqueidentifier,
char,
varchar,
text,
nchar, nvarchar, ntext, binary, varbinary, image.
Auxquels il faut ajouter le type : Cursor Que nous verrons plus en détail.
-
Les valeurs de types chaînes doivent être délimitées par des apostrophes.
Une variable est déclarée à tout endroit du code par l'instruction DECLARE :
Exemple :
DECLARE @maChaine char(32)
-
Une variable est assignée par l'instruction SET :
Exemple :
SET @maChaine = 'toto'
Remarques :
 Avant toute assignation, une variable déclarée est marquée à NULL ;
 il n'existe pas de type "tableau" dans le langage Transact SQL. Cependant, une table
temporaire suffit à un tel usage ;
 l'ordre SQL SELECT peut aussi servir à assigner une ou plusieurs variables, mais dans
ce dernier cas il faut veiller à ce que la réponse à la requête ne produise qu'une seule
ligne ;
 la notion de constante n'existe pas.
Exemple:
SELECT @NomTable = 'TABLES', @NombreLigne = count (*)
FROM INFORMATION_SCHEMA.TABLES
Attention : dans le cas d'une requête renvoyant plusieurs lignes, seule la valeur de la dernière
ligne sera récupéré dans la variable.
ASTUCE : si vous désirez concaténer toutes les valeurs d'une colonne dans une seule
variable, vous pouvez utiliser la construction suivante :
DECLARE @Colonne varchar(8000)
SET @Colonne = ''
SELECT @Colonne = @Colonne +COALESCE (TABLE_NAME + ', ',
'')
FROM INFORMATION_SCHEMA.TABLES
II. Les structures de contrôle et conditions
- La structure :
BEGIN
...
END
Permet de définir des blocs d'instructions.
- Les branchements possibles sont les suivants :
IF [NOT] condition instruction [ELSE instruction]
IF [NOT] EXISTS
instruction]
(requête
select)
instruction
[ELSE
WHILE condition instruction
GOTO etiquette
WAITFOR [DELAY | TIME] temps
Exemple :
Waitfor time ‘18h20’
- Les instructions:
BREAK
Permettent respectivement
autoritairement une boucle.
d'interrompre
ou
de
continuer
CONTINUE
Remarques :



il n'y a pas de THEN dans le IF du Transact SQL, ni de DO dans le WHILE
une seule instruction est permise pour les structure IF et WHILE, sauf à mettre en
place un bloc d'instructions à l'aide d'une structure BEGIN / END
un branchement sur étiquette se précise avec en suffixant l'identifiant de l'étiquette
avec un caractère deux-points ':'
III. Quelques fonctions de base :
On cite ci-après trois fonctions fréquemment utilisées :
USE est une instruction permettant de préciser le nom de la base de données cible. En
effet il arrive que l'on soit obligé de travailler depuis une base pour en traiter une
autre. C'est le cas notamment lorsque l'on veut créer une base de données et y
travailler sur le champ.
La seule base en standard dans MS SQL Server est la base "master" qui sert de matrice
à toutes les bases créées. Elle contient les procédures inhérentes au serveur mais aussi
celles inhérentes aux bases nouvellement créées, ainsi que les métas données
spécifiques de la nouvelle base (tables dont le nom commence par "syS...").
Exemple :
Création d'une base de données depuis la base MASTER et création d'une table dans cette
nouvelle base :
CREATE DATABASE NEW_MABASE
ON
( NAME = 'NEW_MABASE_DB',
FILENAME = 'C:\MaBase.DATA',
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 50 )
LOG ON
( NAME = 'NEW_MABASE_LOG',
FILENAME = 'C:\MaBase.LOG',
SIZE = 5,
MAXSIZE = 25,
FILEGROWTH = 5 )
GO
USE NEW_MABASE
GO
CREATE TABLE T_CLIENT
(CLI_ID INTEGER IDENTITY NOT NULL PRIMARY KEY,
CLI_NOM VARCHAR(32))
GO
PRINT est une instruction permettant de générer une ligne en sortie de procédure.
Elle doit être réservée plus à la mise au point des procédures stockées que pour une
utilisation en exploitation.
EXEC est une instruction permettant de lancer une requête ou une procédure stockée
au sein d'une procédure ou un trigger. La plupart du temps il n'est pas nécessaire
d'utilise l'instruction EXEC, si l'intégralité de la commande SQL ou de la procédure à
lancer est connu. Mais lorsqu'il s'agit par exemple d'un ordre SQL contenant de
nombreux paramètres, alors il est nécessaire de le définir dynamiquement.
- Commentaires
Comme dans le cadre du langage SQL de base, pour placer des commentaries il suffit
d'utiliser les marqueurs suivants :
Début
-/*
Fin
*/
Commentaire
pour une ligne de commentaire
pour plusieurs ligne de commentaire
- Variable de retour
Toute procédure stockée renvoie une variable de type entier pour signaler son état. Si cette
variable vaut 0, la procédure s'est déroulée sans anomalie. Tout autre valeur indique un
problème. Les valeurs de 0 à -99 sont réservées et celles de 0 à -14 sont prédéfinies. Par
exemple la valeur -5 signifie erreur de syntaxe.
On peut assigner une valeur de retour de procédure à l'aide de l'instruction RETURN :
RETURN 1445
- Variables "système"
SQL Server défini un grand nombre de "variables système" c'est à dire des variables définies
par le moteur. En voici quelques unes :
Variable
@@connections
@@datefirts
@@error
@@fetch_status
Description
nombre de connexions actives
premier jour d'une semaine (1:lundi à 7:dimanche)
code de la dernière erreur rencontrée (0 si aucune)
état d'un curseur lors de la lecture (0 si lecture proprement exécutée)
dernière valeur insérée dans une colonne auto incrémentée pour
@@identity
l'utilisateur en cours
@@max_connections nombre maximums d'utilisateurs concurrents
@@procid
identifiant de la procédure stockée en cours
@@rowcount
nombre de lignes concernées par le dernier ordre SQL
@@servername
nom du serveur SGBDR courant
@@spid
identifiant du processus en cours
@@trancount
nombre de transaction en cours
TP 0 :
Ecrire un programme qui affiche les diviseurs du nombre 55 en les stockant dans une table
temporaire et ensuite les affichant !!
B. Les curseurs [4]
I. Définition
Les curseurs sont des mécanismes de mémoire tampons permettant d'accéder aux données
renvoyées par une requête et donc de parcourir les lignes du résultat.
Un curseur se définit dans une instruction DECLARE possédant une requête de type
SELECT. Il convient de définir pour chaque colonne renvoyé une variable de type approprié.
Pour lancer la requête associée (et donc placer les données dans les buffers appropriés) il faut
utiliser l'instruction OPEN.
Un curseur doit être refermé avec l'instruction CLOSE.
Pour libérer la mémoire utilisée par un curseur, il faut utiliser l'instruction DEALLOCATE.
Pour lire les données de la ligne courante et les associées aux variables du curseur il faut
utiliser l'instruction FETCH.
Par défaut l'instruction FETCH navigue en avant d'une ligne à chaque lecture dans l'ensemble
des données du résultat. Pour naviguer différemment, on peut qualifier le FETCH avec les
mots clef NEXT, PRIOR, FIRST, LAST, ABSOLUTE n et RELATIVE n, mais il faut
avoir déclaré le curseur avec l'attribut SCROLL, donc on aura :


Aller à la première ligne : FETCH FIRST FROM curseur_nom
Aller à la dernière ligne : FETCH LAST FROM curseur_nom




Aller à la ligne suivante : FETCH NEXT FROM curseur_nom
Aller à la ligne précédente : FETCH PRIOR FROM curseur_nom
Aller à la ligne X : FETCH ABSOLUTE ligne FROM curseur_nom
Aller à X lignes plus loin que l'actuelle : FETCH RELATIVE ligne FROM
curseur_nom
Enfin la variable @@fetch_Status permet de savoir si la dernière instruction FETCH passée
s'est correctement déroulée (valeur 0), ce qui permet de tester si l'on est arrivé en fin de
parcours de l'ensemble de données.
Une boucle traditionnelle de manipulation d'un curseur prend la forme suivante :
-- déclaration des variables de colonnes pour le curseur
DECLARE @Col1 Type1, @Col2 Type2, @Col3, Type3...
-- declaration du curseur
DECLARE MyCursor CURSOR
FOR
SELECT COL1, COL2, COL3 …
FROM MyTable
-- ouverture du curseur
OPEN MyCursor
-- lecture du premier enregistrement
FETCH MyCursor INTO @Col1, @Col2, @Col3...
-- boucle de traitement
WHILE @@fetch_Status = 0
BEGIN
/*traitement*/
-- lecture de l'enregistrement suivant
FETCH MyCursor INTO @Col1, @Col2, @Col3...
END
-- fermeture du curseur
CLOSE myCursor
-- libération de la mémoire
DEALLOCATE myCursor
On constate que l'instruction FETCH apparaît deux fois. Une première fois avant la boucle
WHILE une seconde fois à l'intérieur et en dernière ligne de la boucle WHILE.
C'est la façon la plus classique et la plus portable d'utiliser des curseurs.
NOTA : les performances sont en baisse lorsque l'on utilise tout autre déplacement que le
NEXT.
II. Fonctionnement des curseurs : [5]
Pour utiliser les curseurs, suivez les étapes suivant :
1. Déclarez le curseur. Cela inclut l'instruction SELECT qui génère un jeu de résultats et
définit les caractéristiques du curseur, comme la possibilité de mettre à jour ses lignes ;
2. Exécutez l'instruction OPEN pour générer le jeu de résultats et remplir le curseur ;
3. Récupérez les lignes du jeu de résultats du curseur. L'opération permettant de récupérer une
ligne ou un ensemble de lignes d'un curseur s'appelle un fetch. L'exécution d'une série
d'opérations " fetch " s'appelle un défilement ;
4. Utilisez les valeurs ou exécutez des opérations sur la ligne à la position courante du
curseur ;
5. Fermez et libérez le curseur.
TP 1 :
Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d'une
promotion d'étudiants :
ETUDIANT (N°Etudiant, Nom, Prénom)
MATIERE (CodeMat, LibelléMat, CoeffMat)
EVALUER (N°Etudiant*, CodeMat*, Date, Note)
Remarque :
Les clés primaires sont soulignées et les clés étrangères sont marquées par *
Questions :
1 – créer une base de données Etudiants.
2 – Remplir chaque table par 3 enregistrements.
3 – Afficher la liste des étudiants, leurs notes par date en utilisant un curseur qui réalisant
la jointure des 3 tables.
Remarque :
Il est possible d'effectuer des mises à jour de données via des curseurs, mais cela n'est pas
conseillé. Dans ce cas il faut préciser en fin de déclaration du curseur : FOR UPDATE OF
liste_colonne
La syntaxe complète SQL 2 de la déclaration d'un curseur dans MS SQL Server est :
DECLARE nom_curseur CURSOR
FOR requête_select
FOR {READ ONLY | UPDATE [OF colonne1 [, colonne2...]]}]
Pour mettre à jour des données dans un curseur, il faut le déclarer avec la clause FOR
UPDATE et utiliser un ordre UPDATE portant sur la table visé avec une clause WHERE dans
laquelle on référence la ligne courante du curseur. Bien entendu la requête située dans le
curseur ne peut porter que sur une seule table !
Exemple :
-- déclaration d'un curseur pour mise à jour
DECLARE NomCurseur CURSOR
FOR
SELECT ...
FROM LaTable
...
FOR UPDATE
...
-- exécution de la mise à jour sous curseur
UPDATE LaTable
SET Colonne1 = ..., Colonne2 = ...
WHERE CURRENT OF NomCurseur
III. Types de curseurs
SQL Server prend en charge quatre types de curseurs :
- les curseurs vers l'avant uniquement,
- les curseurs statiques,
- les curseurs dynamiques,
- les curseurs à jeu de clés (commandés par keyset).
Ces différents types de curseurs n'ont pas tous la même capacité à détecter les modifications
du jeu de résultats et n'utilisent pas tous les mêmes ressources, en particulier au niveau de la
mémoire et de l'espace dans la base de données tempdb.
Si, alors qu'un curseur est ouvert, les données de la table sur laquelle est basé le curseur sont
modifiées par d'autres connexions, les différents types de curseurs ne refléteront pas tous ces
modifications.
Le tableau suivant montre comment le type de curseur choisi détermine si le jeu de résultats
du curseur reflétera les modifications apportées à l'appartenance, à l'ordre ou aux valeurs des
données de la table sous-jacente :
Type du curseur
Appartenance
Ordre
Valeurs
Vers l'avant uniquement
Dynamique
Dynamique
Dynamiques
Statique
Fixe
Fixe
Fixes
Dynamique
Dynamique
Dynamique
Dynamiques
Jeu de clés
Fixe
Fixe
Dynamiques
Vers l'avant uniquement
Un curseur vers l'avant uniquement prend uniquement en charge la récupération (Fetch)
séquentielle des lignes, depuis le début jusqu'à la fin du curseur. Il n'effectue aucune
extraction dans la base de données tant qu'aucune ligne n'a été récupérée à partir du curseur.
Les effets de toutes les instructions INSERT, UPDATE et DELETE exécutées par toute autre
connexion avant la récupération d'une ligne sont visibles quand celle-ci est récupérée.
Statique
Un curseur statique fixe un jeu de résultats à l'ouverture du curseur, ce jeu de résultats n'étant
accessible qu'en lecture seule. Par conséquent, il n'est pas possible de mettre à jour les tables
sous-jacentes d'un curseur statique par son intermédiaire. Le jeu de résultats est stocké dans
tempdb à l'ouverture du curseur statique. Les modifications apportées par d'autres connexions
après l'ouverture du curseur ne sont jamais reflétées par celui-ci. Les curseurs statiques sont
également dénommés non sensitifs ou de capture instantanée.
Dynamique
À l'opposé des curseurs statistiques, un curseur dynamique reflète toutes les modifications
apportées aux valeurs, à l'ordre et à l'appartenance des lignes du jeu de résultats lorsque vous
parcourez celui-ci. Les effets de toutes les instructions UPDATE, INSERT et DELETE
exécutées par n'importe quel utilisateur sont visibles lorsque les lignes sont récupérées via le
curseur. Vous pouvez récupérer des lignes à partir de n'importe quelle position dans le
curseur. Les curseurs dynamiques sont également dénommés curseurs sensitifs.
Jeu de clés
Les curseurs à jeu de clés sont commandés par un ensemble unique d'identificateurs (clés),
appelé un jeu de clés. Ces clés sont construites à partir d'un ensemble de colonnes identifiant
de manière unique un ensemble de lignes dans le jeu de résultats. Ce jeu de clés est l'ensemble
des valeurs de clé de toutes les lignes correspondant à l'instruction SELECT au moment de
l'ouverture du curseur, et l'appartenance ainsi que l'ordre des lignes ne sont jamais modifiés.
Les insertions et les mises à jour réalisées par l'utilisateur par l'intermédiaire du curseur sont
visibles.
Remarque : Les curseurs dont les caractéristiques en matière d'appartenance, d'ordre et de
valeurs sont fixes s'exécutent plus rapidement que les curseurs ayant des caractéristiques
dynamiques, mais leur ouverture peut prendre plus de temps.
Pour déterminer le type de curseur à employer, prenez en compte les informations suivantes :
•Les curseurs dynamiques s'ouvrent plus rapidement que les curseurs statiques ou à jeu
de clés.
•Dans les jointures, les curseurs à jeu de clés et les curseurs statiques peuvent être plus
rapides que les curseurs dynamiques.
•Les curseurs statiques et les curseurs à jeu de clés augmentent l'utilisation de la base
de données tempdb. Les curseurs statiques de serveur construisent la totalité du
curseur dans tempdb. Par conséquent, si un grand nombre d'utilisateurs ouvrent chacun
un curseur statique ou un curseur à jeu de clés, il est possible que l'espace réservé à
tempdb devienne insuffisant.
C. Les procédures et les fonctions
I. UDF : fonction utilisateur
Une UDF, autrement dit User Define Function ou Fonction Définie par l'Utilisateur est
une fonction que le concepteur de la base écrit pour des besoins de traitement au sein des
requêtes et du code des procédures stockées ou des triggers.
Elle fait donc partie intégrante de la base ou elle est considérée comme un objet de la base au
même titre qu'une table, une vue, un utilisateur ou une procédure stockée.
Il existe deux grands types de fonctions : celles renvoyant une valeur et celles renvoyant un
jeu de données (table).
1. Fonction renvoyant une valeur
La syntaxe est assez simple :
CREATE FUNCTION [ utilisateur. ] nom_fonction
( [ { @parametre1[AS] type [ = valeur_défaut ] } [ , @parametre2 ...
] ] )
RETURNS type_résultant
[ AS ]
BEGIN
/*code*/
RETURN valeur_résultante
END
Exemple : calcul de la date de pâque pour une année donnée :
CREATE FUNCTION FN_PAQUE (@AN INT)
RETURNS DATETIME
AS
BEGIN
IF @AN IS NULL
RETURN NULL
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
SET
SET
SET
SET
SET
@G
@C
@H
@I
@J
@G INT
@I INT
@J INT
@C INT
@H INT
@L INT
@JourPaque INT
@MoisPaque INT
@DimPaque DATETIME
=
=
=
=
=
@AN % 19
@AN / 100
(@C - @C / 4 - (8 * @C + 13) / 25 + 19 * @G + 15) % 30
@H-(@H/28)*(1-(@H / 28)*(29/(@H + 1))*((21-@G)/11))
(@AN + @AN / 4 + @I + 2 - @C + @C / 4) % 7
SET @L = @I - @J
SET @MoisPaque = 3 + (@L + 40) / 44
SET @JourPaque = @L + 28 - 31 * (@MoisPaque / 4)
SET @DimPaque = CAST(CAST(@AN AS VARCHAR(4))
+
CASE WHEN @MoisPaque < 10 THEN '0' + CAST(@MoisPaque AS CHAR(1))
ELSE CAST(@MoisPaque AS CHAR(2))
END
+
CASE WHEN @JourPaque < 10 THEN '0' + CAST(@JourPaque AS CHAR(1))
ELSE CAST(@JourPaque AS CHAR(2))
END
AS DATETIME)
RETURN @DimPaque
END
On utilise une telle fonction au sein d'une requête, comme une fonction SQL de base, à
ceci près que, pour une raison obscure, il faut la faire précéder du nom du propriétaire.
Exemple d’appel :
SELECT dbo.FN_PAQUE(2004) AS PAQUE_2004
PAQUE_2004
----------------------2004-04-11 00:00:00.000
2. Fonction renvoyant une table
La syntaxe se base sur une variable "table". En voici la syntaxe :
CREATE FUNCTION [utilisateur.] nom_fonction
([{@parametre1[AS]type[ = valeur_défaut]}[,@parametre2 [AS] type... ] ] )
RETURNS nom_table_résultant TABLE < definition_de_table >
[ AS ]
BEGIN
/* code*/
RETURN
END
Voici un exemple, qui construit une table d'entier limité à MaxInt et comprenant ou non le
zéro (entiers naturels) :
CREATE FUNCTION FN_ENTIERS (@MAXINT integer, @NATUREL bit = 0)
RETURNS @integers TABLE(N int PRIMARY KEY NOT NULL)
AS
BEGIN
DECLARE @A INT
DECLARE @T TABLE (Nt int)
SET @A = 0
-- insertion des 10 premiers chiffres de 0 à 9
WHILE @A < 10
BEGIN
INSERT INTO @T VALUES (@A)
SET @A = @A + 1
END
SET @A = @A -1
-- si @A est supérieur à 9, alors supprimer les valeurs en trop
IF @A > @MAXINT
DELETE FROM @T WHERE Nt > @MAXINT
ELSE
INSERT INTO @T SELECT DISTINCT 1*T1.Nt+10*T2.Nt+100*T3.Nt+1000*T4.Nt
FROM @T AS T1 CROSS JOIN @T AS T2 CROSS JOIN @T AS T3 CROSS JOIN @T
AS T4
WHERE 1*T1.Nt+10*T2.Nt+ 100*T3.Nt + 1000*T4.Nt BETWEEN 10 AND @MAXINT
-- s'il s'agit d'entiers naturels, supprimer le zéro
IF @NATUREL = 1 DELETE FROM @T WHERE N = 0
-- insertion dans la variable de retour
INSERT INTO @integers SELECT DISTINCT N FROM @T
RETURN
END
SELECT *
FROM dbo.FN_ENTIERS (13, 1)
4. Procédures stockées [3]
Le but d'une procédure stockée est triple :



étendre les possibilités des requêtes, par exemple lorsqu'un seul ordre INSERT ou
SELECT ne suffit plus
faciliter la gestion de transactions
permettre une exécution plus rapide et plus optimisée de calculs complexes ne portant
que sur des données de la base
Dans ce dernier cas la méthode traditionnelle nécessitait de nombreux aller et retour entre le
client et le serveur et congestionnait le réseau.
Une procédure stockée est accessible par l'interface de l'Entreprise Manager. Elle peut aussi
être créée par l'analyseur de requête.
En créant une nouvelle procédure stockée on se trouve devant une fenêtre de définition de
code :
ATTENTION :
Les procédures stockées (v7) sont limitées à :
128 Mo de code
1024 paramètres en argument (y compris curseurs)
1. Entête de procédure
Elle ne commence toujours pas les mots clef CREATE PROCEDURE suivi du nom
que l'on veut donner à la procédure stockée.
Les paramètres et leur type, s'il y en as suivent le nom.
L'entête se termine par le mot clef AS.
Exemple :
CREATE PROCEDURE SP_SEARCH_STRING_ANYFIELD
@TableName Varchar(128),
-- nom de la table passé en argument
@SearchWord Varchar(32)
-- mot recherché
AS
...
2. Paramètres, variables de retour et ensemble de données
Une procédure stockée peut accepter de 0 à 1024 paramètres (arguments). Elle a toujours une
valeur de retour qui par défaut est le code d'exécution (entier valant 0 en cas de succès). Elle
peut retourner des lignes de table comme une requête.
Pour déclarer les paramètres il faut les lister avec un nom de variable et un type. Par
défaut les paramètres sont des paramètres d'entrée.
Comme pour toutes les listes le séparateur est la virgule.
On peut déclarer des valeurs par défaut et spécifier si le paramètre est en sortie avec le
mot clef OUTPUT.
Exemple :
CREATE PROCEDURE SP_SYS_DB_TRANSACTION
@TRANS_NUM INTEGER, -- numéro de la transaction concernée
@OK BIT = 0 OUTPUT -- retour 0 OK, 1 problème
AS
...
Pour récupérer la valeur de d'un paramètre OUTPUT il faut préciser une variable de
récupération de nature OUTPUT dans le lacement de l'exécution.
Exemple :
DECLARE @RetourOK bit
EXEC(SP_SYS_DB_TRANSACTION 127, @RetourOK OUTPUT)
SELECT @RetourOK
Trucs :
Un paramètre de type CURSOR (curseur) est un cas particulier et ne peut être utilisé
qu'en sortie et conjointement au mot clef VARYING qui spécifie que sa définition
précise n'est pas connue au moment de la compilation (le nombre et le type des
colonnes n'est pas défini à cet instant).
CREATE PROCEDURE SEARCH_TEXT @resultTable CURSOR VARYING OUTPUT
AS
Un tel paramètre peut être réutilisé dans une autre procédure stocké ou dans un trigger.
Une procédure stockée peut en outre renvoyer un jeu de résultat sous la forme d'un
ensemble de données à la manière des résultats de requête.
CREATE PROCEDURE SP_LISTE_CLIENT
@CLI_ID_DEBUT INTEGER, @CLI_ID_FIN INTEGER
AS
SELECT
UNION
SELECT
FROM
WHERE
UNION
SELECT
'-- début de liste client --'
ALL
CLI_NOM
T_CLIENT
CLI_ID BETWEEN @CLI_ID_DEBUT AND @CLI_ID_FIN
ALL
'-- fin de liste client --'
Bibliographie :
[1] :
http://www.dominiqueverriere.com/Cours/Lists/Categories/Category.aspx?Name=133%20%e
2%80%93%20Programmation%20de%20SQLServer
[2] : http://www.recherche.fr/encyclopedie/SQL
[3] : http://sqlpro.developpez.com/cours/sqlserver/transactsql/
[4] : http://sqlpro.developpez.com/cours/sqlserver/transactsql/#L4.7
[5] : http://www.microsoft.com/france/technet/produits/sql/7.0/techn_interro.mspx