SQL Server: Thủ tục lưu trữ (SP)

Các khóa học qua video:
Python SQL Server PHP C# Lập trình C Java HTML5-CSS3-JavaScript
Học trên YouTube <76K/tháng. Đăng ký Hội viên
Viết nhanh hơn - Học tốt hơn
Giải phóng thời gian, khai phóng năng lực

Giới thiệu

Thủ tục lưu trữ (Stored Procedure - SP) là một nhóm các lệnh Transact-SQL (T-SQL) đóng vai trò như một khối lệnh đơn dùng để thực hiện một công việc (tác vụ) quản trị cụ thể hoặc áp dụng các luật giao dịch phức tạp.

Khối lệnh T-SQL được lưu trữ dưới một tên cụ thể, và vì vậy bạn có thể gọi SP cũng như tái sử dụng nó được nhiều lần. Ngoài ra, SP còn tăng tính bảo mật và giảm các giao dịch Khách/Chủ (Client/Server).

Trong SP bạn có quyền khai báo các biến, đưa vào các điều kiện và những đặc điểm lập trình khác.

Phân loại

Thủ tục lữu trữ gồm ba loại chính là: System SP (thủ tục lưu trữ hệ thống), Extended SP (thủ tục lưu trữ mở rộng) và User-defined SP (thủ tục lữu trữ do người dùng định nghĩa). Cụ thể:

System SP: Dùng để tương tác với các bảng hệ thống và thực hiện các tác vụ quản trị.

Extended SP: Loại SP này sẽ giúp cho SQL Server tương tác với Hệ điều hành (OS).

User-defined SP: Đây là thủ tục lưu trữ được định nghĩa bởi người dùng (lập trình viên), loại SP này còn được gọi là SP tuỳ chỉnh. Dưới đây là hướng dẫn chi tiết gồm cách tạo, sửa và xóa đối với loại SP này.

Tạo thủ tục lưu trữ

Cú pháp:

CREATE PROC | PROCEDURE Tên_SP(Danh_sách_tham_số) [WITH ENCRYPTION | RECOMPILE, ENCRYPTION | ECOMPILE] AS
BEGIN
  [Khai_báo_các_biến_cục_bộ_của_SP]
  Khối_lệnh_T-SQL;
END;

Mỗi SP có quyền truy cập đến tất cả các đối tượng mỗi khi nó được gọi. Bạn có có thể sử dụng tới 2100 tham số trong Danh_sách_đối_số đối với mỗi SP. Dung lượng tối đa cho mỗi SP là 128MB.

Giả sử ta có 3 bảng dữ liệu sau đây:

Bảng Customer (lưu trữ thông tin của Khách hàng):

CustomerID (int) Name (varchar(30)) Birth (date) Gender (bit)
1 Johny Owen 10/10/1980 1
2 Christina Tiny 10/03/1989 0
3 Garry Kelley 16/03/1990 Null
4 Tammy Beckham 17/05/1980 0
5 Divid Phantom 30/12/1987 1

Bảng Product (lưu trữ thông tin Sản phẩm):

ProductID (int) Name (varchar(30)) Pdesc (text) Pimage (varchar(200)) Pstatus (bit)
1 Nokia N90 Mobile Nokia image1.jpg 1
2 HP DV6000 Laptop image2.jpg Null
3 HP DV2000 Laptop iamge3.jpg 1
4 SamSung G488 Mobile SamSung image4.jpg 0
5 LCD Plasma TV LCD image5.jpg 0

Bảng Comment (lưu trữ bình luận của Khách đối với Sản phẩm). Ví dụ: bản ghi đầu tiên của bảng dưới thể hiện rằng 'Jonny Owen' (mã là 1 ở bảng Customer) đã bình luận cho sản phẩm ‘Nokia N90’ (mã là 1 ở bảng Product) vào ngày '15/03/09').

ComID (int identity(1,1)) ProductID (int) CustomerID (int) Date (datetime) Title (varchar(200) Content (text) Status (bit)
1 1 1 15/03/09 Hot product Null 1
2 2 2 14/03/09 Hot price Very much 0
3 3 2 20/03/09 Cheapest Unlimited 0
4 4 2 16/04/09 Sale off 50% 1

Dưới đây sẽ là một số ví dụ cho việc tạo thủ tục lưu trữ sử dụng các bảng trên:

· Tạo SP có tên 'sp_Product' có một tham số tên @productid, nếu tìm thấy @productid trong cột ProductID của bảng Product thì hiển thị tất cả thông tin liên quan đến sản phẩm tương ứng, nếu không thì đưa ra thông báo 'Không tìm thấy sản phẩm có mã @productid'. Ta làm như sau:

CREATE PROC sp_Product(@productid int) AS
BEGIN
  IF(exists(SELECT * FROM Product WHERE productid=@productid))
    SELECT * FROM Product WHERE productid=@productid
  ELSE
    print N'Không tìm thấy sản phẩm có mã ' + str(@productid,3);
END;

Thực thi:

EXEC sp_Product 1; --Có thể bỏ EXEC

 

Thực thi:

EXEC sp_Product 6;

Kết quả:

Không tìm thấy sản phẩm có mã  6

· Tạo thủ tục lưu trữ có tên 'spSearchProduct' có một tham số tên @Name:

- Nếu tìm thấy @Name trong cột Name của bảng Product thì sẽ liệt kê tất cả những bình luận cho những Sản phẩm có tên tương tự (like) @Name.

- Nếu không thì kiểm tra @Name nếu tìm thấy trong Name của bảng Customer thì sẽ liệt kệ tất cả những bình luận của những Khách có tên tương tự (like) @Name.

- Còn nếu @Name nhận giá trị '*' thì sẽ liệt kê tất cả các bình luận đang có.

Đối với yêu cầu này ta làm như sau:

CREATE PROC sp_SearchProduct(@Name VARCHAR(30)) AS
BEGIN
  IF(EXISTS(SELECT Name FROM Product WHERE Name like '%'+@Name+'%'))
    SELECT Name, [Date], Title, Content, [Status] FROM Product a JOIN Comment b ON a.ProductID=b.ProductID WHERE Name like '%'+@Name+'%';
  ELSE IF(@Name = '*')
    SELECT Name, [Date], Title, Content, [Status] FROM Product a JOIN Comment b ON a.ProductID=b.ProductID ;    
  ELSE
    print N'Không tìm thấy sản phẩm có tên tương tự '+@Name;
END;

Thực thi:

EXEC sp_SearchProduct 'No';

Thực thi:

EXEC sp_SearchProduct 'No1';

Kết quả:

Không tìm thấy sản phẩm có tên tương tự No1

Thực thi:

EXEC sp_SearchProduct '*';

· Tạo thủ tục lưu trữ có tên 'spDropOut' có một  tham số là tên của Khách hàng, nếu tìm thấy tên này trong cột Name của bảng Customer thì sẽ xóa tất cả những thông tin của tất cả những Khách hàng có tên tương ứng đó trên tất cả các bảng liên quan của Cơ sở dữ liệu. Ta xử lý yêu cầu này như sau:

CREATE PROC spDropOut(@customerName varchar(30)) AS
BEGIN
  IF(EXISTS(SELECT Name FROM Customer WHERE Name=@customerName))
  BEGIN
    DELETE FROM Comment WHERE CustomerID IN(SELECT CustomerID FROM Customer WHERE Name=@customerName)
    DELETE FROM Customer WHERE Name=@customerName
  END;
  ELSE
    print N'Không tìm thấy khách hàng có tên '+@customerName;
END;

Thực thi:

EXEC spDropOut 'Jonny Owen';

Kết quả:

(1 row(s) affected)
(1 row(s) affected)

Thực thi:

EXEC spDropOut 'Michael Obama';

Kết quả:

Không tìm thấy khách hàng có tên Michael Obama

Sửa thủ tục lưu trữ

Để sửa một thủ tục lưu trữ tùy chỉnh ta sử dụng câu lệnh ALTER theo cú pháp như sau:

ALTER PROC | PROCEDURE Tên_SP(Danh_sách_tham_số) [WITH ENCRYPTION | RECOMPILE, ENCRYPTION | ECOMPILE] AS
BEGIN
  [Khai_báo_các_biến_cục_bộ_của_SP]
  Khối_lệnh_T-SQL;
END;

Ví dụ, giả sử ta tạo một SP để xem thông tin của tất cả các Khách hàng (bảng Customer ở trên), ta làm như sau:

CREATE PROC sp_ViewCustomer AS
BEGIN
  SELECT * FROM Customer;
END;

Bây giờ ta cần sửa SP trên để nó chỉ cho phép xem thông tin của các Khách hàng có năm sinh được nhập từ bàn phím, ta làm như sau:

ALTER PROC sp_ViewCustomer(@year varchar(4)) AS
BEGIN
  SELECT * FROM Customer WHERE datepart(year,Birth)=@year;
END;

Thực thi:

EXEC sp_ViewCustomer 1980;

Còn bây giờ ta muốn ngăn không cho người dùng xem thông tin của SP sp_ViewCustomer ta làm như sau:

ALTER PROC sp_ViewCustomer(@year varchar(4)) WITH ENCRYPTION AS
BEGIN
  SELECT * FROM Customer WHERE datepart(year,Birth)=@year;
END;

Thực thi:

EXEC sp_helptext 'sp_ViewCustomer';

Kết quả:

The text for object 'sp_ViewCustomer' is encrypted.

Xem thông tin thủ tục lưu trữ

Bạn có thể xem thông tin của bất kỳ loại SP nào (bao gồm cả SP hệ thống) nếu SP đó chưa mã hóa.

Cách 1: Sử dụng SP sp_helptext, thông tin chi tiết về định nghĩa SP sẽ được thể hiện đúng như nó được viết trong code. Cú pháp như sau:

EXEC sp_helptext 'Tên_SP';

Ví dụ:

EXEC sp_helptext 'sp_ViewCustomer'; --Xem định nghĩa SP tùy chỉnh
EXEC sp_helptext 'sp_columns'; --Xem định nghĩa System SP

Cách 2: Sử dụng hàm OBJECT_DEFINITION(), thông tin chi tiết về định nghĩa SP sẽ được thể hiện trên một hàng. Cú pháp:

SELECT OBJECT_DEFINITION( OBJECT_ID('Tên_SP');

Ví dụ:

SELECT OBJECT_DEFINITION( OBJECT_ID('sp_ViewCustomer'));
SELECT OBJECT_DEFINITION( OBJECT_ID('sp_columns'));

Cách 3: Sử dụng sys.sql_modules, cách này không áp dụng cho thủ tục lưu trữ hệ thống. Cú pháp sử dụng như sau:

SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('Tên_SP');

Ví dụ:

SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('sp_ViewCustomer');

Cách 4: Sử dụng thủ tục lưu trữ sp_depends, cách này dùng để xem sự phụ thuộc của thủ tục lữu trữ vào các thành phần như Bảng, View. Cú pháp:

EXEC sp_depends 'Tên_SP';

Ví dụ, nếu ta thực hiện câu lệnh EXEC sp_depends 'sp_ViewCustomer';, thì kết quả sẽ như hình dưới đây:

http://v1study.com/public/images/article/sql-sp-exam5.png

Xóa thủ tục lưu trữ

Để xoá một SP tùy chỉnh ta sử dụng câu lệnh DROP PROC. Câu lệnh sau sẽ xóa thủ tục lưu trữ sp_ViewCustomer:

DROP PROC sp_ViewCustomer;

Lưu ý rằng bạn không xóa được thủ tục lưu trữ hệ thống nếu bạn không có quyền.

» Tiếp: Cơ bản về Trigger
« Trước: Mệnh đề WITH
Các khóa học qua video:
Python SQL Server PHP C# Lập trình C Java HTML5-CSS3-JavaScript
Học trên YouTube <76K/tháng. Đăng ký Hội viên
Viết nhanh hơn - Học tốt hơn
Giải phóng thời gian, khai phóng năng lực
Copied !!!