MySQL 5.7 建立 View、Function、Procedure 完整語法範例

MySQL 5.7 支援視圖、預存程序、函數三大程式化物件。本文採用官方範例資料庫 sakila 的實際定義碼，展示完整建立語法與關鍵子句說明，供開發參考。

一、視圖

範例 A：actor_info（複雜多表聯結與字串彙總）

CREATE ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY INVOKER
VIEW `sakila`.`actor_info` AS
SELECT a.actor_id, a.first_name, a.last_name,
       GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
           (SELECT GROUP_CONCAT(f.title ORDER BY f.title ASC SEPARATOR ', ')
            FROM film f
            JOIN film_category fc ON f.film_id = fc.film_id
            JOIN film_actor fa ON f.film_id = fa.film_id
            WHERE fc.category_id = c.category_id AND fa.actor_id = a.actor_id)
           ORDER BY c.name ASC SEPARATOR '; ')
       ) AS film_info
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
LEFT JOIN film_category fc ON fa.film_id = fc.film_id
LEFT JOIN category c ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name;

範例 B：staff_list（多表聯結簡單投影）

CREATE ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `sakila`.`staff_list` AS
SELECT s.staff_id AS ID,
       CONCAT(s.first_name, ' ', s.last_name) AS name,
       a.address, a.postal_code AS `zip code`, a.phone,
       c.city, co.country, s.store_id AS SID
FROM staff s
JOIN address a ON s.address_id = a.address_id
JOIN city c ON a.city_id = c.city_id
JOIN country co ON c.country_id = co.country_id;

關鍵子句說明

• ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}：指定視圖演算法，影響效能。
• DEFINER：指定建立者帳號，影響權限檢查。
• SQL SECURITY {DEFINER | INVOKER}：執行時權限檢查依據。

二、預存程序

範例 A：film_in_stock（簡單查詢與 OUT 參數）

CREATE DEFINER=`root`@`localhost`
PROCEDURE `film_in_stock`(
    IN p_film_id INT,
    IN p_store_id INT,
    OUT p_film_count INT
)
READS SQL DATA
BEGIN
    SELECT inventory_id FROM inventory
    WHERE film_id = p_film_id AND store_id = p_store_id
      AND inventory_in_stock(inventory_id);
    SELECT FOUND_ROWS() INTO p_film_count;
END

範例 B：rewards_report（完整業務邏輯：變數宣告、暫存表、條件判斷、游標替代方案）

CREATE DEFINER=`root`@`localhost`
PROCEDURE `rewards_report`(
    IN min_monthly_purchases TINYINT UNSIGNED,
    IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED,
    OUT count_rewardees INT
)
READS SQL DATA
COMMENT 'Provides a customizable report on best customers'
proc: BEGIN
    DECLARE last_month_start DATE, last_month_end DATE;

    -- 參數驗證
    IF min_monthly_purchases = 0 THEN SELECT 'Minimum monthly purchases parameter must be > 0'; LEAVE proc; END IF;
    IF min_dollar_amount_purchased = 0.00 THEN SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00'; LEAVE proc; END IF;

    -- 計算上個月日期範圍
    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
    SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
    SET last_month_end = LAST_DAY(last_month_start);

    -- 建立暫存表儲存符合條件顧客
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id FROM payment p
    WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased AND COUNT(customer_id) > min_monthly_purchases;

    SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    SELECT c.* FROM tmpCustomer t INNER JOIN customer c ON t.customer_id = c.customer_id;

    DROP TABLE tmpCustomer;
END

三、函數

範例 A：get_customer_balance（複雜計算：租金、逾期費、付款扣除）

CREATE DEFINER=`root`@`localhost`
FUNCTION `get_customer_balance`(p_customer_id INT, p_effective_date DATETIME)
RETURNS DECIMAL(5,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_rentfees DECIMAL(5,2) DEFAULT 0;
    DECLARE v_overfees INT DEFAULT 0;
    DECLARE v_payments DECIMAL(5,2) DEFAULT 0;

    SELECT IFNULL(SUM(f.rental_rate),0) INTO v_rentfees
    FROM film f JOIN inventory i ON f.film_id = i.film_id
    JOIN rental r ON i.inventory_id = r.inventory_id
    WHERE r.rental_date <= p_effective_date AND r.customer_id = p_customer_id;

    SELECT IFNULL(SUM(IF((TO_DAYS(r.return_date)-TO_DAYS(r.rental_date)) > f.rental_duration,
                         (TO_DAYS(r.return_date)-TO_DAYS(r.rental_date)) - f.rental_duration, 0)),0) INTO v_overfees
    FROM rental r JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id
    WHERE r.rental_date <= p_effective_date AND r.customer_id = p_customer_id;

    SELECT IFNULL(SUM(p.amount),0) INTO v_payments
    FROM payment p WHERE p.payment_date <= p_effective_date AND p.customer_id = p_customer_id;

    RETURN v_rentfees + v_overfees - v_payments;
END

範例 B：inventory_in_stock（布林邏輯判斷庫存狀態）

CREATE DEFINER=`root`@`localhost`
FUNCTION `inventory_in_stock`(p_inventory_id INT)
RETURNS TINYINT(1)
READS SQL DATA
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out INT;

    SELECT COUNT(*) INTO v_rentals FROM rental WHERE inventory_id = p_inventory_id;
    IF v_rentals = 0 THEN RETURN TRUE; END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory i LEFT JOIN rental r ON i.inventory_id = r.inventory_id
    WHERE i.inventory_id = p_inventory_id AND r.return_date IS NULL;

    IF v_out > 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF;
END

四、關鍵字區別速查

以上範例均取自 MySQL 官方 sakila 範例資料庫，實務開發時請依據業務需求調整邏輯與權限設定。