1.视图

a.

  CREATE  ALGORITHM = UNDEFINED  DEFINER = `root`@`localhost`   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.

  CREATE  ALGORITHM = UNDEFINED  DEFINER = `root`@`localhost`   SECURITY DEFINER  VIEW `sakila`.`staff_list` AS  SELECT  `s`.`staff_id` AS `ID`,  CONCAT(`s`.`first_name`,  _UTF8' ',  `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.

  CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)  READS  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.

  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  DATA  COMMENT 'Provides a customizable report on best customers'  proc: BEGIN  DECLARE last_month_start DATE;  DECLARE last_month_end DATE;  /* Some sanity checks... */  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;  /* Determine start and end time periods */  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 a temporary storage area for  Customer IDs.  */  CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);  /*  Find all customers meeting the  monthly purchase requirements  */  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;  /* Populate OUT parameter with count of found customers */  SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;  /*  Output ALL customer information of matching rewardees.  Customize output as needed.  */  SELECT c.*  FROM tmpCustomer AS t  INNER JOIN customer AS c ON t.customer_id = c.customer_id;  /* Clean up */  DROP TABLE tmpCustomer;  END

3.函数

a.

  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  #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE  #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:  # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS  # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE  # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST  # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED  DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY  DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS  DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY  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;  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;  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.

  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;  #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE  #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED  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

以上所述是小编给大家介绍的MySQL 5.7 create VIEW or FUNCTION or PROCEDURE,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对沃谷博客网站的支持!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注