Thủ tục lưu trữ (Stored procedure) trong SQL Server 2005

Bài viết sau đây sẽ giới thiệu tới các bạn thủ tục lưu trữ (Stored procedure) trong SQL Server 2005. Để giúp công việc của chúng ta được dễ dàng hơn.

Mục lục

Thủ tục lưu trữ (Stored procedure)

Thủ tục lưu trữ là một đối tượng người dùng trong CSDL, gồm có nhiều câu lệnh T-SQL được tập hợp lại với nhau thành một nhóm, và toàn bộ những lệnh này sẽ được thực thi khi thủ tục lưu trữ được thực thi .

Với thủ tục lưu trữ, một phần nào đó khả năng của ngôn ngữ lập trình được đưa vào trong ngôn ngữ SQL. Thủ tục lưu trữ có thể có các thành phần sau:

Các cấu trúc tinh chỉnh và điều khiển ( IF, WHILE, FOR ) hoàn toàn có thể được sử dụng trong thủ tục .
Bên trong thủ tục lưu trữ hoàn toàn có thể sử dụng những biến như trong ngôn từ lập trình nhằm mục đích lưu giữ những giá trị giám sát được, những giá trị được truy xuất được từ cơ sở tài liệu .
Một tập những câu lệnh SQL được tích hợp lại với nhau thành một khối lệnh bên trong một thủ tục. Một thủ tục hoàn toàn có thể nhận những tham số truyền v ào cũng như hoàn toàn có thể trả về những giá trị trải qua những tham số ( như trong những ngôn từ lập trình ). Khi một thủ tục lưu trữ đã được định nghĩa, nó hoàn toàn có thể được gọi trải qua tên thủ tục, nhận những tham số truyền vào, thực thi những câu lệnh SQL bên trong thủ tục và hoàn toàn có thể trả về những giá trị sau khi thực thi xong. Lợi ích của việc sử dụng thủ tục lưu trữ :
SQL Server chỉ biên dịch những thủ tục lưu trữ một lần và sử dụng lại tác dụng biên dịch này trong những lần tiếp theo trừ khi người dùng có những thiết lập khác. Việc sử dụng lại hiệu quả biên dịch không làm tác động ảnh hưởng đến hiệu suất mạng lưới hệ thống khi thủ tục lưu trữ được gọi liên tục nhiều lần .
Thủ tục lưu trữ được nghiên cứu và phân tích, tối ưu khi tạo ra nên việc thực thi chúng nhanh hơn nhiều so với việc phải thực thi một tập rời rạc những câu lệnh SQL t ương đương theo cách thường thì .
Thủ tục lưu trữ được cho phép tất cả chúng ta thực thi cùng một nhu yếu bằng một câu lệnh đơn thuần thay vì phải sử dụng nhiều dòng lệnh SQL. Điều này sẽ làm giảm thiểu sự lưu thông trên mạng .
Thay vì cấp phép quyền trực tiếp cho người sử dụng trên những câu lệnh SQL và trên những đối tượng người dùng cơ sở tài liệu, ta hoàn toàn có thể cấp phép quyền cho người sử dụng trải qua những thủ tục lưu trữ, nhờ đó tăng năng lực bảo mật thông tin so với mạng lưới hệ thống .
Các thủ tục lưu trữ trả về hiệu quả theo 4 cách :
Sử dụng những tham số output
Sử dụng những lệnh trả về giá trị, những lệnh này luôn trả về giá trị số nguyên .
Tập những giá trị trả vể của mỗi câu lệnh SELECT có trong thủ tục l ưu trữ hoặc của quy trình gọi một thủ tục lưu trữ khác trong một thủ tục lưu trữ .
Một biến con trỏ toàn cục hoàn toàn có thể tham chiếu từ bên ngoài thủ tục .

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

Thủ tục lưu trữ được tạo trải qua câ. u lệnh CREATE PROCEDURE .

CREATE PROCEDURE tên_thủ_tục [(danh_sách_tham_số)]
[ WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION ]
AS
Các_câu_lệnh_của_thủ_tục

Trong đó :
WITH RECOMPILE : nhu yếu SQL Server biên dịch lại thủ tục lưu trữ mỗi khi được gọi. WITH ENCRYPTION : nhu yếu SQL Server mã hóa thủ tục lưu trữ .
Các_câu_lệnh_của_thủ_tục : Các lệnh T-SQL. Các lệnh này hoàn toàn có thể nằm trong cặp BEGIN … END hoặc không .
Ví dụ : Giả sử cần triển khai những việc làm theo thứ tự nh ư sau :
Nhập một đơn đặt hàng mới của người mua có mã người mua là 3 Nhập những cụ thể đơn đặt hàng cho đơn đặt hàng trên .
Để thực thi những việc làm trên tất cả chúng ta cần những câu lệnh như sau :
Trước tiên nhập đơn đặt hàng cho người mua có mã người mua là 3

insert into orders values(3, '7/22/2008')

Tiếp theo thêm những cụ thể đơn đặt hàng cho hóa đơn này. Giả sử rằng đơn đặt hàng có mã là 4 và người mua đặt một mẫu sản phẩm có mã là 1 .

insert into orderdetail values(4, 1,  10)

Cách viết như trên có hạn chế là : trong quy trình thao tác sẽ có rất nhiều đơn đặt hàng mới, do đó người dùng sẽ phải viết đi viết lại những câu lệnh t ương tự nhau cho những người mua khác nhau. Một cách xử lý yếu tố này là dùng thủ tục lưu trữ và dùng tham số để nhận những thông tin biến hóa .

create procedure sp_InsertOrderAndOrderDetail
@customerid int,
@orderdate datetime,
@orderid int,
@itemid int,
@quantity decimal, as begin
insert into orders values(@customerid, @orderdate)
insert into orderdetail values(@orderid, @itemid, @quantity)
end

Thực hiện thủ tực lưu trữ này như sau :

sp_InsertOrderAndOrderDetail ‘3’, ‘22/7/2008’, ‘4’, ‘1’, ‘10’)

2. Lời gọi thủ tục

Thủ tục lưu trữ được gọi theo cấu trúc

Tên_thủ_tục_lưu _trữ [danh_sách_tham_số]

Cần chú ý quan tâm là list tham số truyền vào trong lời gọi phải theo đúng thứ tự khai báo những tham số trong thủ tục lưu trữ .
Nếu thủ tục được gọi từ một thủ tục khác, thực thi bên trong một trigger hay phối hợp với câu lệnh SELECT, cấu trúc như sau ;

Exec Tên_thủ_tục_lưu _trữ [danh_sách_tham_số]

3. Biến trong thủ tục lưu trữ

Trong thủ tục lưu trũ hoàn toàn có thể có những biến nhằm mục đích lưu những tác dụng giám sát hay truy xuất từ CSDL. Các biến trong thủ tục được khai báo bằng từ khóa DECLARE theo cấu trúc nh ư sau :

DECLARE @tên_biến kiểu_dữ_liệu

Ví dụ :

create procedure sp_SelectCustomerWithMaxAge as begin
declare @maxAge int
select @maxAge = max(year(getdate())-year(BIRTHDAY)) from customers select CUSTOMERNAME, BIRTHDAY from customers where year(getdate())-year(BIRTHDAY)=@maxAge
end

4. Giá trị trả về trong thủ tục lưu trữ

Trong những ví dụ trước, nếu đối số truyền cho thủ tục khi có lời gọi đến thủ tục là biến, những đổi khác giá trị của biền trong thủ tục sẽ không đ ược giữ lại khi kết thúc quy trình thực thi thủ tục .
Ví dụ : Có thủ tục lưu trữ như sau

create procedure sp_TestOutput
@a int,
@b int, @c int as
select @c = @a + @b Thực thi thủ tục:
Declare @tong int set @tong = 0 sp_TestOutput 100, 200, @tong select @tong

Kết quả là 0 .

Sử dụng tham số OUTPUT

Trong trường hợp cần phải giữ lại giá trị của đối số sau khi kết thúc thủ tục, ta phải khai báo tham số của thủ tục theo cú pháp như sau :

@tên_tham_số kiểu_dữ_liệu OUTPUT

Ví dụ trên được viết lại như sau :

create procedure sp_TestOutput
@a int,
@b int, @c int output as
select @c = @a + @b

Thực thi thủ tục :

Declare @tong int set @tong = 0 sp_TestOutput 100, 100, @tong output select @tong

Kết quả là 200 .

Sử dụng lệnh RETURN

Tương nhự như việc sử dụng tham số OUTPUT, câu lệnh RETURN trả về giá trị cho đối tượng người tiêu dùng thực thi stored procedure .
Ví dụ :

create procedure sp_TestReturn as begin
declare @out int select @out = count(*) from customers return @out
end

Thực thi thủ tục lưu trữ

declare @a int exec  @a = sp_TestReturn select @a

5. Tham số với giá trị mặc định

Các tham số được khai báo trong thủ tục hoàn toàn có thể nhận những giá trị mặc định. Giá trị mặc định sẽ được gán cho tham số trong trường hợp không truyền đối số cho tham số khi có lời gọi đến thủ tục .
Tham số với giá trị mặc định được khai báo theo cú pháp như sau :
@ tên_tham_số kiểu_dữ_liệu = giá_trị_mặc_định
Ví dụ :

create procedure sp_TestDefault
@customerid int = 3 as begin
select * from customers where customerid = @customerid
end

Thực thi thủ tục lưu trữ theo giá trị mặc định của tham số .
sp_TestDefault

Thực thi thủ tục và truyền giá trị cho tham số :
sp_TestDefault 4

6. Sửa đổi thủ tục

Khi một thủ tục đã được tạo ra, ta hoàn toàn có thể thực thi định nghĩa lại thủ tục đó bằng câu lệnh ALTER PROCEDURE có cú pháp như sau :

ALTER PROCEDURE tên_thủ_tục [(danh_sách_tham_số)]
[ WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION ]
AS
Các_câu_lệnh_của_thủ_tục

Câu lệnh này sử dụng tương tự như câu lệnh CREATE PROCEDURE. Việc sửa đổi lại một thủ tục đã có không làm thay đổi đến các quyền đã cấp phát trên thủ tục cũng như không tác động đến các thủ tục khác hay trigger phụ thuộc v ào thủ tục này.

7. Xóa thủ tục

Để xoá một thủ tục đã có, ta sử dụng câu lệnh DROP PROCEDURE với cú pháp như sau :

DROP PROCEDURE tên_thủ_tục

Khi xoá một thủ tục, toàn bộ những quyền đã cấp cho người sử dụng trên thủ tục đó cũng đồng thời bị xoá bỏ. Do đó, nếu tạo lại thủ tục, ta phải tiến h ành cấp phép lại những quyền trên thủ tục đó.

Source: https://vh2.com.vn
Category : Lưu Trữ VH2