Procedure trong SQL Server có ý nghĩa thế nào trong lập trình? Cách sử dụng Procedure SQL Server có khó không? Hãy cùng Quantrimang.com tìm hiểu nhé!

Procedure (Thủ tục) là một chương trình trong cơ sở dữ liệu gồm nhiều câu lệnh mà bạn lưu lại cho những lần sử dụng sau. Trong SQL Server, bạn có thể truyền các tham số vào procedure, tuy nó không trả về một giá trị cụ thể như function (hàm) nhưng cho biết việc thực thi thành công hay thất bại.

Lợi ích khi dùng Procedure trong SQL

• Dễ dàng chỉnh sửa: Lập trình viên dễ dàng chỉnh sửa code bên trong thủ tục ở SQL Server mà không cần khởi động lại hay triển khai ứng dụng. Ví dụ, nếu truy vấn T-SQL được viết trong ứng dụng và bạn cần thay đổi logic, bạn phải thay code trong ứng dụng đó và triển khai lại nó. Thủ tục trong SQL Server loại bỏ những khó khăn đó bằng cách lưu trữ code trong database. Vì thế, khi muốn thay đổi logic bên trong procedure, bạn chỉ có thể làm việc đó bằng lệnh Alter Procedure đơn giản.
• Giảm tải truy cập mạng: Khi dùng procedure được lưu trữ thay vì viết các truy vấn T-SQL ở cấp ứng dụng, chỉ tên thủ tục được chuyển qua mạng thay vì toàn bộ code T-SQL.
• Có thể tái sử dụng: Bạn có thể triển khai procedure được lưu trữ bằng nhiều người dùng hoặc nhiều ứng dụng client mà không cần viết lại code.
• Bảo mật: Giảm các mối đe dọa bằng cách xóa truy cập trực tiếp tới bảng.

Bài viết sẽ cung cấp cho bạn cú pháp và những ví dụ về cách tạo và xóa bỏ procedure trong SQL Server.

CREATE PROCEDURE (Tạo Procedure)

Cú pháp

Để tạo một procedure trong SQL Server, ta sử dụng cú pháp như dưới đây:

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
[ @parameter [type_schema_name.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
, @parameter [type_schema_name.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]
[ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
[ FOR REPLICATION ]
AS
BEGIN
[declaration_section]
executable_section
END;

Tham số:

• schema_name: Tên schema (lược đồ) sở hữu procedure.
• procedure_name: Tên gán cho procedure
• @parameter: Một hay nhiều tham số được truyền vào hàm.
• type_schema_name: Kiểu dữ liệu của schema (nếu có).
• Datatype: Kiểu dữ liệu cho @parameter.
• Default: Giá trị mặc định gán cho @parameter.
• OUT/OUTPUT: @parameter là một tham số đầu ra
• READONLY: @parameter không thể bị procedure ghi đè lên.
• ENCRYPTION: Mã nguồn (source) của procedure sẽ không được lưu trữ dưới dạng text trong hệ thống.
• RECOMPILE: Truy vấn sẽ không được lưu ở bộ nhớ đệm (cache) cho thủ tục này.
• EXECUTE AS clause: Xác định ngữ cảnh bảo mật để thực thi thủ tục.
• FOR REPLICATION: Procedure đã lưu sẽ chỉ được thực thi trong quá trình replication (nhân bản).

Ví dụ

CREATE PROCEDURE spNhanvien
@nhanvien_name VARCHAR(50) OUT
AS
BEGIN
DECLARE @nhanvien_id INT;
SET @nhanvien_id = 8;
IF @nhanvien_id < 10
SET @nhanvien_name = 'Smith';
ELSE
SET @nhanvien_name = 'Lawrence';
END;

Thủ tục trên được gán tên là spNhanvien, có một tham số là @nhanvien_name, output của tham số sẽ được dựa trên @nhanvien_id.

Sau đó, bạn có thể thực hiện tham chiếu spNhanvien như sau:

USE [test]
GO
DECLARE @site_name varchar(50);
EXEC FindSite @site_name OUT;
PRINT @site_name;
GO

Drop Procedure (Xóa bỏ Procedure)

Một khi đã tạo thành công các procedure thì cũng sẽ có những trường hợp bạn muốn xóa bỏ procedure khỏi cơ sở dữ liệu vì một vài lý do.

Cú pháp

Để xóa bỏ một procedure, ta có cú pháp sau:

DROP PROCEDURE procedurename;

Tham số:

procedure_name: Tên procedure bạn muốn xóa bỏ...

Ví dụ

DROP PROCEDURE spNhanvien;

Thực hiện lệnh này là bạn đã vừa xóa bỏ thủ tục spNhanvien khỏi database.

Thủ tục lưu trữ tạm thời

Bạn có thể tạo các thủ tục tạm thời tương tự như tạo bảng tạm thời. Database tempdb được dùng để tạo những thủ tục này. Bạn có thể chia thủ tục tạm thời thành 2 loại:

• Thủ tục lưu trữ tạm thời cục bộ
• Thủ tục tạm thời cục bộ

Thủ tục lưu trữ tạm thời cục bộ: Bạn có thể tạo kiểu này bằng cách dùng tiền tố # và chỉ truy cập trong phiên mà chúng được tạo. Khi đóng kết nối, quá trình này ngay lập tức kết thúc.

Ví dụ cách tạo một thủ tục tạm thời nội bộ:

CREATE PROCEDURE 
# Temp
AS
BEGIN
PRINT 'Local temp procedure'
END

Thủ tục lưu trữ tạm thời toàn cục: Bạn có thể tạo kiểu này bằng cách dùng ## làm tiền tố và truy cập từ phiên bất kỳ. Khi kết nối được dùng để tạo quá trình này đóng lại, thủ tục sẽ tự động kết thúc.

Trong bài này bạn sẽ được tìm hiểu khái niệm về stored procedure trong SQL Server, qua đó bạn sẽ biết được cách tạo mới, thực thi, thay đổi và xóa stored procedure.

Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.

1. Stored Procedure là gì?

Stored procedure là tập hợp một hoặc nhiều câu lệnh T-SQL thành một nhóm đơn vị xử lý logic và được lưu trữ trên Database Server. Khi một câu lệnh gọi chạy stored procedure lần đầu tiên thì SQL Server sẽ chạy nó và lưu trữ vào bộ nhớ đệm, gọi là plan cache, những lần tiếp theo SQL Server sẽ sử dụng lại plan cache nên sẽ cho tốc độ xử lý tối ưu.

Stored procedure rất tiện lợi cho người quản trị database (DBA), nó giúp DBA tạo ra những nhóm câu lệnh và gửi đến một bô phận khác mà họ sẽ không cần quan tâm đến nội dung bên trong stored procedure có gì, họ chỉ quan tâm đến tham số đầu vào và đầu ra.

Ví dụ bạn viết một stored procedure lấy danh sách sản phẩm bán chạy theo ngày, sau đó bạn gửi stored này qua bộ phận development kèm theo tài liệu hướng dẫn sử dụng thì lúc này bộ phận DEV không cận quan tâm đến nội dung bên trong của stored mà chỉ cần thông tin tham số truyền vào và kết quả trả về của stored.

Bài viết này được đăng tại [free tuts .net]

2. Tạo Stored Procedure trong SQL Server

Chúng ta hãy bắt đầu bằng những ví dụ đơn giản nhất nhé. Giả sử mình có câu truy vấn lấy danh sách sản phẩm như sau:

SELECT product_name, list_price FROM production.products ORDER BY product_name;

Để tạo một stored procedure chứa câu truy vấn này thì mình sẽ viết như sau:

CREATE PROCEDURE uspProductList AS BEGIN

SELECT   
    product_name,   
    list_price  
FROM   
    production.products  
ORDER BY   
    product_name;  

END;

Như vậy cú pháp tạo stored procedure như sau:

CREATE PROCEDURE stored_name AS BEGIN

....  

END;

Trong đó stored_name là tên của stored mà bạn muốn đặt. Như ở ví dụ ở trên thì tên của nó là uspProductList. Đối với câu lệnh CREATE PROCEDURE thì bạn có thể rút ngắn thành

CREATE PROCEDURE uspProductList AS BEGIN

SELECT   
    product_name,   
    list_price  
FROM   
    production.products  
ORDER BY   
    product_name;  

END;

0.

Bây giờ bạn hãy chạy câu SQL tạo stored bằng cách click vào nút Execute ở trên thanh công cụ như hình chụp dưới đây.

Nếu bạn làm đúng tất cả thì sẽ nhận được kết quả là:

Commands completed successfully.

Để xem danh sách stored procedure đã tạo trong SQL Server thì bạn hãy vào database -> Programmability > Stored Procedures. Trường hợp không xuất hiện stored vừa tạo thì hãy click dấu xoay tròn để reload.

3. Gọi Stored Procedure trong SQL Server

Để gọi một stored procedure thì bạn sử dụng lệnh

CREATE PROCEDURE uspProductList AS BEGIN

SELECT   
    product_name,   
    list_price  
FROM   
    production.products  
ORDER BY   
    product_name;  

END;

1 hoặc

CREATE PROCEDURE uspProductList AS BEGIN

SELECT   
    product_name,   
    list_price  
FROM   
    production.products  
ORDER BY   
    product_name;  

END;

2 theo sau là tên của stored.

Hoặc

Như ở ví dụ phần 2 thì kết quả sẽ như hình sau:

4. Thay đổi Stored Procedure trong SQL Server

Để thay đổi stored thì bạn sử dụng lệnh ALTER PROCEDURE và tên của stored sẽ nằm phía sau.

Ví dụ giờ muốn muốn thay đổi cách sắp xếp kết quả trả về từ product_name thành list_price của

CREATE PROCEDURE uspProductList AS BEGIN

SELECT   
    product_name,   
    list_price  
FROM   
    production.products  
ORDER BY   
    product_name;  

END;

3 thì viết như sau:

ALTER PROCEDURE uspProductList

AS  
BEGIN  
    SELECT   
        product_name,   
        list_price  
    FROM   
        production.products  
    ORDER BY   
        list_price   
END;

Hoặc bạn click chuột phải vào stored cần sửa, sau đó chọn menu Modify thì nó sẽ hiển thị ra một trang query mới với cấu trúc của stored cũ.

5. Xóa Stored Procedure trong SQL Server

Để xóa stored procedure thì bạn sử dụng lệnh

CREATE PROCEDURE uspProductList AS BEGIN

SELECT   
    product_name,   
    list_price  
FROM   
    production.products  
ORDER BY   
    product_name;  

END;

4 hoặc

CREATE PROCEDURE uspProductList AS BEGIN

SELECT   
    product_name,   
    list_price  
FROM   
    production.products  
ORDER BY   
    product_name;  

END;

5.

DROP PROCEDURE sp_name;

Hoặc

DROP PROC sp_name

Trong đó sp_name là tên của stored muốn xóa.

Ví dụ mình muốn xóa stored procedure có tên là uspProductList thì sẽ viết như sau:

DROP PROCEDURE uspProductList; Như vậy trong bài này đã giới thiệu khái niệm stored procedure là gì, và qua đây bạn cũng đã biết các thao tác như: Tạo stored, sửa stored, chạy stored và xóa stored. Chúc bạn học tốt và hẹn gặp lại bài tiếp theo.