Introduction :
Définition : c'est un ensemble de commande et requêtes SQL compilés et stockées sur la base de données
Interet : tout est compilé
La requête est sur le serveur
Avantages : plus rapide et maintenance plus facile
le select à l'intérieur à 2 fonctions :
_ affectation d'une variable
_ sert de valeur de retour
Création d'une procédure stockée :
commande :
CREATE PROCEDURE nom_de_la_procedure ( @champ1 type, @champ2 type, ... )La déclaration des paramètres se fait avec "@", on peut rajouter le mot clé "
output" après le type pour spécifier une variable de sortie ( passage par référence ).
Pour déclarer des variables à l'intérieur des procédures stockées la commande est la suivante :
AS DECLARE @champ TYPE // à faire juste après la parenthèse fermant et avant le BEGIN.
Par convention pour les noms des procédures stockées on commence les noms de la façon suivante :
Upd_nom = pour faire un Update
Sel_nom = pour faire un Select
Del_nom = pour faire un Delete
Iud_nom = pour insert, update, delete
Ensuite, pour commencer la procédure stockée :
BEGIN ...
...
...
...
END // pour finir la procédure
Pour détruire une procédure stockée :
DROP PROCEDURE nom_de_la_procedure
les variables systèmes
@@rowcount = nombre de valeur du dernier select
@@error = code d'erreur quand une requète plante
@@fetch_status = position du pointeur sur un curseur
@@langid = langue utilisé ( français, anglais ... )
@@language = FRENCH/ENGLISH ... c'est le libellé de la langue
@@max_connection = nombre max de connexion autorisé
@@servername = nom du serveur
@@spid = numéro qui référence une connexion
@@version = version de sybase
@@proc_id = numero de la procédure stockée
les curseurs
curseurs ou pointeurs sur tableaux
- DECLARE nomcurseur CURSOR FOR
select x,y,z ....- OPEN nomcurseur // compile et execute
- FETCH nomcurseur INTO @x, @y, @z, // se positionne sur l'enregistrement suivant, les variables doivent être du même nombre et du même type.
...
While ( @FETCH_STATUS != 0 ) // on teste la variable système, tant que cette valeur est différente de 0, c'est qu'il y a encore des enregistrement
BEGINFETCH nomcurseurEND- CLOSE nomcurseur // ferme le curseur OBLIGATIORE !
- DEALLOCATE nomcurseur // desalloue le curseur => le curseur n'existe plus à ce moment là.
commandes utiles
exemple :
...
goto Fin ..
...
...
....
iFin
...
....
- EXEC ou EXECUTE pour exécuter une procédure stockée
exemple :
EXEC ou EXECUTE nom_de_la_procedure parametre1, parametre2, parametre3 ...le nombre de parametre doit correpondre parfaitement ainsi que le type.
- WAITFORTIME"heured'execution" pour executer une procedure en différé
- WAITFORDATETIME"heure d'execution" pareil
- WAITFORDELAY"seconde" pour attendre X secondes avant execution
- USE pour changer de base de donnée et ainsi utiliser des table dans d'autres base de données
exemple : USE formation
...
// utilisation des tables de formation
...
USE Ordre
...
// utuilisatiion des tables de Ordre
...
- GRANT pour attribuer des droits d'accès
exemple :
GRANT [XXX] ON [YYY] TO [ZZZ]où :
XXX = select, insert, delete, execute, all ou update
YYY = nom d'une table ou d'une procédure stockée
ZZZ = user, public, group
Attention !
_ pas de GRANT execute sur table
_ par de GRANT insert sur procedure
- REVOKE permet d'enlever des droits
exemple : REVOKE [XXX] ON [YYY] FROM [ZZZ]
les transactions
définition : c'est un ensemble des requête SQL que je veux exécuter et surtout qu'elle se fasse si toutes les commandes ont réussi.
- BEGIN TRAN // début de la transaction
insert .........
if(
@@error != 0)
ROLLBACK else // teste s'il y a des erreurs
delete ......
if(
@@error != 0)
ROLLBACK else // teste s'il y a des erreurs
update ........
if(
@@error != 0)
ROLLBACK else // teste s'il y a des erreurs
delete ........
if(
@@error != 0)
ROLLBACK else
COMMIT // tout est OK donc on effectue effectivement toutes les commandes
END
attention :
ROLLBACK et COMMIT remontent jusqu'à BEGIN TRAN
Les intructions particulières
- Set RowCount X // toutes les lignes suivantes ne renvoyent que X enregistrements
- Set RowCount 0 // annule le Set RowCount X
- TimeStamp // type de variable
_sert à voir si un enregistrement a été modifié par un autre pour vérifier qu'il n'y a pas téléscopage.
_ très utile pour les réservations de billets par exemple
_ valeur mis à jour automatiquement si modification ( insert, delete, update )
_ faire un test sur cette valeur avant de faire des modifs
Exemples
*************************************************************
create procedure iud_deptno
(
@mode char(1),
@deptno int output,
@name varchar(32),
@loc varchar(6),
@return_code int output
)
as
begin // début de la procédure stockée
if upper(@mode)='T'
begin
select @detpno = isnull(max(deptno),0)+1 from dept
insert into dept
( deptno, name, loc )
values ( @deptno, @name,@loc )
select @return_code = @@rowcount
end
if upper(@mode)='U'
begin
update dept
set name = @name,
loc = @loc
where deptno = @deptno
select @return_code = @@rowcount
end
if upper(@mode)='D'
begin
delete dept
where deptno = @deptno
select @return_code = @@rowcount
end
end
*************************************************************