Hướng dẫn lập trình SQL Server Transact-SQL

BEGIN
    -- Khai báo biến
   -- Các câu lệnh T-SQL
END;

Begin

  -- Khai báo một biến
  Declare @v_Result Int;
  -- Khai báo một biến có giá trị 50
  Declare @v_a Int = 50;
  -- Khai báo một biến có giá trị 100
  Declare @v_b Int = 100;


  -- In ra màn hình Console (Dùng cho lập trình viên).
  -- Sử dụng Cast để ép kiểu Int về kiểu chuỗi.
  -- Sử dụng toán tử + để nối 2 chuỗi.
  Print 'v_a= ' + Cast(@v_a as varchar(15));

  -- In ra màn hình Console
  Print 'v_b= ' + Cast(@v_b as varchar(15));

  -- Tính tổng
  Set @v_Result = @v_a + @v_b;

  -- In ra màn hình Console
  Print 'v_Result= ' + Cast(@v_Result as varchar(15));

End;

IF <điều kiện 1>
      Khối lệnh 1;
[ELSE IF <điều kiện 2>
      Khối lệnh 2;
]
....
[ELSE
      Khối lệnh n + 1;
]

BEGIN

-- Khai báo một biến
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';


-- Ghi ra log
PRINT '@v_Action= ' + @v_Action;

END;

WHILE condition
BEGIN
   -- ...statements...
END;

BEGIN

 -- Khai báo 2 biến x và y.
 DECLARE @x integer = 0;
 DECLARE @y integer = 10;

 -- Bước
 DECLARE @step integer = 0;

 -- Trong khi @x < @y
 WHILE (@x < @y)
 BEGIN

   SET @step = @step + 1;

   -- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
   SET @x = @x + 1;
   -- Mỗi lần vòng lặp chạy giá trị của y giảm đi 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;

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

END;

BEGIN

-- Khai báo 2 biến x và y.
DECLARE @x integer = 0;
DECLARE @y integer = 10;

-- Bước
DECLARE @step integer = 0;

-- Trong khi @x < @y
WHILE (@x < @y)
BEGIN

  SET @step = @step + 1;

  -- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
  SET @x = @x + 1;
  -- Mỗi lần vòng lặp chạy giá trị của y giảm đi 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));

  -- Nếu @x > 2 thì thoát ra khỏi vòng  lặp
  -- (Mặc dù điều kiện trong WHILE vẫn đúng).
  IF @x > 2
    BREAK;

END;

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

END;

BEGIN

-- Khai báo 2 biến x và y.
DECLARE @x integer = 0;
DECLARE @y integer = 10;

-- Bước
DECLARE @step integer = 0;

-- Trong khi @x < @y
WHILE (@x < @y)
BEGIN

  SET @step = @step + 1;

  -- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
  SET @x = @x + 1;
  -- Mỗi lần vòng lặp chạy giá trị của y giảm đi 2
  SET @y = @y - 2;

  -- Nếu @x < 3 thì bỏ qua các dòng lệnh bên dưới
  -- Tiếp tục vòng lặp mới.
  IF @x < 3
    CONTINUE;

  -- Nếu @x < 3 các dòng lệnh bên dưới CONTINUE sẽ không được chạy.
  PRINT 'Step =' + CAST(@step AS varchar(10));
  PRINT '@x =' + CAST(@x AS varchar(10)) + ' / @y = ' + CAST(@y AS varchar(10));

END;

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

END;

BEGIN

-- Khai báo một biến @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;

-- Gán giá trị cho các biến lấy từ câu lệnh Select.
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;

-- In ra các giá trị:
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;

-- Định nghĩa một biến có kiểu TABLE.
-- Chú ý: Các giàng buộc cũng có thể tham gia vào khai báo biến kiểu TABLE (Xem trong ví dụ).
Declare @v_variable_name  TABLE  (
  Column1 DataType1,
  Column2 DataType2
);

-- Ví dụ khai báo một biến có kiểu TABLE.
Declare @v_Table TABLE  (
First_Name Varchar(30),
Last_Name Varchar(30),
Dept_ID Integer,
Salary Float
);


-- Các giàng buộc cũng có thể tham gia vào trong định nghĩa biến kiểu TABLE:
-- Ví dụ:
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)
);

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
   @v_Table
Set Salary = Salary + 100
Where Dept_Id = 10;

Delete From @v_Table Where Dept_ID = 10;

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

BEGIN

 DECLARE @v_Emp_ID integer = 1;

 -- Khai báo một biến kiểu TABLE.
 DECLARE @v_Table TABLE (
   First_Name varchar(30),
   Last_Name varchar(30),
   Dept_Id integer,
   Salary float DEFAULT 1000
 );

 -- Sử dụng INSERT INTO để trèn dữ liệu vào @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';

 -- Duyệt các giá trị trên @v_Table.
 SELECT
   *
 FROM @v_Table;

END;

BEGIN

-- Sử dụng SELECT INTO để trèn dữ liệu vào #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';

-- Duyệt các giá trị trên #v_My_Table.
SELECT
 *
FROM #v_My_Table;

END;

-- 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 ] ] ]
[;]

USE learningsql;

BEGIN

--  
-- Khai báo biến:
DECLARE @v_Emp_ID integer;
DECLARE @v_First_Name varchar(50);
DECLARE @v_Last_Name varchar(50);

DECLARE @v_Count integer;

-- Khai báo một con trỏ (CURSOR).
DECLARE My_Cursor CURSOR FOR
SELECT
 Emp.EMP_ID,
 Emp.FIRST_NAME,
 Emp.LAST_NAME
FROM Employee Emp
WHERE Emp.EMP_ID < 3;



-- Mở Cursor
OPEN My_Cursor;

-- Di chuyển con trỏ từ đến dòng đầu tiên.
-- Và gán các giá trị cột vào các biến.
FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;

-- Trong trường hợp còn bản ghi @@FETCH_STATUS = 0.
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT 'First Name = '+ @v_First_Name+' / Last Name = '+ @v_Last_Name;

 -- Di chuyển đến bản ghi tiếp theo.
 -- Và gán các giá trị cột vào các biến.
 FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;
END

-- Đóng Cursor.
CLOSE My_Cursor;
DEALLOCATE My_Cursor;

END;

USE learningsql;

BEGIN

--  
-- Khai báo biến:
DECLARE @v_Emp_ID integer;
DECLARE @v_First_Name varchar(50);
DECLARE @v_Last_Name varchar(50);

-- Khai báo một biến kiểu con trỏ.
DECLARE @My_Cursor CURSOR;

-- Sét câu lệnh truy vấn cho con trỏ.
Set @My_Cursor = CURSOR FOR
SELECT
 Emp.EMP_ID,
 Emp.FIRST_NAME,
 Emp.LAST_NAME
FROM Employee Emp
WHERE Emp.EMP_ID < 3;


-- Mở Cursor
OPEN @My_Cursor;

-- Di chuyển con trỏ đến dòng đầu tiên.
-- Và gán các giá trị cột vào các biến.
FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;

-- Trong trường hợp có bản ghi @@FETCH_STATUS = 0.
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT 'First Name = '+ @v_First_Name+' / Last Name = '+ @v_Last_Name;

 -- Di chuyển đến bản ghi tiếp theo.
 -- Và gán các giá trị cột vào các biến.
 FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;
END

-- Đóng Cursor.
CLOSE @My_Cursor;
DEALLOCATE @My_Cursor;

END;

USE learningsql;

BEGIN

--  
-- Khai báo biến:
DECLARE @v_a float = 20;
DECLARE @v_b float = 0;
DECLARE @v_c float;
DECLARE @v_Error_Number integer;

-- Sử dụng BEGIN TRY .. END TRY để bẫy lỗi.
-- Nếu lỗi xẩy ra trong khối này
-- nó sẽ nhẩy vào khối BEGIN CATCH .. END CATCH.
BEGIN TRY

  ---
  PRINT '@v_a = ' + CAST(@v_a AS varchar(15));
  PRINT '@v_b = ' + CAST(@v_b AS varchar(15));
  --Lỗi chia cho 0 xẩy ra tại đây.
  SET @v_c = @v_a / @v_b;

  -- Dòng bên dưới này sẽ không được chạy.
  -- Chương trình nhẩy vào khối BEGIN CATCH .. END CATCH
  PRINT '@v_c= ' + CAST(@v_c AS varchar(15));

END TRY
-- BEGIN CATCH .. END CATCH phải được đặt ngay
-- phía sau của khối BEGIN TRY .. END TRY.
BEGIN CATCH
  -- Mã lỗi.
  SET @v_Error_Number = ERROR_NUMBER();
  -- In ra mã lỗi:
  PRINT 'Error Number: ' + CAST(@v_Error_Number AS varchar(15));
  -- Nguyên nhân lỗi:
  PRINT 'Error Message: ' + ERROR_MESSAGE();
  --  Mức độ nghiêm trọng của lỗi:
  PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(15));
  -- Mã trạng thái:
  PRINT 'Error State: ' + CAST(ERROR_STATE() AS varchar(15));
  -- Dòng bị lỗi:
  PRINT 'Error Line: ' + CAST(ERROR_LINE() AS varchar(15));
  -- Tên của thủ tục (hoặc function) hoặc trigger, có code gây ra lỗi này.
  PRINT 'Error Procedure: ' + ERROR_PROCEDURE();
END CATCH;

END;

-- function_name:  Tên hàm
-- argument:  Tên tham số
-- mode:  INPUT, OUTPUT,  hoặc không cần viết.
-- datatype:  Kiểu dữ liệu của tham số

CREATE FUNCTION <function_name>
           (
              [
               @argument1 datatype1 [mode1] ,
               @argument2  datatype2 [mode2],
               ...
               ]
            )
           RETURNS  datatype
AS
BEGIN
 -- Khai báo biến sử dụng
 -- Code nội dung của hàm
 -- Trả về giá trị của hàm.
END;

-- Một hàm có tham số:
CREATE FUNCTION Sum_Ab(a Integer, b Integer)
RETURNS Integer
AS
Begin
return a + b;
End;

-- Một hàm không tham số:
CREATE FUNCTION Get_Current_Datetime()
RETURNS Date
AS
Begin
return CURRENT_TIMESTAMP;
End;

-- Hủy Function

DROP FUNCTION <function_name>;

-- Ví dụ:

DROP FUNCTION My_Function;

-- Kiểm tra xem Hàm này đã tồn tại chưa.
-- Nếu đã tồn tại cần phải hủy để có thể tạo mới.
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

 -- Khai báo một biến Float
 DECLARE @v_C float;

 -- Sét giá trị cho biến v_C
 SET @V_C = @p_A + @p_B;

 -- Giá trị trả về của hàm.
 RETURN @v_C;

END;

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;

-- procedure_name:  Tên thủ tục
-- argument:  Tên tham số
-- mode:  Loại tham số: INPUT hoặc OUTPUT, mặc định là INPUT
-- datatype:  Kiểu dữ liệu của tham số
-- Chú ý: Với thủ tục các tham số có thể đặt trong dấu () hoặc không cần thiết.

CREATE PROCEDURE <procedure_name>
     [
      argument1  datatype1 [mode1]  ,
      argument2   datatype2 [mode2]  ,
     ...
      ]
AS
BEGIN
 -- Khai báo biến sử dụng
 -- Nội dung của thủ tục.
END;

-- Hoặc:

CREATE PROCEDURE <procedure_name>
   (
     [
      argument1  datatype1 [mode1]  ,
      argument2   datatype2 [mode2]  ,
     ...
      ]
   )
AS
BEGIN
 -- Khai báo biến sử dụng
 -- Nội dung của thủ tục.
END;

-- Ví dụ một thủ tục không tham số.
CREATE Procedure Do_Something
AS
Begin
     -- Khai báo biến tại đây.
     Declare @v_a Integer;
      -- Làm gì đó tại đây.
      -- ....
End;

-- Ví dụ một thủ tục có tham số
--  Có một tham số đầu vào và một tham số đầu ra.

CREATE Procedure Do_Something (@p_Param1 Varchar(20),
                             @v_Param2 Varchar(50) OUTPUT )
AS
Begin
  -- Khai báo biến tại đây.  
  Declare @v_a Integer;

  -- Làm gì đó tại đây.
  -- ...
End;

-- Hủy một thủ tục:

DROP PROCEDURE <Procedure_Name>

-- Huỷ procedure Get_Employee_Infos nếu nó đã tồn tại.
-- (Để cho phép tạo lại)
IF OBJECT_ID(N'dbo.Get_Employee_Infos', N'P') IS NOT NULL
DROP PROCEDURE Get_Employee_Infos;
GO

-- Thủ tục truyền vào p_Emp_Id
-- Và trả về 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
-- Sử dụng lệnh Print để in ra một chuỗi (Dành cho lập trình viên).
-- Sử dụng Cast để ép kiểu số Integer về dạng chuỗi (Varchar).
-- Sử dụng toán tử + để nối hai chuỗi.
PRINT 'Parameter @p_Emp_Id = ' + CAST(@p_Emp_ID AS varchar(15));
--
-- Query dữ liệu từ bảng và gán giá trị vào các biến.
--
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 dành cho người lập trình
--
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;

-- Insert một Sinh viên vào bảng Student.
Insert into Student (Studen_Id, Student_Name, Class_ID)
values (100, 'Tom', 1);

-- Cập nhập sĩ số của lớp học.
Update Class_Table
set Student_Count = Student_Count + 1
Where Class_Id = 1;

BEGIN

-- Trong ví dụ này các tài khoản Account_ID = 1, 2 thực sự tồn tại trong DB
-- Trong thực tế bạn có thể viết các câu lệnh kiểm tra trước khi bắt đầu giao dịch.
--
-- Tài khoản người A (Đã đảm bảo tồn tại trong DB)
DECLARE @Account_Id_A integer = 1;
-- Tài khoản người B (Đã đảm bảo tồn tại trong DB)
DECLARE @Account_Id_B integer = 2;
-- Số tiền chuyển:
DECLARE @Amount float = 10;
-- Giao dịch tại ngân hàng:
DECLARE @Execute_Branch_Id integer = 1;

-- Ghi ra số Transaction hiện thời.
-- Thực tế lúc này chưa có giao dịch nào.
PRINT '@@TranCount = ' + CAST(@@Trancount AS varchar(5));

PRINT 'Begin transaction';

-- Bắt đầu giao dịch
BEGIN TRAN;

 -- Bẫy lỗi.
 BEGIN TRY
   --
   -- Trừ tiền trong tài khoản người A đi 10$ (Account_ID = 1)
   UPDATE Account
   SET AVAIL_BALANCE = AVAIL_BALANCE - @Amount
   WHERE Account_Id = @Account_Id_A;
   --
   -- Ghi thông tin thời điểm giao dịch vào bảng Acc_Transaction.
   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);
   --
   -- Cộng tiền vào tài khoản người B thêm 10$
   UPDATE Account
   SET AVAIL_BALANCE = AVAIL_BALANCE + @Amount
   WHERE Account_Id = @Account_Id_B;
   --
   -- Ghi thông tin thời điểm giao dịch vào bảng Acc_Transaction.
   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);
   -- Hoàn thành giao dịch
   IF @@Trancount > 0
     PRINT 'Commit Transaction';
 COMMIT TRAN;

END TRY
-- Nếu có lỗi khối Catch sẽ được chạy.
BEGIN CATCH
 PRINT 'Error: ' + ERROR_MESSAGE();
 PRINT 'Error --> Rollback Transaction';
 IF @@Trancount > 0
   ROLLBACK TRAN;
END CATCH;



END;