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

9- Hàm (Function)

Giống như procedure (thủ tục), function (hàm ) là nhóm các lệnh T-SQL thực hiện chức năng nào đó. Khác với thủ tục, các hàm sẽ trả về một giá trị ngay tại lời gọi nó.

Hàm cũng có thể được lưu giữ ngay trên database dưới dạng Store procedure.

Cú pháp tạo function (Hàm).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

Ví dụ:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

-- 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):

1 2 3 4 5 6 7

-- Hủy Function   DROP FUNCTION <function_name>;   -- Ví dụ:   DROP FUNCTION My_Function;

Ví dụ tạo một hàm.

Đây là một ví dụ tạo một function (hàm) đầu tiên của bạn với SQL Server:

1. Tạo một hàm (Function)
2. Biên dịch hàm này
3. Chạy hàm

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

Nhấn biểu tượng  để biên dịch hàm.

Hàm bạn vừa tạo ra ở trên là một hàm đơn giản trả về một giá trị vô hướng (Scalar-value). Bạn có thể nhìn thấy nó đã được tạo ra trên SQLServer Management Studio:

Bạn có thể test hàm bằng cách nhấn phải chuột vào hàm, chọn:

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

Một cửa sổ test được mở ra, bạn có thể sửa đổi các tham số truyền vào:

Sửa đổi các giá trị tham số truyền vào và nhấn nút thực thi để test.

Các hàm có thể tham gia vào câu lệnh SELECT.

1 2 3 4 5 6 7

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;

Kết quả chạy câu lệnh SQL trên:

10- Thủ tục (Procedure)

Một nhóm các lệnh T-SQL thực hiện chức năng nào đó có thể được gom lại trong một thủ tục (procedure) nhằm làm tăng khả năng xử lý,khả năng sử dụng chung, tăng tính bảo mật và an toàn dữ liệu, tiện ích trong phát triển.

Thủ tục có thể được lưu giữ ngay trong database như một đối tượng của database, sẵn sàng cho việc tái sử dụng. Thủ tục lúc này được gọi là Store procedure. Với các Store procedure, ngay khi lưu giữ Store procedure, chúng đã được biên dịch thành dạng p-code vì thế có thể nâng cao khả năng thực hiện.

Thủ tục không trả về giá trị trực tiếp như hàm ( function). Tuy nhiên nó có thể có 0 hoặc nhiều tham số đầu ra.

Cú pháp tạo một thủ tục:

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

-- 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ụ:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

-- 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 thủ tục (Drop procedure):

1 2 3

-- Hủy một thủ tục:   DROP PROCEDURE <Procedure_Name>

Các bước thực hiện một thủ tục:

Ví dụ tạo một thủ tục:

Ở đâ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.

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

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

Nhấn vào biểu tượng  để biên dịch thủ tục.

Thủ tục đã được tạo ra, bạn có thể nhìn thấy trên SQL Server Management Studio:

Test thủ tục

Trong lập trình việc test một thủ tục và dò tìm lỗi là vấn đề rất quan trọng. Nhấn phải chuột vào thủ tục muốn test, chọn:

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

Kịch bản test được tạo ra (Mặc định) như hình minh họa dưới đây:

Sét đặt giá trị cho các tham số đầu vào:

Nhấn nút thực thi thủ tục:

11- Xử lý giao dịch (Transaction)

11.1- Tại sao cần xử lý giao dịch?

Giao dịch (Transaction) là một khái niệm quan trọng trong SQL. Hãy xem một tình huống:

Một giao dịch trong ngân hàng, người A chuyển cho người B một khoản tiền 100$, khi đó trong Database xẩy ra 2 thao tác:

1. Trừ tiền của người A đi 100$
2. Cộng tiền vào cho người B 100$.

Điều gì sẽ xẩy ra nếu chỉ có 1 thao tác thành công?

Xem một ví dụ khác:

Khi bạn thêm một sinh viên vào một lớp học bạn cập nhập lại sĩ số của lớp học. Nếu việc trèn thông tin sinh viên không thành công mà sĩ số lại được cộng thêm 1, tính toàn vẹn của dữ liệu bị hỏng.

1 2 3 4 5 6 7 8

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

Giao dịch được coi là thành công nếu tất cả các đơn vị lệnh thành công. Ngược lại một trong các đơn vị lệnh bị lỗi, toàn bộ giao dịch cần phải được trả về (rollback) trạng thái ban đầu.

11.2- Khai báo và sử dụng giao dịch (Transaction)

Các lệnh liên quan:

1. Bắt đầu transaction:
   • begin tran / begin transaction
2. Hoàn tất transaction:
   • commit/ commit tran / commit transaction
3. Quay lui transaction (Rollback transaction):
   1. rollback / rollback tran / rollback transaction
4. Đánh dấu một savepoint trong transaction: save transaction tên_của_savepoint
5. Biến @@trancount: cho biết số transaction hiện đang thực hiện (chưa được kết thúc với rollback hay commit) trong connection hiện hành.

Ghi chú:

1. Lệnh rollback tran + tên_của_savepoint có tác dụng quay lui (rollback) giao dịch đến vị trí đặt savepoint tương ứng (không có tác dụng kết thúc transaction), các khóa (locks) đã được thiết lập khi thực hiện các thao tác nằm trong phần bị rollback sẽ được mở ra (unlock).
2. Khi khai báo transaction tường minh, phải đảm bảo rằng sau đó nó được rollback hoặc commit tường minh, nếu không, transaction sẽ tiếp tục tồn tại và chiếm giữ tài nguyên, ngăn trở sự thực hiện của các transaction khác.
3. Lệnh rollback chỉ có tác dụng quay lui các giao dịch trên cơ sở dữ liệu (insert, delete, update). Các câu lệnh khác, chẳng hạn lệnh gán, sẽ không bị ảnh hưởng bởi lệnh rollback.

Ví dụ:

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

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;

Kết quả chạy ví dụ: