Thủ tục - Stored Procedures(SPS)

1. Định nghĩa

SPs là tập hợp của các câu lệnh T-SQL được biên dịch trước (pre_compiled). SPs được đặt tên và được xử lý như một khối lệnh thống nhất (chứ không phải thực hiện rời rạc các câu lệnh).

SQL Server cung cấp một số các thủ tục được lưu trữ sẵn trong hệ thống giúp thực hiện một số công việc thường xuyên. Nó được gọi là thủ tục hệ thống –System stored procedures. Còn những thủ tục do người sử dụng tự viết gọi là User stored procedures.

SPs trong SQL Server cũng tương tự như khái niệm về thủ tục trong các ngôn ngữ lập trình khác, bởi vì:

Chấp nhận biến đầu vào và trả lại kết quả khi thực hiện.

Chứa những câu lệnh dùng trong lập trình có thể thao tác với cơ sở dữ liệu và có thể gọi đến các thủ tục khác.

Trả lại giá trị trạng thái khi thủ tục được gọi để xác định việc thực hiện thủ tục thành công hay thất bại.

Các thành phần của SPs

2. Lợi ích khi quản lý dữ liệu bằng SPs

Tăng tốc độ thực hiện: Một trong những lợi ích lớn nhất khi sử dụng SPs là tốc độ. SPs được tối ưu hoá trong ngay ở lần biên dịch đầu tiên, điều này cho phép chúng có thể thực hiện nhanh hơn nhiều lần so với các câu lệnh T-SQL thông thường.

Tốc độ truy cập dữ liệu nhanh hơn: Khi thực thi một câu lệnh SQL thì SQL Server phải kiểm tra permission xem user gửi câu lệnh đó có được phép thực hiện câu lệnh hay không đồng thời kiểm tra cú pháp rồi mới tạo ra một execute plan và thực thi. Nếu có nhiều câu lệnh như vậy gửi qua network có thể làm giảm đi tốc độ làm việc của server. SQL Server sẽ làm việc hiệu quả hơn nếu dùng stored procedure vì người gửi chỉ gửi một câu lệnh đơn và SQL Server chỉ kiểm tra một lần sau đó tạo ra một execute plan và thực thi. Nếu stored procedure được gọi nhiều lần thì execute plan có thể được sử dụng lại nên sẽ làm việc nhanh hơn. Ngoài ra cú pháp của các câu lệnh SQL đã được SQL Sever kiểm tra trước khi lưu nên nó không cần kiểm lại khi thực thi.

Chương trình được modul hoá: Một khi stored procedure được tạo ra nó có thể được sử dụng lại. Ðiều này sẽ làm cho việc bảo trì (maintainability) dễ dàng hơn do việc tách rời giữa business rules (tức là những logic thể hiện bên trong stored procedure) và cơ sở dữ liệu. Ví dụ nếu có một sự thay đổi nào đó về mặt logic thì ta chỉ việc thay đổi code bên trong stored procedure mà thôi. Những ứng dụng dùng stored procedure này có thể sẽ không cần phải thay đổi mà vẫn tương thích với business rule mới.

Nhất quán: Lợi ích nữa của SPs là thiết đặt được ràng buộc dữ liệu để đảm bảo tính nhất quán. Người sử dụng không thể thực hiện tuỳ tiện dữ liệu để làm mất tính đúng đắn của dữ liệu.

Nâng cao khả năng bảo mật dữ liệu: Giả sử chúng ta muốn giới hạn việc truy xuất dữ liệu trực tiếp của một user nào đó vào một số bảng, ta có thể viết một stored procedure để truy xuất dữ liệu và chỉ cho phép user đó được sử dụng stored procedure đã viết sẵn mà thôi chứ không thể thao tác trực tiếp trên các bảng đó. Ví dụ, ta có thể tạo ra SPs để ta làm chủ và chỉ cung cấp quyền EXCUTE cho những SPs này, vì thế những người sử dụng khác không được phép trực tiếp làm việc với dữ liệu.

Ngoài ra stored procedure có thể được encrypt (mã hóa) để tăng cường tính bảo mật.

3. Các kiểu SPs

SPs chia làm 2 loại:

System stored procedures: Thủ tục mà những người sử dụng chỉ có quyền thực hiện, không được phép thay đổi.

User stored procedures: Thủ tục do người sử dụng tạo và thực hiện.

3.1 System stored procedures

Là những stored procedure chứa trong Master Database và thường bắt đầu bằng tiếp đầu ngữ sp_ .

Các stored procedure này thuộc loại built-in và chủ yếu dùng trong việc quản lý cơ sở dữ liệu (administration) và bảo mật (security). Ví dụ bạn có thể kiểm tra tất cả các processes đang được sử dụng bởi user DomainNameAdministrators bạn có thể dùng :

sp_who @loginame='DomainNameAdministrators'

Người ta có thể chia các System stored procedures thành các loại sau:

Các loại của System stored procedures

Có hàng trăm system stored procedure trong SQL Server. Bạn có thể xem chi tiết phân loại và nội dung của từng thủ tục trong SQL Server Books Online.

Sau đây là một số thủ tục hệ thống thường sử dụng:

User-defined Stored Procedures

Người sử dụng có thể sử dụng câu lệnh CREATE PROCEDURE để tạo thủ tục trong CSDL hiện tại.

Database owner mặc định có quyền sử dụng câu lệnh CREATE PROCEDURE.

Cú pháp:

CREATE PROC[EDURE] procedure_name

CREATE PROCEDURE London_Flights AS

PRINT 'This code displays the details of flights to London'

SELECT * FROM flight WHERE destination='Lon'

Các chỉ dẫn

Tên thủ tục phải tuân theo quy tắc đặt tên

Tất cả các đối tượng của cơ sở dữ liệu có thể được tạo trong SPs, trừ những đối tượng: defaults, rules, triggers, procedures, và views.

Những đối tượng đã được tạo có thể được tham chiếu đến ngay khi nó được tạo.

Stored procedures có thể tham chiếu tới những bảng phụ (temporary tables).

Có thể có 2100 biến trong stored procedure.

Chúng ta có thể tạo nhiều biến địa phương trong stored procedure nếu bộ nhớ cho phép.

Kích thước tối đa cho stored procedure là 128 MB.

Thực hiện User-defined Stored Procedures

Cú pháp:

EXEC[UTE] procedure_name

Sử dụng biến trong Stored Procedures

Biến có thể được sử dụng để nhập dữ liệu vào (INPUT) hoặc xuất dữ liệu ra ngoài (OUTPUT)

Cú pháp:

CREATE PROCEDURE procedure_name @Parameter_name data_type AS :

CREATE PROCEDURE city_flights

@v_city varchar(15)

AS

SELECT * FROM flight WHERE destination=@v_city

Thực hiện thủ tục có biến:

Thực hiện User-defined Stored Procedures có biến

Nếu có nhiều biến trong thủ tục thì khi thực hiện ta liệt kê theo thứ tự các biến và phải cách nhau bằng dấu phẩy.

Biên dịch lại - Re-compiling Stored Procedures

Khi người sử dụng làm thay đổi tới những index của bảng. Stored procedures phải được biên dịch lại (recompiled) để chấp nhận những thay đổi đó.

Có 3 cách để biên dịch lại procedures:

Sử dụng sp_recompile system stored procedure: Bạn có thể sử dụng cách này để biên dịch lại thủ tục ở lần chạy kế tiếp của nó.

Cú pháp:

sp_recompile [@objectname=] ‘object’

Chỉ ra WITH RECOMPILE trong câu lệnh CREATE PROCEDURE: SQL Server sẽ biên dịch lại thủ tục ở mỗi lần nó thực hiện.

Cú pháp:

CREATE PROCEDURE procedure_name @Parameter_name data_type WITH RECOMPILE AS :

Chỉ ra WITH RECOMPILE trong câu lệnh EXECUTE:

Biên dịch lại ngay ở lần thực hiện này.

Cú pháp:

EXEC[UTE] procedure_name WITH RECOMPILE

Sửa cấu trúc của Stored Procedures

Câu lệnh ALTER PROCEDURE được sử dụng để sửa SP.

Cú pháp tương tự CREATE PROCEDURE chỉ thay từ CREATE bằng ALTER.

Việc sửa chữa vẫn lưu lại quyền của người sử dụng (user permissions)

4. Thông báo lỗi

Trả về mã lỗi (Code) hoặc câu lệnh RAISERROR có thể được sử dụng để nhắc người sử dụng về lỗi.

Mã lỗi trả về là số nguyên.

Câu lệnh RAISERROR giải thích lỗi và chỉ ra mức độ lỗi.

Return Codes

Return codes là số nguyên, giá trị mặc định là 0.

Giá trị của Return codes phải được trả về vào một biến

Cú pháp:

DECLARE @return_variable_name data_type

EXECUTE @return_variable_name = procedure_name

ALTER PROCEDURE Titles_Pub @v_pubid char(4) AS DECLARE @v_return int SELECT @v_return=COUNT(*) FROM titles WHERE pub_id = @v_pubid IF @v_return>0 SELECT * FROM titles WHERE pub_id = @v_pubid ELSE RETURN @v_return+1

Câu lệnh RAISERROR

Trong SPs, chúng ta có thể sử dụng câu lệnh PRINT để hiển thị thông báo lỗi cho người sử dụng. Tuy nhiên, những lời nhắc này chỉ là tạm thời và chỉ hiển thị cho người sử dụng chúng ta cần sử dụng câu lệnh RAISERROR để ghi lại những lỗi này và gán cho nó mức severity.

Cú pháp:

RAISERROR ({msg_id | msg_str}{,severity, state}

[WITH option[...n]]

WHILE @v_ctr > 0

BEGIN SELECT @v_ctr * @v_ctr

SELECT @v_ctr = @v_ctr – 1 IF @v_ctr = 2

BEGIN RAISERROR('Counter has fallen below 3', 1, 2)

BREAK END END

Kết quả:

25

16

9

Nội dung thông báo:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 50000, Level 1, State 50000

Counter has fallen below 3

Câu hỏi trắc nghiệm

1. Ký hiệu nào theo sau đứng trước tên biến trong câu lệnh EXECUTE?

A. &

B. #

C. ?

D.@

2. Câu lệnh nào sau đây sẽ kết thúc thực hiện thủ tục (Những câu lệnh đằng sau câu lệnh này sẽ không được thực hiện)?

A. RETURN.

B. EXIT.

C. HALT.

D. FINISH.

3. Người dùng nào sau đây có quyền mặc định để chạy Stored Procedure?

A. Data Owner.

B. Row Owner.

C. Table Owner.

D: Database Owner.

4. Hiệu quả của việc chỉ ra WITH RECOMPILE trong khi định nghĩa Stored Procedure (SP) là gì?

A. SP được biên dịch lại ở ngay lần tiếp theo nó thực hiện .

B. SP được biên dịch lại ở tất cả các lần nó thực hiện.

C: SP được biên dịch lại khi SQL Server khởi động

C: SP được biên dịch lại khi có chỉ số được tạo trên các bảng mà nó tham chiếu tới

5. Chúng ta có thể sửa chữa được những thủ tục hệ thống (System stored procedures)

A.Đúng.

B.Sai.

6. Thông báo được định nghĩa cùng RAISERROR có thể chứa đựng tối đa bao nhiêu ký tự?

A. 64

B. 510

C. 255

D. 128