Le Tutoriel de programmation SQL Server Transact-SQL

Qu'est ce que Transact-SQL?
L'aperçu de Transact-SQL
Commencer avec SQL Server Management Studio
Les déclaration Transact-SQL de base
Affecter des valeurs aux variables à partir de l'instruction Select
Les types de données spéciales dans T-SQL
Le curseur (Cursor)
Gestion des exceptions
Fonction (Function)
La procédure (Procedure)
La transaction (Transaction)
Trigger

1. Qu'est ce que Transact-SQL?

Transact-SQL (ou encore appelé T-SQL) est un langage de programmation procédural database. Il est le monopole de Microsoft, qui est utilisé dans SQL Server.

Les langages de procédure sont conçus pour étendre les capacités de SQL tout en permettant de bien intégrer SQL. Plusieurs fonctionnalités telles que les variables locales et le traitement des chaînes / données sont ajoutées. Ces fonctionnalités rendent la langue Transact-SQL est Turing-complete.

Ils sont également utilisés pour écrire des procédures stockées: des extraits de code résidant sur le serveur pour gérer des règles commerciales complexes qui sont difficiles ou impossibles à gérer avec des opérations basées sur des ensembles purs (pure set-based operations).

Un système Turing Complete signifie un système dans lequel on peut écrire un programme qui trouvera une réponse (bien que sans aucune garantie concernant la durée d'exécution ou la mémoire).

2. L'aperçu de Transact-SQL

T-SQL est organisé par chaque bloc de déclaration. Un bloc de déclaration peut embrasser un autre bloc de déclaration. Un bloc de déclaration commence par BEGIN et finit par END. Il existe de nombreuses déclarations dans le bloc, et les déclarations sont séparées l'une de l'autre par un point-virgule (;).

La structure du boc:

BEGIN
    -- Declare variables
    -- T-SQL Statements
END;

3. Commencer avec SQL Server Management Studio

Dans ce document, je vais vous donner des instructions de programmer SQL Server, sur l'outil virtuel SQL Server Management Studio.

C'est l'image de SQL Server Management Studio alors que vous l'ouvrez. Il existe quelques database disponibles alors que vous installez SQLServer complètement.

Vous pouvez créer learningsql, une petite base de données est utilisée dans certains documents SQLServer sur o7planning.org.

Exemple de base de données SQL Server pour l'apprentissage SQL

Cliquez sur le bouton droit à database, sélectionnez "New Query" afin d'ouvrir une fenêtre de travailler avec ce database.

Vous êtes prêt à programmer database avec SQL Server.

Voici un bloc d'une déclaration simples, somme de deux nombres:

Begin

  -- Declaring a variable
  Declare @v_Result Int;
  -- Declaring a variable with a value of 50
  Declare @v_a Int = 50;
  -- Declaring a variable with a value of 100
  Declare @v_b Int = 100;


  -- Print out Console (For developer).
  -- Using Cast to convert Int to String
  -- Using + operator to concatenate 2 string
  Print 'v_a= ' + Cast(@v_a as varchar(15));

  -- Print out Console
  Print 'v_b= ' + Cast(@v_b as varchar(15));

  -- Sum
  Set @v_Result = @v_a + @v_b;

  -- Print out Console
  Print 'v_Result= ' + Cast(@v_Result as varchar(15));

End;

Cliquez sur l'icône

afin d'exécuter la déclaration du bloc, et de voir le résultat sur SQL Server Management Studio:

4. Les déclaration Transact-SQL de base

Ici je vais vous introdure la vue générale des déclarations de base de T-SQL. Vous en comprendrez plus à travers des exemples dans les sections suivantes.

La commande du marquage If-elsif-else

Syntaxe:

IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;

Exemple:

Else_If_Example

BEGIN

 -- Declare a variable
 DECLARE @v_Option integer;
 DECLARE @v_Action varchar(30);

 SET @v_Option = 2;

 IF @v_Option = 1
   SET @v_Action = 'Run';
 ELSE IF @v_Option = 2
     BEGIN
       PRINT 'In block else if @v_Option = 2';
       SET @v_Action = 'Backup';
     END;
 ELSE IF @v_Option = 3
   SET @v_Action = 'Stop';
 ELSE
   SET @v_Action = 'Invalid';


 -- Logging
 PRINT '@v_Action= ' + @v_Action;

END;

Résultats de l'exécution de l'exemple:

La boucle WHILE

Syntax:

LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;

Dans la boucle WHILE, vous pouvez utiliser BREAK afin de sortir de la boucle.
Utiliser CONTINUE pour sauter les déclarations dans le bloc WHILE et au-dessous de lui, afin de continuer une nouvelle boucle.

While_Example1

BEGIN

 -- Declaring 2 variables x and y.
 DECLARE @x integer = 0;
 DECLARE @y integer = 10;

 -- Step
 DECLARE @step integer = 0;

 -- While @x < @y
 WHILE (@x < @y)
 BEGIN

   SET @step = @step + 1;

   -- Every time loop execute, x increases by 1.
   SET @x = @x + 1;
   -- Every time loop execute, x decreases by 2.
   SET @y = @y - 2;

   PRINT 'Step =' + CAST(@step AS varchar(10));
   PRINT '@x =' + CAST(@x AS varchar(10)) + ' / @y = ' + CAST(@y AS varchar(10));

 END;

 -- Write log
 PRINT 'x,y = ' + CAST(@x AS varchar(10)) + ', ' + CAST(@y AS varchar(10));

END;

Résultats de l'exécution de l'exemple:

BREAK est la déclaration permettant de sortir de la boucle. Voici un exemple:

While_Example2

BEGIN

 -- Declaring 2 variables x and y
 DECLARE @x integer = 0;
 DECLARE @y integer = 10;

 -- Step
 DECLARE @step integer = 0;

 -- While @x < @y
 WHILE (@x < @y)
 BEGIN

   SET @step = @step + 1;

   -- Every time the loop execute, x increases by 1
   SET @x = @x + 1;
   -- Every time the loop execute, y decreases by 1
   SET @y = @y - 2;

   PRINT 'Step =' + CAST(@step AS varchar(10));
   PRINT '@x =' + CAST(@x AS varchar(10)) + ' / @y = ' + CAST(@y AS varchar(10));

   -- If @x > 2 then exit the loop
   -- (Although conditions in the WHILE is still true).
   IF @x > 2
     BREAK;

 END;

 -- Write log
 PRINT 'x,y = ' + CAST(@x AS varchar(10)) + ', ' + CAST(@y AS varchar(10));

END;

Résultats de l'exécution de l'exemple:

La déclaration CONTINUE permet d'ignorer les instructions ci-dessous (en boucle) afin de continuer une nouvelle boucle.

While_Example3

BEGIN

 -- Declaring 2 variables x and y.
 DECLARE @x integer = 0;
 DECLARE @y integer = 10;

 -- Step
 DECLARE @step integer = 0;

 -- While @x < @y
 WHILE (@x < @y)
 BEGIN

   SET @step = @step + 1;

   -- Every time the loop execute, x increases by 1
   SET @x = @x + 1;
   -- Every time the loop execute, x decreases by 2
   SET @y = @y - 2;

   -- If @x < 3 , then skip the statements below
   -- And continue new step
   IF @x < 3
     CONTINUE;

   -- If @x < 3 the statements below 'CONTINUE' will not be run.
   PRINT 'Step =' + CAST(@step AS varchar(10));
   PRINT '@x =' + CAST(@x AS varchar(10)) + ' / @y = ' + CAST(@y AS varchar(10));

 END;

 -- Write log
 PRINT 'x,y = ' + CAST(@x AS varchar(10)) + ', ' + CAST(@y AS varchar(10));

END;

5. Affecter des valeurs aux variables à partir de l'instruction Select

Les variables peuvent être affectées à la valeur d'une requête. Voici un exemple illustratif:

Assign_Value_Example

BEGIN

-- Declaring a variable @v_Emp_ID
DECLARE @v_Emp_ID integer = 1;

DECLARE @v_First_Name varchar(30);
DECLARE @v_Last_Name varchar(30);
DECLARE @v_Dept_ID integer;

-- Assgin values to variables
SELECT
  @v_First_Name = emp.First_Name,
  @v_Last_Name = emp.Last_Name,
  @v_Dept_Id = emp.Dept_Id
FROM Employee Emp
WHERE Emp.Emp_ID = @v_Emp_Id;

-- Print out values
PRINT '@v_First_Name = ' + @v_First_Name;
PRINT '@v_Last_Name = ' + @v_Last_Name;
PRINT '@v_Dept_Id = ' + CAST(@v_Dept_ID AS varchar(15));

END;

The results run the example:

6. Les types de données spéciales dans T-SQL

Le type de données TABLE (Explicite)

T-SQL vous permet de déclarer une variable dans le type de données TABLE.

Syntaxe:

-- Define a variable of type TABLE.
-- NOTE: The constraints can also participate in declaration (See example).
Declare @v_variable_name  TABLE  (
  Column1 DataType1,
  Column2 DataType2
);

Exemple:

-- Declare a variable of type TABLE.
Declare @v_Table TABLE  (
First_Name Varchar(30),
Last_Name Varchar(30),
Dept_ID Integer,
Salary Float
);


-- The constraints can also participate in declaration:
Declare @v_table TABLE  (
 Product_ID Integer IDENTITY(1,1) PRIMARY KEY,
 Product_Name  DataType2 NOT NULL Default ('Unknown'),
 Price Money CHECK (Price < 10.0)
);

Exemple: Insérer des données à une variable du type TABLE.

Insert Into
    @v_Table (First_Name, Last_Name, Dept_ID, Salary)
Select Emp.First_Name, Emp.Last_Name, Emp.Dept_Id, 1000
From
    Employee Emp
Where Emp.Emp_ID < 4;

Vous pouvez également Update sur la variable du type TABLE:

Update
   @v_Table
Set Salary = Salary + 100
Where Dept_Id = 10;

Delete sur une variable du type TABLE:

Delete From @v_Table Where Dept_ID = 10;

Query sur une variable du type TABLE:

Select * from @v_Table
Where Dept_ID = 10
Order by First_Name;

Exemple:

BEGIN

 DECLARE @v_Emp_ID integer = 1;

 -- Declare a variable of type TABLE.
 DECLARE @v_Table TABLE (
   First_Name varchar(30),
   Last_Name varchar(30),
   Dept_Id integer,
   Salary float DEFAULT 1000
 );

 -- Using INSERT INTO statement to insert data into @v_Table.
 INSERT INTO @v_Table (First_name, Last_Name, Dept_ID)
   SELECT
     emp.First_Name,
     emp.Last_Name,
     emp.Dept_Id
   FROM Employee Emp
   WHERE Emp.Emp_ID < 4;

 -- Update @v_Table
 UPDATE @v_Table
 SET Salary = Salary + 100
 WHERE First_name = 'Susan';

 -- Query @v_Table.
 SELECT
   *
 FROM @v_Table;

END;

Résultats de l'exécution de l'exemple:

Le type des données TABLE (Implicite)

T-SQL vous permet de déclarer une variable du type TABLE implicitement. Des noms de variable commence par #.

Table_Example

BEGIN

-- Using SELECT INTO statement to insert data into #v_My_Table.  
SELECT
  emp.First_Name,
  emp.Last_Name,
  emp.Dept_Id,
  1000 Salary INTO #v_My_Table
FROM Employee Emp
WHERE Emp.Emp_ID < 4;

-- Update #v_My_Table
UPDATE #v_My_Table
SET Salary = Salary + 100
WHERE First_name = 'Susan';

-- Query #v_My_Table.
SELECT
  *
FROM #v_My_Table;

END;

Résultats de l'exécution de l'exemple:

7. Le curseur (Cursor)

Qu'est ce qu'un curseur?

Cursor est un type de variable structurée qui nous permet de traiter des données avec différentes lignes. Le nombre de lignes dépend de l'instruction de requête de données. Pendant le traitement, nous manipulons cursor dans chaque ligne de données. Cette ligne de données est spécifiée par un curseur. En déplaçant le curseur, vous pouvez accéder à toutes les lignes.

Déclaration le Curseur

Syntaxe:

-- ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]

-- Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

Exemple avec Cursor

Cursor_Example

USE learningsql;

BEGIN

--  
-- Declare a variable:
DECLARE @v_Emp_ID integer;
DECLARE @v_First_Name varchar(50);
DECLARE @v_Last_Name varchar(50);

DECLARE @v_Count integer;

-- Declare a CURSOR.
DECLARE My_Cursor CURSOR FOR
SELECT
  Emp.EMP_ID,
  Emp.FIRST_NAME,
  Emp.LAST_NAME
FROM Employee Emp
WHERE Emp.EMP_ID < 3;



-- Open Cursor
OPEN My_Cursor;

-- Move the cursor to the first record.
-- And assign column values to variables.
FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;

-- The FETCH statement was successful. ( @@FETCH_STATUS = 0 )
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'First Name = '+ @v_First_Name+' / Last Name = '+ @v_Last_Name;

  -- Move to the next record.
  -- And assign column values to the variables
  FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;
END

-- Close Cursor.
CLOSE My_Cursor;
DEALLOCATE My_Cursor;

END;

Résultats de l'exécution de l'exemple:

Exemple de curseur (Déclarer le curseur comme variable)

Cursor_Example2

USE learningsql;

BEGIN

--  
-- Declare a variable:
DECLARE @v_Emp_ID integer;
DECLARE @v_First_Name varchar(50);
DECLARE @v_Last_Name varchar(50);

-- Declaring a cursor variable.
DECLARE @My_Cursor CURSOR;

-- Set Select statement for CURSOR variable.
Set @My_Cursor = CURSOR FOR
SELECT
  Emp.EMP_ID,
  Emp.FIRST_NAME,
  Emp.LAST_NAME
FROM Employee Emp
WHERE Emp.EMP_ID < 3;


-- Open Cursor
OPEN @My_Cursor;

-- Move the cursor to the first line.
-- And assign column values to the variables.
FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;

-- The FETCH statement was successful. ( @@FETCH_STATUS = 0)
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'First Name = '+ @v_First_Name+' / Last Name = '+ @v_Last_Name;

  -- Move to the next record.
  -- And assign column values to the variables.
  FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;
END

-- Close Cursor.
CLOSE @My_Cursor;
DEALLOCATE @My_Cursor;

END;

The results run the example:

8. Gestion des exceptions

Lors que T-SQL programme, des erreurs peuvent se produire dans votre code, par exemple l'erreur de division par 0. Ou l'erreur lors que vous insérez un enregistrement mais une valeur en double dans la clé primaire.

Considérons un exemple simple, la gestion des erreurs de division par 0.

TryCatch_Example

USE learningsql;

BEGIN

--  
-- Declare a variable:
DECLARE @v_a float = 20;
DECLARE @v_b float = 0;
DECLARE @v_c float;
DECLARE @v_Error_Number integer;

-- Use BEGIN TRY .. END TRY to trap errors.
-- If an error occurs in this block
-- It will jump to block BEGIN CATCH .. END CATCH.
BEGIN TRY

  ---
  PRINT '@v_a = ' + CAST(@v_a AS varchar(15));
  PRINT '@v_b = ' + CAST(@v_b AS varchar(15));
  -- Divide by 0 error, occurring here.
  SET @v_c = @v_a / @v_b;

  -- Below this line will not be running.
  -- Program jump to block BEGIN CATCH .. END CATCH
  PRINT '@v_c= ' + CAST(@v_c AS varchar(15));

END TRY
-- BEGIN CATCH .. END CATCH must be placed immediately behind BEGIN TRY .. END TRY.
BEGIN CATCH
  -- Error Number.
  SET @v_Error_Number = ERROR_NUMBER();
  -- Print out error number:
  PRINT 'Error Number: ' + CAST(@v_Error_Number AS varchar(15));
  -- Error message:
  PRINT 'Error Message: ' + ERROR_MESSAGE();
  --  The severity of the error:
  PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(15));
  -- Error State:
  PRINT 'Error State: ' + CAST(ERROR_STATE() AS varchar(15));
  -- Line Number:
  PRINT 'Error Line: ' + CAST(ERROR_LINE() AS varchar(15));
  -- Name of procedure (or function, or trigger).
  PRINT 'Error Procedure: ' + ERROR_PROCEDURE();
END CATCH;

END;

The results run the example:

Erreur d'informations:

Fonction	Description
ERROR_NUMBER()	Renvoie le numéro d'erreur.
ERROR_MESSAGE()	Renvoie le texte complet du message d'erreur. Le texte comprend les valeurs fournies pour tous les paramètres substituables tels que les longueurs, les noms d'objet ou les heures.
ERROR_SEVERITY()	Renvoie la gravité de l'erreur.
ERROR_STATE()	Renvoie le numéro d'état de l'erreur.
ERROR_LINE()	Renvoie le numéro de ligne dans la routine qui a provoqué l'erreur.
ERROR_PROCEDURE()	Renvoie le nom de stored procedure ou trigger où l'erreur s'est produit.

9. Fonction (Function)

Comme procedure (la procédure), function (la fonction) est un groupe de l'instruction T-SQL qui joue certains rôles. Contrairement à la procédure, une fonction renverra une valeur dès qu'on l'appelle.

Des fonctions sont également stockées sur database sous forme de Store procedure.

La syntaxe crée function (la fonction).

-- function_name:  
-- argument: 
-- mode:  INPUT, OUTPUT,  default INPUT
-- datatype: 

CREATE FUNCTION <function_name>
           (
              [
               @argument1 datatype1 [mode1] ,
               @argument2  datatype2 [mode2],
               ...
               ]
            )
           RETURNS  datatype
AS
BEGIN
 -- Declare variables
 -- Statements
 -- Return value
END;

Exemple:

-- Function with parameters
CREATE FUNCTION Sum_Ab(a Integer, b Integer)
RETURNS Integer
AS
Begin
return a + b;
End;

-- Function without parameters
CREATE FUNCTION Get_Current_Datetime()
RETURNS Date
AS
Begin
return CURRENT_TIMESTAMP;
End;

Omettre Function (Drop function):

-- Drop Function

DROP FUNCTION <function_name>;

-- For example:

DROP FUNCTION My_Function;

Exemple de la création une fonction.

Voici est un exemple de la création de votre première function (la fonction) avec SQL Server:

Créer une fonction (Function)
Compiler cette fonction
Exécuter la fonction

-- Check the existence of the function
-- If it did exist, should drop it in order to create a new one.
IF OBJECT_ID(N'dbo.My_Sum', N'FN') IS NOT NULL
DROP FUNCTION My_Sum;
GO

CREATE FUNCTION My_Sum (@p_a float, @p_b float)
RETURNS float
AS
BEGIN

-- Declaring a variable type of Float
DECLARE @v_C float;

-- Assign value for v_C
SET @V_C = @p_A + @p_B;

-- Return value.
RETURN @v_C;

END;

Cliquez sur l'icône

afin de compiler la fonction.

La fonction que vous avez créée est une fonction simple. Il renvoie une valeur Scalar (Scalar-value). Vous pouvez voir qu'il a été créé sur SQLServer Management Studio:

Vous pouvez tester la fonction en cliquant sur le bouton droit de la souris sur la fonction, sélectionnez:

Script function as -> SELECT to -> New Query Editor Window

Une fenêtre d'essai est ouverte, vous pouvez modifier les valeurs des paramètres:

Modifiez les valeurs des paramètres et appuyez sur le bouton de l'exécution.

La fonction peut participer à l'instruction SELECT.

SELECT
  acc.account_id,
  acc.cust_id,
  acc.avail_balance,
  acc.pending_balance,
  dbo.MY_SUM(acc.avail_balance, acc.pending_balance) balance
FROM account acc;

Résultat de l'execution la requête SQL ci- dessus:

10. La procédure (Procedure)

Un groupe de l'instructions T-SQL effectuant une fonction peut être rassemblé dans une procédure visant à augmenter la capacité de manipulation, de réutilisation, de sécurité, de sécurité des données et d'utilité dans le développement.

La procédure peut être sauvegardée sur la base de données comme un objet de base de données et prête à être réutilisée. La procédure est alors appelée Store procedure. Pour conserver Store procedure, après Store procedure est sauvegardée, elle est traduite en p-code pour que sa capacité d'exécution soit augmentée.

Les procédures ne renvoient pas les valeurs directement en tant que fonction (function). Cependant, il peut avoir 0 ou plus de paramètres de sortie.

La syntaxe à créer une procédure:

-- procedure_name:
-- argument: 
-- mode:  input type: INPUT or OUTPUT, default is INPUT
-- datatype: 
-- Note: The procedure parameters can put in an (), or unnecessary.

CREATE PROCEDURE <procedure_name>
     [
      argument1  datatype1 [mode1]  ,
      argument2   datatype2 [mode2]  ,
     ...
      ]
AS
BEGIN
 -- Declare variables.
 -- Statements ..
END;

-- OR:

CREATE PROCEDURE <procedure_name>
   (
     [
      argument1  datatype1 [mode1]  ,
      argument2   datatype2 [mode2]  ,
     ...
      ]
   )
AS
BEGIN
 -- Declare variables.
 -- Statements ..
END;

Exemple:

-- Procedure without parameters.
CREATE Procedure Do_Something
AS
Begin
     -- Declare variables here.
     Declare @v_a Integer;
      -- Do something here
      -- ....
End;

-- Procedure with parameters
--  1 input parameter and 2 output parameters

CREATE Procedure Do_Something (@p_Param1 Varchar(20),
                             @v_Param2 Varchar(50) OUTPUT )
AS
Begin
  -- Declare variables
  Declare @v_a Integer;

  -- Do something here.
  -- ...
End;

Omettre la procédure (Drop procedure):

-- Drop Procedure:

DROP PROCEDURE <Procedure_Name>

Les étapes d'un procédure:

Exemple de la création d'une procédure:

Ở đây tôi tạo một thủ tục đơn giản, với tham số truyền vào là @p_Emp_ID và có 3 tham số đầu ra, @v_First_Name, @v_Last_Name, @v_Dep_ID.
Ici, je crée une procédure simple avec un paramètre de saisie nommé @p_Emp_ID et trois paramètres de sortie, y compris @v_First_Name, @v_Last_Name, @v_Dep_ID.

Get_Employee_Infos

-- Drop procedure Get_Employee_Infos if it already exists.
-- (To enable recreate)
IF OBJECT_ID(N'dbo.Get_Employee_Infos', N'P') IS NOT NULL
 DROP PROCEDURE Get_Employee_Infos;
GO

-- Procedure with input parameter: p_Emp_Id
-- And output: v_First_Name, v_Last_Name, v_Dept_Id.
CREATE PROCEDURE Get_Employee_Infos (@p_Emp_Id integer
       , @v_First_Name varchar(50) OUTPUT
       , @v_Last_Name varchar(50) OUTPUT
       , @v_Dept_Id integer OUTPUT)
AS
BEGIN
 -- Use the Print command to print out a string (for programmers).
 -- Use Cast to convert Integer to string (Varchar).
 -- Use the + operator to concatenate two strings.
 PRINT 'Parameter @p_Emp_Id = ' + CAST(@p_Emp_ID AS varchar(15));
 --
 -- Query data from the table and assign values to variables.
 --
 SELECT
   @v_First_Name = Emp.First_Name,
   @v_Last_Name = Emp.Last_Name,
   @v_Dept_Id = Emp.Dept_Id
 FROM Employee Emp
 WHERE Emp.Emp_Id = @p_Emp_Id;

 --
 -- Log (For developers).
 --
 PRINT 'Found Record!';
 PRINT ' @v_First_Name= ' + @v_First_Name;
 PRINT ' @v_Last_Name= ' + @v_Last_Name;
 PRINT ' @v_Dept_Id= ' + CAST(@v_Dept_Id AS varchar(15));

END;

Cliquez sur l'icône

pour compiler la procédure.

Après que la procédure est créée, vous pouvez la voir sur SQL Server Management Studio:

Tester la procédure

Dans le programme, la procédure de test et l'erreur de détection sont très importantes. Cliquez avec le bouton droit de la souris sur la procédure que vous souhaitez tester, sélectionnez:

Script stored Procedure as -> EXECUTE to -> New Query Editor Window

Le script de test est créé (par défaut) comme indiqué ci-dessous:

Définir des valeurs pour les paramètres d'entrée:

Pressez sur le bouton pour exécuter la procédure:

11. La transaction (Transaction)

Pourquoi doit le traitement de la transaction?

La transaction (Transaction) est un concept dans SQL. Voyons une situation:

Dans une transaction bancaire, une personne A transfère une somme de 100 dollars à personne B. À ce moment-là, dans la base de données, il y a deux manipulations:

Débit 100 dollars du compte d'une personne
Crédit 100 dollars du compte de la personne B

Que se passerait-il si une seule manipulation réussissait?

Voir un autre exemple:

Lorsque vous ajoutez un élève à une classe, vous devez mettre à jour le nombre d'élèves. Si l'insertion de l'information des élèves échoue, mais le nombre d'élèves est ajouté 1, intégralité des données est interrompue.

-- Insert into Student table.
Insert into Student (Studen_Id, Student_Name, Class_ID)
values (100, 'Tom', 1);

-- Update Studen_Count.
Update Class_Table
set Student_Count = Student_Count + 1
Where Class_Id = 1;

La transaction est considérée comme réussie si toutes les unités d'instruction sont exécutées avec succès. Au contraire, l'une des unités d'instruction a une erreur, la transaction entière doit reculer (rollback) vers l'état initial.

La déclaration et l'utilisation des transactions (Transaction)

Related statements:

Begin transaction:
- begin tran / begin transaction
Finish transaction:
- commit/ commit tran / commit transaction
Rollback transaction:
- rollback / rollback tran / rollback transaction
Mark a savepoint in transaction: save transaction name_of_savepoint
@@trancount variable: shows the number of transactions is being executed (has not been finished with rollback or commit) in the current connection.

Remarques :

La commande rollback tran + nom_du_savepoint aide à reculer (rollback) la transaction vers la position correspondante de savepoint (sans aucun effet de terminer la transaction), la serrure (locks) sera déverrouillée lorsque des manipulations dans une partie rollbacked sont exécutées (unlock).
Lors que vous déclarez une transaction explicite, vous devez vous assurer qu'elle est rollback ou commit explicite, si non, transaction continuera exister et s'occuper des ressources, en empêchant l'exécution des autres transaction.
La commande rollback aide seulement à reculer des transactions sur la base de données (insert, delete, update). Des autres commandes, par exemple, assigner la commande, ne sont pas influencées par celle de rollback.

Exemple:

Transaction_Example1

BEGIN

-- In this example the accounts ACCOUNT_ID = 1, 2 actually exists in DB
-- In fact you can write statements to check before the start of transaction
--
-- account A (Already guarantees exist in DB)
DECLARE @Account_Id_A integer = 1;
-- account B (Already guarantees exist in DB)
DECLARE @Account_Id_B integer = 2;
-- Amount
DECLARE @Amount float = 10;
-- Bank
DECLARE @Execute_Branch_Id integer = 1;

-- Write out transaction Count.
-- In fact, at this time there is no transaction yet
PRINT '@@TranCount = ' + CAST(@@Trancount AS varchar(5));

PRINT 'Begin transaction';

-- Begin transaction
BEGIN TRAN;

  -- Error trapping.
  BEGIN TRY
    --
    -- Subtract $10 from account A
    UPDATE Account
    SET AVAIL_BALANCE = AVAIL_BALANCE - @Amount
    WHERE Account_Id = @Account_Id_A;
    --
    -- Insert transaction info  into Acc_Transaction table.
    INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD,
     ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID)
      VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'CDT',
       @Account_Id_A, -@Amount, @Execute_Branch_Id);
    --
    -- Add $10 to Account B.
    UPDATE Account
    SET AVAIL_BALANCE = AVAIL_BALANCE + @Amount
    WHERE Account_Id = @Account_Id_B;
    --
    -- Insert transaction info  into Acc_Transaction table.
    INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD,
      ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID)
      VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'CDT', @Account_Id_B,
       @Amount, @Execute_Branch_Id);
    -- Commit transaction
    IF @@Trancount > 0
      PRINT 'Commit Transaction';
  COMMIT TRAN;

END TRY
-- If there are errors Catch block will be execute.
BEGIN CATCH
  PRINT 'Error: ' + ERROR_MESSAGE();
  PRINT 'Error --> Rollback Transaction';
  IF @@Trancount > 0
    ROLLBACK TRAN;
END CATCH;



END;

Résultats de l'exécution de l'exemple:

12. Trigger

Le document de Trigger est séparé, vous pouvez voir l'instruction à:

SQL Server Trigger

Le Tutoriel de programmation SQL Server Transact-SQL

1. Qu'est ce que Transact-SQL?

2. L'aperçu de Transact-SQL

3. Commencer avec SQL Server Management Studio

4. Les déclaration Transact-SQL de base

5. Affecter des valeurs aux variables à partir de l'instruction Select

6. Les types de données spéciales dans T-SQL

7. Le curseur (Cursor)

8. Gestion des exceptions

9. Fonction (Function)

10. La procédure (Procedure)

11. La transaction (Transaction)

12. Trigger

Tutoriels de base de données SQL Server

Tutoriels de base de données SQL Server

Articles les plus récents