Tutorial procédures stockées

[olibara]

Bonjour

Je sais a peine creer un bete select sous la forme de procédure stockée mais j'aimerais apprendre plus sur la syntaxe et les possibilités de celles-ci. Je viens d'essayer dans le forum les mots clef "procedure" "tutorial procédure" mais ca ne donne pas grand chose, sinon rien !

J'ai trouvé plusieurs chose expliquant en long et en large le principe des procédures stoquées mais PAS tous les détail d'instruction et de syntaxe
Un peu comme si on expliquait ce qu'est un livre sans expliquer l'alphabet le vocabulaire la syntaxe et la grammaire


Quelqu'un connait-il donc un lien qui me permettrait d'apprendre un peu plus sur la syntaxe et les subtilités de programmation des procédures stockées


ET tant qu'on y est la différence entre les fonctions et les procédures stockées

Et pourquoi pas les trigger !

Merci beaucoup !

[elsuket]

Bonjour,

Pour ce qui est de la syntaxe, vous pouvez voir la documentation, consultable ou téléchargeable à partir des liens de ma signature.
En particulier :
- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE TRIGGER

Tous les détails vous sont donnés, syntaxe et utilisation (directement ou dans les liens à la fin des pages).

Pour faire condensé :

- Une procédure stockée contient une ou plusieurs instructions DML (SELECT, INSERT, UPDATE, DELETE).
Elle peut retourner des lignes, une valeur (paramètre marqué OUTPUT, ou rien du tout.
Elle peut prendre en entrée des paramètres dont la valuation est optionnelle, ce qui permet de faire des appels sans passer des valeurs pour tous les paramètres.

J'ai écrit un billet sur les autres avantages des procédures stockées.

- Il existe 3 types de fonctions : scalaire (ne retournant qu'une valeur), de table en ligne (une seule instruction retourne un ensemble de données), ou de table à plusieurs instructions.
Les fonctions scalaires sont assez contre-performantes, qu'elles soient avany ou après le WHERE d'une requête, puisqu'elles sont appelées pour chaque ligne du jeu de données.
On peut les appeler dans un SELECT avant le FROM.

Les fonctions de table vous permettent de retourner plusieurs lignes.

Dans les deux cas, vous pouvez appeler les fonctions en passant des colonnes en paramètre.

Vous pouvez aussi utiliser les fonctions scalaires pour spécifier des contraintes de type CHECK ou DEFAULT, ou ajouter une colonne calculée.

- Les triggers sont un cas très particulier de procédures stockées : ils ne prennent aucun paramètre, et sont attachés à une table (trigger DML), à une base de données ou une instance SQL Server (trigger DDL, depuis SQL Server 2005)

Les triggers DML exposent les tables virtuelles inserted et deleted qui vous permettent d'accéder aux lignes que vous venez d'ajouter (inserted), de supprimer (deleted) ou aux deux dans le cas d'un UPDATE.
Ils sont déclenché sur l'occurrence de l'exécution d'une instruction DML sur la table à laquelle ils sont attachés.

Les triggers DDL vous permettent d'auditer et éventuellement d'engendrer des actions suivant l'évenement qui survient dans la base de données ou l'instance à laquelle ils sont attachés.
Vous avez besoin pour cela d'utiliser la fonction EVENTDATA() et d'un peu de XQuery.

Enfin, vous pouvez :

- appeler les fonctions dans les procédures stockées,
- appeler les procédures stockées directement ou par exemple dans un trigger
- créer des fonctions, triggers, et procédures stockées (et aggrégats également) d'assembly (codés en VB.NET ou C#)
- créer des procédures stockées étendues, écrites en C++

Vous ne pouvez pas appeler une procédure stockée dans une fonction, sauf si c'est une procédure stockée étendue.
Vous ne pouvez pas non plus gérer les erreurs dans les fonctions.

Je décris ici ce que l'on ne peut pas faire dans les fonctions.

En revanche dans les triggers et les procédures stockées, vous pouvez utiliser RAISERROR() (attention, bientôt déprécié), et le contrôle TRY ... CATCH.

SQLPro a publié un paquet de fonctions utilitaires ici

Enjoy !

@++

[olibara]

Merci beaucoup Elsuket

J'apprécie beaucoup la patience de ta réponse et le détail d'information qu'elle contient

Cependant comme je l'avais signalé je reste un peu sur ma faim car ce que je cherche surtout c'est la syntaxe de programmation utilisable dans une procédure stockée

Peut on faire des boucle (while, for) : quelle syntaxe ?
Peut on utiliser des instruction conditionelle : quelle syntaxe

Bref on peut trouver facilement comment creer des Function, procedure, trigger

- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE TRIGGER

Mais pas le jeu d'instruction et la syntaxe

C'est comme si je cherchais un tuto de programation cSharp et que je trouve les explication pour compiler une source

[olibara]

Petit up !

Le lien que tu m'a donné sur les modèles de sqlPro permettent heureusement de trouver un bon éventail de la syntaxe disponible

[elsuket]

Pour les boucles, seul le WHILE est disponible.
Notez que les boucles sont peu utilisées (ou en tout cas devraient l'être) puisque SQL est un langage ensembliste.
Je les utilise parfois pour traiter un UPDATE en lots, de façon a ne pas remplir le journal de transactions trop vite par rapport aux backups de celui-ci.
Supposons par exemple que vous deviez ajouter une colonne à une table puis la valuer à 0, et que cette table contient des millions de lignes.
On peut écrire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
ALTER TABLE dbo.maTable
ADD uneColonne INT
 
SELECT	primary_key_column
INTO	temp_table
FROM	dbo.maTable
 
WHILE EXISTS
(
	SELECT	*
	FROM	dbo.temp_table
)
BEGIN
	UPDATE		TOP (1000) dbo.maTable
	SET		uneColonne = 0
	FROM		dbo.maTable AS T
	INNER JOIN	dbo.temp_table AS TMP
				ON T.primary_key_column = TMP.primary_key_column
 
	DELETE	TOP (1000)
	FROM	dbo.temp_table
END
 
-- Si la table est toujours accédée, il se peut que des lignes aient été ajoutées
UPDATE	dbo.maTable
SET	uneColonne = 0
WHERE	uneColonne IS NULL
 
ALTER TABLE dbo.maTable
ALTER COLUMN uneColonne INT NOT NULL

Pour les instructions conditionnelles, vous n'avez que IF (uneCondition) BEGIN ... END.
N'oubliez pas le CASE qui est d'une aide précieuse.

Pour les triggers j'ai donné quelques exemples simples dans ce billet.

Voici un exemple de procédure stockée que je n'ai pas encore publié, et qui permet de maintenir les index d'une base de données.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
-- EXEC sp__IndexMaintenance 1, 1, 0, 0
ALTER PROCEDURE sp__IndexMaintenance
	@_minutes_of_REBUILD int
	, @_minutes_of_REORGANIZE int
	, @_maintain_LOB_index bit = 0
	, @_index_max_size_kb int = 0
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @start_date_time datetime = GETDATE()
		, @end_date_time_REBUILD datetime
		, @end_date_time_REORGANIZE datetime
		, @index_name sysname
		, @table_name sysname
		, @fill_factor tinyint
		, @sql nvarchar(max)
		, @index_frag_id int
		, @cr char(2)
		, @tab char(1)
		, @rebuild bit
		, @sql_begin varchar(512)
 
	SELECT	@end_date_time_REBUILD = DATEADD(minute, @_minutes_of_REBUILD, @start_date_time)
		, @end_date_time_REORGANIZE = DATEADD(minute, @_minutes_of_REBUILD + @_minutes_of_REORGANIZE, @start_date_time)
		, @cr = CHAR(13) + CHAR(10)
		, @tab = CHAR(9)
		, @rebuild = 1
		, @index_frag_id = 0
 
	-- Rebuilding or reorganizing indexes
	WHILE	@index_frag_id IS NOT NULL
	BEGIN
		SET @index_frag_id = 0
 
		-- Searching an index to be rebuilt or reorganized
		SELECT	@sql = 'SELECT TOP 1 @index_frag_id = index_frag_id' + @cr
				+ @tab + ', @index_name = index_name' + @cr
				+ @tab + ', @table_name = table_name' + @cr
				+ @tab + ', @fill_factor = fill_factor' + @cr
				+ 'FROM	index_frag' + @cr
				+ 'WHERE	frag_pct ' + CASE @rebuild WHEN 1 THEN ' > 30' WHEN 0 THEN ' BETWEEN 10 AND 30' END + @cr
				+ CASE @_maintain_LOB_index 
					WHEN 0 THEN 'AND	(is_not_online = 0 AND has_lob_column = 0)'
					ELSE ''
				END + @cr
				+ CASE
					WHEN @_index_max_size_kb = 0 THEN '' 
					ELSE 'AND	index_size_kb <= ' + CAST(@_index_max_size_kb AS varchar(10))
				END + @cr
				+ 'AND	maintained_date_time IS NULL' + @cr
				+ 'ORDER	BY user_searches DESC'
 
		PRINT	@sql
		PRINT	'-------------------'
 
		EXEC	sp_executeSQL
			@sql
			, N'@index_frag_id int OUTPUT, @index_name sysname OUTPUT, @table_name sysname OUTPUT, @fill_factor tinyint OUTPUT'
			, @index_frag_id = @index_frag_id OUTPUT
			, @index_name = @index_name OUTPUT
			, @table_name = @table_name OUTPUT
			, @fill_factor = @fill_factor OUTPUT
 
		/*
		When @index_frag_id = 0 (no index found matching the query filters)
		- if we were in the REBUILD phase, we jump to the REORGANIZE phase
		- if we were in the REORGANIZE phase, we quit
		*/
		IF @index_frag_id = 0
		BEGIN
			IF @rebuild = 1
				SET @rebuild = 0
			ELSE
				SET @index_frag_id = NULL
		END
		ELSE
		BEGIN
			/*
			There still are some indexes to be maintained,
			but we need to check if we won't be out of the maintenance period, so :
			- if we were in the REBUILD phase, we jump to the REORGANIZE phase
			- if we were in the REORGANIZE phase, we quit
			*/
			IF GETDATE() >= @end_date_time_REBUILD
				SET @rebuild = 0
			IF GETDATE() >= @end_date_time_REORGANIZE
				SET @index_frag_id = NULL
		END
 
		-- Building and executing the sql statement to maintain the index
		SET @sql_begin = 'ALTER INDEX ' + @index_name + ' ON ' + @table_name
 
		IF @rebuild = 0
			SET @sql = @sql_begin + ' REORGANIZE'
		ELSE		
			SET @sql = @sql_begin + ' REBUILD ' 
			+ CASE 
				WHEN @fill_factor = 0 AND @_maintain_LOB_index = 0 THEN 'WITH (ONLINE = ON)'
				WHEN @fill_factor > 0 AND @_maintain_LOB_index = 0 THEN 'WITH (ONLINE = ON, FILLFACTOR = ' + CAST(@fill_factor AS varchar(3)) + ')'
				WHEN @fill_factor > 0 AND @_maintain_LOB_index = 1 THEN 'WITH (FILLFACTOR = ' + CAST(@fill_factor AS varchar(3)) + ')'
				WHEN @fill_factor = 0 AND @_maintain_LOB_index = 1 THEN ''
			END
 
		PRINT	@sql
 
		-- Mark the index as maintained
		UPDATE	index_frag
		SET	maintained_date_time = GETDATE()
		WHERE	index_frag_id = @index_frag_id
	END
END

J'espère que cela vous donne une idée de ce que l'on peut faire avec une procédure stockée

@++

[olibara]

Merci beaucoup Elsuket

Ton aide est précieuse !

[olibara]

Bonjour Elsuket

Excuse moi d'abuser de ta patience mais je cherche depuis hier la solution a un problème pour lequel vraissemblablement personne n'a de réponse

Est-il possible dans un trigger de tester une valeur insérée dans une table afin de declencher une action et si oui quelle est la syntaxe ?

Voici une de nombreuses et infructueuses tentatives

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE ZIStat
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER TRIGGER dbo.tg_Test
   ON  ZIStat.dbo.MailingList
   AFTER INSERT
AS 
BEGIN
 DECLARE @name varchar(50)
 
  SELECT @name = Name
  FROM INSERTED 
  IF @Name='fred'
  {
    -- EXEC sp_somesp
  }
END
GO

[elsuket]

Excuse moi d'abuser de ta patience

Je viens sur ce forum par pur plaisir de partager ce que je sais, donc il n'y a vraiment aucun problème

En fait ton trigger ne fonctionne pas correctement parce que tu récupères la valeur que tu as insérée pour la colonne Name.
Or il se peut que tu insères plusieurs lignes dans la table ZIStat.dbo.MailingList.
Dans un tel cas, la valeur de @name est celle de la "dernière" ligne de l'ensemble de lignes que tu viens d'ajouter à la table.

Tu peux donc écrire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ALTER TRIGGER dbo.tg_Test
	ON  ZIStat.dbo.MailingList
AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON
 
	IF EXISTS
	(
		SELECT	*
		FROM	inserted
		WHERE	name = 'Fred'
	)
	BEGIN
		EXEC	dbo.sp_somesp
	END
END
GO

SET NOCOUNT ON est une option de session qui permet de ne pas retourner le nombre de lignes affectées par les instructions du module (procédure, fonction table, trigger).

N'oublie pas de qualifier les noms des objets que tu utilises par le nom du schéma auquel ils appartiennent.
Par défaut c'est dbo, mais si tu le précises cela évite à SQL Server de le chercher

@++

[olibara]

Merci Elsuket

C'est tres clair et je vois bien l'idée du IF Exists
Je peux travailler avec ca

Mais mon idée venait de la logique d'environnement que je maitrise avec une application C#

L'idée est la siuivante
Une application mobile genere des enregistrement
Ces enrigistrement sont sauvé dans un fichier texte
A la fin des operations un enregistrement "marqueur" est généré, il se disntingue pas des valeurs exceptionelles

Le mobile essaye régulierement se sauver (inserer) ses enregistrement via WIFI dans la DB distante

Des que l'enregistrement marqueur (qui sera le dernier) est vu par la DB je souhaite lancer une procedure de consolidation.

Pour le moment il n'y a qu'un seul mobile mais ta remarque me fait penser
que l'on pourrait envoyer plusieurs lot en séquence avec plusieurs marqueur et qu'un deuxieme mobile pourrait intervenir


Ma vision des choses etait que le triger réagissait de maniere unitaire a chaque insertion physique de chaque record dans la DB

Mais si j'ai bien compris maintenant, le trigger se fait a chaque commande insert qui pourrait contenir plusieurs records.

Dans mon cas néanmoins le marqueur sera TOUJOURS le dernier record d'un lot donc je pourrais théoriquement tester directement les valeurs de colonnes sans utiliser un IF EXISTS
Est-ce possible aussi ?

[elsuket]

Ma vision des choses etait que le triger réagissait de maniere unitaire a chaque insertion physique de chaque record dans la DB

Mais si j'ai bien compris maintenant, le trigger se fait a chaque commande insert qui pourrait contenir plusieurs records.

Que tu insères une seule ligne ou 2 millions, le trigger se déclenchera, et dans ton cas toutes les lignes que tu as insérées seront exposées par la table virtuelle inserted.
Donc elle contient respectivement une ligne ou 2 millions

Pour le moment il n'y a qu'un seul mobile mais ta remarque me fait penser
que l'on pourrait envoyer plusieurs lot en séquence avec plusieurs marqueur et qu'un deuxieme mobile pourrait intervenir

Le trigger te permettrait de gérer autant de mobiles que tu souhaites, pourvu que le serveur hébergeant la base de données le supporte

Dans mon cas néanmoins le marqueur sera TOUJOURS le dernier record d'un lot donc je pourrais théoriquement tester directement les valeurs de colonnes sans utiliser un IF EXISTS
Est-ce possible aussi ?

C'est effectivement faisable mais ce serait une mauvaise utilisation des triggers.
Supposons par exemple qu'un jour tu aies besoin d'ajouter un enregistrement spécifique dans le jeu de données généré par ton application.
Tu n'auras donc qu'à rajouter un test dans le trigger pour effectuer des traitements pour les lignes ayant une valeur particulière

@++

[olibara]

C'est effectivement faisable mais ce serait une mauvaise utilisation des triggers.
Supposons par exemple qu'un jour tu aies besoin d'ajouter un enregistrement spécifique dans le jeu de données généré par ton application.
Tu n'auras donc qu'à rajouter un test dans le trigger pour effectuer des traitements pour les lignes ayant une valeur particulière

Tu a raison et je comprends parfaitement, mais ca m'interesse quand meme de savoir comment éventuelement faire dans ce cas

Car mon usage ici est un trigger declanché volontairement par l'application dans la DB et non un contexte particulier qui doit etre géré par la DB elle meme
J'aurais pu le faire en appel de sp mais le but est de minimiser le dialogue WIFI et les problèmes de conexion

[olibara]

C'est effectivement faisable mais ce serait une mauvaise utilisation des triggers.
Supposons par exemple qu'un jour tu aies besoin d'ajouter un enregistrement spécifique dans le jeu de données généré par ton application.
Tu n'auras donc qu'à rajouter un test dans le trigger pour effectuer des traitements pour les lignes ayant une valeur particulière

Tu a raison et je comprends parfaitement, mais ca m'interesse quand meme de savoir comment éventuelement faire dans ce cas

Car mon usage ici est un trigger declanché volontairement par l'application dans la DB et non un contexte particulier qui doit etre géré par la DB elle meme
J'aurais pu le faire en appel de sp mais le but est de minimiser le dialogue WIFI et les problèmes de conexion

[soazig]

Bonjour,
Attention

Car mon usage ici est un trigger declenché volontairement par l'application dans la DB et non un contexte particulier qui doit etre géré par la DB elle meme

Un trigger n'est pas déclenché volontairement.
Un trigger se déclenche quand certaines conditions sont remplies.
Si ces conditions sont remplies, lorsque tu fais un update massif pur une maintenance quelconque ou une correction de bug, ton trigger se déclenchera.

Il ne faut surtout pas oublier qu'un trigger peut se déclencher n'importe quand (à condition que les conditions soit remplies), y compris dans des circonstances auxquelles tu n'as pas pensé.
A+
Soazig

[olibara]

Merci Soazig

Ta remarque est evidement pertinente mais dans le contexte d'exploitation que je met en place les "conditions remplies" sont controlées par l'application

Mais il est vrai que je commence a me poser la question de la pertinence du trigger par rapport a l'appel d'une procedure stockée par l'application lorsque les "conditions sont remplies"

J'ai deux contrainte qui me font encore pencher pour le trigger

- 1 certaines conditions remplies peuvent etre plus facilement vérifiée au niveau de la DB (lorsque que device sont en fonction)

- 2 le trigger dispense l'application qui genere les donnée de s'assurer que que la commande pouvant ettre executée par le trigger est bien executée (en cas de perte de communication WIFI)

[soazig]

Bonjour,
Je ne comprends pas ce que tu veux dire

"conditions remplies" sont controlées par l'application

Sauf à dire que ton application lorsque les conditions seront remplies mettront un jour un flag de ta table, flag que tu te garderas de mettre à jour lorsque tu effectueras des correctifs ou des scripts.
Je me suis trouvée confrontée à un problème de trigger embêtant en dehors de ce à quoi les développeurs avait prévu.
Ce trigger a été mis en place sur des tables existantes et devait assurer une pseudo-intégrité référentielle (pas taper il existait avant que j'arrive sur le projet), et visait donc à empêcher la création, modification de lignes qui aurait violé cette intégrité référentielle.
Ce trigger ne vérifiait pas le moins du monde que les colonnes qu'ilscontrolait avait changé.
Lorsque j'ai rajouté à ma table une colonne, et que je l'ai alimenté par un update massif du genre.

Code :

UPDATE ma_table SET ma_nouvelle_colonne=10;

Le trigger s'est déclenché et oups comme les données initiales n'étaient pas intègre, empêchait la modification.

D'où ma mise en garde,visant à bien cibler les conditions d'actions d'un trigger.
A+
Soazig