Mysql数据库基本知识五：函数

======================MYSQL 编程-函数===================
1，【定义函数语法】：
create function 函数名（参数）  
returns 返回值的数据类型
函数选项
begin
函数体;
return 语句;
end

【函数选项说明】：
no sql：表示函数体中不包含SQL语句。
reads sql data：表示函数体中包含select查询语句，但不包含更新语句。
modifies sql data：增删改。（又有更新又有查询）

*参数属于局部变量。定义参数：名字 数据类型
除参数以外的变量，用declare定义。

【例1】创建一个名为row_no_fn()的函数，该函数的功能是为查询结果集添加行号。
create function row_no_fn()
returns int
no sql
begin 
declare rh int;
set @rh=@rh+1;
return @rh;
end;

set @rh=0;
select row_no_fn(),sid,sname,sgender
from student;

【例2】创建一个名为get_name_fn()的函数，该函数的功能是为根据学生学号查询学生姓名。
create function get_name_fn1(xh char(4))
returns varchar(10)
reads sql data
begin
declare xm varchar(10);
select sname into xm
from student
where sid=xh;
return xm;
end;

select get_name_fn1('s001')

【例3】创建一个名为get_choose_number_fn()的函数，该函数的功能是为根据学生学号
返回该生选修了几门课程。

create function get_choose_number_fn(xh char(4))
returns int
reads sql data
begin
declare kcs int;
select count(cid) into kcs
from register
where sid=xh;
return kcs;
end;

select get_choose_number_fn('s002');

注意：函数不能直接返回结果集，需要用变量来返回。
当函数实现查询功能时，函数体分为一下几步：
1，定义变量装结果。
2，通过参数查询的结果，把结果赋给变量。
3，通过变量返回结果。

在Mysql中，函数只能返回一个值。
否则会显示：Result consisted of more than one row

2，【修改函数】：
先删除，再建立。
drop function 函数名
create function 函数名（[参数]）  

3，【条件控制语句】
--------------------------------------------------------------------------
一个分支：
if    then
end    if;

两个分支：
if    then
else
end    if；

三个分支：
if    then
elseif    then
else
end    if

四个分支：
if    then
elseif    then
elseif    then
else
end    if

例：【判断角色】
create function get_name_fn (no int,role char(20)) returns char(20)
reads sql data
begin
    declare name char(20);
    if(‘student'=role) then
        select sname into name 
                          from student where sid=no;
    elseif('teacher'=role) then
        select tname into name 
                           from teacher where tid=no;
    else set name='输入有误！';
    end if;
    return name;
end;
-------------------------------------------------------------------------------
【case语句】：
case 表达式
when value1 then  语句块1;
when value2 then  语句块2;
…
else 语句块n;
end case;

【查询中的case与程序中的case，有哪些区别？】
程序中为：case ...  end case;
查询中为：case ... end;

程序中then后为语句块，需要加分号；
查询中then后为常量，不需要加分号

例：根据输入的参数，判断返回是周几。
create function get_week_fn(week_no  int)
 returns char(20)
no sql
begin
    declare week char(20);
    case week_no
    when 0 then set week = '星期一';
    when 1 then set week = '星期二';
    when 2 then set week = '星期三';
    when 3 then set week = '星期四';
    when 4 then set week = '星期五';
    else set week = '今天休息';
    end case;
    return week;
end;
--------------------------------------------------------------------------------
MySQL提供了三种循环语句，分别是while、repeat以及loop。

退出循环
java：break
mysql：leave
结束本次循环：
java：continue
mysql：iterate

1．【while语句】
while 条件表达式 do
循环体;
end while [循环标签];

2．【leave语句】leave语句用于结束循环语句（例如while语句）。

leave 循环标签;

3．【iterate语句】iterate语句用于跳出本次循环，继而进行下次循环。

iterate 循环标签;

4．repeat语句
当条件表达式的值为false时，反复执行循环，直到条件表达式的值为true。
repeat语句的语法格式如下：

[循环标签:]repeat 
循环体;
until 条件表达式
end repeat [循环标签];

5．loop语句的语法格式
由于loop循环语句本身没有停止循环的语句，
因此loop通常使用leave语句跳出loop循环，loop的语法格式如下。

[循环标签:] loop
循环体;
if 条件表达式 then 
leave [循环标签]; 
end if;
end loop;

【例】创建函数teacherinfo2有两个参数：teacher id和type。
根据编号(teacher id)来查询teacher表中的记录。
如果type的值是1时，则返回教师姓名；如果type的值是2时，则返回教师年龄；
如果type为其他值，则返回字符串“Error”。

create function teacherinfo2(jsh char(20),type int) 
returns char(20)
reads sql data
begin
declare fhz char(20);
if(type=1) then
select Teachername into fhz 
from teacher 
where TeacherID=jsh;
elseif(type=2) then
select year(now())-year(Brith) into fhz 
from teacher 
where TeacherID=jsh;
else set fhz='Error';
end if;
return fhz;
end;

注意：局部变量名字不能与字段名相同。

显示所有函数：show function status;
查看指定函数的详细信息：show create function 函数名;

============================存储过程==========================
【定义过程语法】：
create procedure 存储过程名（参数1，参数2，…）
[存储过程选项]
begin
存储过程语句块;
end;

过程的定义语法：
1, 指定过程名与参数。
2, 指定选项：no sql/reads sql data/modifies sql data。 
3, 在语句块中定义过程体。

语法上，存储过程与函数的区别：
1，返回值个数：
函数：只返回一个值
过程：可以返回一个或多个或不返回值
2，返回形式：
函数用returns指明返回类型，用return返回结果，存储过程都不需要。
存储过程通过参数（out，inout）返回结果或直接返回结果集。
3，参数的区别：
函数：参数不分类型
过程：参数分为 in out  inout三种
4，调用的区别：
函数：select
过程：call
5，使用的区别：
函数：可以直接嵌入SQL语句（例如select语句）
过程：不可以SQL语句，需要用call单独调用。
6，函数中限制比较多，过程限制比较少一点。

【参数语法】：参数类型 参数名 数据类型
in（输入参数）外界向过程
out（出入参数）过程向外界
inout（输入输出参数）两个方向都可以
【例】：in xh char(4)
注意：函数无参数类型。

【例】定义一个存储过程get_student_course_proc(),实现根据学生的学号，
统计该生已经选修的课程情况，包括课程号，课程名。

create procedure get_student_course_proc(in xh char(4),out kch char(3),out kcm char(20))
reads sql data
begin
select c.cid,c.cname into kch,kcm
from course c join register r on c.cid=r.cid
where sid=xh;
end;

【调用过程】：call
1, 通过参数显示结果。
set @kcm='JAVA程序设计';
set @pjf=0;
call p_AvgGrade2(@kcm,@pjf);
select @pjf;

结论：通过参数返回结果时，只能返回单行结果***********。

2,直接显示结果集：into 变量名 删掉，是直接返回结果集。
call 过程名('输入的参数');

【删除存储过程】：drop procedure 过程名;
【显示所有过程】：show procedure sataus;