MySQL 觸發器與存儲過程實戰：建立、參數、呼叫、角谷猜想演示

觸發器建立語法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
    -- 多條 SQL 用 BEGIN...END 包裹
    SQL_stmt1;
    SQL_stmt2;
END;

檢視：SHOW TRIGGERS

存儲過程基礎：建立、呼叫、刪除

-- 無參數
DELIMITER $$
CREATE PROCEDURE productpricing()
BEGIN
    SELECT AVG(prod_price) AS priceaverage FROM products;
END$$
DELIMITER ;

CALL productpricing();

-- 刪除
DROP PROCEDURE productpricing;

OUT 參數：從過程返回值

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT MIN(prod_price) INTO pl FROM products;
    SELECT MAX(prod_price) INTO ph FROM products;
    SELECT AVG(prod_price) INTO pa FROM products;
END;

CALL productpricing(@low, @high, @avg);
SELECT @low, @high, @avg;

IN/OUT 混合：訂單總額計算

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT SUM(item_price * quantity) INTO ototal
    FROM orderitems WHERE order_num = onumber;
END;

CALL ordertotal(20005, @total);
SELECT @total;

動態分頁存儲過程（完整範例）

DELIMITER $$
CREATE PROCEDURE get_page(
    IN tableName VARCHAR(100),
    IN fieldsNames VARCHAR(100),
    IN pageIndex INT,
    IN pageSize INT,
    IN sortName VARCHAR(500),
    IN strWhere VARCHAR(500)
)
BEGIN
    DECLARE fieldlist VARCHAR(200);
    SET fieldlist = IF(fieldsNames='' OR fieldsNames IS NULL, '*', fieldsNames);

    SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName);
    IF strWhere != '' AND strWhere IS NOT NULL THEN
        SET @strSQL = CONCAT(@strSQL, ' WHERE ', strWhere);
    END IF;
    IF sortName != '' AND sortName IS NOT NULL THEN
        SET @strSQL = CONCAT(@strSQL, ' ORDER BY ', sortName);
    END IF;
    SET @strSQL = CONCAT(@strSQL, ' LIMIT ', (pageIndex-1)*pageSize, ',', pageSize);

    PREPARE stmt1 FROM @strSQL;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

進階：角谷猜想遞迴演示（WHILE/LOOP/ITERATE/LEAVE）

CREATE PROCEDURE jgguess(IN number INT)
BEGIN
    DECLARE param1 INT DEFAULT 1;
    SET @a = CONCAT(number);

    jiaogu: LOOP
        SET param1 = number % 2;
        IF param1 = 1 THEN
            SET number = number * 3 + 1;
        ELSE
            SET number = number / 2;
        END IF;
        SET @a = CONCAT(@a, '->', number);
        IF number > 1 THEN
            ITERATE jiaogu;
        ELSE
            LEAVE jiaogu;
        END IF;
    END LOOP jiaogu;
END;

CALL jgguess(9);
SELECT @a;  -- 9->28->14->7->22->11->34->17->52->26->13->40->20->10->5->16->8->4->2->1

存儲函數/過程管理指令

SHOW CREATE PROCEDURE proc_name;
SHOW CREATE FUNCTION func_name;
DROP PROCEDURE [IF EXISTS] proc_name;
DROP FUNCTION [IF EXISTS] func_name;
ALTER PROCEDURE proc_name ...;  -- 僅限修改特性，不可改邏輯