vs中mysql函数定义_(009)mysql中的存储过程和自定义函数

概述

存储过程和自定义函数是事先经过编译并存储在数据库中的一段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块中，可以用在嵌套中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。

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

创建结果：

创建函数

调用函数执行结果

调用自定义函数

使用条件定义、处理

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

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 ;

结果：

调用存储过程

调用用户变量

使用光标

在存储过程中可以使用光标对结果集进行循环处理。光标使用参看链接如下：

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 语句来进行流程控制。具体流程使用参见如下链接：

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

参考