Cách viết và sử dụng Stored Procedures

Trong những bài học trước đây khi dùng Query Analyzer chúng ta có thể đặt tên và save các nhóm câu lệnh SQL vào một file dưới dạng script để có thể sử dụng trở lại sau này. Tuy nhiên thay vì save vào text file ta có thể save vào trong SQL Server dưới dạng Stored Procedure. Stored Procedure là một nhóm câu lệnh Transact-SQL đã được compiled (biên dịch) và chứa trong SQL Server dưới một tên nào đó và được xử lý như một đơn vị (chứ không phải nhiều câu SQL riêng lẻ).

Stored Procedure có một số ưu điểm chính như sau:

- Performance : Khi thực thi một câu lệnh SQL thì SQL Server phải kiểm tra permission xem user gởi câu lệnh đó có được phép thực hiện câu lệnh hay không đồng thời kiểm tra cú pháp rồi mới tạo ra một execute plan và thực thi. Nếu có nhiều câu lệnh như vậy gởi qua network có thể làm giảm đi tốc độ làm việc của server. SQL Server sẽ làm việc hiệu quả hơn nếu dùng stored procedure vì người gởi chỉ gởi một câu lệnh đơn và SQL Server chỉ kiểm tra một lần sau đó tạo ra một execute plan và thực thi. Nếu stored procedure được gọi nhiều lần thì execute plan có thể được sử dụng lại nên sẽ làm việc nhanh hơn. Ngoài ra cú pháp của các câu lệnh SQL đã được SQL Sever kiểm tra trước khi save nên nó không cần kiểm lại khi thực thi.

- Programming Framework : Một khi stored procedure được tạo ra nó có thể được sử dụng lại. Ðiều này sẽ làm cho việc bảo trì (maintainability) dễ dàng hơn do việc tách rời giữa business rules (tức là những logic thể hiện bên trong stored procedure) và database. Ví dụ nếu có một sự thay đổi nào đó về mặt logic thì ta chỉ việc thay đổi code bên trong stored procedure mà thôi. Những ứng dụng dùng stored procedure này có thể sẽ không cần phải thay đổi mà vẫn tương thích với business rule mới. Cũng giống như các ngôn ngữ lập trình khác stored procedure cho phép ta đưa vào các input parameters (tham số) và trả về các output parameters đồng thời nó cũng có khả năng gọi các stored procedure khác.

- Security : Giả sử chúng ta muốn giới hạn việc truy xuất dữ liệu trực tiếp của một user nào đó vào một số tables, ta có thể viết một stored procedure để truy xuất dữ liệu và chỉ cho phép user đó được sử dụng stored procedure đã viết sẵn mà thôi chứ không thể “đụng” đến các tables đó một cách trực tiếp. Ngoài ra stored procedure có thể được encrypt (mã hóa) để tăng cường tính bảo mật.

Các Loại Stored Procedure

Stored procedure có thể được chia thành 5 nhóm như sau:

1. System Stored Prcedure : Là những stored procedure chứa trong Master database và thường bắt đầu bằng tiếp đầu ngữ sp_ . Các stored procedure này thuộc loại built-in và chủ yếu dùng trong việc quản lý database (administration) và security. Ví dụ bạn có thể kiểm tra tất cả các processes đang được sử dụng bởi user DomainName\Administrators bạn có thể dùng
sp_who @loginame=’DomainName\Administrators’ . Có hàng trăm system stored procedure trong SQL Server. Bạn có thể xem chi tiết trong SQL Server Books Online.

2. Local Stored Procedure : Ðây là loại thường dùng nhất. Chúng được chứa trong user database và thường được viết để thực hiện một công việc nào đó. Thông thường người ta nói đến stored procedure là nói đến loại này. Local stored procedure thường được viết bởi DBA hoặc programmer. Chúng ta sẽ bàn về cách tạo stored prcedure loại này trong phần kế tiếp.

3. Temporary Stored Procedure : Là những stored procedure tương tự như local stored procedure nhưng chỉ tồn tại cho đến khi connection đã tạo ra chúng bị đóng lại hoặc SQL Server shutdown. Các stored procedure này được tạo ra trên TempDB của SQL Server nên chúng sẽ bị delete khi connection tạo ra chúng bị cắt đứt hay khi SQL Server down. Temporary stored procedure được chia làm 3 loại : local (bắt đầu bằng #), global (bắt đầu bằng ##) và stored procedure được tạo ra trực tiếp trên TempDB. Loại local chỉ được sử dụng bởi connection đã tạo ra chúng và bị xóa khi disconnect, còn loại global có thể được sử dụng bởi bất kỳ connection nào. Permission cho loại global là dành cho mọi người (public) và không thể thay đổi. Loại stored procedure được tạo trực tiếp trên TempDB khác với 2 loại trên ở chỗ ta có thể set permission, chúng tồn tại kể cả sau khi connection tạo ra chúng bị cắt đứt và chỉ biến mất khi SQL Server shut down.

4. Extended Stored Procedure : Ðây là một loại stored procedure sử dụng một chương trình ngoại vi (external program) vốn được compiled thành một DLL để mở rộng chức năng hoạt động của SQL Server. Loại này thường bắt đầu bằng tiếp đầu ngữ xp_ .Ví dụ,
xp_sendmail dùng đề gởi mail cho một người nào đó hay xp_cmdshell dùng để chạy một DOS command… Ví dụ xp_cmdshell ‘dir c:\’ . Nhiều loại extend stored procedure được xem như system stored procedure và ngược lại.

5. Remote Stored Procedure : Những stored procedure gọi stored procedure ở server khác.

Viết Stored Procedure

Tên và những thông tin về Stored Procedure khi được tạo ra sẽ chứa trong SysObjects table còn phần text của nó chứa trong SysComments table. Vì Stored Procedure cũng được xem như một object nên ta cũng có thể dùng các lệnh như CREATE, ALTER, DROP để tạo mới, thay đổi hay xóa bỏ một stored procedure. Chúng ta hãy xem một ví dụ sau về Stored Procedure: Ðể tạo một stored procedure bạn có thể dùng Enterprise Manager click lên trên Stored Procedure -> New Stored Procedure…. Trong ví dụ này ta sẽ tạo ra một stored procedure để insert một new order vào Orders table trong Practice DB. Ðể insert một order vào database ta cần đưa vào một số input như OrderID, ProductName (order món hàng nào) và CustomerName (ai order). Sau đó ta trả về kết quả cho biết việc insert đó có thành công hay không. Result = 0 là insert thành công.

CREATE PROCEDURE AddNewOrder 
               @OrderID smallint,
               @ProductName varchar(50),
               @CustomerName varchar(50),
               @Result smallint=1 Output
               AS
 
               DECLARE @CustomerID smallint
               BEGIN TRANSACTION
               If not Exists(SELECT CustomerID FROM Customers WHERE [Name]=@CustomerName)
               --This is a new customer. Insert this customer to the database
                               BEGIN
                                              SET @CustomerID= (SELECT Max(CustomerID) FROM Customers)
                                              SET @CustomerID=@CustomerID+1
                                              INSERT INTO Customers VALUES(@CustomerID,@CustomerName)
                                              If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)
                                              --This order exists and could not be added any more so Roll back
                                                             BEGIN
                                                                            SELECT @Result=1
                                                                            ROLLBACK TRANSACTION
                                                             END
                                              Else
                                              --This is a new order insert it now
                                                             BEGIN
                                                                            INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID)
                                                                            SELECT @Result=0
                                                                            COMMIT TRANSACTION
                                                             END
                               END
               Else
               --The customer exists in DB go ahead and insert the order
                               BEGIN
                                              If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)
                                              --This order exists and could not be added any more so Roll back
                                                             BEGIN
                                                                            SELECT @Result=1
                                                                            ROLLBACK TRANSACTION
                                                             END
                                              Else
                                              --This is a new order insert it now
                                                             BEGIN
                                                                            INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID)
                                                                            SELECT @Result=0
                                                                            COMMIT TRANSACTION
                                                             END
                               END
               Print @Result
               Return  

Ðể tạo ra một stored procedure ta dùng lệnh CREATE PROCEDURE theo sau là tên của nó (nếu là temporary stored procedure thì thêm dấu # trước tên của procedure. Nếu muốn encrypt thì dùng WITH ENCRYPTION trước chữ AS) và các input hoặc ouput parameters. Nếu là output thì thêm keyword OUTPUT đằng sau parameter. Ta có thể cho giá trị default cùng lúc với khai báo data type của parameter. Kể từ sau chữ AS là phần body của stored procedure.

Trong ví dụ ở trên trước hết ta khai báo một biến @CustomerID sau đó bắt đầu một transaction bằng BEGIN TRANSACTION (toàn bộ công việc insert này được thực hiện trong một Transaction nghĩa là hoặc là insert thành công hoặc là không làm gì cả- all or nothing). Trước hết ta kiểm tra xem người khách hàng là người mới hay cũ. Nếu là người mới thì ta “tiện tay” insert vào Customers table luôn còn nếu không thì chỉ insert vào Orders table mà thôi. Nếu là người customer mới ta lấy CustomerID lớn nhất từ Customers table bỏ vào biến @CustomerID và sau đó tăng lên một đơn vị dùng cho việc Insert Customer ở dòng kế tiếp.

Sau khi insert người customer mới ta tiếp tục công việc bằng việc kiểm tra xem Order muốn insert có tồn tại chưa (vì nếu order đã tồn tại thì khi insert SQL Server sẽ báo lỗi do OrderID là Primary key). Nếu như order trên vì lý do nào đó đã có trong DB thì ta roll back và trả kết quả =1 còn nếu không thì ta insert một order mới vào và commit transaction với kết quả trả về =0.

Tương tự như vậy nếu người customer đã tồn tại (sau chữ else đầu tiên) thì ta chỉ việc insert order giống như trên. Trong mọi trường hợp kể trên ta đều in ra kết quả và return.

Ví dụ trên đây chỉ mang tính học hỏi còn trên thực tế database có thể phức tạp hơn nhiều nên việc viết stored procedure đòi hỏi kiến thức vững chắc về SQL và kỹ năng về programming.