MySQL系列（八）函数的创建和使用

（1）会话变量等基本操作的使用
① 使用查询结果给变量赋值；

set @sname= (select sname from student where studentno = '19101020201');
select studentno,sname,birthdate from student where sname = @sname;

② 利用select语句将表中数据赋值给变量;

select @sname:=sname from student limit 0,1;

③ 利用select语句输出表达式；

select 1 + 7 + 3, '2018-01-20' + interval 6 month;

④ 改变MySQL命令的结束标记；

delimiter //
select studentno,sname,phone from student where sname like '赵%'//

第一行代码，delimiter定义好结束符为“//”，最后一行又将结束符改为原来的“;”。

⑤ 利用预处理sql语句输出course表中的前5条记录的部分数据；

set @a = 5;
prepare STMT
from "select courseno,cname,type, period from course limit ?";
execute STMT using @a;

（2）自定义函数的创建和调用。
① 创建一个名为f_course的函数返回表course中的指定课程号的课程名；

delimiter &&
create function f_course(c_no varchar(6))
returns char(6)
begin return (select cname from course where courseno =c_no);
end&& 

② 调用函数f_course；

select f_course('c05109');

③ 查看指定数据库（如teaching）中的所有自定义函数名；

select name from mysql.proc where db = 'info1' and type ='function';

由于Mysql8.0以上版本已经没有mysql.proc这个表，故在此只展示相关查询代码。

④ 查看指定函数f_course的信息；

select * from information_schema.routines where routine_name='f_course';

⑤ 修改储存函数f_course的定义，将读写权限改为reads sql data，并加上注释信息“find function name”；

alter function f_course
reads sql data comment 'find function name';

⑥ 练习利用MySQL Workbench工具创建、修改和删除过程函数。
a. 打开 MySQL Workbench 软件，在菜单栏中单击“新建自定义函数”按钮，即可新建自定义函数，如下图所示。

b. 在创建自定义函数对话框中，设置函数的名称和函数的方法体，单击 Apply 按钮，即可成功创建自定义函数，如下图所示。

（3）条件语句的应用
① 删除表course中指定课程号在score中的记录；

delimiter $$
create function delete_cno (c_no char(6))
returns char(6)
begin declare c_name char(6);
select cname into c_name from course where courseno= c_no;
if c_name is null then
delete from score where courseno = c_no;
return 'yes';
else
return ‘no’;
end if;
end $$

② 创建函数ex_case，通过case语句首先判断传入参数的至是否为100，如果条件成立则输出1，如果条件不成立则再判断该传入参数的值是否为200，如果成立则输出2，如果条件不成立则再判断该传入参数的值是否为300，如果成立则输出3，当以上条件都不成立则输出0；

delimiter //
create function ex_case( x int)
returns int
begin
case x
when 100 then set x=1;
when 200 then set x=2;
when 300 then set x=3;
else set x=0;
end case;
return x;
end //
select ex_case(200);

（4）循环语句的应用
① 定义函数ex_while，应用while语句求50~100的和；

delimiter //
create function ex_while(n int) returns int
begin
declare sum int default 0;
declare m int default 50;
while m <= n do
set sum=sum+m;
set m=m+1;
end while;
return sum;
end //
delimiter ;
select ex_while(100);

② 定义函数ex_loop，应用loop语句求20~70的和。通过leave语句退出循环并输出结果；

delimiter //
create function ex_loop(n int) returns int
begin
declare sum int default 0;
declare m int default 20;
loop_label:loop
set sum=sum+m;
set m=m+1;
if m>n then
leave loop_label;
end if;
end loop;
return sum;
end //
delimiter ;
select ex_loop(70);