普通的SQL执行过程:
SQL命令要经过MySQL引擎的语法分析,如果语法正确,就会进行编译,编译成mysql引擎可以识别的命令。可识别命令再执行,将执行的结果返回给客户端。
试想如果把语法分析与编译的环节去掉,mysql的执行效率就可以提高,因此要用到存储过程。
存储过程:
存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。存储过程的效率要比单一的SQL执行效率高。
以往我们写两个SQL语句,mysql引擎会对这两个SQL语句逐一进行语法分析,逐一编译,再逐一执行。而采用存储过程以后,只有在创建时会进行语法分析与编译,以后客户端再调用,直接调用编译后的结果,这样就直接省略了两个环节。
优点:
增强了SQL语句的功能与灵活性(存储过程写控制语句可以完成复杂的判断增强了灵活性)
实现较快的执行速度(存储过程在创建的时候就进行了编译,将来使用的时候不用再重新编译。一般的SQL语句每执行一次就需要编译一次,所以使用存储过程提高了效率。存储过程也可以理解为预先编写的sql子程序,在程序中直接调用这一子过程显然要比逐条运行sql语句效率高)
减少网络流量(客户端每次直接发送复杂SQL语句到mysql服务器引擎执行时,通过http协议所提交的数据量相对而言较大。但通过存储过程只要提交存储过程的名字以及相关参数即可,因此存储过程的名字也要尽量精简)
创建无参存储过程:
CREATE PROCEDURE 是创建存储过程的关键字
调用存储过程:CALL 存储过程名字(参数1,参数2);
创建带有IN类型参数的存储过程:
DELIMITER // 将分隔符(原本是分号)改成//
IN fade_id SMALLINT UNSIGNED代表存储过程传入的参数,注意写上参数数据类型,最好跟数据库中定义该字段时相同
注意不能是IN id SMALLINT UNSIGNED,若这样写,在下面的SQL语句就是WHERE id = id。虽然我们认为第一个id是字段,第二个id是参数,但是mysql会认为两个id都是字段,这样执行会全部记录都被删除!
BEGIN … END内是执行的合法SQL语句!
在调用存储过程时注意先将分隔符转换为分号
可以看到成功删除了id为10对应的记录
创建带有 IN 和 OUT 类型参数的存储过程:
OUT clientNums SMALLINT UNSIGNED 同样要规定返回数据的类型
INTO 关键字将SELECT 查询的结果赋予clientNums参数
在调用存储过程时OUT对应的参数用@nums代替,即定义了一个变量存储返回的数据。SELECT @nums;就能查看返回的数据
可以看到数据表中id为8对应的记录被删除并返回了删除后正确的记录数目
创建带有多个OUT类型参数的存储过程:
先看看现在client表中的数据(共有11条数据记录,其中有3个年龄为100的数据记录):
创建存储过程:
ROW_COUNT()查看的是SQL影响的记录条数,不需要加表名
运行并查看结果:
查看返回的两个OUT类型的值,确实是3条记录被删除(年龄为100的记录有三条)。剩下的数据记录共有8条
数据表中不再有age = 100的记录
总结:
有关存储过程,这里只讲到怎么使用与简单的数据库执行效率,网络流量问题。还有很多关于存储过程的知识例如:权限安全,结合数据库事务处理SQL逻辑以及实际开发中的适当运用(与spring的事务比较)还有待完善。