MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

				?

									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`

				?

									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`)))

				?

									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

				?

									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

				?

									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

				?

									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

相关文章

热门资讯