(九)mysql中的存储过程和自定义函数

概述

存储过程和自定义函数是事先经过编译并存储在数据库中的一段SQL语句的集合。相对普通查询优点：

• 可以简化应用开发人员的工作。
• 减少数据库与应用服务器之间的数据传输。
• 提高了数据处理的效率。

存储过程与函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用in(输入)，out(输出)，inout(输入输出)，而函数的参数只能是in类型。

创建、修改、调用 存储过程或函数

#创建存储过程
create procedure sp_name([proc_paramenter[,...]])
    [characteristic ...] routine_body

#proc_parameter: [in|out|inout] param_name type
#type:任何可用的MySQL数据类型
#routine_body:是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。
___________________________________________________
#创建函数
create function sp_name([func_parameter[,...]])
    return type
    [characteristic ...]routine_body

___________________________________________________
#修改存储过程或函数
alter {procedure|function} sp_name [characteristic ...]

___________________________________________________
#调用过程的语句
call sp_name([parameter[,...]]);

___________________________________________________
#删除存储过程或函数
drop {procedure|function} [if exists] sp_name;

___________________________________________________
#查看存储过程或函数状态
show {procedure|function} status like 'sp_name';

___________________________________________________
#查看存储过程或函数的定义
show create {procedure|function} sp_name;

其中characteristic的取值为：

MySQL的存储过程或函数中允许包含DDL语句，也允许存储过程中执行事务处理。存储过程和函数中可以调用其他的过程或函数。

存储过程及函数基本使用举例

举例说明：

delimiter //

create procedure procedure_test(
        in v_min int,in v_max int,out num int)
reads sql data
begin

#查询出test1表中sid在v_min 和v_max之间的记录
select * from test1
where sid > v_min and sid < v_max;

#将返回的记录行数写入num变量中输出
select found_rows() into num;

end //

delimiter ;

注意：“DELIMITER //”语句的作用是将MYSQL的结束符设置为$$，因为MYSQL默认的语句结束符为分号;，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END //”结束存储过程。

存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER 也可以指定其他符号为结束符
创建的存储过程及test1数据

调用存储过程极其结果

输出变量

删除存储过程

查看存储过程

查看存储过程定义

使用变量举例

变量的作用范围只能在begin…end块中，可以用在嵌套中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。
变量定义赋值-参考链接

#创建一个函数返回v_min、v_max之间的sid之和。
delimiter //
create function function_test
    (v_min int,v_max int)
returns int
reads sql data
begin
declare temp int;
select sum(sid) into @temp
from test1
where sid > v_min and sid < v_max;
return @temp
end //

delimiter ;

创建结果：

调用函数执行结果

使用条件定义、处理

待补充

使用光标

待补充

使用流程控制

待补充