Ưu Ðiểm Của Stored Procedure
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. 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 : 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. 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ồntạ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
Ta cũng có thể dùng các lệnh CREATE, ALTER, DROP để tạo mới, thay đổi hay xóa bỏ một stored procedure.
Ví dụ: Tạo một stored procedure dùng Enterprise Manager click lên trên Stored Procedure -> New Stored Procedure....
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 và CustomerName. 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 AddNew
@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
Dùng lệnh CREATE 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.
Các ví Dụ tham khảo nữa nhe:
CREATE procedure XOA_KHACHHANG
(
@MSKH char(10)
) as
Begin
if (exists(select *from KHACHHANG where MSKH=@MSKH))
begin
if(not exists(select *from HOADON where MSKH=@MSKH))
begin
delete KHACHHANG where MSKH=@MSKH
if @@rowcount=0
raiserror ('Xoa khach hang khong thanh cong',1,1)
else
raiserror ('Xoa khach hang thanh cong',1,1)
end
else
raiserror ('Khong the xoa khach hang nay ',1,1)
end
else
raiserror ('Khavh hang nay khong ton tan',1,1)
end
CREATE procedure SUA_KHACHHANG
(
@MSKH char(10),
@TENKH varchar(50),
@DIACHI varchar(100),
@PHAI char(5),
@DIENTHOAI varchar(15)
) as
Begin
if (exists(select *from KHACHHANG where MSKH=@MSKH))
begin
if isnumeric(@DIENTHOAI)=1 or @DIENTHOAI=''
begin
if @DIENTHOAI='' or DATALENGTH(@DIENTHOAI)=6 or DATALENGTH(@DIENTHOAI)=7 or DATALENGTH(@DIENTHOAI)=8 or DATALENGTH(@DIENTHOAI)=9 or DATALENGTH(@DIENTHOAI)=10 or DATALENGTH(@DIENTHOAI)=11
begin
if @DIENTHOAI=''
set @DIENTHOAI=null
update KHACHHANG
set TENKH=@TENKH,DIACHI=@DIACHI,PHAI=@PHAI,DIENTHOAI=@DIENTHOAI where MSKH=@MSKH
if @@rowcount=0
raiserror ('Sua khach hang khong thanh cong',2,2)
else
raiserror ('Sua khach hang thanh cong',2,2)
end
else
raiserror ('So dien thoai ko hop le',3,1)
end
else
raiserror ('So dien thoai ko hop le',3,1)
end
else
raiserror ('khach hang nay khong ton tai',4,2)
end
