Thủ tục lưu trữ SQL: Tự động hóa và Tối ưu hóa Truy vấn

Thủ tục lưu trữ (stored procedure) trong SQL là tập hợp các câu lệnh SQL được lưu và lưu trữ trong cơ sở dữ liệu. Chúng có thể được thực thi theo yêu cầu để thực hiện các tác vụ thao tác và kiểm tra dữ liệu, giúp giảm nhu cầu phải viết lặp lại mã SQL cho các thao tác phổ biến. Thủ tục lưu trữ hữu ích trong quản trị cơ sở dữ liệu nhờ thúc đẩy hiệu quả và khả năng tái sử dụng. Ngoài ra, chúng còn hỗ trợ tăng cường bảo mật và khả năng bảo trì cho cơ sở dữ liệu. Trong bài viết này, chúng ta sẽ bàn về cách tạo và thực thi thủ tục lưu trữ SQL, các trường hợp sử dụng phổ biến và những thực tiễn tốt nhất.

Khi bắt đầu, tôi rất khuyến khích bạn học các khóa của DataCamp: Giới thiệu về SQL và Học SQL để nắm kiến thức nền tảng về trích xuất và phân tích dữ liệu bằng SQL. Ngoài ra, Phiếu tóm tắt SQL Cơ bản mà bạn có thể tải về là tài liệu tham khảo hữu ích vì có đầy đủ các hàm SQL thông dụng nhất.

Thủ tục lưu trữ trong SQL là gì?

Thủ tục lưu trữ trong SQL là tập hợp các câu lệnh SQL được lưu và lưu trữ trong chính cơ sở dữ liệu. Mục đích của thủ tục lưu trữ là thực hiện một chuỗi thao tác trên cơ sở dữ liệu như truy vấn, chèn, cập nhật hoặc xóa dữ liệu.

Khác với các truy vấn SQL thông thường được thực thi như những lệnh riêng lẻ, thủ tục lưu trữ đóng gói một tập câu lệnh SQL, giúp dễ dàng tái sử dụng mã mà không phải viết đi viết lại các lệnh SQL.

Lợi ích của thủ tục lưu trữ SQL bao gồm:

• Khả năng tái sử dụng mã: Sau khi tạo, thủ tục lưu trữ có thể được gọi nhiều lần theo nhu cầu, loại bỏ sự dư thừa trong mã SQL.
• Hiệu năng được cải thiện: Thủ tục lưu trữ thường chạy nhanh hơn vì được biên dịch sẵn và lưu trên máy chủ cơ sở dữ liệu, giúp giảm độ trễ mạng và thời gian biên dịch.
• Bảo mật: Thủ tục lưu trữ có thể cải thiện bảo mật dữ liệu và kiểm soát truy cập dữ liệu nhạy cảm bằng cách cấp quyền cho người dùng thực thi thủ tục mà không cần truy cập trực tiếp vào bảng.

Cú pháp và cấu trúc cơ bản

Cú pháp tạo thủ tục lưu trữ có thể hơi khác nhau tùy hệ quản trị cơ sở dữ liệu (ví dụ: MySQL, SQL Server, Oracle). Dưới đây là ví dụ tổng quát dùng cú pháp SQL Server:

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;Được hỗ trợ bởi

Trong cú pháp trên:

• CREATE PROCEDURE: Lệnh dùng để định nghĩa một thủ tục lưu trữ mới.

• ProcedureName: Tên đặt cho thủ tục lưu trữ. Tên này phải là duy nhất trong cơ sở dữ liệu.

• @Parameter1, @Parameter2: Tham số là tùy chọn; chúng cho phép thủ tục nhận dữ liệu đầu vào. Mỗi tham số được định nghĩa với ký hiệu @ và kiểu dữ liệu (ví dụ: INT, VARCHAR(50)).

• AS BEGIN...END: Các câu lệnh SQL nằm trong BEGIN và END tạo thành phần thân của thủ tục, nơi logic chính được thực thi. Trong ví dụ này, thủ tục truy xuất bản ghi từ một bảng dựa trên các điều kiện cụ thể.

Tham số đầu vào và đầu ra

Các tham số đầu vào và đầu ra cho phép bạn truyền giá trị vào và ra khỏi một thủ tục lưu trữ.

Ví dụ, nếu @Parameter1 được định nghĩa là tham số đầu vào, nó có thể được gán bất kỳ giá trị nào khi gọi thủ tục, ảnh hưởng đến logic SQL hoặc kết quả đầu ra. Trong ví dụ dưới đây, tham số @UserID truy xuất dữ liệu cụ thể cho UserID được cung cấp.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;Được hỗ trợ bởi

Tham số đầu ra, được định nghĩa bằng từ khóa OUTPUT, cho phép thủ tục lưu trữ gửi một giá trị trở lại môi trường gọi. Chẳng hạn, nếu một thủ tục tính chiết khấu, nó có thể dùng tham số đầu ra để trả kết quả về cho chương trình gọi.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;Được hỗ trợ bởi

Để gọi thủ tục này, bạn sẽ dùng:

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;Được hỗ trợ bởi

Thủ tục lưu trữ trong MySQL

Như đã đề cập, việc thực thi thủ tục lưu trữ trong SQL có thể thực hiện theo các cách khác nhau tùy hệ quản trị cơ sở dữ liệu và công cụ sử dụng.

Tạo thủ tục lưu trữ trong MySQL

Tạo thủ tục lưu trữ trong MySQL bao gồm định nghĩa tên thủ tục, các tham số và các câu lệnh SQL cấu thành phần thân. Ví dụ sau tạo một thủ tục tên là GetEmployeeDetails nhận EmployeeID làm tham số đầu vào và truy xuất thông tin chi tiết cho nhân viên cụ thể đó.

DELIMITER $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;Được hỗ trợ bởi

Thực thi thủ tục lưu trữ trong MySQL

Cách phổ biến nhất để thực thi thủ tục lưu trữ là dùng các lệnh SQL. Trong MySQL, chúng ta dùng lệnh CALL để thực thi thủ tục lưu trữ.

CALL ProcedureName();Được hỗ trợ bởi

Với thủ tục GetEmployeeDetails đã định nghĩa, câu lệnh thực thi sẽ như sau:

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);Được hỗ trợ bởi

Thủ tục lưu trữ trong SQL Server

SQL Server cung cấp cú pháp và lệnh riêng để tạo, thực thi và quản lý thủ tục lưu trữ. Cách tiếp cận này giúp dễ dàng xây dựng các quy trình SQL hiệu quả và tái sử dụng, có thể xử lý tác vụ phức tạp với ít lặp lại.

Tạo thủ tục lưu trữ trong SQL Server

Tạo thủ tục lưu trữ trong SQL Server bao gồm định nghĩa tên thủ tục, các tham số và các câu lệnh SQL tạo nên phần thân. Ví dụ sau tạo thủ tục tên GetEmployeeDetails nhận @EmployeeID làm tham số đầu vào và truy xuất thông tin chi tiết cho nhân viên tương ứng.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;Được hỗ trợ bởi

Thực thi thủ tục lưu trữ trong SQL Server

Trong SQL Server, lệnh EXEC hoặc EXECUTE được dùng để gọi thủ tục lưu trữ. Ví dụ sau minh họa cách thực thi thủ tục GetEmployeeDetails với tham số đầu vào cụ thể. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 102
EXEC GetEmployeeDetails @EmployeeID = 102;Được hỗ trợ bởi

Bạn cũng có thể thực thi các tham số đầu ra bằng cách khai báo biến trong lệnh. Trong ví dụ sau, @TotalSales được khai báo là biến để nhận giá trị đầu ra từ CalculateTotalSales. 

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;Được hỗ trợ bởi

Tôi khuyên bạn học khóa Giới thiệu về SQL Server để hiểu các chức năng khác nhau của SQL Server phục vụ truy vấn dữ liệu. Ngoài ra, hãy cân nhắc lộ trình nghề nghiệp đầy đủ SQL Server Developer, không chỉ trang bị kỹ năng tạo, cập nhật và thực thi thủ tục lưu trữ mà còn hỗ trợ bạn về các hàm tổng hợp, join, chèn và xóa bảng, cùng nhiều nội dung khác.

Các trường hợp sử dụng phổ biến của Thủ tục lưu trữ

Thủ tục lưu trữ SQL hữu ích trong các tình huống cần lặp lại các tác vụ phức tạp. Dưới đây là những ứng dụng thực tế của thủ tục lưu trữ trong quản lý dữ liệu và hoạt động kinh doanh. 

Xác thực dữ liệu và đảm bảo tính toàn vẹn

Thủ tục lưu trữ có thể dùng để kiểm tra dữ liệu trước khi cập nhật hoặc chèn. Trong ví dụ dưới đây, một thủ tục lưu trữ kiểm tra email của khách hàng có là duy nhất trước khi chèn bản ghi mới vào bảng Customers, đảm bảo tính nhất quán dữ liệu. Cách này tập trung hóa logic kiểm tra trong cơ sở dữ liệu, giảm trùng lặp và đảm bảo áp dụng thống nhất trên các ứng dụng khác nhau.

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;Được hỗ trợ bởi

Xử lý dữ liệu và lập báo cáo tự động

Bạn cũng có thể dùng thủ tục lưu trữ để tạo báo cáo định kỳ hoặc xử lý các tập dữ liệu lớn. Ví dụ, một thủ tục có thể tổng hợp dữ liệu doanh số hằng ngày từ nền tảng thương mại điện tử và lưu vào bảng báo cáo, giúp các nhóm dễ dàng truy cập thông tin doanh số mà không cần chạy truy vấn phức tạp.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;Được hỗ trợ bởi

Quản lý giao dịch

Bằng cách sử dụng thủ tục lưu trữ, bạn có thể đảm bảo nhiều thao tác được thực hiện như một giao dịch duy nhất. Ví dụ, trong hệ thống ngân hàng, một thủ tục có thể xử lý cả hành động ghi nợ và ghi có trong việc chuyển tiền, đảm bảo cả hai cùng thành công hoặc cùng thất bại.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;Được hỗ trợ bởi

Kiểm soát truy cập và bảo mật dữ liệu

Bạn cũng có thể dùng thủ tục lưu trữ SQL để kiểm soát quyền truy cập dữ liệu nhạy cảm. Ví dụ, một thủ tục có thể hạn chế truy cập trực tiếp vào bảng bằng cách cho phép người dùng gọi thủ tục chỉ truy xuất các trường liên quan, như số dư tài khoản, mà không có chi tiết giao dịch.

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;Được hỗ trợ bởi

Di chuyển dữ liệu và quy trình ETL

Thủ tục lưu trữ cũng được dùng để nạp, biến đổi và di chuyển dữ liệu giữa các hệ thống. Một thủ tục có thể tự động trích xuất dữ liệu từ cơ sở dữ liệu nguồn, biến đổi theo nhu cầu và chèn vào bảng đích, đơn giản hóa tích hợp dữ liệu cho báo cáo hoặc phân tích.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extract
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transform
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Load
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;Được hỗ trợ bởi

Thực tiễn tốt nhất cho Thủ tục lưu trữ

Viết thủ tục lưu trữ hiệu quả và dễ bảo trì sẽ đảm bảo cơ sở dữ liệu của bạn hoạt động tối ưu. Dưới đây là các mẹo khi viết thủ tục lưu trữ cho cơ sở dữ liệu SQL của bạn.

• Dùng quy ước đặt tên nhất quán: Để thủ tục dễ nhận diện và hiểu, hãy dùng định dạng tên nhất quán và có tính mô tả. Đồng thời, tránh tiền tố sp_ trong SQL Server vì dành cho thủ tục hệ thống nhằm ngăn xung đột và vấn đề hiệu năng tiềm ẩn.

• Triển khai xử lý lỗi: Bao bọc các câu lệnh SQL trong khối TRY...CATCH để bắt và xử lý lỗi, đồng thời duy trì tính toàn vẹn dữ liệu. 

• Tối ưu cho hiệu năng: Hạn chế dùng con trỏ (cursor) vì chúng có thể chậm và tốn tài nguyên. Thay vào đó, hãy ưu tiên các thao tác theo tập hợp (set-based) thường hiệu quả hơn. Đồng thời, đánh chỉ mục cho các cột được dùng thường xuyên và tránh các phép join phức tạp trên bảng lớn để giảm tiêu tốn bộ nhớ và cải thiện hiệu quả.

• Tham số hóa thủ tục lưu trữ: Sử dụng tham số thay vì giá trị mã cứng để cho phép bạn truyền giá trị động vào thủ tục, giúp linh hoạt và tái sử dụng tốt hơn.

Hãy xem khóa SQL Trung cấp để tìm hiểu thêm về sử dụng các hàm tổng hợp và join để lọc dữ liệu. Đồng thời, thử các lộ trình kỹ năng Nền tảng SQL Server và Nền tảng SQL để cải thiện kỹ năng join bảng và phân tích dữ liệu.

Kết luận

Thủ tục lưu trữ SQL nâng cao khả năng tái sử dụng mã và tối ưu hóa hiệu năng trong quản trị cơ sở dữ liệu. Chúng cũng tăng cường bảo mật thông qua kiểm soát truy cập và đảm bảo tính toàn vẹn dữ liệu. Với vai trò là người làm dữ liệu, tôi khuyến khích bạn thực hành tạo và thực thi thủ tục lưu trữ để nắm vững các thực tiễn quản trị cơ sở dữ liệu tốt nhất.

Nếu bạn quan tâm trở thành một nhà phân tích dữ liệu thành thạo, hãy xem lộ trình nghề nghiệp Associate Data Analyst in SQL để học các kỹ năng cần thiết. Khóa học Lập báo cáo trong SQL cũng phù hợp nếu bạn muốn học cách xây dựng dashboard chuyên nghiệp bằng SQL. Cuối cùng, tôi khuyến nghị bạn lấy Chứng chỉ SQL Associate để chứng minh năng lực sử dụng SQL cho phân tích dữ liệu và nổi bật giữa các chuyên gia dữ liệu khác.