quantriWEB.com | VietNam News Daily For Webmaster - Tìm hiểu về Stored Procedures trong SQL Server

Chi tiết về Stored Procedure trong SQL Server.

I.     Một số khái niệm về Stored Procedures( Những thủ tục lưu)

- Một thủ tục lưu là một tập hợp các câu lệnh SQL chưa biên dịch

- Stored procedure thực thi va hoàn thành các lệnh logic thông qua các ứng dụng khác nhau , các câu lệnh SQl luôn được kiểm tra một lần tại đây.

- Một khi Stored procedure được thiết lập thì những ứng dụng cần thiết tương tự chỉ cân thực hiên lại Store procedure

- Sử dụng Stored procedure để quản lý các đăng nhập và Stored procedure hệ thống để quản lý CSDL người dùng .

- Người phát triển CSDL hoặc người quản trị CSDL viết ra các thủ tục lưu để thực thi các nhiệm vụ quản trị thông thường hoặc áp dụng cho những CSDL phức tạp. Thủ tục lưu chứa các câu lệnh thao tác với dữ liệu hoặc các câu lệnh nhận dữ liệu trả về.

II .   Phân loại Thủ tục Lưu

• Thủ tục lưu hệ thống đề cập đến những phương pháp quản trị dư liệu và cập nhật thông tin vào các bảng .

• Thủ tục lưu do người dùng CSDL định nghĩa.

III. Các lợi ích của Những thủ tục lưu

Lợi ích của việc sử dụng các thủ tục lưu :

Tăng cường tốc độ sử lý
Truy cập dữ liệu nhanh chóng hơn
Lâp trình môđun
Tính nhất quán
Tiết kiêm thời gian
Dễ dàng quản ly và gỡ rối
Nâng cao độ an toàn bảo mật

IV Các dạng thủ tục lưu trữ

IV.1. Các thủ tục lưu hệ thống

- SQL server cung cấp các thủ tục lưu hệ thống , các thủ tục nay là một tập hơp các câu lệnh T_SQL . Những thủ tục này cho phép cập nhật CSDL và cập nhật các bảng.

- Tên của tất cả các thủ tục lưu hệ thống đều bắt đầu với “sp_”. Thủ tục Lưu hệ thống được đặt trong CSDL master

- Một số thủ tục lưu thông dụng

 Các thủ tục đăng nhập :

Chúng ta có thể sử dụng Stored procedures để tạo lập và gỡ bỏ các đăng nhập .

1) Để tạo lập một đăng nhập chúng ta thực hiên câu lệnh sau:

EXEC sp_addlogin ‘ ThaiMinh’ , ‘123456’

Ở đây tên đăng nhập là “ThaiMinh” và mật khẩu là “123456”

2) Để gỡ bỏ một đăng nhập khi nó không còn cần thiết nữa, chúng ta có thể sử dụng các câu lệnh sau:

EXEC sp_droplogin ‘ThaiMinh’

- Các thủ tục danh mục : Cung cấp một giao diện danh mục thống nhất cho việc truy cập vào các cầu nối CSDL , cũng như SQL server , từ cùng một ứng dụng , kết nối với CSDL mở của Microsoft (ODBC) API (application programming interface )

-Các thủ tục mở rộng : Các thủ tục mở rộng cho phép người dùng khôi phục và thực hiện một thư viện liên kết động (DLL) . THư viên liên kết động lá một tập hợp các thủ tục , chương trình con được lưu trữ trong một file.dll . Nó được nạp vào khi một chương trình cần đến.

- Các thủ tục hệ thống :

Thủ tục lưu trữ hệ thống Miêu tả

Sp_databases          Liệt kê tất cả các CSDL hiện tại trong server

Sp_server_info        Liệt kê thông tin về server , ví dụ như bộ kí tự , phiên bản và thứ tự sắp xếp

Sp_stored_procedures Liệt kê tất cả các thủ tục lưu co trong môi trường hiên tại .

Sp_tables                Liệt kê tất cả các đối tượng mà có thể được truy vấn trong môi trường hiện tại.

Sp_start_job           Bắt đầu một nhiệm vụ tư động ngay lập tức

Sp_stop_job           Dừng lại một nhiệm vụ tư động khi nó đang chạy

Sp_password         Thêm hoặc thay đổi mật khẩu của tài khoản đăng nhập.

Sp_configure          Thay đổi các tuỳ chọn trong cấu hình toàn cục của SQL server

Sp_help                 Hiển thị thông tin về một đối tượng CSDL nào đó.

Sp_helptext           Hiển thị chuỗi ký tự thực cho một quy tắc , hoặc huỷ thủ tục lưu trư không mã hoá , hàm định nghĩa bởi người sử dụng , bẫy lỗi hoặc view.

- Các thủ tục bảo mật : SQL server cung cấp một số thủ tục hệ thống giúp cho việc quản lý bảo mật .

- Các thủ tục con trỏ : thủ tục được sử dụng để thực hiện các chức năng của một con trỏ

- Các thủ tục truy vấn phân tán : SQl server cung cấp các thủ tục thực thi và quản lý các truy vấn phân tán . Các truy vấn phân tán nhận về dữ liệu không đồng nhất từ các nguồn kết hợp , bao gồm một hay nhiều CSDL của sql server.

- Các thủ tục về truy vấn dùng cho các tiến trình (agent) trong SQL server : các tiến trình trong sql server sử dụng các thủ tục này để lập lịch cho các tác vụ .

- Các thủ tục sql Mail : Các thủ tục này được sử dụng để thực hiện các thao tác về thư điện tử trong sql server.

IV.2 Các thủ tục lưu đinh nghĩa bởi người sử dụng

Bên cạnh việc sử dụng các thủ tục lưu được cài đặt sẵn , chúng ta có thể tạo ra những thủ tục lưu của riêng mình .

Cú pháp: CREATE PROC[EDURE] TÊN_THỦ _TỤC
Vd : Một thủ tục lưu tên là Titles_1389 hiển thi chi tiết các tiêu đề của publisher ID 1389 như sau:
CREATE PROCEDURE Titles_1389
AS “hiển thị các tiêu đề cho publisher 1389 ”
Select * from titles where pub_id = ‘1389’ như sau:

IV.3 Thực thi một thủ tục lưu

Cú Pháp : EXEC[UTE] TÊN_THỦ_TỤC
Vd : Câu lệnh thực thi thủ tục lưu ở trên là :
EXECUTE Titles_1389 như hình sau:

IV.4 Sử dụng tham biến trong thủ tục lưu
Cú pháp : CREATE PROCEDURE tên_ thủ_ tục
@Tên _tham_biến loại_ dữ_ liệu
As…
VD:
CREATE PROCEDURE Titles_Pub
@v_pubid char(4)
As
Select * from titles where pub_id = @v_pubid
Cụ thể nhìn hình sau:

Mỗi thủ tục được tao ra , sử dụng câu lệnh EXECUTE để truyền tham số và thực thi thủ tục . Lệnh EXECUTE để lấy ra các giá trị của tiêu đề cho publisher voi ID la 0877 sẽ như sau:

EXECUTE Titles_Pub ‘0877’
Kết quả của lệnh trên chỉ ra trong hình sau :

IV.5 Biên dịch lại thủ tục lưu

- Sử dụng thủ tục lưu hệ thống sp_recompile : thủ tục này buộc một thủ tục lưu phải được biên dich lại khi nó thực hiên ở lần kế tiêp
Cú pháp : sp_recompile [ @ objectname = ] ‘object ’

-Chỉ định WITH RECOMPILE với câu lệnh CREATE PROCEDURE
Mệnh đề WITH RECOMPILE được sử dụng để tạo ra thủ tục lưu , SQL sẽ biên dịch lại thủ tục mỗi lần nó được thực thi . Tuy nhiên điều này làm chậm lại quá trình sử lý thủ tục .

Cú pháp của nó:

CREATE PROCEDURE tên_thủ _tục
@parameter_name data_type
With recompile As…
- Chỉ định WITH RECOMPILE với lệnh EXECUTE

EXEC[UTE] tên ¬¬¬¬¬¬¬¬_thủ _tục WITH RECOMPILE

IV.6 Những thủ tục thông báo lỗi

Mã trả về phải trả lại giá trị của chúng vào trong một biến số. Câu lệnh để khai báo biến số và sử sụng nó trong suốt quá trình thực thi thủ tục là :

DECLARE @return_variable_name data_type

EXECUTE @return_variable_name = procedure_name

Trong đó @return_variable_name chứa mã trả về bởi thủ tục lưu

VD: Hãy sửa đổi thủ tục Titles_pub để trả về giá trị 0, giá trị mặc định của sql server , thực hiện thành công thì hiển thị các hàng . Nếu không có hàng nào được tìm thấy , nó sẽ trả về giá trị 1

ALTER PROCEDURE titles_Pub ; khai báo sửa đổi thủ tục
@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