Vì sao nên tránh viết SQL code trong ứng dụng

Trong ứng dụng khi cần tương tác với database, có lẽ một cách làm rất phổ biến là tạo lập một chuỗi chứa lệnh SQL, ghép các giá trị  nhập vào của người dùng thành một lệnh SQL hoàn chỉnh, rồi thực hiện chuỗi lệnh SQL đó.

Như ví dụ dưới đây:

string cmdStr = "INSERT INTO Customer(Name, Address, Email, Phone) VALUES('" + txtName.Text + "', '" + txtEmail.Text + "','" + txtPhone.Text + "')";
conn.Open();
SqlCommand cmd = new SqlCommand(cmdStr, conn);
cmd.ExecuteNonQuery();
Cách làm này có ưu điểm tiện lợi, giúp quá trình phát triển code nhanh (không phải chuyển qua lại giữa Visual Studio và Management Studio). Tuy nhiên nó tiềm ẩn rất nhiều vấn đề (cách làm tối ưu là viết một thủ tục trong database rồi từ ứng dụng gọi thủ tục này và truyền các tham số cho nó):

1. An ninh: việc viết lệnh SQL thẳng trong ứng dụng như vậy sẽ tạo ra lỗ hổng SQL injection, tức là hacker có thể khéo léo nhập thẳng vào trường text một chuỗi có chứa đoạn lệnh SQL và để cho database sẽ thực hiện đoạn lệnh đó. Ví dụ, khi hacker nhập vào trường txtPhone giá trị:
123');delete from order--
thì đoạn lệnh mà ứng dụng gửi cho SQL Server sẽ là:
INSERT INTO Customer(Name, Address, Email, Phone) Values('','','123');delete from order--')
Chú ý là hacker cố tình đưa hai dấu gạch ngang "--" vào cuối để biến toàn bộ đoạn ký tự phía sau thành đoạn giải thích. Kết quả là SQL Server sẽ thực hiện hai lệnh, "INSERT INTO Customer…" và "delete from order", và bảng order bị xóa sạch. Tương tự hacker có thể đưa vào các lệnh khác như "drop table order" để xóa hẳn bảng khỏi database, hoặc "select * from user" để lấy hết thông tin tài khoản của người dùng.

Nếu dùng thủ tục thì vấn đề hoàn toàn bị hóa giải, vì toàn bộ giá trị của trường text sẽ được lưu vào cột tương ứng trong bảng bên trong database. Khi đó chỉ có một lệnh INSERT được thực hiện và giá trị của Phone của bản ghi mới sẽ là "123');delete from order--"
 
2. Hiệu năng (performance): cách làm trên sẽ dẫn đến mỗi lần thực hiện SQL Server sẽ biên dịch lại câu lệnh. Khi SQL Server nhận được một câu lệnh, nó sẽ kiểm tra xem câu lệnh này đã có kế hoạch thực thi lưu trong cache hay chưa. Nó băm (hash) câu lệnh để chuyển thành một con số và đối chiếu với bảng băm trong cache, nếu tìm thấy có nghĩa là câu lệnh này đã thực hiện trước đó rồi và SQL Server dùng luôn kế hoạch thực thi đã có sẵn. Nếu không tìm thấy có nghĩa đây là câu lệnh mới và SQL Server sẽ biên dịch, tạo kế hoạch thực thi, lưu vào cache, và thực hiện câu lệnh. Vì với hàm băm, câu lệnh chỉ cần khác đi một chút (chỉ cần thêm một dấu cách) là đã cho số băm khác nhau, nên với mỗi giá trị người dùng nhập vào sẽ tạo thành một câu lệnh mới và SQL Server lại phải trải qua các bước biên dịch, tạo kế hoạch thực thi, lưu vào cache trước khi tiến hành thực hiện câu lệnh.

Trong nhiều trường hợp, chi phí cho các bước kể trên có thể rất lớn. Ví dụ với một câu lệnh mất 100 mili giây để biên dịch trong khi cũng mất 100 mili giây để thực hiện, thì chi phí để thực hiện câu lệnh này bị tăng gấp đôi. Một hậu quả khác là mỗi lần biên dịch thì kế hoạch thực thi mới sẽ chiếm chỗ trong cache, trong khi kích thước của cache chỉ có hạn. Đến một lúc kế hoạch thực thi của các câu lệnh khác sẽ bị loại khỏi cache để giải phóng chỗ, và đến khi các câu lệnh khác đó được thực hiện thì lại cần biên dịch lại. Như vậy hiệu năng của toàn bộ hệ thống bị giảm.

Khi dùng thủ t��c thì tình huống sẽ thay đổi hẳn, vì SQL Server chỉ băm câu lệnh gọi đến thủ tục mà bỏ qua các tham số, cho nên khi đã thực hiện EXEC Proc1 @param=1 thì đến khi gặp EXEC Proc1 @param=2 SQL Server không cần biên dịch lại thủ tục nữa.
 
3. Bảo mật: Khi ta viết thẳng lệnh SQL vào ứng dụng như trên thì user được dùng để kết nối vào database (trong connection string) cần phải có quyền INSERT vào bảng. Thông thường các ứng dụng có đủ các thao tác đọc/ghi/xóa vào database, cho nên user trên cũng đòi hỏi đủ các quyền SELECT/INSERT/UPDATE/DELETE vào các bảng trong database. Khi hacker chiếm được quyền truy nhập vào database với user trên, hắn ta có thể mặc sức tung hoành làm bất cứ điều gì hắn muốn trong database.

Khi dùng thủ tục thì user không cần bất cứ quyền nào trực tiếp trên bảng, user chỉ cần quyền thực thi thủ tục và khi chạy thì thủ tục thực hiện các thao tác trên bảng cho user. Ta có thể dỡ bỏ hết các quyền của user trên tất cả các bảng và chỉ cấp quyền thực thi trên các thủ tục cần thiết. Ta cũng có thể đồng thời dỡ bỏ quyền truy nhập vào các bảng hệ thống, cho nên nếu hacker có truy nhập được vào database thông qua user trên, hắn ta sẽ mù tịt không biết database có các bảng nào để mà phá.

Nếu hắn truy nhập được vào mã nguồn của ứng dụng thì có thể biết được tên của các thủ tục được dùng trong ứng dụng và chạy các thủ tục này, nhưng mức độ phá hoại của hacker bị khống chế ở mức thấp hơn nhiều so với khi user có đủ các quyền.


 4. Bảo trì: thường có những đoạn lệnh SQL được dùng lại ở một vài nơi khác nhau trong ứng dụng, hoặc thậm chí ở các ứng dụng khác nhau truy nhập chung vào một database. Khi viết thẳng SQL code trong ứng dụng ta sẽ phải viết lại đoạn lệnh trên ở tất cả những nơi nó được dùng. Điều này đã phạm vào lỗi "lặp lại code" (duplication of code) trong phát triển phần mềm. Khi cần phải sửa lại câu lệnh SQL trên (vì lỗi hoặc cần viết theo cách tối ưu hơn, hoặc cấu trúc database thay đổi dẫn đến cần viết lại) ta sẽ phải tìm đến tất cả các nơi có dùng câu lệnh SQL đó để sửa.

Với thủ tục thì ta chỉ cần sửa ở một nơi và nó có tác dụng cho toàn ứng dụng. Giống như .net tách mã chương trình ra khỏi mã html, dùng thủ tục cũng tách mã SQL ra khỏi mã chương trình. Lúc đó thủ tục có chức năng như là cổng truy nhập, hay API, mà qua đó ứng dụng giao tiếp với database. Nó tạo thành một lớp ngăn cách giữa ứng dụng và database, và che dấu toàn bộ cấu trúc database khỏi ứng dụng. Ứng dụng không cần biết database gồm có những bảng gì, mỗi bảng có các cột nào, hay các bảng quan hệ với nhau ra sao. Vì thế ta có thể dễ dàng thay đổi cấu trúc của database khi cần và sửa lại các thủ tục có liên quan mà không ảnh hưởng gì đến ứng dụng.
 
Kết luận: Việc viết mã SQL trong ứng dụng có rất nhiều vấn đề như đã chỉ ra. Với những ứng dụng nhỏ hoặc ứn dụng có những đặc thù nhất định, những vấn đề trên có thể không bộc lộ hết ra, nhưng khi ta quyết định chọn phương pháp này thì cũng cần ý thức được những hệ quả có thể xảy ra của nó. Có những trường hợp ta không muốn lưu mã nguồn trong database, ví dụ để ứng dụng có thể dễ dàng chuyển đổi giữa các hệ CSDL khác nhau. Khi đó ta bắt buộc phải viết SQL code trong ứng dụng, nhưng để giảm nhẹ các vấn đề của nó, ta nên dùng chuỗi SQL có tham số (dùng cmd.Parameters.Add) để tránh được lỗi SQL injection và biên dịch lại câu lệnh (điểm 1 và 2 ở trên). Còn khi ta có lựa chọn dùng thủ tục thì nên áp dụng phương pháp này. Đây là phương pháp mà Microsoft khuyến cáo khi viết ứng dụng trên môi trường phát triển của họ.

So sánh hai cách xóa dữ liệu DELETE và TRUNCATE

SQL Server cung cấp 2 phương pháp để xóa dữ liệu, DELETE và TRUNCATE. Cú pháp của hai lệnh này như sau:

DELETE

DELETE dbo.Tblxxx WHERE...

hoặc

DELETE a FROM dbo.Tblxxx a WHERE...

Khi cần xóa dữ liệu với điều kiện liên quan đến bảng khác:

DELETE a
FROM dbo.Tblxxx a
JOIN dbo.Tblyyy b ON a.Col1 = b.Col1

hoặc:

DELETE a
FROM dbo.Tblxxx a
WHERE EXISTS(SELECT 1 FROM dbo.Tblyyy b WHERE a.Col1 = b.Col1)

TRUNCATE không có tùy biến nào

TRUNCATE TABLE dbo.Tblxxx

Tuy cùng để xóa dữ liệu, nhưng hai lệnh này có những khác nhau cơ bản:

+ DELETE cung cấp các lựa chọn để xóa những dòng dữ liệu thỏa mãn các điều kiện nhất định, như WHERE hoặc JOIN với các bảng khác.
+ TRUNCATE không có lựa chọn nào, mà luôn cắt bỏ toàn bộ dữ liệu của bảng. Nói cách khác, ta không thể TRUNCATE 1 nửa hay 1 phần của bảng.

+ DELETE hỗ trợ transaction. Khi lệnh DELETE nằm trong 1 transaction và trong một tình huống nào đó transaction được ROLLBACK thì các bản ghi bị xóa bởi lệnh DELETE sẽ trở lại bảng không có gì suy xuyển.
+ TRUNCATE thì ngược lại, không hỗ trợ transaction. Một khi đã thực hiện thì không thể lấy lại dữ liệu được nữa.

+ DELETE khi thực hiện bao gồm quá trình tìm các bản ghi thỏa mãn điều kiện của câu lệnh, và xóa các bản ghi này. Việc tìm các bản ghi cần xóa được thực hiện giống hệt như một câu lệnh SELECT, cũng tối ưu hóa, lựa chọn giữa các phương án thực hiện khác nhau và chọn ra phương án tối ưu (dựa vào index, statistics…).
+ TRUNCATE thì chỉ có một phương án thực hiện duy nhất, đó là cắt bỏ tất cả các dòng dữ liệu của bảng.

+ Với DELETE, các bản ghi bị xóa sẽ được kiểm tra xem có vi phạm ràng buộc FOREIGN KEY không. Ví dụ ta có hai bảng MAT_HANG và BAN_HANG là quan hệ 1-n thông qua MA_MH; nếu MA_MH=1 đã có giao dịch, nghĩa là bảng BAN_HANG đã có bản ghi với MA_MH=1, thì khi DELETE bản ghi với MA_MH=1 từ bảng MAT_HANG (bảng cha) SQL SERVER sẽ báo lỗi và không cho xóa.
Nếu trước đó, khi ta định nghĩa ràng buộc FOREIGN KEY mà có lựa chọn CASCADE DELETE, thì thay vì báo lỗi SQL Server sẽ đồng thời xóa hết các bản ghi trong cả bảng BAN_HANG với MA_MH=1.
+ TRUNCATE thì không có những đoạn kiểm tra dài dòng như thế. Nếu bảng có ràng buộc FOREIGN KEY, SQL Server sẽ báo lỗi và không cho thực hiện (nhớ là lựa chọn CASCADE DELETE trong khai báo FOREIGN KEY chỉ ảnh hưởng đến lệnh DELETE chứ không tác dụng đối với TRUNCATE).

+ Vì DELETE hỗ trợ transaction và dùng transaction log, nó có thể dùng với bảng nằm trong một replication hoặc database có dùng log shipping.
+ TRUNCATE thì vì không ghi gì vào transaction log nên khi gặp một trong các tình huống trên sẽ bị từ chối ngay.

+ Với DELETE, nếu bảng có index thì các index cũng sẽ được cập nhật để xóa đi các node tương ứng với các bản ghi bị xóa.
+ TRUNCATE thì rất đơn giản, các index của bảng cũng bị cắt cụt theo.

+ DELETE không ảnh hưởng đến giá trị IDENTITY. Nếu bảng có 100 bản ghi và cột IDENTITY có giá trị từ 1-100; nay ta DELETE bản ghi có cột IDENTITY=100 rồi INSERT một bản ghi mới; bản ghi mới sẽ có cột IDENTITY=101.
+ TRUNCATE luôn đặt lại IDENTITY trở về 1. Bản ghi đầu tiên được INSERT sau khi TRUNCATE sẽ có cột IDENTITY=1.

+ DELETE thực ra chỉ đánh dấu xóa các bản ghi chứ ngay sau đó dữ liệu của các bản ghi bị xóa vẫn nằm nguyên tại chỗ. Dần dần khi ta INSERT thêm dữ liệu vào bảng thì các bản ghi mới sẽ ghi đè lên các vùng lưu trữ đó. Ta có thể kiểm tra để thấy kích thước bảng không thay đổi ngay cả sau khi chạy DELETE FROM TblName (xóa hết các bản ghi).
+ TRUNCATE thì xóa hết dữ liệu đồng thời giải phóng vùng lưu trữ giành cho bảng, trả lại cho SQL Server. Ta có thể so sánh DELETE như là xóa file, còn TRUNCATE thì như format lại ổ cứng.

Vì những lý do trên, DELETE luôn luôn chậm hơn TRUNCATE. Càng có nhiều bản ghi DELETE càng chậm, còn TRUNCATE thì không phụ thuộc vào lượng dữ liệu. DELETE có phạm vi ứng dụng rộng hơn; còn TRUNCATE thì chỉ dùng được mỗi một việc, nhưng nó lại làm rất nhanh. Vì vậy, hãy nhớ dùng TRUNCATE khi có thể được.

 

Tính giai thừa của số nguyên trong Mssql

Bài này và trong các bài tiếp sau tôi sẽ minh họa cho bạn cách để lập trình với ngôn ngữ lập trình SQL. Bài này minh họa bằng việc tính giai thừa của một số nguyên. (beginner)

Thuật toán thì chắc hẳn bạn đã biết công thức tính giai thừa của số nguyên: N! = 1*2*3*...*(N-1)*N

Để minh họa cho dễ hiểu tôi viết hàm này trong C# như sau:

private long fGiaiThua(int n)
{
    long k = 1;
    int i = 1;
    while (i <= n)
    {
        k = k * i;
        i++;
    }
    return k;
}
Tương ứng là hàm viết trong SQL như sau:
CREATE FUNCTION fGiaiThua(@n int)
RETURNS bigint
AS
BEGIN
    DECLARE @k bigint SET @k=1
    DECLARE @i int SET @i=1
    WHILE @i<=@n
    BEGIN
        SET @k=@k*@i
        SET @i=@i+1
    END
    RETURN @k
END

Giờ tôi sẽ chia bảng và so sảng để bạn thấy lập trình với SQL cũng như ngôn ngữ khác thôi

CSHARP

SQL

Khai báo hàm fGiaiThua: public|privare KieuTraVe TenHam(DanhSachCacBien)
private long fGiaiThua(int n)
Khai báo hàm fGiaiThua: create function TenHam(DanhSachCaBien) returns KieuTraVe
create function fGiaiThua(@n int) returns bigint
Khai báo biến kết quả của hàm:
long k = 1;
Khai báo biến kết quả của hàm:
DECLARE @k bigint SET @k=1 (với SQL2008 DECLARE @k bigint =1)
Dùng vòng lặp:
long k = 1;
int i = 1;
while (i <= n)
{
k = k * i;
i++;
}
Dùng vòng lặp:
DECLARE @k bigint SET @k=1
DECLARE @i int SET @i=1
WHILE @i<=@n
BEGIN
SET @k=@k*@i
SET @i=@i+1
END
Trả về kết quả
return k;
Trả về kết quả
return @k

So sánh giữa hai ngôn ngữ bạn thấy lập trình với SQL cũng không khó nhỉ. Nếu muốn tìm hiểu về lập trình SQL bạn thử viết một hàm bẳng C# sau đó chuyển hàm đó sang SQL. Khi làm được vài ví dụ bạn sẽ quen và có khi thấy thích lập trình trên SQL hơn là trên C#.

Cách truyền các tham số trong Store Procedure

Trong Store Procedure của bạn có khi bạn cần truyền tham số vào để thực hiện với yêu cầu truy vân thực tế nào đó. Ví dụ khi bạn viết Store và bạn muốn truy vấn Top 10 hay Top 20 bản ghi? Nhưng Tham số Top này lại không cố định không lẽ khi muốn Select top 10 bạn lại viết một câu truy vấn hoặc Srore, Muốn Select Top 20 bạn lại viết một câu truy vấn khác? Điều này có cách thực hiện đó là bạn truyền biết @Top vào trong một Store Procedure.
Nhưng khi bạn khai báo biết @Stop thì bạn cũng không thể sử dụng câu truy vấn Select @Top {Danh sách các trường} from TableName được. Vậy cách thực hiện ở đây là bạn khai báo một biến  (@SQL) và truyền nó vào biến này sau đó thực hiện Execute biến này như ví dụ sau:

Ví dụ 1:

CREATE PROCEDURE spGetTop
    @Top int
AS
BEGIN
    DECLARE @SQL nvarchar(4000) SET @SQL=''
    SET  @SQL =@SQL+ ' SELECT Top '+Cast(@Top AS varchar(12)) +' 
        CustomerID, 
        CompanyName, 
        ContactName, 
        ContactTitle  
            FROM Customers c'
    EXEC   (@SQL)
END

Trong ví dụ trên mình đã tạo một Store để truy vấn các trường CustomerID, CompanyName, ContactName, ContactTitle  của bảng Customers trong cơ sở dữ liệu Northwind. Bạn chú ý là biến truyền vào là kiểu Int nên khi bạn đưa vào biến @SQL là kiểu nvarchar bạn cần thực hiện convert sang varchar bằng hàm Cast. Khi thực thi Store trên: Nếu muốn truy vấn 10 bản ghi bạn EXEC spGetTop 10,Cần truy vấn 20 bản ghi bạn EXEC spGetTop 20.

Một ví dụ khác là bạn có thể truyền tên của bảng cần lấy dữ liệu vào trong Store. Khi bạn viết một Store truy vấn dữ liệu từ một bảng nào đó mà dữ liệu truy vấn từ tên bảng lại có thể thay đổi. Mình lấy ví dụ như sau:

Ví dụ 2:

CREATE PROCEDURE spGetTableName
    @TableName nvarchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(4000) SET @SQL=''
    SET  @SQL =@SQL+ ' SELECT * FROM '+@TableName
    EXEC   (@SQL)
END

Trong ví dụ 2 này mình minh họa cách truyền tên bảng (@TableName vào trong Store).
- Khi thực thi Store này bạn muốn truy vấn dữ liệu từ bảng Customers bạn thực thi như sau: EXEC spGetTableName 'Customers' .
- Khi bạn muốn truy vấn dữ liệu từ bảng Products bạn thực thi như sau: EXEC spGetTableName 'Products'

Trong ví dụ trên mình minh họa cách truyền Tên bảng Vào Store với Select *. Vậy khi bạn muốn truyền cả tên bảng, cả danh sách các trường cần truy vấn? Ví dụ sau mình sẽ minh họa cho bạn cách truyền cả tên bảng, cả danh sách các trường muốn lấy dữ liệu.

Ví dụ 3:

CREATE PROCEDURE spGetTableNameAndField
    @TenBang nvarchar(50), 
    @DanhSachCacTruong nvarchar(200)
AS
BEGIN
    DECLARE @SQL nvarchar(4000) SET @SQL=''
    SET @SQL='SELECT '+ @DanhSachCacTruong + ' FROM '+@TenBang
    EXEC (@SQL)
END

Giả sử mình muốn truy vấn các trường CustomerID, CompanyName, ContactName, ContactTitle của bảng  Customers bạn thực thi như sau:

EXEC spGetTableNameAndField 'Customers','CustomerID, CompanyName, ContactName, ContactTitle '

Trên đây mình minh họa cách truyền các tham số vào trong Store Procedure để bạn tham khảo. Tùy điều kiện yêu cầu cụ thể của bạn mà bạn thực hiện truyền các tham số cho phụ hợp. Đọc thêm bài viết Quan hệ C# và Database :: Stored Procedure Về Store Procedure

 

Function và Trigger

Các bài viết trong: Quan hệ C# và Database
Phần 1: Kết nối C# với Database
Phần 2: Sql Command
Phần 3: SqlDataReader & Dataset
Phần 4: Stored Procedure
Phần 5: Function và Trigger

Trong bài trước mình đã giới thiệu về Stored ProcedureTrong bài tiếp theo của loạt bài Quan hệ C# và Database mình sẽ giới thiệu về  Function và Trigger - một phần cũng không kém phần quan trong trong lập trình với cơ sở dữ liệu

1. Hàm - Functions


Cũng giống như Stored Procedure Hàm là một đối tượng trong cơ sở dữ liệu bao gồm một tập nhiều câu lệnh SQL được nhóm lại với nhau thành một nhóm. Điểm khác biệt giữa hàm và thủ tục là hàm trả về một giá trị thông qua tên hàm. Điều này cho phép ta sử dụng hàm như là một thành phần của một biểu thức chẳng hạn như trong các câu lệnh truy vấn hay các câu lệnh thực hiện cập nhật dữ liệu

Trong SQL có rất nhiều các hàm được định nghĩa sẵn (Được chia theo nhóm - Trong 1 Database bạn chọn Programmability/Functions/System Functions) như các hàm về chuỗi (String Functions), các hàm về ngày tháng (Date and Time Functions), Các hàm toán học (Mathematical Function), ... Ngoài những hàm do hệ quản trị cơ sở dữ liệu cung cấp sẵn, bạn có thể tự xây dựng các hàm nhằm phục vụ cho mục đích riêng của mình - Các hàm do người dùng định nghĩa. Các hàm do người dùng định nghĩa thường có 2 loại: Loại 1 là Hàm với giá trị trả về là "dữ liệu kiểu bảng" - Table-valued Functions; Loại 2 là Hàm với giá trị trả về là một giá trị - Scalar-valued Functions và các hàm này cũng sẽ được Hệ quản trị phân thành 2 nhóm.

Các hàm sẵn có của SQL bạn tự tìm hiểu và sử dụng, trong bài viết này mình giới thiệu qua về những hàm "Do người dùng định nghĩa".

Cú pháp của hàm như sau:

CREATE  FUNCTION dbo.fuGetCurrYear ()  
RETURNS int
AS  
BEGIN 
    RETURN   YEAR(getdate())
END

Xem ví dụ trên bạn sẽ thấy nó rất đơn giản nhưng qua đây bạn cũng đã biết được việc viết hàm trong SQL như thế nào.

Ví dụ 2: Tiếp theo mình sẽ viết một ví dụ nữa để bạn hiểu và có thể viết cho mình các hàm tự định nghĩa:
Ví dụ này sẽ có 2 tham sô trong Danh_Sach_Cac_Tham_So. Hàm sẽ trả về số ngày của tháng, năm do bạn truyền vào; Bạn biết khi lập trình với Pascal bạn đã quen với bài toán tính số ngày của thàng - Với năm nhuận thì tháng 2 có 29 ngày, các năm khác có 28 ngày. (Qua hàm này bạn cũng sẽ hiểu hơn về điều khiển IF (Xem thêm bài viết Kỹ thuật phân trang bằng Store Procedure để hiểu hơn về cách sử dụng IF trong SQL) và sử dụng Case - (Xem bài viết về Hàm Case trong SQL để hiểu hơn về Case)

CREATE  FUNCTION dbo.fuDaysInMonth (
    @Thang  Int,
    @Nam    Int
)  
RETURNS int
AS  
BEGIN   
    DECLARE @Ngay   Int
    IF @Thang = 2 
        BEGIN
            IF ((@Nam % 4 = 0 AND @Nam % 100 <> 0) 
                OR (@Nam % 400 = 0))
                SET @Ngay = 29
            ELSE
                SET @Ngay = 28
        END
    ELSE
        SELECT @Ngay =  
            CASE @Thang
                WHEN 1 THEN 31
                WHEN 3 THEN 31
                WHEN 5 THEN 31
                WHEN 7 THEN 31
                WHEN 8 THEN 31
                WHEN 10 THEN 31
                WHEN 12 THEN 31
                WHEN 4 THEN 30
                WHEN 6 THEN 30
                WHEN 9 THEN 30
                WHEN 11 THEN 30
            END
    RETURN @Ngay
END

Ví dụ 3: Bạn xem tiếp ví dụ sau để xác định thứ trong tuần của một giá trị kiểu ngày

CREATE FUNCTION fuThu
(
    @ngay DATETIME
)
RETURNS NVARCHAR(10)
AS
     BEGIN
          DECLARE @KetQua NVARCHAR(10)
          SELECT @KetQua=CASE DATEPART(DW,@ngay)
                        WHEN 1 THEN N'Chủ nhật'
                        WHEN 2 THEN N'Thứ hai'
                        WHEN 3 THEN N'Thứ ba'
                        WHEN 4 THEN N'Thứ tư'
                        WHEN 5 THEN N'Thứ năm'
                        WHEN 6 THEN N'Thứ sáu'
                        ELSE N'Thứ bảy'
                      END    
          RETURN (@KetQua)  /* Trị trả về của hàm */
END

 

Một hàm khi đã được định nghĩa có thể được sử dụng như các hàm do hệ quản trị cơ sở dữ liệu cung cấp (thông thường trước tên hàm ta phải chỉ định thêm tên của người sở hữu hàm bằng dbo.) như ví dụ dưới đây:

SELECT e.FirstName, e.LastName,
dbo.fuThu(e.BirthDate) AS ThuOfBirth
FROM Employees e
Bạn có thể tham khảo thêm bài viết Xử lý từ khóa tìm kiếm cho bài viết

Tiếp theo mình sẽ nói về Hàm với giá trị trả về là "dữ liệu kiểu bảng"

Nếu đã biết về SQL chắc hẳn bạn đã biết cách tạo View từ các bảng trong CSDL, Nhưng với View bạn không thể truyền các tham số được, điều này phần nào đó làm giảm tính linh hoạt trong việc sử dụng View. Vậy nên khi bạn cần sử dụng dữ liệu dạng View mà có các tham số thì việc sử dụng hàm là một giải pháp hợp lý nhất.
Ví dụ 4: Giả sử Mình tạo 1 View như sau:
CREATE VIEW vProducts
 as
 SELECT     
    Categories.CategoryID, 
    Categories.CategoryName, 
    Products.ProductName, 
    Products.QuantityPerUnit, 
    Products.UnitPrice
FROM         
    Categories INNER JOIN
    Products ON Categories.CategoryID = Products.CategoryID
 WHERE Categories.CategoryID=1
 Bạn xem ví dụ bạn thấy rằng mình tạo ra 1 View vProducts có  Categories.CategoryID=1 và bạn muốn truy vấn các trường của bảng Products ừng với CategoryID=1 và bạn chỉ cần câu lệnh Select * from vProducts là bạn đã có kết quả như ý. Nhưng với những CategoryID khác thì View vProducts  không làm được trừ phi bạn. Vậy bạn thử sử dụng hàm sau để làm minh họa nhé

Ví dụ 5:
Tạo một hàm trả về dữ liệu dạng bảng tùy theo giá trị của biến @CategoryID truyền vào:
CREATE FUNCTION fuGetProducts
(
    @CategoryID int
)RETURNS TABLE
 AS 
    RETURN
    (
        SELECT     
        Categories.CategoryID, 
        Categories.CategoryName, 
        Products.ProductName, 
        Products.QuantityPerUnit, 
        Products.UnitPrice
    FROM         
        Categories INNER JOIN
        Products ON Categories.CategoryID = Products.CategoryID
     WHERE Categories.CategoryID=@CategoryID)
 Bạn chạy thử hàm trên(Chú ý là khi hàm trả về dạng bảng bạn cũng coi đó như 1 table hoặc 1 View và bạn có thể truy vấn theo 1 hay nhiều trường của hàm) như sau:  

SELECT CategoryID, 
CategoryName,
ProductName,
QuantityPerUnit,
UnitPrice 
FROM  dbo.fuGetProducts(1)
Sẽ tra về dữ liệu chính là Select * from vProducts ở trên. Nếu muốn lầy Theo CategoryID=2 bạn dùng câu lệnh Select * from dbo.fuGetProducts(2)...

Trên đây chỉ là một ví dụ nhỏ về hàm trả lại dữ liệu kiểu bảng hy vọng bạn sẽ hiểu phần nào về loại hàm này. Trong thực tế chúng ta sẽ cần nó để thực hiện các yêu cầu phức tạp hơn tùy vào dữ liệu thiết kế, quan hệ dữ liệu và yêu cầu mà bạn viết hàm để sử dụng

2. Trigger

Cũng tương tự như thủ tục lưu trữ (Stored Prodedure), một trigger là một đối tượng chứa một tập các câu lệnh SQL và tập các câu lệnh này sẽ được thực thi khi trigger được gọi. Điểm khác biệt giữa thủ tục lưu trữ và trigger là: Các thủ tục lưu trữ được thực thi khi người sử dụng có lời gọi đến chúng còn các trigger lại được "gọi" tự động khi xảy ra những giao tác làm thay đổi dữ liệu trong các bảng.

Mỗi một trigger được tạo ra được gắn liền với một bảng nào đó trong cơ sở dữ liệu của bạn. Khi dữ liệu trong bảng bị thay đổi (Là khi xảy ra các sự kiện INSERT, UPDATE hay DELETE) thì trigger sẽ được tự đông kích hoạt. Để xem các Trigger của một bảng trong SQL 2005 bạn chọn bảng đó, chọn Triggers

Sử dụng trigger một cách hợp lý trong cơ sở dữ liệu sẽ có tác động rất lớn trong việc tăng hiệu năng của cơ sở dữ liệu. Các  trigger thực sự hữu dụng với những khả năng sau:

    * Một trigger có thể nhận biết, ngăn chặn và huỷ bỏ được những thao tác làm thay đổi trái phép dữ liệu trong cơ sở dữ liệu.
    * Các thao tác trên dữ liệu (xoá, cập nhật và bổ sung) có thể được trigger phát hiện ra và tự động thực hiện một loạt các thao tác khác trên cơ sở dữ liệu nhằm đảm bảo tính hợp lệ của dữ liệu.
    * Thông qua trigger, ta có thể tạo và kiểm tra được những mối quan hệ phức tạp hơn giữa các bảng trong cơ sở dữ liệu mà bản thân các ràng buộc không thể thực hiện được.

Khi xảy ra ra một sự kiện thao tác dữ liệu một bản ghi trong CSDL nó sẽ lưu ra một bản ghi trong Trigger nó có tên là inserted đối với các thao tác Insert hay Update và deleted đối với Delete

Cú pháp chung để tạo một Trigger như sau:

CREATE TRIGGER Ten_Trigger
ON Ten_Bang
FOR {[INSERT] | [UPDATE] | [DELETE]}
AS
BEGIN
    Cac_Cau_Lenh_Cua_Trigger
END

Như vậy khi tạo ra một trigger ta phải chỉ rõ là tạo ra trigger trên table nào và được trigger khi nào (insert, update hay delete. Sau chữ AS là các câu lệnh SQL xử lý công việc, có thể dùng cặp Begin ... End hoặc không). Bạn có thể tham khảo thêm bài viết về Trigger Xây dựng cơ sở dữ liệu - TRIGGER

Giờ ta sẽ tìm hiều ví dụ để hiều hơn về Trigger nhé.
Vẫn với CSDL Northwind giả sử trong bảng Employees mình thêm 1 trường là Age - là tuổi của Employees. Giờ mình sẽ viết 1 trigger gắn với bảng Employees để khi thay đổi BirthDay thì trường Age sẽ tự động được cập nhật. Bạn xem ví dụ sau.

CREATE TRIGGER trigCalcAge
   ON Employees
  FOR  UPDATE, Insert 
AS 
BEGIN
    DECLARE @age int
    DECLARE @EmployeeID int 
    SELECT 
        @age=YEAR(GETDATE())-year(BirthDate),
        @EmployeeID=EmployeeID 
    FROM inserted
    IF UPDATE (BirthDate)
    UPDATE Employees 
        SET Age = @age 
    WHERE EmployeeID=@EmployeeID
END

Xem ví dụ trên bạn thấy khi có thay đổi nó sẽ tạo 1 bản ghi inserted và chúng ta có thể lấy các giá trị của bản ghi đó. Khi thay đổi dữ liệu bạn sẽ không cần cập nhật trường Age.
Tương tự như vậy với trường hợp Xóa dữ liệu.
Một điều chú ý là với Trigger nó chỉ thực hiện với sự thay đổi dữ liệu của từng bản ghi. Với trường hợp cập nhật dữ liệu theo bó thì khi đó bạn cần các kỹ thuật xử lý phức tạp hơn.
Ví dụ câu lệnh sau: Update Employees Set BirthDate='12/12/1990 12:00:00 AM' Câu lệnh này sẽ update toàn bộ dữ liệu (Cập nhật theo bó) của bảng Employees  Nhưng khi đó Trigger của ta chỉ update trường Age ở bản ghi đầu tiên. Để xử lý trường hợp này có nhiều cách chẳng hạn như dùng vòng lặp (dùng con trỏ).

Trong bài viết này mình chỉ giới thiệu qua về Trigger như  vậy để bạn hiểu và ứng dụng nó vào database và yêu cầu của Project của bạn theo yêu cẩu cụ thể


Một số tài liệu cho bạn tham khảo:

1. Lập trình cơ sở dữ liệu SQL server

GTSQLServer.doc (861,00 kb)

2. Câu lệnh truy vấn SQL server

Cau lenh truy van SQL.pdf (379,56 kb)

3. Giáo trình thực hành SQL server

Giao trinh thuc hanh SQL.pdf (430,24 kb)