MySQL 儲存程序、函數、視圖完整實戰代碼庫：從入門到進階範例集

函數（Function）範例

標量函數：獲取部門名稱

DELIMITER $$
DROP FUNCTION IF EXISTS `f_GetDepartmentName` $$
CREATE FUNCTION `f_GetDepartmentName`(did INT) RETURNS VARCHAR(400)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE str VARCHAR(100) DEFAULT '';
    SELECT IFNULL(DepartmentName, '') INTO str 
    FROM DepartmentList WHERE DepartmentID = did;
    RETURN str;
END $$
DELIMITER ;

-- 測試調用
SELECT f_GetDepartmentName(1);

標量函數：獲取作者名稱（簡化寫法）

DELIMITER $$
CREATE FUNCTION `f_GetAuthorName`(did INT) RETURNS VARCHAR(400)
DETERMINISTIC
READS SQL DATA
BEGIN
    RETURN (SELECT IFNULL(AuthorName, '') FROM AuthorList WHERE AuthorID = did);
END $$
DELIMITER ;

視圖（View）範例

DELIMITER $$
DROP VIEW IF EXISTS `View_BookInfoList` $$
CREATE VIEW `View_BookInfoList` AS
SELECT 
    b.BookInfoID, b.BookInfoKind, b.BookInfoPlace, b.BookInfoSeries,
    b.BookInfoAuthor, b.BookInfoPress, b.BookInfoLanguage, b.BookInfoStatus,
    b.BookInfoOperatorId, b.BookInfoISBN, b.BookInfoBarCode, b.BookInfoName,
    b.BookInfoRemarks, b.BookInfoAddDate, b.BookInfoPublish, b.BookInfoPrice,
    k.BookKindName, p.BookPlaceName,
    f_GetAuthorName(b.BookInfoAuthor) AS AuthorName
FROM BookInfoList b
JOIN BookKindList k ON b.BookInfoKind = k.BookKindID
JOIN BookPlaceList p ON b.BookInfoPlace = p.BookPlaceID;
$$ DELIMITER ;

儲存程序（Procedure）完整 CRUD 模板

刪除操作

DELIMITER $$
DROP PROCEDURE IF EXISTS `DeleteBookKind` $$
CREATE PROCEDURE `DeleteBookKind`(IN param1 INT)
BEGIN
    DELETE FROM BookKindList WHERE BookKindID = param1;
END $$
DELIMITER ;

查詢所有

DELIMITER $$
CREATE PROCEDURE `proc_Select_BookKindListAll`()
BEGIN
    SELECT * FROM BookKindList;
END $$
DELIMITER ;

統計計數（OUT 參數）

DELIMITER $$
CREATE PROCEDURE `BookKindCount`(OUT param1ID INT)
BEGIN
    SELECT COUNT(*) INTO param1ID FROM BookKindList;
END $$
DELIMITER ;

-- 調用
CALL BookKindCount(@cnt);
SELECT @cnt;

條件更新（IF 邏輯）

DELIMITER $$
CREATE PROCEDURE `proc_Update_BookKindList`(
    IN param1ID INT, 
    IN param1Name NVARCHAR(1000), 
    IN param1Parent INT
)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM BookKindList WHERE BookKindName = param1Name) THEN
        UPDATE BookKindList 
        SET BookKindName = param1Name, BookKindParent = param1Parent 
        WHERE BookKindID = param1ID;
    ELSE
        UPDATE BookKindList 
        SET BookKindParent = param1Parent 
        WHERE BookKindID = param1ID;
    END IF;
END $$
DELIMITER ;

單條查詢

DELIMITER $$
CREATE PROCEDURE `proc_Select_BookKindList`(IN param1 INT)
BEGIN
    SELECT * FROM BookKindList WHERE BookKindID = param1;
END $$
DELIMITER ;

插入記錄

DELIMITER $$
CREATE PROCEDURE `proc_Insert_BookKindList`(
    IN param1Name NVARCHAR(1000), 
    IN param1Parent INT
)
BEGIN
    INSERT INTO BookKindList(BookKindName, BookKindParent) 
    VALUES(param1Name, param1Parent);
END $$
DELIMITER ;

插入並返回自增 ID（OUT 參數）

DELIMITER $$
CREATE PROCEDURE `proc_Insert_BookKindOut`(
    IN param1Name NVARCHAR(1000),
    IN param1Parent INT,
    OUT ID INT
)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM BookKindList WHERE BookKindName = param1Name) THEN
        INSERT INTO BookKindList (BookKindName, BookKindParent) 
        VALUES (param1Name, param1Parent);
        SELECT LAST_INSERT_ID() INTO ID;
    ELSE
        SET ID = 0; -- 或 -1 表示已存在
    END IF;
END $$
DELIMITER ;

最佳實踐總結

• DELIMITER $$：避免分號衝突，定義結束恢復 DELIMITER ;
• DETERMINISTIC/READS SQL DATA：函數聲明特性，助優化器決策
• 參數模式：IN 輸入、OUT 輸出、INOUT 雙向
• 錯誤處理：生產環境建議添加 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
• 避免業務邏輯過重：複雜計算移至應用層，存儲過程專注數據邏輯