THỦ TỤC LƯU TRỮ (STORED PROCEDURE)
Tổng quan về thủ tục
Đoạn chương trình kịch bản (programming scripts) chứa các câu lệnh SQL nhúng (embedded SQL) được lưu trữ dưới dạng đã biên dịch và được thực thi trực tiếp bởi SQL Server.
Thủ tục lưu trữ cho phép lưu trữ logic ứng dụng trên cơ sở dữ liệu (CSDL) Khi được gọi lần đầu, SQL Server sẽ tạo và lưu trữ lịch thực thi trong bộ nhớ đệm Ở những lần gọi tiếp theo, SQL Server sẽ nhanh chóng sử dụng lại lịch thực thi đã lưu, mang lại hiệu suất đáng tin cậy.
Thủ tục lưu trữ là một mã SQL được chuẩn bị sẵn và có thể tái sử dụng nhiều lần Nếu bạn thường xuyên viết lại một truy vấn SQL, hãy lưu nó dưới dạng thủ tục lưu trữ và chỉ cần gọi nó khi cần Bên cạnh đó, bạn cũng có thể truyền các tham số cho thủ tục lưu trữ, giúp nó hoạt động dựa trên các giá trị tham số đó.
Người dùng chọn Thủ tục lưu trữ nhằm giảm thiểu mã chương trình dư thừa, cải thiện tốc độ thực thi câu lệnh, dễ dàng bảo trì và nâng cao mức độ an ninh.
Phân loại thủ tục
Thủ tục do người dùng định nghĩa: Là thủ tục do người dùng tạo ra để thực hiện các thao tác nhằm đáp ứng nhu cầu công việc riêng
Thủ tục lưu trữ tạm thời là một loại thủ tục do người dùng xác định và được lưu trữ trong cơ sở dữ liệu tempdb Có hai loại thủ tục tạm thời: cục bộ và toàn cục Thủ tục tạm thời cục bộ, được đánh dấu bằng tiền tố (#), chỉ hiển thị với kết nối người dùng hiện tại và sẽ tự động bị xóa khi kết nối đó bị đóng Ngược lại, thủ tục tạm thời toàn cục, với hai dấu (##) ở đầu tên, có thể được truy cập bởi bất kỳ người dùng nào sau khi được tạo ra và sẽ bị xóa vào cuối phiên thông qua quy trình.
Thủ tục lưu trữ hệ thống trong SQL Server được tích hợp sẵn dưới dạng lưu trữ vật lý trong cơ sở dữ liệu master msdb Các thủ tục này thường bắt đầu bằng tiền tố sp_ và đóng vai trò quan trọng trong việc hỗ trợ các DBA quản lý và bảo mật dữ liệu.
Thủ tục lưu trữ mở rộng cho phép người dùng tạo ra các quy trình bên ngoài bằng ngôn ngữ lập trình như C Những thủ tục này hoạt động như các thư viện liên kết động, được SQL Server tự động tải và khởi động khi cần thiết.
Thủ tục lưu trữ hệ thống
Các đặc điểm của từng loại thủ tục lưu trữ hệ thống được thống kê trong bảng sau:
Tên thủ tục Đặc điểm chi tiết
Stored Procedures Được sử dụng để quản lý các cấu hình Active Geo- Replication trong Cơ sở dữ liệu Azure SQL
Stored Procedures trong Catalog được sử dụng để triển khai các chức năng từ điển dữ liệu ODBC, giúp tách biệt các ứng dụng ODBC khỏi những thay đổi đối với các bảng hệ thống cơ bản.
Procedures Được sử dụng để bật, tắt hoặc báo cáo về các đối tượng chứa dữ liệu thay đổi
Cursor Stored Procedures Được sử dụng để thực hiện chức năng biến con trỏ
Procedures Được sử dụng để làm việc với bộ thu thập dữ liệu và các thành phần sau: bộ thu thập, mục thu thập và loại bộ sưu tập
Procedures Được sử dụng để bảo trì chung của SQL Server Database Engine
Procedure (Transact SQL) Được sử dụng để thực hiện các hoạt động email từ bên trong một phiên bản của SQL Server
Stored Procedures Được sử dụng để thiết lập các nhiệm vụ bảo trì cốt lõi được yêu cầu để quản lý hiệu suất cơ sở dữ liệu
Procedures Được sử dụng để thực hiện và quản lý các truy vấn phát tán
Stored Procedures Được sử dụng để cấu hình và quản lý các tính năng FileStream và FileTable
Procedures Được sử dụng để cấu hình Firewall của cơ sở dữ liệu Azure SQL
Procedures Được sử dụng để triển khai và truy vấn các chỉ mục toàn văn
Procedures Được sử dụng để định cấu hình, sửa đổi và giám sát các nhật ký cấu hình vận chuyển
Procedures Được sử dụng để định cấu hình, sửa đổi và giám sát các nhật ký cấu hình vận chuyển
Dùng để cấu hình kho dữ liệu quản lý
Dùng để kích hoạt các đối tượng tự động hóa tiêu chuẩn để sử dụng trong một lô Transact SQL tiêu chuẩn
Stored Produres Được sử dụng để quản lý dựa trên chính sách
PolyBase stored procedures Thêm hoặc xóa một mát tính khỏi nhóm PolyBase scale-out
Procedures Được sử dụng để quản lý sao chép
Security Stored Procedures Quản lý bảo mật
Xóa bản sao lưu file_snapshot cùng với tất cả ảnh chụp nhanh của nó hoặc để xóa ảnh chụp nhanh tệp sao lưu riêng lẻ
Procedures Được sử dụng để phân tích và cải thiện hiệu suất lập chỉ mục của các chỉ mục không gian
Theo dõi hiệu suất và hoạt động
Procedures Được sử dụng bở SQL Server Agent để quản ;ý các hoạt động theo lịch trình và theo hướng sự kiện
Procedures Được sử dụng để quản lý cơ sở dữ liệu mở rộng
Sử dụng cho bảng thời gian
XML Stored Procedures Được sử dụng để quản lý văn bản XML
Bảng 1: Thủ tục lưu trữ hệ thống
Thủ tục do người dùng định nghĩa
Module hóa: Bạn chỉ cần viết Stored Procedure 1 lần, sau đó có thể gọi nó nhiều lần ở trong ứng dụng
Thủ tục lưu trữ giúp cải thiện hiệu suất bằng cách thực thi mã nhanh hơn và giảm tải băng thông Khi được tạo ra, thủ tục lưu trữ sẽ được biên dịch và lưu vào bộ nhớ, cho phép thực thi nhanh hơn so với việc gửi từng đoạn lệnh SQL tới SQL Server Việc gửi nhiều đoạn lệnh sẽ yêu cầu SQL Server biên dịch lại nhiều lần, gây tốn thời gian so với việc đã được biên dịch sẵn.
Giảm tải bằng thông: Nếu bạn gửi nhiều câu lệnh SQL thông qua network đến SQL
Việc sử dụng Stored Procedure giúp tối ưu hiệu suất đường truyền bằng cách gom nhiều câu lệnh SQL thành một lần gọi duy nhất, thay vì gửi nhiều lần qua mạng.
Trong SQL Server, có những tác vụ cấp cao mà người dùng thông thường không thể truy cập Để giải quyết vấn đề này, việc sử dụng Stored Procedure là cần thiết, giúp người dùng thực hiện các thao tác mà không cần quyền truy cập trực tiếp vào cơ sở dữ liệu.
Người dùng thường có thể thực hiện 10 tác vụ này mà không gặp vấn đề gì Việc này cho phép họ truy cập gián tiếp mà không làm ảnh hưởng đến bảo mật của SQL Server.
4.2.1 Cú pháp CREATE {PROCEDURE|PROC}[tên_schema]
[{@tên_tham_số [type_schema_name]}
[VARYING||= default||OUT||OUTPUT||READONLY]]
[ WITH RECOMPILE|ENCRYPTION|EXECUTE AS Clause]
[ FOR REPLICATION|AS{[BEGIN] Câu_lệnh_SQL; [END] }
- Tên_schema: tên lược đồ chứa thủ tục, các thủ tục bị ràng buộc bởi lược đồ
- Tên_thủ_tục: tên của thủ cục, tuân thủ các quy tắc cho số nhận dạng và phải là duy nhất trong lược đồ
- @tên_tham_số: chỉ định tên tham só bằng cách sử dụng dấu @
- [type_schema_name] : kiểu dữ liệu của tham số và lược đồ chứa kiểu dữ liệu
- VARYING: tập kết quả được hỗ trợ làm tham số đầu ra
Giá trị mặc định là giá trị được chỉ định cho một tham số, cho phép thủ tục thực thi mà không cần người dùng nhập giá trị cho tham số đó.
- OUT | OUTPUT: chỉ ra tham số là một tham số đầu ra
- READONLY: chỉ ra rằng tham số không thể được cập nhật hoặc sửa đổi
- RECOMPILE: cho thủ tục biên dịch lại mỗi khi gọi
Mã hóa là một biện pháp bảo mật quan trọng, vì nếu người dùng không có quyền truy cập vào bảng hệ thống hoặc tệp cơ sở dữ liệu, họ sẽ không thể truy xuất nội dung của thủ tục.
- FOR REPLICATION: thủ tục được sao chép
- EXECUTE AS: ngữ cảnh bảo mật dể thực thi thủ tục được lưu trữ sau khi nó được truy cập
- [BEGIN] [END]: bao gồm các câu lệnh
Lệnh thực thi: EXECUTE | EXEC tên_thủ_tục [danh_sách_tham_số];
4.3 Sửa thủ tục Để sửa thủ tục lưu trữ ta sử dụng câu lệnh: ALTER PROCEDURE
[tên_schema][{@tên_tham_số[type_schema_name]}
[VARYING||= default||OUT||OUTPUT||READONLY]]
[ WITH RECOMPILE|ENCRYPTION|EXECUTE AS Clause]
[ FOR REPLICATION|AS{[BEGIN] Câu_lệnh_SQL; [END] }
4.4 Xóa thủ tục Để xóa thủ tục ta dùng câu lệnh sau: DROP[PROCEDURE|PROC]
DROP[PROCEDURE|PROC][tên_schema];
4.5.1 Tạo một thủ tục lưu trữ để chứa hàm truy vấn các sản phẩm trong bảng products của CSDL BikeStore
• uspProductList là tên của thủ tục lưu trữ
• Từ khóa AS ngăn cách tiêu đề và phần thân
• Nếu thủ tục lưu trữ có một câu lệnh, từ khoá BEGIN và END là tùy chọn
4.5.2 Thực thi thủ tục lưu trữ vừa tạo
Hình 1: Tạo thủ tục lưu trữ
4.5.3 Sửa đổi thủ tục lưu trữ
Thay đổi nội dung của thủ tục lưu trữ thành sắp xép các sản phẩm theo giá niêm yết thay vì tên sản phẩm
AS BEGIN SELECT product_name, list_price FROM production.products ORDER BY list_price END;
Hình 2: Thực thi thủ tục đã tạo
4.5.4 Xóa thủ tục lưu trữ
4.5.5 Tạo thủ tục lưu trữ có tham số
Thêm một tham số vào thủ tục lưu trữ để tìm các sản phẩm có giá niêm yết lớn hơn giá đầu vào:
@min_list_price AS DECIMAL )
Hình 3:Kết quả thủ tục vừa tạo
BEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price ORDER BY list_price;
Add a parameter named @min_list_price to the stored procedure, beginning with the @ symbol The keyword AS DECIMAL specifies the data type of the parameter.
• Sử dụng tham số @min_list_price trong mệnh đề WHERE của SELECT để lọc các sản phấm thỏa mãn điều kiện
4.5.6 Thực thi thủ tục lưu trữ có tham số Để thực thi chúng ta truyền cho thủ tục một đối số:
Hình 4: Kết quả thực thi thủ tục lưu trữ chứa tham số
4.5.7 Tạo thủ tục lưu trữ có nhiều tham số
Câu lệnh sau sửa đổi thủ tục lưu trữ uspFindProducts bằng các thêm một tham số được có thêm @max_list_price:
@min_list_price AS DECIMAL, @max_list_price AS DECIMAL )
AS BEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price AND list_price
3.2.3 Thực thi table function Để thực thi một table function ta sử dụng nó trong mệnh đề FROM vủa SELECT như sau:
Hình 9: Table Function trong Programmability
Ta sử dụng từ khóa ALTER như ví dụ sau:
SELECT product_name, model_year, list_price
Hình 10: Thực thi table function
WHERE model_year BETWEEN @start_year AND @end_year
3.2.5 Table function đa câu lệnh
Hàm table function là một loại hàm chứa nhiều câu lệnh và trả về giá trị dưới dạng bảng Hàm này rất hữu ích vì cho phép thực hiện nhiều truy vấn trong một hàm và tổng hợp kết quả trả về một cách hiệu quả.
RETURNS @contacts TABLE ( first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(255), phone VARCHAR(25), contact_type VARCHAR(20)
Hình 11:Kết quả thực thi câu lệnh sửa đổi
SELECT first_name, last_name, email, phone,
SELECT first_name, last_name, email, phone,
DROP FUNCTION [ IF EXISTS ] [ schema_name ] function_name; Trong đó:
- Tùy chọn IF EXISTS cho phép bạn xóa function nếu nó tồn tại
- Tùy chọn schema_name chỉ định tên của lược đồ mà function do người dùng tự định nghĩa thuộc về Tên lược đồ là tùy chọn
- Function_name là tên của function muốn xóa
Hình 12: Kết quả thực thi đa câu lệnh
