您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 視圖、函數與存儲過程完整實戰範例

編輯：MySQL 綜合教學

MySQL 視圖、函數與存儲過程完整實戰範例

本文提供一個完整的圖書管理系統資料庫腳本，涵蓋建表、自定義函數、視圖創建、存儲過程（增刪改查、統計）等核心對象，適合作為 MySQL 進階開發的參考範本。

一、資料庫與核心表結構

CREATE DATABASE Liber;
USE Liber;

-- 圖書分類表
CREATE TABLE BookKindList (
  BookKindID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  BookKindName NVARCHAR(500) NOT NULL,
  BookKindParent INT NULL
);

-- 圖書位置表
CREATE TABLE BookPlaceList (
  BookPlaceID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  BookPlaceName NVARCHAR(500) NOT NULL,
  BookPlaceParent INT NULL
);

-- 圖書系列表
CREATE TABLE BookSeriesList (
  BookSeriesID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  BookSeriesName NVARCHAR(500) NOT NULL
);

-- 職位、部門、語種、出版社、作者、圖書狀態、借閱狀態等字典表...
-- (略，完整腳本包含 DepartmentList, LanguageList, PressList, AuthorList, BookStatusList, LendStatusList)

-- 圖書主信息表
CREATE TABLE BookInfoList (
  BookInfoID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  BookInfoKind INT NOT NULL,
  BookInfoPlace INT NOT NULL,
  BookInfoISBN VARCHAR(50) NOT NULL,
  BookInfoBarCode VARCHAR(60) NOT NULL,
  BookInfoName NVARCHAR(500) NOT NULL,
  BookInfoSeries INT DEFAULT 1,
  BookInfoAuthor INT NULL,
  BookInfoPress INT NULL,
  BookInfoLanguage INT NULL,
  BookInfoPublish DATETIME,
  BookInfoImage TEXT NULL,
  BookInfoStatus INT DEFAULT 1 NOT NULL,
  BookInfoRemarks TEXT NULL,
  BookInfoOperatorId INT NULL,
  BookInfoAddDate DATETIME NOT NULL,
  BookInfoPrice FLOAT DEFAULT 1.00,
  BookUseCode VARCHAR(100)
);

二、自定義函數：封裝常用查詢邏輯

1. 獲取部門名稱

DELIMITER $$
DROP FUNCTION IF EXISTS `f_GetDepartmentName` $$
CREATE FUNCTION `f_GetDepartmentName` (did INT) RETURNS VARCHAR(100)
BEGIN
  RETURN (SELECT DepartmentName FROM DepartmentList WHERE DepartmentID = did);
END $$
DELIMITER ;

2. 獲取作者名稱

DELIMITER $$
DROP FUNCTION IF EXISTS `f_GetAuthorName` $$
CREATE FUNCTION `f_GetAuthorName` (did INT) RETURNS VARCHAR(400)
BEGIN
  RETURN (SELECT AuthorName FROM AuthorList WHERE AuthorID = did);
END $$
DELIMITER ;

調用示例：SELECT f_GetDepartmentName(1); SELECT f_GetAuthorName(1);

三、視圖：簡化多表關聯查詢

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

視圖將主表與字典表關聯，並嵌入函數調用，應用層可直接 SELECT * FROM View_BookInfoList 獲取豐富信息。

四、存儲過程：標準化 CRUD 與業務邏輯

採用 DELIMITER $$ ... $$ DELIMITER ; 定義分隔符，參數模式：IN（輸入）、OUT（輸出）、INOUT（雙向）。

1. 刪除分類

CREATE PROCEDURE `DeleteBookKind` (IN param1 INT)
BEGIN
  DELETE FROM BookKindList WHERE BookKindID = param1;
END

2. 查詢所有分類

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

3. 統計分類數量

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

4. 更新分類（防重名）

CREATE PROCEDURE `proc_Update_BookKindList` (
  IN param1ID INT, IN param1Name NVARCHAR(1000), IN param1Parent INT
)
BEGIN
  IF NOT EXISTS (SELECT * 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

5. 查詢單條分類

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

6. 新增分類

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

7. 新增分類並返回自增 ID

CREATE PROCEDURE `proc_Insert_BookKindOut` (
  IN param1Name NVARCHAR(1000), IN param1Parent INT, OUT ID INT
)
BEGIN
  IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName = param1Name) THEN
    INSERT INTO BookKindList (BookKindName, BookKindParent) VALUES (param1Name, param1Parent);
    SELECT LAST_INSERT_ID() INTO ID;
  END IF;
END