MySQL 5.7 create VIEW or FUNCTION or PROCEDURE
2022-11-12 09:35:54
内容摘要
这篇文章主要为大家详细介绍了MySQL 5.7 create VIEW or FUNCTION or PROCEDURE,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
1.视图
文章正文
这篇文章主要为大家详细介绍了MySQL 5.7 create VIEW or FUNCTION or PROCEDURE,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
1.视图
a.
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <code> 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`</code> |
b.
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | <code> CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL 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`)))</code> |
2.存储过程
a.
代码如下:
1 2 3 4 5 6 7 8 9 10 11 | <code> 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 </code> |
b.
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | <code> 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 ; /* 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 </code> |
3.函数
a.
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | <code> 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 </code> |
b.
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <code> 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 </code> |
以上所述是小编给大家介绍的MySQL 5.7 create VIEW or FUNCTION or PROCEDURE,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对512笔记网站的支持!
注:关于MySQL 5.7 create VIEW or FUNCTION or PROCEDURE的内容就先介绍到这里,更多相关文章的可以留意
代码注释