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

2,669 阅读5分钟

概述

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

  • 可以简化应用开发人员的工作,可重用。
  • 减少数据库与应用服务器之间的数据传输。
  • 提高了数据处理的效率。
  • 安全性提高。由于存储过程也可以使用权限控制,而且参数化的存储过程可以防止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的取值为:

说明
language sql 说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL
[not] deterministic 指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。
{contains sql \no sql \ reads sql data \modifies sql data} 指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句,但是不包含读写数据的语句;NO SQL表明子程序不包含SQL语句;READS SQL DATA:说明子程序包含读数据的语句;MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL。
sql_security{definer\invoker} 指明谁有权限来执行。DEFINER 表示只有定义者才能执行;INVOKER 表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER。
comment 'string' 注释信息,可以用来描述存储过程或函数

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块中,可以用在嵌套中。变量的定义必须写在复合语句的开头,并且在任何其他语句的前面。可以一次声明多个相同类型的变量。
(10)mysql中的变量-参考链接

#创建一个函数返回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 ;

创建结果:

创建函数

调用函数执行结果
调用自定义函数

使用条件定义、处理

条件定义和处理可以用来定义在处理过程中遇到问题(错误,警告,异常)时的处理步骤。条件处理程序定义参见链接如下:
(11)mysql中的条件定义、处理-参考链接

1. 未使用条件定义处理程序

delimiter $

create procedure pro_condition()
begin
set @x=1;
select * from test111;
set @x=2;
end$

delimiter ;

结果

调用存储过程

调用用户变量

2. 使用了条件定义处理程序

drop procedure if exists pro_condition;

delimiter $

create procedure pro_condition()
begin
declare continue handler for 1146 set @x2=1;
set @x=1;
select * from test111;
set @x=2;
end$

delimiter ;

结果:

调用存储过程

调用用户变量

使用光标

在存储过程中可以使用光标对结果集进行循环处理。光标使用参看链接如下:
(12)mysql中的光标-参考链接

DELIMITER $

CREATE PROCEDURE pro_cursor()
BEGIN
DECLARE sid_i INT;
DECLARE cur_test CURSOR FOR SELECT sid FROM test1;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_test;

SET @x1=0;
SET @x2=0;
OPEN cur_test;
REPEAT
    FETCH cur_test INTO sid_i;
    SET @x1=@x1+sid_i;
    SET @x2=@x2+1;
UNTIL 0 END REPEAT;

CLOSE cur_test;

END $

DELIMITER ;
#查看结果
call pro_cursor();
select @x1,@x2;

test1表结构及数据

test1表数据

结果
查询结果

使用流程控制

存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和WHILE 语句来进行流程控制。具体流程使用参见如下链接:
(13)mysql中的流程控制-参考链接

delimiter $
create procedure pro_flow_control(in sid_v int)
begin
    if sid_v > 5 then
        select * from test1 where sid>5;
    else
        select * from test1 where sid<=5;
    end if;
end $
delimiter ;

结果:

sid_v小于5

sid_v大于5

参考

辛星解读mysql中的存储过程的优劣