MySQL存储过程与存储函数

一、存储过程

1、存储过程的定义和特点

定义：

事先经过编译并存储在数据库中的一段SQL语句集合，是一种数据库对象。它可以接受参数、返回参数值，可以被用户调用。

执行存储过程，就是执行存储过程中预定义的SQL脚本，由于事先经过编译，所以执行速度更快。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

优点：

• 封装、复用
• 可以接收参数，也可以返回数据
• 降低了客户机与服务器之间的通信量，减少网络交互，效率提升

应用系统经常需要执行的操作，一次操作涉及到多个子操作，建议为此操作创建存储过程

2、存储过程的基本语法

1.创建存储过程

create procedure 存储过程名([参数列表])

begin

        --SQL STATEMENT

end;

2.调用存储过程

call 存储过程名([参数]);

3.查看存储过程

查询指定数据库的存储过程及状态信息：

select * from information_schema.routines where routine_schema='数据库';

查询指定存储过程的定义：

show create procudure 存储过程名;

4.删除存储过程

drop procedure [if exists] 存储过程名;

3、存储过程的参数

类型	含义
IN	该类参数作为输入，需要调用时传入值，默认是输入参数
OUT	该类参数作为输出，可以作为返回值
INOUT	该类参数既可作为输入参数，又可作为输出参数

用法：

create procedure 存储过程名([in/out/inout 参数名 参数类型])

begin

--SQL STATEMENT

end;

(1)定义根据学号查询学生各科成绩的存储过程

create procedure p_GetScoreBySno(in sno char(8))
begin
select s.Sno,s.Sname, c.Cno,c.Cname,sc.Grade
from s join sc on s.Sno=sc.Sno join c on sc.Cno=c.Cno
where s.Sno=sno;
end;
call p_GetScoreBySno('20220105');

(2)根据传入参数score，判断当前分数的等级，并返回：

create procedure p_param(in score int, out result varchar(10))
begin 
    if score>=85 then set result:='优秀';
    elseif score>=60 then set result:='及格';
    else set result:='不及格';
    end if;
end;
call p_param(80,@result);
select @result;

(3)将输入的百分制分数进行换算，换算成八分制，然后返回分数，如90分转为7.2

create procedure p_inout(inout score decimal(4,1))
begin 
set score=score*0.08;
end;
set @score=60;
call p_inout(@score);
select @score;

(4)设计一个存储过程p_avg，输出sc表中每个学生的平均成绩 

create procedure p_avg()
begin
		select Sno,avg(Grade) from sc group by Sno;
end;
call p_avg();

(5)设计一个存储过程p_grade，查询并显示sc表中课程编号为’003’的学生的学号、分数信息

create procedure p_grade()
begin 
		select Sno,Grade from sc where Cno='003';
end
call p_grade();

(6)设计一个存储过程，查询某学生修读的总学分。学号为输入参数，总学分为输出参数，编写程序并调用该存储过程

create procedure p_mojar(in in_Sno char,out out_credit tinyint)
begin
		select Sno,sum(Ccredit) from sc join c on sc.Cno=c.Cno;
		set out_credit=sum(Ccredit);
end
call p_mojar('20220101', @out_credit);

(7)查看存储过程的定义

show create procedure p_mojar;

(8)设计一个存储过程，用于修改指定学生指定课程的分数，这三项信息通过输入参数指定，并输出该生的平均成绩，要求输出格式为：“该生的最新平均成绩为：”+grade。编写并调用该存储过程(修改学号’20220104’的学生的修读课程‘003’的成绩改为96)。

create procedure p_updateAndSelect(in in_sno char(8),in_cno char(3), in_grade int, out 
out_result char(20))
begin
  declare avg_score float;
  update sc set grade=in_grade where sno=in_sno and cno=in_cno;
  select avg(grade) into avg_score from sc where sno=in_sno;
   set out_result=concat('该生的最新平均成绩为：',cast(avg_score as char(10)));
end;
call p_updateAndSelect('20220101','003',80,@result);
select @result;

(9)设计一个存储过程p_credit完成这样的功能：输入学号、课程名称，通过输出参数获取该学生该课程的成绩和学分，如果分数大于等于60，则返回对应课程的学分，否则返回学分值0，请编写程序并调用该存储过程。

create procedure p_credit(in in_sno char(8),in_cno varchar(20),out out_grade int,out out_credit int)
begin
		select sc.grade,c.ccredit into out_grade,out_credit
		from sc join c on sc.cno=c.cno
		where sc.sno=in_sno and c.cname=in_cno;
		if out_grade<60 then set out_credit=0;
		end if;
end;
call p_credit('20220103','数据库系统',@grade,@credit);
select * from sc where sno='20220103';
select @grade,@credit;

思考：能用视图完成该存储过程的功能吗？

答：由于视图不能带参数，索引不能通过视图实现此功能

二、游标

1、游标的定义

游标(Curcor）是用来存储查询结果集的数据类型，再存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、打开、获取游标记录和关闭

2、游标的基本语法

1.声明游标：declare 游标名称 cursor for 查询语句;

2.打开游标：open 游标名称;

3.获取游标记录：fetch 游标名称 into 变量[,变量];

4.关闭游标：close 游标名称;

(1)查询并显示课程名中包含指定内容的课程信息

create procedure p_cursor(in str varchar(10))
begin
    declare cno char(4);
    declare cname varchar(20);
    --声明游标
    declare c_cursor cursor
        for select * from c where cname like concat('%',str,'%');
    --打开游标
    open c_cursor;
        while true do
            --获取游标记录
            fetch c_cursor into cno,cname;
            select cno,cname;
        end while;
    --关闭游标
    close c_cursor;
end;

 (2) 设计一个存储过程p_score，查询某门课程某个分数段的学生信息，其中课程名称和分数段均为输入参数，显示学号、姓名、分数，要求使用游标获取查询结果集，请编写程序并调用该存储过程。 

create procedure p_score(in_c varchar(20),in_s int,in_e int)
begin
		declare sno char(8);
		declare sname char(8);
		declare grade int;
		declare sc_cursor cursor for
			select s.sno,s.sname,sc.grade 
			from sc join c on sc.cno=c.cno join s on s.sno=sc.sno
			where c.cname=in_c and (sc.grade>=in_s and sc.grade<=in_e);
	  declare exit handler for not found close sc_cursor;
		open sc_cursor;
		while true do
			fetch sc_cursor into sno,sname,grade;
			select sno,sname,grade;
		end while;
		close sc_cursor;
end;
call p_score('数据库系统',60,100);

三、存储函数

1、存储函数的定义和特点

定义：存储函数是有返回值的存储过程，存储函数的参数只能是in类型的

2、存储函数的基本语法

创建：

create function 存储函数名([参数列表])

returns 类型 [characteristic...]

begin

        --SQL STATEMENT

        return ...;

end;

characteristic说明：

• deterministic：相同的输入参数总是产生相同的结果
• no sql：不包含sql语句
• reads sql data：包含读取数据的语句，但不包含写入数据的语句

 （1）实现从1到n的累加值

create function fun(n int)
returns int deterministic
begin
    declare result int default 0;
    while n>0 do
        set result=result+n;
        set n=n-1;
    end while;
    return result;
end;
fun(100);

 (2) 设计一个存储函数fun_avg，根据课程名称查询课程的平均成绩。课程名称为输入参数，函数返回平均分，编写程序并调用该存储函数。 

create function fun_avg(in_cname varchar(20))
returns float deterministic
begin
  declare avg_score float;
  select avg(Grade) into avg_score from sc join c on sc.cno=c.cno and cname=in_cname;
  return avg_score;
end;
select fun_avg('数据库系统');

四、知识点 

（1）存储过程的优点有哪些？

① 减少网络通信量：调用一个行数不多的存储过程与直接调用MySQL语句的网络通信量少

② 执行速度更快：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化，其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用

③ 更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况夏对数据库进行任何改动，而这些改动不会对应用程序造成影响

④ 分布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制

⑤ 事先经过编译并存储在数据库中的一段SQL语句集合，是一种数据库对象，它可以接受参数，返回参数，可以被用户调用

（2）存储函数与存储过程的不同点有哪些。

①存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值

②存储函数只能由输入参数，而且不能带in，而存储过程可以有多个in,out,inout参数

③存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert、update、delect、create、等语句

④存储函数只完成查询工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强

⑤存储过程可以调用存储函数、但函数不能调用存储过程

⑥存储过程一般是作为一个独立的部分来执行（CALL调用），而函数可以作为查询语句的一部分来调用

（3）什么情况下使用游标？

游标是用来查询结果集的数据结构，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、打开、获取游标记录和关闭。用在存储过程需要读取select语句返回多行数据的情况