系统整理Mysql学习笔记——存储过程和函数

前言

存储过程和函数的概念非常类似，但是它们和java中的方法概念也基本类似，都是提高代码的重用性和简化操作的。
可以将存储过程和函数看成是一组预先编译好的SQL语句的集合，一次编译，减少了和数据库服务器的连接次数。
存储过程和函数的不同之处是什么？
主要是在于入参和返回值上

1. 存储过程：可以有零个或者多个参数和返回值；
2. 函数：参数不限，有且只有一个返回值。

对比来看，函数很像是java中的方法，而存储过程类似于java使用引用传递返回值的方式。

一、存储过程

1 创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体
END

参数列表分为三部分，分别是参数模式、参数名、参数类型，如：IN name VARCHAR(20)
关于参数模式同样是分为三种：

• IN：该参数可以作为入参，也就是该参数需要调用方传入值；
• OUT：该参数可以作为输出，也就是该参数可以作为返回值；
• INOUT：改参数既可以作为输入值又可以作为输出值。

如果存储过程体仅仅只有一句话，BEGIN END 可以省略。
存储过程中的每条SQL的结尾都要求必须加分号，因此它本身就和正常一句话的结尾的分号相冲突了，尤其是在类似于Linux的命令行的系统中，SQL解释器认为分号就是一条SQL的结束应该是立即执行，但是很明显在存储过程中并不是如此，存储过程要求END才是截止，因此这里就引入了DELIMITER的概念，重新定义SQL结束符，关于这方面的概念可以看一下这个博客

DELIMITER $$  # 定义结束符

CREATE PROCEDURE insert_default()  # 创建一个无参无返回值的存储过程
BEGIN
	INSERT INTO `grade_new_9` (id,seat,age) VALUES (17,123,5);
END $$
CALL insert_default()$$  # 调用


#####################################
DELIMITER $$
CREATE PROCEDURE insert_int_param(IN id VARCHAR(20),IN seat VARCHAR(20), IN age VARCHAR(20)) # 多入参无返回值的存储过程
BEGIN
	INSERT INTO `grade_new_9` (`grade_new_9`.id,`grade_new_9`.seat,`grade_new_9`.age) VALUES (id,seat,age);
END $$
CALL insert_int_param('50','50','50')$$  # 调用
#####################################
DELIMITER $$
# 创建一个多入参一个反参的存储过程
CREATE PROCEDURE select_out_param(IN id VARCHAR(20),IN seat VARCHAR(20), OUT count INT)
BEGIN
		SELECT COUNT(*) INTO count 
		FROM `grade_new_9`
		WHERE `grade_new_9`.id = id AND `grade_new_9`.seat = seat;
END $$
SET @count:=0 $$
CALL select_out_param('50','50',@count) $$
SELECT @count $$
#####################################
DELIMITER $$
创建一个平方的存储过程
CREATE PROCEDURE select_inout_param(INOUT a INT,INOUT b INT)
BEGIN
		SET a = a*2;
		SET b = b*2;
END $$
SET @a:=15 $$
SET @b:=20 $$
CALL select_inout_param(@a,@b) $$
SELECT @a,@b $$

2 调用

CALL 存储过程名(实参列表);

调用的例子也写到了上面。比较简单

3 查看和删除

查看存储过程可以使用

SHOW CREATE PROCEDURE 存储过程名;
# 如：
SHOW CREATE PROCEDURE insert_default;

删除存储过程可以使用：

DROP PROCEDURE 存储过程名;
# 如
DROP PROCEDURE insert_default;

二、函数

1 创建函数

CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型 [characteristic]
BEGIN
	函数体
	....
	return 返回值
END

• 参数列表分为两部分，分别是参数名和参数类型；
• 函数体肯定会有return语句，没有就会报错；
• 函数体中假如只有一句话，那么可以省略BEGIN END；
• 使用DELIMITER设置结束标记。

characteristic主要是针对于当开启了bin-log时，需要在创建函数时指定对应的安全级别，如果不指定就会报错，如下：

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)

对应的参数及含义有：

1. DETERMINISTIC 不确定的
2. NO SQL 没有SQl语句，不会修改数据
3. READS SQL DATA 只是读取数据，不会修改数据
4. MODIFIES SQL DATA 要修改数据
5. CONTAINS SQL 包含了SQL语句

这块也可以看一下这个API

当然除了在创建函数的时候去明确指定安全级别，也可以设置系统全局变量log_bin_trust_function_creators为TRUE，直译这个变量的含义就是信任函数的创建，因此就不需要在创建函数的时候去指定对应的级别了。具体操作为

SET GLOBAL log_bin_trust_function_creators=TRUE;

以上内容借鉴于三篇博客
对于mysql 函数中 函数characteristic no sql 位置问题
MySQL自定义函数（CREATE FUNCTION)
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法

例子：

DROP FUNCTION IF EXISTS simple_fun;  # 如果存在则删除函数
DELIMITER $$  # 定义结束符
CREATE FUNCTION simple_fun(a INT, b INT) RETURNS INT NO SQL   # 创建函数并指定返回值为INT类型，并且指定当前函数中没有SQL
BEGIN
		DECLARE re INT;
		SET re:= a-TRUNCATE(a/b,0)*b;
		RETURN re;
END $$
SELECT simple_fun(8,3)$$

以上函数模拟MYSQL系统函数MOD()，对两个数进行取余。

2 调用

SELECT 函数名（参数列表）

类似于系统函数是一样的，只要在允许函数出现的地方使用即可。

3 查看和删除

查看函数

SHOW CREATE FUNCTION 函数名;
# 如:
SHOW CREATE FUNCTION simple_fun;
# 显示所有自定义的函数
SHOW FUNCTION STATUS;  

删除函数

DROP FUNCTION [IF EXISTS] 函数名;
# 如：
DROP FUNCTION IF EXISTS simple_fun; 

结语

最简单的语法就是上面所示，肯定是有很多细节没有深挖，有其他内容，望大佬路过，不吝赐教。