1.視圖 a. CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY INVOKER VIEW `sakila`.`actor_info` ASSELECT `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` ASCSEPARATOR ', ')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` ASCSEPARATOR '; ') 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` SQL SECURITY DEFINER VIEW `sakila`.`staff_list` ASSELECT `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 SQL DATA BEGINSELECT inventory_idFROM inventoryWHERE film_id = p_film_idAND store_id = p_store_idAND inventory_in_stock(inventory_id);SELECT FOUND_ROWS() INTO p_film_count; ENDb. 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 DATACOMMENT 'Provides a customizable report on best customers' proc: BEGINDECLARE last_month_start DATE;DECLARE last_month_end DATE;/* Some sanity checks... */IF min_monthly_purchases = 0 THENSELECT 'Minimum monthly purchases parameter must be > 0';LEAVE proc;END IF;IF min_dollar_amount_purchased = 0.00 THENSELECT '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 forCustomer IDs.*/CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);/*Find all customers meeting themonthly purchase requirements*/INSERT INTO tmpCustomer (customer_id)SELECT p.customer_idFROM payment AS pWHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_endGROUP BY customer_idHAVING SUM(p.amount) > min_dollar_amount_purchasedAND 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 tINNER JOIN customer AS c ON t.customer_id = c.customer_id;/* Clean up */DROP TABLE tmpCustomer; END3.函數(shù) a. CREATE DEFINER=`root`@`localhost` FUNCTION `get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2)READS SQL DATADETERMINISTIC 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 SPECIFIEDDECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLYDECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALSDECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLYSELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfeesFROM film, inventory, rentalWHERE film.film_id = inventory.film_idAND inventory.inventory_id = rental.inventory_idAND rental.rental_date <= p_effective_dateAND 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_overfeesFROM rental, inventory, filmWHERE film.film_id = inventory.film_idAND inventory.inventory_id = rental.inventory_idAND rental.rental_date <= p_effective_dateAND rental.customer_id = p_customer_id;SELECT IFNULL(SUM(payment.amount),0) INTO v_paymentsFROM paymentWHERE payment.payment_date <= p_effective_dateAND payment.customer_id = p_customer_id;RETURN v_rentfees + v_overfees - v_payments; ENDb. CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)READS SQL DATA BEGINDECLARE 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 POPULATEDSELECT COUNT(*) INTO v_rentalsFROM rentalWHERE inventory_id = p_inventory_id;IF v_rentals = 0 THENRETURN TRUE;END IF;SELECT COUNT(rental_id) INTO v_outFROM inventory LEFT JOIN rental USING(inventory_id)WHERE inventory.inventory_id = p_inventory_idAND rental.return_date IS NULL;IF v_out > 0 THENRETURN FALSE;ELSERETURN TRUE;END IF; END

　　

轉(zhuǎn)載于:https://www.cnblogs.com/geovindu/p/5950101.html

《新程序員》：云原生和全面數(shù)字化實踐50位技術專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的MySQL 5.7 create VIEW or FUNCTION or PROCEDURE的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。