Thủ tục - Stored Procedures(SPS)

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

 Thủ tục - Stored Procedures(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:

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

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

 

File: 
Bạn thấy bài viết này như thế nào?: 
Average: 10 (1 vote)
Ảnh của Tommy Tran

Tommy Tran owner Express Magazine

Drupal Developer having 9+ year experience, implementation and having strong knowledge of technical specifications, workflow development. Ability to perform effectively and efficiently in team and individually. Always enthusiastic and interseted to study new technologies

  • Skype ID: tthanhthuy
  • Phone/Zalo: (+84) 944 225 212
  • WhatsApp: (+84) 944 225 212
  • Line Messenger: (+84) 944 225 212
  • Email: asaleotestf@gmail.com
  • Telegram Messenger: https:/t.me/tommytran0401

Quảng cáo việc làm

 

Thích hợp các bạn nữ mảng thợ may làm việc tại nước NGA

Đơn hàng Tuyển dụng 100 Thợ may đi Nga(đợt 1 tháng 3.2021, đợt 2 tháng 5.2021). Lương thực lãnh 800 USD, bao ăn ở, vé máy bay và visa, phí xuất cảnh(1800 USD)trả khi đi làm có lương. Bạn có thể liên hệ CÔNG TY qua Phone/Zalo: (+84) 944 225 212. Công ty sẽ tư vấn cho bạn.

Xem chi tiết: >>> https://bit.ly/3o9NOfR

Advertisement

 

jobsora

Dich vu khu trung tphcm

Dich vu diet chuot tphcm

Dich vu diet con trung

Quảng Cáo Bài Viết

 
Cách làm picture and Image Replace trong Drupal 7

Cách làm picture and Image Replace trong Drupal 7

The Picture module is a backport of Drupal 8 Responsive Image module. It allows you to select different images to be loaded for different devices and resolutions using media queries and Drupal’s image styles

 Webmaster và Kiến thức quảng bá Web, Tối ưu Website cho Google

Webmaster và Kiến thức quảng bá Web, Tối ưu Website cho Google

Phân tích các câu hỏi thường gặp của Webmaster và đưa ra các giải pháp tối ưu cho công cụ tìm kiếm Google.

Social Netwok

CIA lập nhóm đặc nhiệm theo dõi tin nhắn mạng xã hội

Theo AP, Cơ quan Tình báo trung ương Mỹ (CIA) vừa lập ra một "nhóm đặc nhiệm" mang tên Open Source Center, chuyên theo dõi tin nhắn được gửi lên các mạng xã hội như Twitter và Facebook mỗi ngày, nhằm phân tích ý kiến của công chúng trên toàn thế giới.