Bài tập sử dụng store procedure trong sql server

Stored procedure là tập hợp một hoặc nhiều câu lệnh T-SQL được gom thành một nhóm đơn vị xử lý logic và được lưu trữ trên Database Server. Khi stored procedure được thực thi, ở lần gọi lần đầu tiên, SQL Server sẽ chạy nó và lưu trữ vào bộ nhớ đệm, gọi là plan cache, những lần tiếp theo SQL Server sẽ sử dụng lại plan cache nên sẽ cho tốc độ xử lý tối ưu. Việc này rất hiệu quả trong việc tối ưu cơ sử dữ liệu lớn với các ứng dụng cần hiệu năng cao

Ngoài ra, stored procedure vô cùng tiện lợi cho người quản trị database (DBA), nó giúp DBA tạo ra những khối lệnh đã được đặt tên sẵn và gửi cho dev mà không cần quan tâm đến nội dung bên trong stored procedure chứa gì, chỉ cần quan tâm quan tâm đến tham số đầu vào và đầu ra. Ví dụ, bạn viết một stored procedured lấy ra danh sách đơn hàng trong tháng, rồi đưa cho dev để tích hợp vào ứng dụng, dev không cần can thiệp vào CSDL để viết code mới, chỉ cần gọi tới stored procedure bạn đã khai báo và sử dụng thôi.

Ví dụ bạn viết một stored procedure lấy danh sách sản phẩm bán chạy theo ngày, sau đó bạn gửi stored này qua bộ phận development kèm theo tài liệu hướng dẫn sử dụng thì lúc này bộ phận DEV không cận quan tâm đến nội dung bên trong của stored mà chỉ cần thông tin tham số truyền vào và kết quả trả về của stored.

Lợi ích khi sử dụng Store Procedured:

• Imporve Security: Database administrator có thể gia tăng bảo mật bằng cách liên kết các đặc quyền với store procedures. Người dùng có thể nhận quyền để thực thi stored procedure mặc dù không có quyền truy cập vào bảng hoặc view.
• Precompiled Execution: Store procedure được biên dịch trong lần thực thi đầu tiên. Cho mỗi lần thực thi tiếp theo, SQL Server sử dụng lại phiên bản biên dịch trước, giúp giảm thiểu thời gian thực thi và gia tăng hiệu năng
• Reduced Client/Server Traffic: stored procedure giúp giảm thiểu traffic hệ thống, khi câu lệnh T-SQL được thực thi, network sử dụng tài nguyên riêng biệt cho mỗi câu thực thi. Còn khi Stored procedure thực thi, câu lệnh SQL được nhóm lại và thực thi như là một đơn vị, giúp giảm lưu lượng mạng.
• Reuse of code: Stored procedures có thể sử dụng lại nhiều lần, Điều này giúp loại bỏ sự cần thiết phải nhập lặp đi lặp lại hàng trăm câu lệnh Transact-SQL mỗi khi thực hiện một tác vụ tương tự.

Các loại Stored Procedures

User defined Stored Procedures:

có thế được gọi là custom stored procedures, các procedures này sử dụng để có thể sử dụng lại các câu lệnh T-SQL cho việc xử lý các tác vụ lapwj lại. Có 2 kiểu user-defined stored procedures là T-SQL stored procedures và Common Language Runtime (CLR) stored procedures. CLR stored procedures dựa trên một số phương thức của .NET Framwork cả 2 loại stored procedure này có thể nhận vào và trả về các tham số.

Extend Stored Procedures:

Extend Stored Procedure giúp SQL Serer trong việc tương tác với hệ điều hành. Extended stored procedures không nằm trong objects của SQL Server. Chúng là stored procedure được phát triển như là Dinamic Link Libraries (DDL) thực thi bên ngooài môi trường SQL Server. Ứng dụng tương tác với SQL Server và gọi DLL trong khi chạy, SQL Server phân bổ không gian để chạy các extend stored procedures. Các extend stored procedures sử dụng tiền tố ‘xp’

System Stored Procedures:

System stored procedures thường được sử dụng để tương tác với các bảng hệ thống và xử lý các tác vụ quản trị như là update các bảng hệ thống, system stored procedures đi kèm tiền tố ‘sp_’. Các procedures này được đặt trong database Resource. Các procedures có thể nhìn thấy ở trong sys schema của mỗi system hoặc user-defined database. System stored procedures cho phép các quyền GRANT, DENY, REVOKE.

Một system stored procedure là một tập các câu lệnh T-SQL được biên dịch trước và các câu lệnh sẽ được thực thi như một đơn vị. System procedures được sử dụng trong quản trị CSDL và các quản trị các hoạt động, thông tin của hệ thống.. Procedures này cung cấp giải pháp để dễ dàng truy cập vào các thông tin metadata về database objects như system tables, user-defined tables, views, indexes.

System stored procedures xuất hiện một cách hợp lý trong sys schema của hệ thống và user-defined database. Khi tham chiếu đến một system stored procedures, sys schema identifier sẽ được sử dụng. Các system stored procedures trong hệ thống được lưu trữ vật lý trong cơ sở dữ liệu ẩn bên trong database Resource có tiền tố sp_. Các system stored procedures thuộc sở hữu của database administrator ( cấp quản trị cao nhất trong hệ thống CSDL).

Lưu ý: Các bảng hệ thống được tạo mặc định tại thời điểm tạo database mới, các bảng này lưu trữ các thông tin metadata về các user-defined objects như là tables và views. Người dùng không thể truy cập hoặc cập nhật bảng hệ thống sử dụng system stored procedures ngoại trừ được cấp đặc quyền bởi database administrator.

Phân loại các System Stored Procedures

• Catalog Stored Procedures: Tất cả các thông tin về bảng trong database đưojc lưu trữ trong tập các các bảng được gọi là system catalog. Thông tin từ system catalog có thể truy vấn bằng sử dụng các catalog procedures. Ví dụL sp_tables là một catalog stored procedures hiển thị danh sách các bảng của database hiện thời
• Security Stored Procedures: Securirty Stored Procedures sử dụng để quản trị bảo mật trong CSDL. Ví dụ: sp_changedbowner là một security stored procedure sử dụng để thay đổi owner của database hiện thời.
• Cursor Stored Procedures: Cursor procedures được sử dụng để triển hkai các tính năng của con trỏ. Ví dụ sp_cursor_list là stored procedure lấy ra toàn bộ các con trỏ đang được mở bởi kết nối và mô tả các thuộc tính của chúng.
• Distributed Query Stored Procedures: Distributed stored procedures được sử dụng để quản trị các câu truy vấn phân tán. Ví dụ: sp_indexes là một distributed query stored procedure , trả về thông tin index cho một bảng xác định.
• Database Mail and SQL Mail Stored Procedures: được sử dụng để xử lý các tác vụ làm việc với email trong sQL server. Ví dụ: sp_send_dbmail là database mail stored procedure gửi email cho ngời nhận xác định. Nội dung email có thể là một tập kết quả, file attachment hoặc cả 2.

Temporary Stored Procedures

Các thủ tục được lưu trữ được tạo ra để sử dụng tạm thời trong một phiên được gọi là các temporary stored procedure. Các thủ tục này được lưu trữ trong bảng tempdb. Bảng hệ thống tempdb là một tài nguyên toàn cục khả dụng cho toàn bộ người dùng kết nối thông qua instance SQL Server. Nó chứa toàn bộ các temporary tables (bảng tạm) và temporary stored proedures.

SQL Server hỗ trợ 2 kiểu temporary stored procedures local và global, sự khác biệt như sau:

Local Temporary ProcedureGlobal Temporary ProcedureChỉ tồn tại khi user tạo ra nóKhả dụng cho toàn bộ userXóa khi kết thúc phiên làm việc hiện thờiXóa khi kết thúc phiên làm việc cuối cùngChỉ có thẻ sử dụng bởi ownerCó thể sử dụng bởi bất kỳ user nàoSử dụng tiền tố # trước procedure name Sử dụng tiền tố ## trước procedure name

Lưu ý: Một phiên (session) được thành lập khi usser kết nối vào database và nó kết thúc khi user ngắt kết nối. Tên hoàn thiện của global temporary stored procedure bao gồm tiền tố ## không thể quá 128 ký tự. Tên hooàn thiện của local temporary stored procedure bao gồm tiền tố # không thể quá 116 ký tự.

Remote Store Procedures

Stored procedures có thể chạy trên remote SQL SErver ( điều kiển từ xa) được gọi là remote stored procedures. Remote soted procedure có thể sử dụng chỉ khi server cho phép remote access ( truy cập từ xa, không phải cục bộ). Khi remote stored procedure đưojc thực thi từ một instance SQL Server cục bộ tới máy khách, câu lệnh sẽ có thể gặp phải lỗi hủy bỏ. Khi mà bắt gặp lỗi, caua lệnh gây ra lỗi sẽ châm dứt nhưng remote stored procedure vẫn tiếp tục thực thi.

Extended Stored Procedures

Extended stored procedure được sử dụng để xử lý các tác vụ không thể xử lý bằng các cú pháp T-SQL thông thuường. Extended stored procedure sử dụng tiền tố ‘xp_’. Các stored procedures này sẽ được chứa trong schema dbo của database master.

Cú pháp:

EXECUTE 

Ví dụ sử dụng extened stored procedure xp_fileexist để kiểm tra file Mytext.txt có tồn tại không:

EXECUTE xp_fileexist 'C:\MyTest.txt'

User Defined Stored Procedure

Trong SQL Server, người dùng được phép tạo các stored procedure tùy chỉnh để thực thi các tác vụ khác nhau, các stored procedure này được gọi là user-defined hoặc custom stored procedure.

Ví dụ, giá sử bảng Customer_Details lưu trữ toàn bộ dữ liệu của customer, chúng ta sẽ cần viết các câu lệnh T-SQL mỗi khi muốn xem dữ liệu chi tiết về customer. Thay vào đó, chúng ta có thể tạo ra một costom stored procedure nhằm mục đích hiển thị chi tiết customer mỗi khi thủ tục được thi để sử dụng lại nhiều lần.

Tạo ra một custom stored procedure yêu cầu quyền CREATE PROCEDURE trong database và quyền ALTER schema cho thủ tục được tạo.

Cú pháp:

CREATE {PROC|PROCEDURE} proc_name [{@parameter data_type}] AS 

Ví dụ khởi tạo một cusstom stored procedure có tên uspGetCustTerritory sử dụng để hiển thị thông tin chi tiết customer:

use AdventureWorks2019
CREATE PROCEDURE uspGetCustTerritory
AS
SELECT TOP 10 CustomerID,Customer.TerritoryID,Sales.SalesTerritory.Name FROM Sales.Customer JOIN Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID
go
exec uspGetCustTerritory

Sử dụng parameter (tham số)

Sức mạnh của stored procedure đến từ việc có thể đưa tham số vào bên trong nó, dữ liệu sẽ được truyền vào trong stored procedure thông qua chương trình gọi, có 2 kiểu tham số như sau:

• Input parameters cho phép chương trình gọi truyền values vào stored procedure. Các giá trị này được hứng vào biến được định nghĩa trong stored procedures.
• Output parameters cho phép stored procedure truyền giá trị lại cho chương trình gọi. Các giá trị này được hứng vào biến của chương trình gọi.

Input parameters

Giá trị được truyền vào từ chương trình gọi tới stored procedure, các giá trị này được hứng vào biến được định nghĩa trong stored procedures. Input parameter được định nghĩa tại thời điểm tạo stored procedure. Các giá trị truyền vào input parameter có thể là biến hoặc hằng số, giá trị sẽ được truyền vào stored procedure tại thời điểm gọi procedure. Store procedure xử lý tác vụ cụ thể bằng các giá trị này.

Cú pháp:

CREATE PROCEDURE 
@parameter 
AS 
EXEC| EXECUTE  

Ví dụ tạo stored procedure uspGetSales với parameter territory để nhận vào tên của territory và hiển thị sale details và salesperson id cho territory này. Sau đó, code thực thư stored procedure với giá trị ‘Northwest’ sẽ được truyền vào như một tham số đầu vào:

use AdventureWorks2019;
GO
CREATE PROCEDURE uspGetSales @territory varchar(40)
AS
SELECT BusinessEntityID, B.SalesYTD, B.SalesLastYear FROM
Sales.SalesPerson A
JOIN sales.SalesTerritory B
ON A.TerritoryID = B.TerritoryID WHERE
B.Name=@territory;
GO
exec uspGetSales 'Northwest'

Kết quả khi thực thi stored procedure

Output parameters

Stored procedures thỉnh thooảng cần trả output ra cho chương trình gọi. Nó sẽ chuyển dữ liệu từ stored procedure sang chương trình gọi và được xử lý thông qua output parameters. Output parameters được định nghĩa tại thời điểm trạo stored procedure. Để xác định output parameter, từ khóa OUTPUT được sử dụng khi khai báo parameter. Và việc gọi câu lệnh cũng cần xác định biến với từ khóa OUTPUT.

Cú pháp:

CREATE PROCEDURE 
@parameter  OUTPUT
AS 
EXEC| EXECUTE  

Ví dụ stored procedure upsGetTotalSales với input parameter là @territory để nhận vào tên của territory và đauà ra là parameter @sum để lấy ra tổng của sales year to date:

use AdventureWorks2019;
GO
CREATE PROCEDURE uspGetTotalSales 
@territory varchar(40),
@sum int OUTPUT AS
SELECT @sum= SUM(B.SalesYTD) FROM
Sales.SalesPerson A JOIN
Sales.SalesTerritory B
ON A.TerritoryID = B.TerritoryID
WHERE B.Name = @territory
GO

Ở bước tiếp theo, chúng ta thực thi stored procedure trên bằng cách khai báo ra biến @sumsale để hứng giá trị output.

DECLARE @sumsale int;
exec uspGetTotalSales 'NorthWest', @sumsale OUTPUT;
SELECT @sumsale AS 'Total sales northWest';

OUTPUT parameters có các đặc tính sau:

• Không thể mang kiểu dữ liệu text hoặc image data type
• Câu lệnh gọi phải chứa biến để nhận giá trị trả ra.
• Biến có thể sử dụng trong cuộc gọi lại câu lệnh T-SQL tiếp theo để trả ra cho người dùng

Mệnh đề OUTPUT trả ra thông tin mỗi hàng mà có câu lệnh INSERT, UPDATE, DELETE được thực thi. Mệnh đề này hữu dụng để nhận các giá trị identity, hoặc cột được tính toán sau khi thực hiện tác vụ INSERT hoặc UPDATE.

Tất nhiên, chúng ta cũng có thể sử dụng SSMS để tạo Stored Procedures.

Sử dụng SSMS tạo Stored Procedure

ALTER (Sửa đổi) Stored Procedure

Các quyền mà liên kết với stored procedure sẽ mất khi stored procedure được khởi tạo lại. Tuy nhiên, khi sửa đổi thông qua câu lệnh ALTER, quyền đã định nghĩa cho procedure vẫn giữ được trạng thái.

Cú pháp:

ALTER PROCEDURE 
@parameter  [OUTPUT]
[WITH {ENCRYPTION|RECOMPILE}]
AS 

Sửa đổi định nghĩa của stored procedure tên uspGetTotals để thêm cột CostYTD trong bảng Sales.SalesTerritory

EXECUTE xp_fileexist 'C:\MyTest.txt'

0

Lưu ý: khi bạn thay đổi định nghĩa trong stored procedure, các objects phụ thuộc có thể xảy ra lỗi khi thực thi, vấn đề này xảy ra nếu các dependent objects không được cập nhật để phản ánh sựa thay đổi của stored procedure.

DROP Stored Procedure

Stored procedure có thể drop khi không cần sử dụng nữa, nếu chương trình gọi tới một procedure đã được xóa sẽ có lỗi trả ra.

Nếu một procedure mới được tạo cùng tên và cùng parameter với procedure đã drop, tất cả những lần gọi tới procedure cũ đều thực thi thành công, vì nó sẽ tham chiếu tới procedure mới mà cùng tên và cùng tham số với procedure đã xóa.

Trước khi drop stored procedure, có thể thực thi system stored procedure tên sp_depends để nhận diện object nào phụ thuộc vào procedure.

Cú pháp:

EXECUTE xp_fileexist 'C:\MyTest.txt'

1

EXECUTE xp_fileexist 'C:\MyTest.txt'

2

Nested Stored Procedures

SQL Server 2019 cho phép cho phép các thủ tục được lưu trữ được gọi bên trong các thủ tục được lưu trữ khác. Kiến trúc stored procedure này gọi tới stored procedure khác gọi là nested stored procedure.

Khi một thủ tục được lưu trữ gọi một thủ tục được lưu trữ khác, tầng lồng ghép được cho là sẽ tăng lên một. Tương tự, khi thủ tục lưu trữ hooàn thành việc thực thi và chuyển luồng điều khiển về stored procedure gọi, tầng lồng ghép giảm đi một. Tầng lồng ghép tối đa hỗ trợ bởi SQL Server 2019 là 32.