你的位置:首页 > 数据库

[数据库]MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

1.视图a.CREATE   ALGORITHM = UNDEFINED   DEFINER = `root`@`localhost`   SQL SECURITY INVOKERVIEW `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`   SQL SECURITY DEFINERVIEW `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 SQL DATABEGIN   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;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 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;END3.函数a.CREATE DEFINER=`root`@`localhost` FUNCTION `get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2)  READS SQL DATA  DETERMINISTICBEGIN    #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;ENDb.CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)  READS SQL DATABEGIN  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