MySQL 5.7 create VIEW or FUNCTION or PROCEDURE
1.视图 a. CREATEALGORITHM = UNDEFINEDDEFINER = root@localhostSQL SECURITY INVOKERVIEW sakila.actor_info ASSELECTa.actor_id AS actor_id,a.first_name AS first_name,a.last_name AS last_name,GROUP_CONCAT(DISTINCT CON
<p>1.视图</p>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
以上所述是小编给大家介绍的MySQL 5.7 create VIEW or FUNCTION or PROCEDURE，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对每日运维网站的支持！
