SQL 儲存程序：自動化並優化查詢

教學
SQL

SQL 儲存程序是保存並存儲在資料庫中的一組 SQL 語句。它們可以根據需要執行，以執行資料操作和驗證任務，減少為常見操作編寫重複 SQL 代碼的需求。儲存程序在資料庫管理中有助於提高效率和重用性。此外，它們還支持增強的資料庫安全性和可維護性。在本文中，我們將討論如何創建和執行 SQL 儲存程序、常見的使用案例和最佳實踐。

在開始之前，我強烈建議參加DataCamp的SQL入門和學習SQL課程，以學習使用SQL提取和分析數據的基礎知識。另外，SQL基礎知識速查表，你可以下載，這是一個有用的參考資料，因為它包含了所有最常見的SQL函數。

什麼是SQL中的存儲過程？

在SQL中，存儲過程是一組SQL語句，保存在數據庫中。SQL存儲過程的目的是在數據庫上執行一系列操作，比如查詢、插入、更新或刪除數據。

與常規的 SQL 查詢不同，存儲過程封裝了一組 SQL 語句，使得重用代碼變得簡單，而無需重複編寫 SQL 命令。

SQL 存儲過程的好處包括：

  • 代碼可重用性：一旦創建了存儲過程，它可以被調用無數次，消除了 SQL 代碼中的冗餘。
  • 性能提升：存儲過程的執行速度通常更快，因為它們是預編譯的並存儲在數據庫伺服器上，減少了網絡延遲和編譯時間。
  • 安全性:儲存過程可以通過授予用戶執行儲存過程的權限而不直接訪問表格，從而提高數據安全性並控制對敏感數據的訪問。

基本語法和結構

創建儲存過程的語法可能略有不同，具體取決於數據庫系統（例如MySQL、SQL Server、Oracle）。以下是一個使用SQL Server語法的通用示例:

-- 創建一個名為ProcedureName的儲存過程
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL語句放在這裡
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

在上面的語法中;

  • 創建存儲過程: 此命令用於定義新的存儲過程。

  • 過程名稱: 存儲過程的名稱。它應該在數據庫中是唯一的。

  • @參數1, @參數2: 參數是可選的；它們允許該程序接收數據輸入。每個參數都用一個 @ 符號和數據類型來定義（例如，INT，VARCHAR(50)）。

  • 當 BEGIN…END: 在 BEGIN 和 END 內的 SQL 語句形成程序的主體，在這裡執行主邏輯。該程序根據特定條件從表中檢索記錄。

輸入和輸出參數

輸入和輸出參數允許您向存儲過程傳遞值並從中獲取值。

例如，如果 @Parameter1 被定義為輸入參數，則在調用過程時可以為其分配任何值，這會影響 SQL 邏輯或輸出。在下面的示例中，@UserID 參數檢索與提供的 UserID 相關的數據。

-- 創建一個過程以通過 UserID 獲取特定用戶的數據
CREATE PROCEDURE GetUserData 
 -- 輸入參數：要檢索的用戶 ID
    @UserID INT 
AS
BEGIN
    -- 從 Users 中選擇所有列，條件是 UserID 與輸入參數匹配
    SELECT * FROM Users WHERE UserID = @UserID;
END;

輸出參數由 OUTPUT 關鍵字定義，允許存儲過程將值發送回調用環境。例如，如果一個過程計算折扣，它可以使用輸出參數將結果傳回調用程序。

-- 創建一個過程以計算折扣後的價格
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- 輸出：折扣後的最終價格
AS
BEGIN
    -- 通過將折扣率應用於原始價格來計算最終價格
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

要呼叫此程序，您可以使用：

-- 宣告一個變數以儲存折扣後的最終價格
DECLARE @FinalPrice DECIMAL(10, 2);
-- 使用價格為 100 和 10% 的折扣執行 CalculateDiscount 程序
-- 將輸出儲存在 @FinalPrice 變數中
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- 選擇並顯示最終折扣價格
SELECT @FinalPrice AS FinalPrice;

MySQL中的儲存程序

如我所提到的，執行 SQL 中的儲存程序可以根據資料庫系統和使用的工具以不同方式進行。

在 MySQL 中創建儲存程序

在 MySQL 中創建儲存程序涉及定義程序的名稱、參數以及構成其主體的 SQL 語句。以下範例創建了一個名為 GetEmployeeDetails 的程序，該程序將 EmployeeID 作為輸入參數，並檢索該特定員工的詳細資訊。

DELIMITER $
-- 創建一個程序以通過 EmployeeID 檢索特定員工的詳細信息
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- 從 Employees 中選擇所有列，其中 EmployeeID 與輸入參數匹配
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

在 MySQL 中執行儲存過程

執行儲存過程最常見的方法是使用 SQL 命令。在 MySQL 中，我們使用 CALL 命令來執行儲存過程。

CALL ProcedureName();

使用定義的 GetEmployeeDetails 程序，執行查詢將如下所示：

-- 執行儲存過程以檢索 EmployeeID 101 的詳細信息
CALL GetEmployeeDetails(101);

SQL Server 中的儲存過程

SQL Server 提供了特定的語法和命令來創建、執行和管理儲存過程。這種方法使得建立高效且可重用的 SQL 程序變得更容易，能夠以最小的重複處理複雜任務。

在 SQL Server 中創建儲存過程

在 SQL Server 中創建儲存過程涉及定義過程的名稱、參數和構成其主體的 SQL 語句。以下示例創建了一個名為 GetEmployeeDetails 的過程，該過程接受 @EmployeeID 作為輸入參數，並檢索該特定員工的詳細信息。

-- 創建一個過程以通過 EmployeeID 檢索特定員工的詳細信息
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- 輸入參數：要檢索的員工 ID
AS
BEGIN
    -- 從 Employees 中選擇所有列，條件是 EmployeeID 與輸入參數匹配
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

在 SQL Server 中執行儲存過程

在 SQL Server 中，EXEC 或 EXECUTE 命令調用儲存過程。以下示例顯示如何使用特定輸入參數執行儲存過程 GetEmployeeDetails。

-- 使用 EmployeeID 設置為 102 來執行 GetEmployeeDetails 過程
EXEC GetEmployeeDetails @EmployeeID = 102;

您也可以通過在命令中聲明變量來執行輸出參數。在以下示例中，@TotalSales 被聲明為一個變量，用於接收 CalculateTotalSales 的輸出。

-- 聲明一個變量來存儲總銷售金額
DECLARE @TotalSales DECIMAL(10, 2);
-- 對 SalespersonID 為 5 執行 CalculateTotalSales，將結果存儲在 @TotalSales 中
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- 顯示總銷售金額
SELECT @TotalSales AS TotalSales;

我建議參加我們的SQL Server入門課程，以了解SQL Server用於查詢數據的不同功能。同時，考慮參加我們完整的SQL Server開發人員職業培訓課程，這將不僅使您具備創建、更新和執行存儲過程的技能，還將幫助您掌握聚合函數、連接、插入和刪除表格等更多技能。

存儲過程的常見用途

SQL存儲過程在需要重複複雜任務的情況下非常有用。以下是存儲過程在數據管理和業務運營中的真實應用。

數據驗證和完整性執行

存儲過程可用於在更新或插入數據之前驗證數據。在下面的示例中，存儲過程在將新記錄插入Customers表之前檢查客戶的電子郵件是否唯一，從而確保數據一致性。這將驗證邏輯集中在數據庫內，減少冗余並確保在不同應用程序中統一執行。

-- 創建一個添加新客戶的過程，檢查重複電子郵件
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- 檢查電子郵件是否已存在於Customers表中
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- 如果電子郵件已在使用中則拋出錯誤
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- 如果電子郵件是唯一的，則插入新客戶詳細信息
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

自動數據處理和報告

您也可以使用儲存過程來生成定期報告或處理大型數據集。例如，儲存過程可以整合電子商務平台的每日銷售數據並將其存儲在報告表中，使各團隊能夠更輕鬆地訪問銷售洞察，而無需運行複雜的查詢。

-- 創建一個程序以生成每日銷售報告
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- 將今天的日期和總銷售額插入到 SalesReport 表中
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- 從 Sales 表中選擇當前日期和今天的銷售總和
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

事務管理

通過使用儲存過程，您可以確保多個操作作為單一事務執行。例如，在銀行系統中，儲存過程可以同時處理資金轉移中的借記和貸記操作，確保這兩個操作要麼一起成功，要麼一起失敗。

-- 創建一個程序以在帳戶之間轉移資金
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- 開始一個事務以確保原子性
    -- 從發送者的帳戶餘額中扣除指定金額
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- 將指定金額添加到接收者的帳戶餘額
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- 檢查錯誤，如果發生錯誤則回滾；否則，提交事務
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- 如果發生錯誤，撤銷所有更改
    ELSE
        COMMIT TRANSACTION;    -- 如果沒有錯誤，確認更改
END;

訪問控制和數據安全

您還可以使用SQL存儲過程來控制對敏感信息的數據訪問。例如，存儲過程可以限制直接訪問表格，允許用戶調用僅檢索相關字段（如帳戶餘額）的過程，而不包含交易詳情。

-- 創建一個程序以檢索帳戶餘額，並進行授權檢查
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- 檢查該帳戶是否存在，並且是否屬於指定用戶
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- 如果授權，選擇並返回帳戶餘額
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- 如果未授權，拋出錯誤
        THROW 50000, 'Unauthorized access.', 1;
END;

數據遷移和ETL過程

存儲過程也用於在系統之間加載、轉換和遷移數據。存儲過程可以自動執行從源數據庫提取數據，根據需要對其進行轉換，並將其插入到目標表中，簡化數據集成，以供報告或分析使用。

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- 提取
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- 轉換
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- 加載
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

存儲過程的最佳實踐

編寫高效且易於維護的存儲過程可確保您的數據庫性能最佳。以下是為您的SQL數據庫編寫存儲過程的建議。

  • 使用一致的命名规范：为了使存储过程易于识别和理解，请使用一致且描述性强的命名格式。此外，避免在 SQL Server 中使用以 sp_ 为前缀的命名，因为该前缀通常用于系统存储过程，以避免潜在冲突和性能问题。

  • 實現錯誤處理: 將 SQL 語句包裹在 TRY...CATCH 塊中，以捕獲和處理錯誤並保持數據完整性。

  • 優化性能: 減少使用游標，因為它們可能會變慢且耗費資源。儘量使用基於集合的操作，這通常更有效率。同時，對常用列進行索引，避免在大表中進行複雜的連接，以減少內存開銷並提高效率。

  • 參數化存儲過程：使用參數而不是硬編碼的值，以允許您將動態值傳遞給您的過程，使其更靈活和可重複使用。

查看我們的中級 SQL課程，了解如何使用聚合函數和連接來過濾數據。同時，試試我們的SQL Server 基礎和SQL 基礎技能軌道，提升您的表連接和數據分析技能。

結論

SQL 存儲過程增強了數據庫管理中的代碼重用性和性能優化。存儲過程還通過控制訪問和確保數據完整性增強了數據庫安全性。作為數據從業者，我鼓勵您練習創建和執行存儲過程，掌握最佳的數據庫管理實踐。

如果您有興趣成為一名精通的數據分析師，請查看我們的 SQL 資料分析助理 職業路徑，以學習必要的技能。如果您想學習如何使用 SQL 建立專業儀表板，SQL 報告 課程也非常合適。最後，我建議獲得 SQL 資料分析助理認證 以展示您在數據分析中使用 SQL 的精通程度，並在其他數據專業人員中脫穎而出。

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure