MySQL中存储过程和自定义函数

一、存储过程和自定义函数的作用

  存储过程和函数是一组预先编译好的 SQL 语句的集合，理解成批处理语句。可以简化操作、提高代码的重用性、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

二、存储过程

1、语法

1）创建

  ① 参数列表包含三个部分：参数模式、参数名、参数类型。
    in：表示该参数可作为输入，也就是该参数需要条用方传入值 。
    out：表示该参数可作为输出，也就是该参数可作为返回值。
    inout：表示该参数既可以作为输入有可以作为输出，也就是该参数既需要传入值，又可以返回值。
  ② begin end 表示存储过程的范围，如果存储过程体只有一句话，begin end 可以省略。
  ③ 存储过程体中的每条 SQL 语句的结尾必须要加分号，存储过程的结尾可以使用 delimiter 重新设置，默认为分号(;)。

create procedure 存储过程名(参数列表)
begin
	存储过程体
end

2）重设结尾标记

  该语句能重新设置存储过程和函数以及对变量操作的结尾标记，该语句不需要分号（;）。

delimiter 结束标记

3）调用

call 存储过程名(参数列表);

4）查看存储过程的信息

show create procedure 存储过程名;

5）删除存储过程

drop procedure 存储过程名;  

2、案例

1）空参列表

  创建：

create procedure zyx()
begin
	insert into zyx values (1,"zyxwmj.top",18);
end;

  调用：

call zyx();

2）创建带 in 模式和 out 模式参数的存储过程

  创建：

delimiter $
create procedure zyx(in name varchar(20),out count int)
begin
	-- 查看传来的name值
	select name; 
	select count(*) into count
	from zyx
	where zyx.name =name;
end$

  调用：

-- 创建用户变量
set @count=0$
-- 调用存储过程
call zyx("zyx",@count)$
-- 查看变量的值
select @count$

3）创建带 inout 模式参数的存储过程

  创建：

create procedure zyx(inout a int)
begin
	set a=a*2;
end;

  调用：

set @zyx=10;
call zyx(@zyx);
select @zyx;

三、自定义函数

1、语法

1）创建

  ① 函数的参数列表包含两部分：参数名和参数类型。
  ②函数体肯定会有 return 语句，如果没有会报错。
  ③函数体中仅有一条语句时，可以省略 begin end 语句。
  ④也可以使用 delimiter 语句设置结束语句。
  ⑤如果声明出错往下看。

create function 函数名(参数列表) returns 返回类型
begin
	函数体
	return 值;
end;

2）调用

select 函数名(参数列表);

3）查看函数

show create function 存储过程名;

4）删除函数

drop function 函数名;

2、实例

  创建：

create function zyx(name varchar(20)) returns int
begin
	declare count int;
	select count(*) into count
	from zyx where zyx.name =name;
	return count;
end;

  调用：

select zyx("zyx");

四、补充

1、存储过程和函数的区别

  ①存储过程和函数的作用一样。
  ②存储过程可以有零个或多个返回值，适合做批量插入、批量更新。
  ③函数有且仅有一个返回值，适合做处理数据后返回一个结果。
  ④参数列表中的参数是局部变量，如果参数和表的字段同名，调用字段时需要加上表名。

2、创建函数报错

1）报错信息

  报错信息的大致意思是这个函数没有确定性，没有sql或读取声明中的SQL数据，并启用二进制日志记录，您可能希望使用不太安全的log_bin_trust_function_creators变量。

This function has none of DETERMINISTIC, NO SQL, 
or READS SQL DATA in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators variable)

2）解决方案

  将log_bin_trust_function_creators变量设置为1，使用以下方式设置变量值，MySQL重启后还需要重新设置，可以使用更改配置文件的方法。

set @@global.log_bin_trust_function_creators=1;

  这个变量的作用官方解释是：启用二进制日志记录时，此变量适用。它控制着是否可以信任存储函数创建者，而不创建不导致将不安全事件写入二进制日志的存储函数。如果设置为0（默认值），用户不允许创建或更改存储功能，除非他们有SUPER除了特权CREATE ROUTINE或ALTER ROUTINE特权。设置为0也强制执行的功能必须与被宣布为限制 DETERMINISTIC特性，或与 READS SQL DATA或NO SQL特性。如果变量设置为1，MySQL不会在创建存储函数时强制执行这些限制。此变量也适用于触发器创建。官网链接