mysql自定义函数
1,简单函数
需求:编写函数,传入一个film_id,返回电影名(title)
delimiter $$
create function get_film_title(id int) returns varchar(64)
reads sql data
begin
declare film_title varchar(64) default "";
select title into film_title from film where film_id= id;
return film_title;
end;
$$
delimiter ;
select get_film_title(28);
归纳:
创建函数使用create function funName (params) returns returnType;
函数体放在begin和end之间
return指定函数的返回值
函数调用:select get_film_title(params);
2,自定义函数实例1:
需求:输入id,查出电影的名字以及长度,租金。
delimiter $$
create function sel_film_info(id int) returns varchar(128)
reads sql data
begin
declare film_info varchar(128) default "";
select concat(title,'-',length,'-',replacement_cost) into film_info from film where film_id=id;
return film_info;
end;
$$
delimiter ;
select sel_film_info(28);
3,自定义函数示例2
需求:输入语言id,查询该语言下语言name和电影总数
delimiter $$
create function sel_count_film_language(id int)returns varchar(128)
reads sql data
begin
declare retu varchar(32) default "";
select concat(count(*),'-',name) into retu from film left join language l on l.language_id=film.language_id where film.language_id = id;
return retu;
end;
$$
delimiter ;
select sel_count_film_language(2)