實作練習 : Stored Procedure + Stored Function + Trigger 的錯誤處理

前面實作練習使用了Trigger、Stored Procedure、Stored Function來完成訂單的處理，但是在處理的過程中沒有加入「錯誤處理」，也就是當發生錯誤時應該怎麼處理。

在資料庫操作中，錯誤處理是一個非常重要的部分，它確保當操作失敗或發生異常時，系統能夠優雅地應對並保護資料完整性和一致性。錯誤處理的主要目的是識別錯誤、記錄錯誤資訊、進行必要的補救措施（如Rollback），並將錯誤訊息適當地傳達給使用者或系統管理員。

錯誤處理的語法 : 

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 錯誤處理程序
-- 這裡加入需要的邏輯
END;

(1) Stored Procedure處理錯誤 : 

-- 建立一個資料表Products :

CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
Stock INT NOT NULL

);

-- 建立一個資料表ProductLogs :

CREATE TABLE ProductLogs (
LogID INT AUTO_INCREMENT PRIMARY KEY,
ActionDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Action VARCHAR(255)
);

再建立Stored Procedure : 

DELIMITER //
CREATE PROCEDURE CreateProduct(
IN p_Name VARCHAR(100),
IN p_Price DECIMAL(10, 2),
IN p_Stock INT

)

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 錯誤處理：回滾事務並記錄錯誤訊息
       ROLLBACK;
        -- 插入日誌
        INSERT INTO ProductLogs (Action) VALUES ('INSERT  Problem');
    END;

    -- 開始事務
    START TRANSACTION;
    -- 插入產品
    INSERT INTO Products (ProductName, Price, Stock) VALUES (p_Name, p_Price, p_Stock);
    -- 提交事務
    COMMIT;

END //
DELIMITER ;

呼叫 Call CreateProduct('Product d', 100.00, 50);

當INSERT INTO products 發生錯誤，就會進入錯誤處理
(例如把Products表格欄位改一下，故意讓他無法插入資料)

但是如果INSERT INTO productLogs又發生錯誤呢? 需要再修改如下 : 

DELIMITER //

CREATE PROCEDURE CreateProduct(
IN p_Name VARCHAR(100),
IN p_Price DECIMAL(10, 2),
IN p_Stock INT
)

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 二次錯誤處理：防止插入日誌失敗
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            -- 錯誤訊息的其他處理方法，比如記錄到一個系統日誌表，或是僅記錄一個簡單的錯誤訊息
            -- 這裡我們只是顯示錯誤訊息，不進行其他操作
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Both product insertion and log insertion failed.';
        END;
        -- 錯誤處理：回滾事務並記錄錯誤訊息
        ROLLBACK;
        -- 插入日誌
        INSERT INTO ProductLogs (Action) VALUES ('INSERT Problem');
    END;

    -- 開始事務
    START TRANSACTION;
    -- 插入產品
    INSERT INTO Products (ProductName, Price, Stock) VALUES (p_Name, p_Price, p_Stock);
    -- 提交事務
    COMMIT;
END //
DELIMITER ;

如果INSERT INTO productLogs又發生錯誤，就會跳出「Both product insertion and log insertion failed.」

同樣把ProductLogs表格欄位改一下，故意讓他無法插入資料

(2) Stored Function處理錯誤 : 

建立Stored Function : 

DELIMITER //

CREATE FUNCTION GetProductPrice(p_ProductID INT) RETURNS DECIMAL(10, 2)

BEGIN

    DECLARE v_Price DECIMAL(10, 2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    BEGIN

        -- 錯誤處理：返回-1表示錯誤
        INSERT INTO ProductLogs (Action) VALUES ('GetProductPrice Problem');

        RETURN -1;

    END;

    SELECT Price INTO v_Price FROM Products WHERE ProductID = p_ProductID;

    RETURN v_Price;

END //

DELIMITER ;

如果SELECT發生錯誤，就會進入錯誤處理。如果想處理二次錯誤，就仿照上面再改寫。

 

(3) Trigger 處理錯誤 :  

-- 建立測試的表格

CREATE TABLE IF NOT EXISTS my_table (
id INT AUTO_INCREMENT PRIMARY KEY,
column1 INT NOT NULL ); 

CREATE TABLE IF NOT EXISTS another_table (
id INT AUTO_INCREMENT PRIMARY KEY,
some_column INT NOT NULL
);

-- 建立Stored Procedure : 

DELIMITER //

CREATE PROCEDURE my_procedure(IN some_value INT)

BEGIN

    -- 假設要插入的值不能為負數，否則會觸發錯誤

    IF some_value < 0 THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative values are not allowed.';

    ELSE

        -- 執行一些操作，可能會產生錯誤

        INSERT INTO my_table (column1) VALUES (some_value);

    END IF;

END //

DELIMITER ;

-- 建立Trigger : 

DELIMITER //

CREATE TRIGGER my_trigger

BEFORE INSERT ON another_table

FOR EACH ROW

BEGIN

    -- 呼叫存儲程序並傳遞所需的參數

    CALL my_procedure(NEW.some_column);

END //

DELIMITER ;

-- 正常插入，不會觸發錯誤
INSERT INTO another_table (some_column) VALUES (10);

-- 插入負值，就會觸發錯誤
INSERT INTO another_table (some_column) VALUES (-5);

以上這些範例展示了如何在一個簡單的表格上進行儲存程序、儲存函數和觸發器的錯誤處理，以及使用PHP來調用這些功能。這樣可以確保在執行過程中，如果發生任何錯誤，系統能夠正確地處理並回滾所有改變，保持資料庫的一致性和穩定性。

在使用Trigger、Stored Procedure、Stored Function時，有些注意事項 : 

(1)存儲程序（Stored Procedure）： 

--可以呼叫其他的存儲程序。 

--可以呼叫存儲函數。 

--可以在觸發器內被呼叫。 

(2)存儲函數（Stored Function）： 

--可以呼叫其他的存儲函數。 

--不能呼叫存儲程序。 

--可以在觸發器內被呼叫。 

(3)觸發器（Trigger）： 

--可以呼叫存儲程序。 

--可以呼叫存儲函數。 

--不能包含或呼叫其他觸發器。

--不能使用Commit或是Rollback。

至於以上的「不能」，原因是什麼呢?

Stored Function基本上是為了返回數值，在取得數值的過程，再呼叫存儲函數去做些破壞性的動作，只會引發更複雜的問題；另外Stored Function在取得數值時，如果再呼叫Stored Procedure，只會讓效能降低，因此Stored Function不能再呼叫Stored Procedure是合理的。

同樣的，Trigger不能再引發其他Trigger，是為了避免無止盡的迴圈。Trigger不能使用Commit/Rollback，目的在於維持資料的一致性。