MySQL 自定義函數 CREATE FUNCTION 完整範例與進階用法

基本語法結構

CREATE FUNCTION function_name (param1 TYPE, param2 TYPE, ...)
RETURNS return_type
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA]
BEGIN
    -- 函數邏輯
    RETURN value;
END

實戰範例：字串替換函數

DELIMITER $$

CREATE FUNCTION myReplace(
    in_string VARCHAR(255),
    in_find_str VARCHAR(20),
    in_repl_str VARCHAR(20)
) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE l_new_string VARCHAR(255);
    DECLARE l_find_pos INT;

    SET l_find_pos = INSTR(in_string, in_find_str);

    IF (l_find_pos > 0) THEN
        SET l_new_string = INSERT(in_string, l_find_pos, LENGTH(in_find_str), in_repl_str);
    ELSE
        SET l_new_string = in_string;
    END IF;

    RETURN l_new_string;
END$$

DELIMITER ;

-- 測試
SELECT myReplace('ABC', 'A', 'Z');  -- 回傳 'ZBC'

-- 移除函數
DROP FUNCTION myReplace;

關鍵點解析

• DELIMITER：暫時變更語句結束符，避免函數體內 ; 被誤判。
• DETERMINISTIC：相同輸入必得相同輸出，允許優化器快取結果。
• 變數宣告：DECLARE var TYPE [DEFAULT value]; 僅能在 BEGIN...END 區塊開頭。
• 流程控制：支援 IF...THEN...ELSE...END IF、CASE、LOOP/WHILE/REPEAT。

進階：需回傳多值時改用 Stored Procedure

CREATE PROCEDURE get_stats(
    IN p_product_id INT,
    OUT p_total_stock INT,
    OUT p_avg_price DECIMAL(10,2)
)
BEGIN
    SELECT SUM(stock), AVG(price)
    INTO p_total_stock, p_avg_price
    FROM product_stock
    WHERE product_id = p_product_id;
END;

-- 呼叫
CALL get_stats(1001, @total, @avg);
SELECT @total, @avg;

函數只能回傳單一標量值，需回傳多值或結果集請改用預存程序（OUT 參數）或直接 SELECT。

簡易函數範例

CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
RETURN 'Hello World!';

SELECT HelloWorld();  -- Hello World!