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 Show
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:
Các loại Stored ProceduresUser 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
Temporary Stored ProceduresCá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 ProceduresStored 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 ProceduresExtended 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:
Ví dụ sử dụng extened stored procedure xp_fileexist để kiểm tra file Mytext.txt có tồn tại không:
User Defined Stored ProcedureTrong 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:
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:
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 parametersGiá 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:
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: Kết quả khi thực thi stored procedureOutput parametersStored 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:
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:
Ở 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.
OUTPUT parameters có các đặc tính sau:
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 ProcedureALTER (Sửa đổi) Stored ProcedureCá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:
Sửa đổi định nghĩa của stored procedure tên uspGetTotals để thêm cột CostYTD trong bảng Sales.SalesTerritory
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 ProcedureStored 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:
1
2 Nested Stored ProceduresSQL 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. |