mysql 自定义函数 分区 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 ;

创建结果：

调用函数执行结果

使用条件定义、处理

条件定义和处理可以用来定义在处理过程中遇到问题（错误，警告，异常）时的处理步骤。

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表结构及数据

结果

使用流程控制

存储过程和函数中可以使用流程控制来控制语句的执行。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 ;

结果：