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

MySQL 5.7 視圖、存儲過程、函數、觸發器與事件建立完整範例

編輯：MySQL 綜合教學

MySQL 5.7 視圖、存儲過程、函數、觸發器與事件建立完整範例

本文匯總 MySQL 5.7 官方範例資料庫 sakila 中的進階物件建立語法，涵蓋視圖、存儲過程、自訂函數、觸發器與事件排程器，供開發者參考學習。

1. 視圖

範例 A：actor_info - 彙總演員資訊與電影分類

CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY INVOKER 
VIEW `sakila`.`actor_info` AS
SELECT 
  `a`.`actor_id` AS `actor_id`,
  `a`.`first_name` AS `first_name`,
  `a`.`last_name` AS `last_name`,
  GROUP_CONCAT(DISTINCT CONCAT(`c`.`name`, ': ', 
    (SELECT GROUP_CONCAT(`f`.`title` ORDER BY `f`.`title` ASC SEPARATOR ', ')
     FROM ((`sakila`.`film` `f` 
     JOIN `sakila`.`film_category` `fc` ON `f`.`film_id` = `fc`.`film_id`)
     JOIN `sakila`.`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 (((`sakila`.`actor` `a`
LEFT JOIN `sakila`.`film_actor` `fa` ON `a`.`actor_id` = `fa`.`actor_id`)
LEFT JOIN `sakila`.`film_category` `fc` ON `fa`.`film_id` = `fc`.`film_id`)
LEFT JOIN `sakila`.`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` AS `address`,
  `a`.`postal_code` AS `zip code`,
  `a`.`phone` AS `phone`,
  `sakila`.`city`.`city` AS `city`,
  `sakila`.`country`.`country` AS `country`,
  `s`.`store_id` AS `SID`
FROM (((`sakila`.`staff` `s`
JOIN `sakila`.`address` `a` ON `s`.`address_id` = `a`.`address_id`)
JOIN `sakila`.`city` ON `a`.`city_id` = `sakila`.`city`.`city_id`)
JOIN `sakila`.`country` ON `sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`);

2. 存儲過程

範例 A：film_in_stock - 查詢庫存電影

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;
  DECLARE 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 AS 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 AS t
  INNER JOIN customer AS c ON t.customer_id = c.customer_id;

  DROP TABLE tmpCustomer;
END

3. 自訂函數

範例 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);
  DECLARE v_overfees INTEGER;
  DECLARE v_payments DECIMAL(5,2);

  -- 1. 租金費用
  SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
  FROM film, inventory, rental
  WHERE film.film_id = inventory.film_id
    AND inventory.inventory_id = rental.inventory_id
    AND rental.rental_date <= p_effective_date
    AND rental.customer_id = p_customer_id;

  -- 2. 逾期費用
  SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
      (TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration, 0)),0) INTO v_overfees
  FROM rental, inventory, film
  WHERE film.film_id = inventory.film_id
    AND inventory.inventory_id = rental.inventory_id
    AND rental.rental_date <= p_effective_date
    AND rental.customer_id = p_customer_id;

  -- 3. 已付款項
  SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
  FROM payment
  WHERE payment.payment_date <= p_effective_date
    AND payment.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 LEFT JOIN rental USING(inventory_id)
  WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

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