Các kiểu Stored Procedures (SPS)

VDO xin chào quý khách!

Hôm nay lại được gặp lại quý khách trong chuyên mục  Kiến thức máy chủ , bài viết trước chúng ta đang đi tìm hiểu về định nghĩa và Lợi ích khi quản lý dữ liệu bằng Stored Procedures (SPS) rồi ,Sau đây chúng ta sẽ cùng đi tìm hiểu tiếp xem Các kiểu Stored Procedures (SPS)

Các kiểu Stored Procedures (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:

System stored procedure Chức năng
sp_Databases Danh sách những Database có thể (avaiable) trên Server (Danh sách này sẽ là khác nhau tuỳ thuộc vào quyền của người sử dụng)
sp_server_info Chi tiết những thông tin về Server, ví dụ như tập các đặc tính, phiên bản…
sp_stored_procedures Danh sách tất cả các thủ tục có thể trên môi trường hiện tại
sp_tables Danh sách tất các bảng có thể trên môi trường hiện tại
sp_start_job Khởi động tất cả các automated task ngay lập tức
sp_stop_job Ngừng lại tất cả các automated task đang chạy
sp_password Thay đổi password cho login account
sp_configure Thay đổi lựa chọn cấu hình chung của SQL SERVER. Khi người sử dụng không lựa chọn thì hệ thống sẽ hiển thị cấu hình mặc định.
sp_help Hiển thị thông tin về bất kỳ đối tượng nào trong Database
sp_helptext Hiển thị nội dung (text) của các đối tượng

User-defined Stored Procedures

Cú pháp

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

Thực hiện User-defined Stored Procedures

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

Kết quả thực hiện:

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

Kết luận : Trên đây là một số kiểu Stored Procedures (SPS) các bạn có thể tham khảo thêm, mọi chi tiết thắc mắc vui lòng liên hệ trực tiếp với VDO chúng tôi :

CÔNG TY CỔ PHẦN DỮ LIỆU TRỰC TUYẾN VIỆT NAM (VDO)

Địa chỉ: Số 61,Mễ Trì Thượng, Phường Mễ Trì, Nam Từ Liêm, Thành phố Hà Nội, Việt Nam

Địa chỉ: Số 159 Phan Xích Long, Phường 7, Quận Phú Nhuận, Thành phố Hồ Chí Minh, Việt Nam

Hotline: 024 7305 6666

Hotline: 028 7308 6666

Tổng đài hỗ trợ kỹ thuật và tư vấn dịch vụ: 1900 0366