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

Đăng ký nhận thông tin về những video mới nhất

Giới thiệu

Thủ tục tàng trữ ( Stored Procedure – SP ) là một nhóm những lệnh Transact-SQL ( T-SQL ) đóng vai trò như một khối lệnh đơn dùng để triển khai một việc làm ( tác vụ ) quản trị đơn cử hoặc vận dụng những luật thanh toán 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 những biến, đưa vào những điều kiện kèm theo và những đặc thù 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 tàng trữ mạng lưới hệ thống ), Extended SP ( thủ tục tàng trữ lan rộng ra ) 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 vấn đến toàn bộ những đối tượng người dùng mỗi khi nó được gọi. Bạn có hoàn toàn có thể sử dụng tới 2100 tham số trong Danh_sách_đối_số so với mỗi SP. Dung lượng tối đa cho mỗi SP là 128MB .
Giả sử ta có 3 bảng tài liệu sau đây :

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

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

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′).

Dưới đây sẽ là 1 số ít ví dụ cho việc tạo thủ tục tàng trữ sử dụng những 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ổng thể thông tin tương quan đến loại sản phẩm tương ứng, nếu không thì đưa ra thông tin ‘ Không tìm thấy loại sản phẩm có mã @ productid ‘. Ta làm như sau :

BEGIN
  IF(exists(SELECT * FROM Product WHERE
    SELECT * FROM Product WHERE
  ELSE
    print N’Không tìm thấy sản phẩm có mã ‘ + str(@productid,3);
END;CREATE PROC sp_Product ( @ productid int ) ASBEGINIF ( exists ( SELECT * FROM Product WHERE [ email protected ] ) ) SELECT * FROM Product WHERE [ email protected ] ELSEprint N’Không tìm thấy mẫu 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 mẫu sản phẩm có mã 6
· Tạo thủ tục tàng 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ổng thể những phản hồi cho những Sản phẩm có tên tựa như ( 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ệ toàn bộ những phản hồi của những Khách có tên tương tự như ( like ) @ Name .
– Còn nếu @ Name nhận giá trị ‘ * ‘ thì sẽ liệt kê tổng thể những phản hồi đang có .
Đối với nhu yếu này ta làm như sau :

BEGIN
  IF(EXISTS(SELECT Name FROM Product WHERE Name like ‘%’
    SELECT Name, [Date], Title, Content, [Status] FROM Product a JOIN Comment b ON a.ProductID=b.ProductID WHERE Name like ‘%’
  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ự ‘
END;CREATE PROC sp_SearchProduct ( @ Name VARCHAR ( 30 ) ) ASBEGINIF ( EXISTS ( SELECT Name FROM Product WHERE Name like ‘ % ‘ [ email protected ] + ‘ % ‘ ) ) SELECT Name, [ Date ], Title, Content, [ Status ] FROM Product a JOIN Comment b ON a. ProductID = b. ProductID WHERE Name like ‘ % ‘ [ email protected ] + ‘ % ‘ ; ELSE IF ( @ Name = ‘ * ‘ ) SELECT Name, [ Date ], Title, Content, [ Status ] FROM Product a JOIN Comment b ON a. ProductID = b. ProductID ; ELSEprint N’Không tìm thấy loại sản phẩm có tên tương tự như ‘ [ email protected ] END ;Thực thi :
EXEC sp_SearchProduct ‘ No ‘ ;
Thực thi :
EXEC sp_SearchProduct ‘ No1 ‘ ;
Kết quả :
Không tìm thấy loại sản phẩm có tên tựa như No1
Thực thi :
EXEC sp_SearchProduct ‘ * ‘ ;
· Tạo thủ tục tàng 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ổng thể những thông tin của toàn bộ những Khách hàng có tên tương ứng đó trên toàn bộ những bảng tương quan của Cơ sở tài liệu. Ta giải quyết và xử lý nhu yếu này như sau :

BEGIN
  IF(EXISTS(SELECT Name FROM Customer WHERE
  BEGIN
    DELETE FROM Comment WHERE CustomerID IN(SELECT CustomerID FROM Customer WHERE
    DELETE FROM Customer WHERE
  END;
  ELSE
    print N’Không tìm thấy khách hàng có tên ‘
END;CREATE PROC spDropOut ( @ customerName varchar ( 30 ) ) ASBEGINIF ( EXISTS ( SELECT Name FROM Customer WHERE [ email protected ] ) ) BEGINDELETE FROM Comment WHERE CustomerID IN ( SELECT CustomerID FROM Customer WHERE [ email protected ] DELETE FROM Customer WHERE [ email protected ] END ; ELSEprint N’Không tìm thấy người mua có tên ‘ [ email protected ] 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 người mua có tên Michael Obama

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

Để sửa một thủ tục tàng 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ổng thể những 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ỉ được cho phép xem thông tin của những Khách hàng có năm sinh được nhập từ bàn phím, ta làm như sau :

BEGIN
  SELECT * FROM Customer WHERE datepart(year,Birth)
END;ALTER PROC sp_ViewCustomer ( @ year varchar ( 4 ) ) ASBEGINSELECT * FROM Customer WHERE datepart ( year, Birth ) [ email protected ] END ;Thực thi :
EXEC sp_ViewCustomer 1980 ;
Còn giờ đây 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 :

BEGIN
  SELECT * FROM Customer WHERE datepart(year,Birth)
END;ALTER PROC sp_ViewCustomer ( @ year varchar ( 4 ) ) WITH ENCRYPTION ASBEGINSELECT * FROM Customer WHERE datepart ( year, Birth ) [ email protected ] 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 hoàn toàn có thể xem thông tin của bất kể loại SP nào ( gồm có cả SP mạng lưới 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 triển khai câu lệnh EXEC sp_depends ‘ sp_ViewCustomer ‘ ;, thì hiệu quả sẽ như hình dưới đây :

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 tàng trữ sp_ViewCustomer :
DROP PROC sp_ViewCustomer ;
Lưu ý rằng bạn không xóa được thủ tục tàng trữ mạng lưới hệ thống nếu bạn không có quyền.

Source: https://laodongdongnai.vn
Category: Thủ Tục