Thủ Tục Lưu Trữ (Stored procedure-P1)

Đôi nét về Stored-Procedure & Trigger & Function

Khi chúng ta tạo một ứng dụng với Microsoft SQL Server, ngôn ngữ lập trình Transact-SQL là ngôn ngữ chính giao tiếp giữa ứng dụng và database của SQL Server. Khi chúng ta tạo các chương trình bằng Transact-SQL, hai phương pháp chính có thể dùng để lưu trữ và thực thi cho các chương trình là:

• Chúng ta có thể lưu trữ các chương trình cục bộ và tạo các ứng dụng để gởi các lệnh đến SQL Server và xử lý các kết quả,
• Chúng ta có thể lưu trữ những chương trình như các stored procedure trong SQL Server và tạo ứng dụng để gọi thực thi các stored procedure và xử lý các kết quả.

Đặc tính của Stored-procedure trong SQL Server :

• Chấp nhận những tham số vào và trả về những giá trị được chứa trong các tham số ra để gọi những thủ tục hoặc xử lý theo lô.
• Chứa các lệnh của chương trình để thực hiện các xử lý trong database, bao gồm cả lệnh gọi các thủ tục khác thực thi.
• Trả về các trạng thái giá trị để gọi những thủ tục hoặc thực hiện các xử lý theo lô để cho biết việc thực hiện thành công hay thất bại, nếu thất bại thì lý do vì sao thất bại.

Ta có thể dùng Transact–SQL EXCUTE để thực thi các stored procedure. Stored procedure khác với các hàm xử lý là giá trị trả về của chúng không chứa trong tên và chúng không được sử dụng trực tiếp trong biểu thức.
Stored procedure có những thuận lợi so với các chương trình Transact-SQL lưu trữ cục bộ là:

• Stored procedure cho phép điều chỉnh chương trình cho phù hợp: Chúng ta có chỉ tạo stored procedure một lần và lưu trữ trong database một lần, trong chương trình chúng ta có thể gọi nó với số lần bất kỳ. Stored procedure có thể được chỉ rõ do một người nào đó tạo ra và sự thay đổi của chúng hoàn toàn độc lập với source code của chương trình.
• Stored procedure cho phép thực thi nhanh hơn: nếu sự xử lý yêu cầu một đoạn source code Transact – SQL khá lớn hoặc việc thực thi mang tính lặp đi lặp lại thì stored procedure thực hiện nhanh hơn việc thực hiện hàng loạt các lệnh Transact-SQL. Chúng được phân tích cú pháp và tối ưu hóa trong lần thực thi đầu tiên và một phiên bản dịch của chúng trong đó sẽ được lưu trong bộ nhớ để sử dụng cho lần sau, nghĩa là trong những lần thực hiện sau chúng không cần phải phân tích cú pháp và tối ưu lại, mà chúng sẽ sử dụng kết quả đã được biên dịch trong lần đầu tiên.
• Stored procedure có thể làm giảm bớt vấn đề kẹt đường truyền mạng: giả sử một xử lý mà có sử dụng hàng trăm lệnh của Transact-SQL và việc thực hiện thông qua từng dòng lệnh đơn, như vậy việc thực thông qua stored procedure sẽ tốt hơn, vì nếu không khi thực hiện chúng ta phải gởi hàng trăm lệnh đó lên mạng và điều này sẽ dẫn đến tình trạng kẹt mạng.
• Stored procedure có thể sử dụng trong vấn đề bảo mật của máy: vì người sử dụng có thể được phân cấp những quyền để sử dụng các stored procedure này, thậm chí họ không được phép thực thi trực tiếp những stored procedure này.

Các hàm cập nhật, xóa, và một số ví dụ về cách sử dụng các hàm Store Procedure.

1. Lệnh cập nhật Procedure

-Cú Pháp :

ALTER PROCEDURE procedure_name
[ {@parameter data_type } ]
AS
Begin
[khai báo các biến cho xử lý]
{Các câu lệnh transact-sql}
End

2. Lệnh xóa Procedure

- Cú Pháp:

DROP PROCEDURE procedure_name

- Ví dụ: Tạo stored-procedure tính tổng của 2 số nguyên

--Tạo stored-procedure sp_tong

CREATE PROCEDURE sp_Tong
@So1 int, @So2 int, @Tong int out
AS
Begin
SET @Tong = @So1 + @So2;
End

--Biên dịch stored-procedure : F5
--Kiểm tra

Declare @Sum int
Exec sp_Tong 1, -2, out @Sum
Select @Sum

- Tạo stored procedure liệt kê những thông tin của đầu sách, thông tin tựa sách và số lượng sách hiện chưa được mượn của một đầu sách cụ thể (ISBN).

CREATE PROCEDURE sp_ThongtinDausach
@isbn int
AS
Begin
SELECT tuasach, tacgia, ngonngu, bia, trangthai, count(*)
FROM dausach ds, tuasach ts, cuonsach cs
WHERE
ds.ma_tuasach = ts.ma_tuasach AND
ds.isbn = cs.isbn AND
ds.isbn = @isbn AND
tinhtrang = yes
GROUP BY tuasach, tacgia, ngonngu, bia, trangthai
End