MySQL 5.7 create VIEW or FUNCTION or PROCEDURE, mysqlprocedure

MySQL 5.7 create VIEW or FUNCTION or PROCEDURE, mysqlprocedure

1. View

A.

CREATEALGORITHM = UNDEFINEDDEFINER = `root`@`localhost`SQL SECURITY INVOKERVIEW `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`,': ',(SELECTGROUP_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.

CREATEALGORITHM = UNDEFINEDDEFINER = `root`@`localhost`SQL SECURITY DEFINERVIEW `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. Stored Procedure

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 DATABEGINSELECT 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;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 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;END

3. Functions

A.

CREATE DEFINER=`root`@`localhost` FUNCTION `get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2)READS SQL DATADETERMINISTICBEGIN#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;END

B.

CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)READS SQL DATABEGINDECLARE 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

The above is a small series of MySQL 5.7 create VIEW or FUNCTION or PROCEDURE, I hope to help you, if you have any questions, please leave a message, the small series will reply to you in a timely manner. Thank you very much for your support for the help House website!