betacode

Руководство SQL Server Transact-SQL

  1. Что такое Transact-SQL 
  2. Обзор Transact-SQL
  3. Начать с SQL Server Management Studio
  4. Базовые команды Transact-SQL 
  5. Прикрепить данные запроса в переменную
  6. Особенные виды данных в T-SQL
  7. Курсор (Cursor)
  8. Обработка исключения
  9. Функция (Function)
  10. Процедура (Procedure)
  11. Обработка транзакции (Transaction)
  12. Trigger

1. Что такое Transact-SQL 

Transact-SQL (так же называется T-SQL) это база данных (database) процедурный язык программирования принадлежащее монопольно Microsoft и используется в SQL Server.

Процедурный язык был создан для расширения возможностей SQL с возможностью хорошо интегрировать с SQL. Добавлены некоторые функции, как локальные переменные и обработка строк/данных. Эти функции делают язык Turing-complete (**).

Они так же используются для записи процедур хранения: Фрагмент кода находящийся на сервере управляет сложными бизнес правилами, которые сложно или невозможно управлять операциями на основе набора (pure set-based operations).
Система Turing-Complete означает система, в которой можно написать программу и она сможет найти ответ (несмотря на то, что нет гарантии во времени работы или памяти).

2. Обзор Transact-SQL

T-SQL организован блоками команд, один блок команд можно вложить в другой блок команд, блок команд начинающийся с BEGIN и заканчивающийся на END, в блоке имеется много конманд, и команды отделены друг от друга точкой запятой(;).
Структура блока:
BEGIN
    -- Declare variables
    -- T-SQL Statements
END;

3. Начать с SQL Server Management Studio

В данной статье я покажу вам программирование SQL Server, на визуальном инструменте SQL Server Management Studio.
Это иллюстрация SQL Server Management Studio при открытии. Есть некоторые примеры database, когда вы полностью устанавливаете SQLServer.
Или вы можете создать learningsql, маленькую базу данных использующуюся в некоторых статьях по рукодству пользования SQLServer на o7planning.org.
Нажать на правую мышь на database, выбрать "New Query" чтобы открыть окно для этого database.
Вы готовы программировать database с SQL Server.
Ниже показывается легкий блок команд, посчитать сумму 2 чисел:
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;
Нажмите на знак чтобы запустить блок команд, и смотрите результат на SQL Server Management Studio:

4. Базовые команды Transact-SQL 

Здесь я ознакомлю вас с общими базовыми командами T-SQL. Вы поймете лучше благодаря примерам в следующих частяъ.
Команда веток If-elsif-else
Синтаксис:
IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;
Пример:
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;
Результаты запуска примера:
Цикл WHILE
Syntax:
LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;
В цикле WHILE вы можете использовать BREAK чтобы выйти из цикла.
Используйте команду CONTINUE чтобы пропустить команды в блоке WHILE и ниже, чтобы начать новый цикл.
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;
Результаты запуска примера:
BREAK это команда позволяющая выйти из цикла, ниже показывается пример:
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;
Результаты запуска примера:
Команда CONTINUE позволяет пропустить команды находящиеся ниже (в цикле), чтобы начать новый цикл.
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. Прикрепить данные запроса в переменную

Переменным могут присвоить значение из запроса. Смотрите иллюстрированный пример ниже:
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;
Результаты запуска примера:

6. Особенные виды данных в T-SQL

Виды данных TABLE (Неявный вид)
T-SQL позволяет вам объявит переменные с видом данных TABLE.
Синтаксис:
-- 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
);
Пример:
-- 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)
);
Пример: Вставить данные в переменные вида 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;
Вы так же можете обновить Update на переменных вида TABLE:
Update
   @v_Table
Set Salary = Salary + 100
Where Dept_Id = 10;
Delete на переменных вида TABLE:
Delete From @v_Table Where Dept_ID = 10;
Query данных на переменных вида TABLE:
Select * from @v_Table
Where Dept_ID = 10
Order by First_Name;
Пример:
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;
Результаты запуска примера:
Вид данных TABLE (Явный вид)
T-SQL позволяет вам объявить переменные вида TABLE косвенным образом. Название переменной начинается с #.
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;
Результаты запуска примера:

7. Курсор (Cursor)

Что такое курсор?
Cursor это структурированный вид переменной, который позволяет вам обрабатывать данные в нескольких строках. Количество линий зависит от команды запроса данных. В процессе обработки, вы манипулируете cursor через каждую строку данных. Эта строка данных определяется курсором. Передвигая курсор, вы можете иметь доступ ко всем строкам данных.
Объявить курсор
Синтаксис
-- 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 ] ] ]
[;]
Пример с курсором
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;
Результаты запуска примера:
Пример использования курсора (Объявление вида переменной)
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. Обработка исключения

При программировании T-SQL могут появиться некоторые ошибки в вашем коде, например ошибка при делении на 0. Или ошибка когда вы вставляете запись, но она дублирует значение с первичным ключом, ... Вам нужно исправить эти ситуации.
Смотрите простой пример, как исправить ошибку при делении на 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;
Pезультат
Информация ошибки:
Функция
Описание
ERROR_NUMBER()
Возвращает номер ошибки.
ERROR_MESSAGE()
Возвращает полностью сообщение об ошибке. Сообщение включает значения предоставленные параметрам, как длина, название объекта, или время.
ERROR_SEVERITY()
Возвращает степень серьезности ошибки.
ERROR_STATE()
Возвращает статус ошибки.
ERROR_LINE()
Возвращает номер строки кода совершившая ошибку
ERROR_PROCEDURE()
Возвращает название stored procedure или trigger, где произошла ошибка.

9. Функция (Function)

Как procedure (процедура), function (функция) являются командами T-SQL, выполняющими определенную роль. В отличии от процедуры, функция возвращает значение сразу при вызове.
Функция так же может быть сохранена в database в виде Store procedure.
Синтаксис создания function (Функции).
-- 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;
Пример:
-- 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;
Отмена функции (Drop function):
-- Drop Function

DROP FUNCTION <function_name>;

-- For example:

DROP FUNCTION My_Function;
Пример создания функции:
Пример создания вашей первой function (функции) с SQL Server:
  1. Создать функцию (Function)
  2. Компилировать данную функцию
  3. Запуск функции
-- 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;
Нажмите на знак чтобы компилировать функцию.
Функция, которую вы создали, является простой функцией возвращающая скалярное значение (Scalar-value). Вы можете увидеть как она создается на SQLServer Management Studio:
Можете протестировать функцию, нажав на правую мышь, выберите:
  • Script function as -> SELECT to -> New Query Editor Window
Откроется тестовое окно, вы можете поменять параметры значений:
Поменять параметры значений и нажать на запуск.
Функции могут участвовать в команде 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;
Результаты запроса SQL:

10. Процедура (Procedure)

No ADS
Группа команд T-SQL, выполняющие определенные функции могут быть собраны в процедуре (procedure), чтобы увеличить возможность выполнения, общего пользования, безопасности, безопасности данных, и полезность в развитии.

Процедуры могут быть сохранены в database как объект в database, готовый для переиспользования. Процедура в данный момент называется Store procedure. Чтобы выполнить Store procedure, сразу после сохранения Store procedure, они компилируются в p-code поэтому могут повысить возможность выполненния.
Процедуры не возвращают значения напрямую как функции (function). Но могут иметь 0 или более параметров на выход.
Синтаксис создания процедуры:
-- 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;
Пример:
-- 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;
Отменить процедуру (Drop procedure):
-- Drop Procedure:

DROP PROCEDURE <Procedure_Name>
Шаги для выполнения процедуры:
Пример создания процедуры:
Здесь я создаю простую процедуру, с входным параметром @p_Emp_ID и с 3-мя выходными параметрами, @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;
Нажмите на знак чтобы компилировать процедуру.
После того, как вы создали процедуру, вы увидите на SQL Server Management Studio:
Тест процедуры
В программировании тест процедуры и обнаружение ошибок, это очень важно. Нажмите на правую мышь на процедуру, которую хотите протестировать, выберите:
  • Script stored Procedure as -> EXECUTE to -> New Query Editor Window
Создан текстовый тест (По умолчанию) как в иллюстрации ниже:
Настройти значения для входных параметров:
Нажмите на кнопку выполнения процедуры:

11. Обработка транзакции (Transaction)

No ADS
Почему нужно обрабатывать транзакции?
Транзакция (Transaction) это важное понятие в SQL. Рассмотрим ситуацию:

Транзакция в банке, клиент A отправляет клиенту B сумму денег в 100$, при этом в Database происходят 2 манипуляции:
  1. Дебит 100$ из счета клиента А
  2. Кредит 100$ в счет клиента В.
Что произойдет если выпонена только одна манипуляция?
Посмотрим другой пример:
Когда вы добавляете студента в класс, вам нужно обновить количество студентов класса. Если добавление студента не выполнено, но в количестве добавляется 1, то нарушает завершенность данных.
-- 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;
Транзакция считается выполненной если все единицы команд выполнены успешно. Напротив, если одна единица команды нарушена, вся транзакция должна быть отменена (rollback) в начальный статус.
Объявление и использование транзакций (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.
Примечание:
  • Команда rollback tran + tên_của_savepoint помогает отменить(rollback) транзакцию до соответствующей позиции savepoint (без эффекта завершения транзакции), блокировки (locks) будут разблокированы (unlock) при выполнении манипуляций rollback определенных частей.
  • При объявлении явной транзакции, нужно удостовериться что она может быть отменена (rollback) или зафиксирована в явном виде (commit), если нет, транзакция будет продолжать существовать и занимать ресурсы, предотвращая выпонения других транзакций.
  • Команда rollback помогает отменить только транзакции в базе данных (insert, delete, update). Другие команды, например прикрепить, не будут зависеть от команды rollback.
Пример:
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;
Результаты запуска примера:

12. Trigger

Статья про Trigger отделена, вы можете посмотреть инструкцию по ссылке:
  • Руководство SQL Server Trigger
No ADS

Новейшие статьи

    Show More