MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

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

以上所述是小编给大家介绍的mysql 5.7 create view or function or procedure，希望对大家有所帮助