mysql函数 存储过程 事务 mysql存储函数和存储过程

在其它变编程中，为了方便某些重复的操作，而将一些逻辑代码提炼出来，然后作为一个新方法或者新的类。而SQL作为一个也可称之为编程语言，自然也是可以有类似的操作。而这个就是存储过程和存储方法，这两者能够将复杂的SQL语言封装在一起，使用者或者应用程序无需指定其执行的复杂逻辑，只需要简单的调用即可。

其实解决问题上存储过程和存储函数很像，但是各自又有不同。

存储过程

概念

存储过程其实简单的理解就是一组经过预编译的SQL语句的封装。

存储过程预先存储在MySQL服务器上，需要执行的时候，客户端之间向服务器发出调用的信号即可，服务器会将预编译的SQL全部执行。前面说过存储过程

这样做的好处：

• 将经常使用的SQL用存储过程存封装起来，方便多次使用。同时减少了操作中的失误，提高销量。
• 很多时候服务器和客户端是分开，这样减少网络传输。

这个时候需要对比一下视图以及函数了。

• 对比视图：它和视图有着同样的优点都会方便用户操作，但是和视图不同，视图时操作虚拟表，通常不对底层的数据直接操作。而存储过程时封装号的SQL，可以直接操作底层的数据表，相比对于面向集合操作方式的视图，能更好的实现一些复杂的数据处理。
• 对比函数：虽然两者的目的和本质没有什么区别，但是其创建关键字不一样，以及存储过程没有返回值，而函数有。这里记住就行，后面结合实例演示就会明白。

存储过程分类

存储过程其分类主要时根据其参数类型而划分的，前作主要的关键字 IN,OUT ,INOUT。如下：

• 无参数：无参数无返回值
• 仅仅带IN：有参数无返回
• 仅仅带OUT：有参数有返回
• 即带IN又OUT：有参数又返回
• 带INOUT：有参数和返回值

上面只是一个简单举例而已，其中三个关键字可以单独以及搭配使用，而且也可以各自多个作为参数。

这个时候有了一个疑问，不是说存储过程没有返回值吗？为什么上面我说了有返回值?

其实存储函数的我所说的返回值，本质就是对一个可以参数赋予权限可以赋值在外面可以得到。而不像是存储函数有一个RETURN。这演示的就会更加明白。

创建存储过程

格式：

CREATE PROCEDURE 存储名([IN 或 OUT 或 INOUT])
[ charecteristics]
BEGIN
   存储过程
END

对上面的进行简单解释

• 参数：可以没有或者有上面三个其中一个或者多个，当然也可以出现相同性质的参数。三个关键字定义了参数的

• IN: 当前参数为输入参数，有点像是java中的形参。
  存储过程中是只会读取整个参数的值，如果没有定义参数种类，也就是上面三个之一，默认就是IN.
• OUT: 当前参数的输出参数也就是表示出参。
  执行完毕之后调用整个存储过程的客户端或者应用程序可以读取整个参数的返回值。
• INOUT: 当前参数具有上面两个参数功能，可以输入也可输出。

• charecteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
|[NOT] DETERMINISIIC  
| {CONTAINS SQL | NOT SQL| READS SQL DATA| MODIFIES SQL DATA}
|SQL SECURITY {DEFINER | IVOKER }
| COMMENT 'string'

• LANGUAGE SQL: 说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
• [NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC-表示结果的确定的，每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC-表示结果时不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认是NOT DETERMINISTIC。
• CONTAINS SQL：表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句。
• NOT SQL：表示当前存储过程的子策划功能性中不包含任何SQL语句。
• READS SQL DATA:表示当前存储过程的子程序中包含读取数据的SQL语句
• MODIFIES SQL DATA：表示当前存储过程的子程序包含写数据的SQL语句。
• SQL SECURITY {DEFINER | IVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储的过程。DEFINER -表示只有当前存储过程的创建者或者定义者才能执行当前存储过程。IVOKER -表示拥有当前存储过程的访问权限的用户能够执行当前的存储过程。如果没有设置相关的值，默认就是DEFINER 。
• COMMENT 'string':注释信息，可以用来描述存储过程。

补充：

BEGIN和END: 在存储过程体中可以有多条SQL语句，如果仅有一条SQL语言，则可以省略BEGIN和END.如果拥有多条SQL语句其结尾都需要有（;）号为结束符。

DECLARE: DECLARE 用来声明变量，使用的位置在BEGIN和END中间。而且需要在其他语句使用之前进行变量声明。

SET:赋值语句，用于对变量进行赋值

SELECT ... INTO : 把数据表中查询的结果存放到变量中，也就是变量赋值。

DELIMITER：声明新的结束标志，因为如果有多个SQL语句，然后用（;）作为结束符，所以需要重新对系统声明一个新的结束符，防止彼此影响。这个具体例子在演示的时候就明白了。

演示

表还是使用前面的三张表进行演示，如果不知道可以看一下前面文章，就不在重复黏贴，需要sql文件就私聊我。

例子1

先来一个简单的；

# 用存储过程演示一下搜索表emp

DELIMITER //  #因为再方法体如果有多个sql需要；作为结束符所以重新为存储过程定义一个新的结束符号 现在用//作为结束符
CREATE PROCEDURE select_all_data()
BEGIN 
SELECT * FROM emp;
END //
DELIMITER ; # 再把这个声明结束符号再声明回去

然后我们看一下如何调用这个存储过程。

CALL select_all_data();

从这个地方可以看出存储过程可以将一些写好的sql语句像是JAVA种的类一样，封装起来自己需要的时候就调用。

例子2

当然一些聚合函数也是可以放在存储过程种的。

# 要求： 查询员工表种的平均工资是多少
DELIMITER $ #这个结束符可以用很多字符，但是一般用$和//两种
CREATE PROCEDURE select_avg_salary()
BEGIN 
SELECT AVG(sal) FROM  emp;
END $
DELIMITER ; 

##调用存储过程
CALL select_avg_salary();

例子3

前面两个演示的存储过程都没有参数，下面开始演示一些带有参数的存储过程。

# 查询员工表种最少工资是多少，然后将其赋值给传递的参数minsal


DELIMITER $
CREATE PROCEDURE min_salary(OUT minsal decimal(7,2)) #这个地方需要有数据类型的 最好和数据库种的类型长度一致
BEGIN
SELECT MIN(sal) INTO minsal FROM emp;  # INTO 可以理解为将其赋值给minsal
END $

DELIMITER ;

这个调用就有点不同了。

CALL min_salary(@minsal); # 这个地方为什么要使用@ 后面再聊变量的时候，具体聊
SELECT @minsal;

例子4

现在看一个传入参数的如果使用

#要求 ：创建一个存储过程，然后传给一个用户名，然后得到指定用户的收入。

DELIMITER $
CREATE PROCEDURE get_salary(IN by_ename VARCHAR(20))  
BEGIN
SELECT sal FROM emp WHERE ename=by_ename;
END $

DELIMITER ;

#调用
CALL get_salary('CLARK');# 如果用户名不存在和用查询语句一样，没有复合条件的数据
# 也可以这样调用
SET @by_ename='CLARK';#  SET @by_ename :='CLARK';(:= 也是一个赋值符号 讲变量的时候具体聊)
CALL get_salary(@by_ename);

例子5

再来一两个参数，一个为输入一个为输出。

#要求 ：创建一个存储过程，然后传给一个用户名，然后得到指定用户的收入赋值给 salary。

DELIMITER $
CREATE PROCEDURE by_name_sal(IN by_ename VARCHAR(20),OUT salary DECIMAL(7,2))
BEGIN 
SELECT sal INTO salary FROM emp WHERE ename =by_ename;
END $
DELIMITER ;

#调用
CALL by_name_sal('CLARK',@salary);
SELECT @salary;

例子6

前面好几类，现在演示另一种参数INOUT

# 要求 输入员工名得到领导名字 使用INOUT参数
DELIMITER $
CREATE PROCEDURE enamme_mname(INOUT _name VARCHAR(20))
BEGIN
SELECT ename INTO _name FROM emp WHERE empno = (SELECT  mgr FROM emp WHERE ename= _name);
END $
DELIMITER ;

# 调用
SET @_name :='CLARK'; # 因为是输入值也是输出值，所以需要提前进行赋值才可以
CALL enamme_mname(@_name);
SELECT @_name;

## 再来一个神奇的地方，
SET @__name :='CLARK'; # 这个地方可以看出 声明的变量名和存储过程中的不一样一个_name 一个是 __name
CALL enamme_mname(@__name);
SELECT @__name;

但是仍然可以出结果，所以这个地方理解的话，可以将其理解传递的时候是传递的类似JAVA中指针，而不是本身变量名，前面几个存储过程也是同样会有这样现象。

存储函数

前面说过了定义以及其存在的意义和前面存储过程差不多，所以不在赘述了。

创建存储函数

格式如下：

CREATE FUNCTION 函数名(参数 参数类型，…………)
RETURNS 返回值类型
[characteristics ....]
BEGIN
 函数体  # 函数体中肯定 RERURN 语句
END

现在进行说明：

• 参数列表：存储函数中的参数默认是IN参数，指定参数为IN,OUT或者INOUT只对存储过程时合法的。因为有返回值所以就不需所谓的OUT了。
• RETURNS 返回值类型：表示函数返回数据的类型。
  RETURNS字句只能对存储函数做指定对存储函数来说是必须有，所以其指定了返回类型，而且函数体也必须包含RETURN value语句。
• characteristics：创建函数时指定的对函数的约束。取值和创建存储过程两者相同所以就不在重复说了。
• BEGIN …………END: 和存储过程也是差不多，如果是一行sql也可以省略。

演示

还是老规矩演示比说的更容易理解。

例子1

#创建一个存储函数 然后得到员工名为CLARK的工作。
DELIMITER $
CREATE FUNCTION c_job()

RETURNS VARCHAR(20)
BEGIN
RETURN  (SELECT job FROM emp WHERE  ename='CLARK');
END $
DELIMITER :

如果执行的话，再5.0版本不会有提示错误，但是在5.7和8.0版本中就会报错。

提示没有带上限制条件，这个在存储过程中也没有演示，现在演示一下。

DELIMITER $
CREATE FUNCTION c_job()
RETURNS VARCHAR(20)
NOT SQL #这个表示函数中不能有SQL故意这样写看会如何
BEGIN
RETURN  (SELECT job FROM emp WHERE  ename='CLARK');
END $
DELIMITER ;

提示报错，但是报错又不是十分明显，这个在后面说优缺点的时候会提，不在这里说。

如果修改限制

DELIMITER $
CREATE FUNCTION c_job()
RETURNS VARCHAR(20)
READS SQL DATA  #本身就是读取的一个SQL 
BEGIN
RETURN  (SELECT job FROM emp WHERE  ename='CLARK');
END $
DELIMITER ;

可以看出运行不错。

然后调用

#存储函数的调用和存储过程不一样。
SELECT c_job();

例子2

当然如果不想写限制条件，所以可以直接设置环境变量。这个在创建存储函数前设置。

# 不过这样设置重启或者重新打开msyql服务器就会失效
SET GLOBAL log_bin_trust_function_creators=1;

# 创建一个存储函数带有一个员工名参数 然后得到员工名的工作。
DELIMITER $
CREATE FUNCTION c_name_jo1b(byname VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
RETURN  (SELECT job FROM emp WHERE  ename=byname);
END $
DELIMITER ;

# 调用
SELECT c_name_jo1b('CLARK');

其实自己创建的函数也是可以在查询表的语句中使用的，当然需要复合逻辑。这个和系统带的函数有些像，不如使用AVG等

# 为了演示可以这样用，实例的使用自己时没有意义的，毕竟这个表有工作名需要关联或者存储函数来得到
SELECT ename,c_name_jo1b(ename) FROM emp;

对比存储函数和存储过程

	关键字	调用	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	不是常规的返回值，近似返回值可以有0个或者多个	一般用于更新
存储函数	FUNCTION	SELECT 存储函数()	只能是一个	一般用户查询结果为一个值并返回时

通过例子可以看出存储函数可以在查询语句中使用，而存储过程不行。反之，存在过程的功能更加强大，包括能执行对表的操作（创建，删除等）和事务操作，这些时存储函数不具备的。

还有删除和更新，以及表的关联不在演示，具体使用更多的是说SQL查询语句中的写法，让其写在方法体内即可，所以不太熟悉如何写查询SQL就看前面的文章，还有INSERT，DELETE 等语句甚至可以创建视图，不再举更多的例子。

存储函数和存储过程的查看与删除

上面演示了创建，自然也会有查看和删除。现在再看一下其如果用或者说如何写SQL.

查看

毕竟创建了存储函数和存储过程，自然会查看其是否存在。自然MYSQL 自然会存储存储过程和存储函数的状态信息。

# 第一种 通过SHOW CREATE {PROCEDURE|FUNCTION} 存储名 
# 例子
SHOW CREATE  FUNCTION c_job

# 第二种 通过 SHOW {PROCEDURE|FUNCTION}  STATUS [LIKE 'pattern'] 
# 例子1
SHOW FUNCTION STATUS # 这个现实全部的存储方法
# 例子2 存储过程和存储函数使用方式一样 所以演示一个存储过程的带匹配条件的
SHOW PROCEDURE STATUS LIKE 'get_salary'

# 第三种 可以查看系统表  information_schema
SELECT * FROM  information_schema.ROUTINES
WHERE ROUTINE_NAME='get_salary' AND ROUTINE_TYPE='PROCEDURE';
# 当然条件的语句中的两个字段又可以单独使用

修改

存储函数和存储过程的修改，不影响存储过程或函数功能，只能修改相关特性，使用ALTER实现。

ALTER {PROCDDURE|FUNCTION} 存储过程和存储函数名  [characteristic ..]

其中characteristic指定了存储过程或者函数的特征。

| {CONTAINS SQL | NOT SQL| READS SQL DATA| MODIFIES SQL DATA}
|SQL SECURITY {DEFINER | IVOKER }
| COMMENT 'string'

这个修改其实不能修改存储函数或过程的功能，只能修改上面的描述的特性，如果逻辑有问题只能删除重写。

# 比如修改一个存储函数的注释

ALTER PROCEDURE get_salary
COMMENT '测试';

删除

删除的话两个都需要DROP

DROP {PROCEDURE|FUNCTION} [IF EXISTS] 存储过程或者函数的名

这个很简单就不再演示。

优缺点

尽管存储函数和过程有诸多优点，但是其还有有明显的缺点，所有大厂公司明确说到禁止使用这存储函数和过程。

优点

• 存储过程或函数可以重复利用，简单说就是一次写完可以多次利用，有点JAVA中类的概念可以减少开发者的工作量。
• 减少传输 其本身就在服务器端，如果远程数据库的时候，不需要传递很长或者很多逻辑的sql，直接调用即可。
• 有一定的安全性，毕竟存储过程和函数都可以设置一些使用权限。

缺点

比如阿里直接禁止使用存储过程。在其开放手册中，那是为什么？

• 可移植性差： 因为数据库不同，有些SQL无法通用，比如mysql和Oracle中的sql语句都无法通用。
• 很难调试：当然有些DBMS支持，但是还是很少。而写的存储过程很复杂的时候，维护以及查找错误很难，有些第三方可以，但其是收费的，增加开放成本。
• 版本维护难： 比如数据表中修改某些字段或者字段属性，就会导致存储过程或者函数失效，而在开发中其实需要对版本进行管理。而存储过程和函数本身没有版本欧冠年至，所以迭代的版本的时候会很麻烦。
• 不是高并发：高并发的场景是减少数据库的压力，有时会采用分库分表的方式，对可扩展性要求很高。这个时候存储过程和函数不但难以维护还会给数据库带来更多的压力。

还是那句话，用不用还是根据所在公司的要求吧，最好还是掌握一下。