MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

?

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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204

1.视图 a. 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`   b.   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`)))   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 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   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 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   3.函数 a. 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   b. 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