Mysql存储过程详解

1、Mysql存储过程详解Mysql存储过程详解今天在做项的时候遇到了使存储过程的知识点，发现这块知识点之前在学习mysql基础的时候根本就没听进去，也没有好好的消化，因此特意翻出来重新学习下mysql存储过程MySQL 5.0 版本开始持存储过程。、存储过程的介绍存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，户可通过指定存储过程的名字并给定参数(需要时)来调执。存储过程思想上很简单，就是数据库 SQL 语层的代码封装与重。通俗来讲：存储过程其实就是能完成些特定操作或功能的组SQL语句，创建的存储过程保存在数据库的数据字典中、存储过程的优缺点优点存储过程可封装，可以包含复杂

2、的sql逻辑。存储过程可以回传值，也可以接受参数。存储过程法使 SELECT 指令来运，因为它是程序，与查看表，数据表或户定义函数不同。使存储过程的好处：可以增强组sql的执效率，因为我们每个sql语句的执前都要先连接数据库再执，使组sql语句只需连接次数据库就可执。缺点存储过程往往定制化于特定的数据库，因为不同数据库持的编程语不同。当切换到其他商的数据库系统时，原有的存储过程会失效，需要对其进重写。存储过程的性能调校与撰写，受限于各种数据库系统。、存储过程的使3.1存储过程就是具有名字的段代码，来完成个特定的功能。创建的存储过程保存在数据库的数据字典中。CREATE PROCEDURE()B

3、EGINsql语句）END其中，BEGIN和END不就相当于Java中法的括号嘛，存储过程体也就相当于组sql语句，当存储过程体中的sql只有句的时候，BEGIN和END也可以省略，相当于womenjava中的if、while语句，括号如果只有句那么可以省略括号，当然我个觉得如果存储过程真的只有句的话直接调sql难道不好吗？注意：存储过程的参数列表这点和Java中的法是不同的，Java中的参数列表只有 （参数类型1 参数名1，参数类型2 参数名2），存储过程列表包含三部分：参数模式，参数名，参数类型参数模式介绍IN：该参数可以作为输，也就是该参数需要在调存储过程的时传值OUT：该参数可以作为输

4、出，也就是说该参数可以作为存储过程的返回值INOUT：该参数既可以作为输可以作为输出，也就是该参数既需要调时传值，可以作为返回值参数举例BEGINEND注意 ：存储过程体中的每条sql语句的结尾都需要加分号，但是mysql中默认以分号为执结束标记，那么在执存储过程的过程中，在执完第条sql语句后就执结束了！这样会产意想不到的错误，因此我们要设置结束标记，并把结束标记设置在存储过程的末尾(该操作Mysql8.0以上可以忽略),我这的mysql8进讲解语法为：DELIMITER 结束标记举例：如果想设置$为结束标记，DELIMITER $CALL 存储过程名(实参列表);3.2、案例1、创建空参的

5、存储过程在admin表中原本有两条数据，我们批量插下先创建个存储过程create PROCEDURE insertBatch()BEGINinsert into admin(name,password) values(Tom,123456),(Jerry,123456);END执调后会发现Functions中多了个insertBatch然后调这个存储过程即可call insertBatch()数据插成功！案例2、创建带IN模式的参数存储过程我们拿下这两张表举例，个歌表，个歌曲表歌表歌曲表创建存储过程，实现根据歌名查询对应的歌曲#创建存储过程，我们要通过singerName进查询，且参数必须要输

6、，参数前加IN,参数就是singerName，参数类型为VARCHARcreate PROCEDURE selectSongBySingerName(IN singerName VARCHAR(20)BEGINsselectCALL selectSongBySingerName();调成功案例3、创建带IN,OUT模式的参数存储过程根据歌名，返回对应的第个歌曲名前置知识：代表局部变量，也叫户变量：代表全局变量#创建存储过程，第个参数同上，第个参数：我们需要返回输出歌曲名，参数前OUTCREATE PROCEDURE getSongBySingerName(IN singerName VARCH

7、AR(20),OUT songName VARCHAR(50)BEGIN#这的INTO就是把查询出来的值赋给变量的过程select as INTO songNamefrom singer LEFT JOIN songon singer.id =song.singer_idwhere =singerNamelimit 1;END#调存储过程，传参数“林俊杰”，返回他的第个歌曲# 代表局部变量也叫户变量，代表全局变量CALL getSongBySingerName(,songName)select songName同理我们也可以设置返回两个或两个以上参数

8、返回值的存储过程，过程很简单，就不再赘述案例4、创建带INOUT模式参数的存储过程INOUT模式参数在实际开发中尽量少，举个例明就好了传歌数量和歌曲数量两个值，最终两个值都翻倍并返回#singerCountsongCount即是变量需要被返回，所以要设置为INOUTCREATE PROCEDURE (INOUT singerCount INT,INOUT songCount INT )BEGINselect count(0) from singer INTO singerCount ;select count(0) from song INTO songCount ;set singerCou

9、nt =singerCount * 2;set songCount =songCount * 2;END#调法计算出结果select songCount原本的singerCount和songCount分别为39和93，查询结果符合预期3.3SHOW CREATE PROCEDURE储存过程名字3.4DROP PROCEDURE储存过程名字存储过程基础讲完了，以下拓展有精的伙伴可以充实下，也可以选择跳过企业级项案例现有张部门表t_department，我们需要在增添数据，每个部门都有个部门，股东会是最的部门，parentId为-14.1Department类由mybatis-plus-gener

10、ator动成，如下DataApiModelProperty(value =)ApiModelProperty(value =)ApiModelProperty(value =)ApiModelProperty(value =)private Boolean isParent;我们在Department类中再加个字段children,由Department组成的List集合，因为部门和部门之间是嵌套的，其结果是个树形结构private ListDepartmentchildren;既然查询结果要让部门层层嵌套在部门中，那么该怎么进查询呢，可不是简单的select* from t_departme

11、nt了！我们可以通过parentId = -1先查询出最外层的部门股东会，再通过把股东会的id作为parentId查询出董事会，再通过把董事会的id为parentId查询出总办，在通过把总办为parentId查询出财务部，市场部，依次类推，其实就是递归调mysql查询那么如何使递归调呢，可以通过resultMap进递归调resultMap idBaseResultMap typecom.zlq.server.pojo.Department id columnid propertyid/selectcom.zlq.server.mapper.DepartmentMapper.getAllDepa

12、rtment columnididinclude refidBase_Column_List/from t_department4.2我们在表中设了默认值，enabled默认为1，isParent默认为0depPath就是从后往前看，最后是的id，再往前是它的id，再往前是它id的id，以此类推，直到指向最外层的部门添加时，我们只需要传name和parentId两个参数即可实现添加，如我要在技术部下添加技术总监，添加逻辑为：1. 通过前端传来的name（技术开发部）和parentId（3）进添加，enabled默认为1，isParent默认为0，depPath现在为空2. 通过前端传来的par

13、entId（3）为id查询出它的部门技术部的depPath（.2)3. 查询新增部门的id（14）4. 修改id为14的那的deptpath，将部门的depPath后追加.14 （最后结果为：.2.14)5. 修改其部门的isParent为1对这么复杂的过程我们不妨使存储过程- DEFINER=rootlocalhost：定义户，使本机的ip地址CREATErootlocalhost PROCEDUREaddDep(in depName varchar(32),inparentId int,in enabled boolean,out result int,out

14、result2 int)begin- 定义int类型变量，来存储最后插部门的iddeclare did int;- 定义varchar类型变量，来存储插部门的部门的deptPathdeclare pDepPath varchar(64);insert into t_department set namedepName,parentIdparentId,enabledenabled;- 系统带函数row_count()，为受影响数，赋值给result，此值在操作成的前提下必维1select row_count() into result;- 系统带函数last_insert_id()，为最后插的

15、数据的id，赋值给didselect last_insert_id() into did;- result2为最后插部门的idset result2did;- 查询插数据的部门的depPath，设置到pDepPathselect depPath into pDepPath from t_department where idparentId;- 修改插部门的depPath，使拼接，上步部门的depPath拼接最后插部门的idupdate t_department set depPathconcat(pDepPath,.,did) where iddid;- 修改插部门的部门的isParent参

16、数为1我们在Department类中加个字段,因为存储过程返回结果中有resultprivate Integer result;mybatis中的sql语句为DepartmentMappervoid addDepartment(Department department );DepartmentServiceImplreturn RespBean.error();4.3删除部门的逻辑也较复杂，其删除逻辑为：查询要删除的部门下有没有部门？如果有，说明要删除的数据下有关联数据直接返回结果，不能对其进删如果没有，继续判断先查询是否有员在这个部门中？如果有，还是不能删如果没有，继续判断查询要删除的部门

17、的部门有没有部门，也就是查询你要删除的部门有没有同级部门如果有，什么也不动如果没有，设置你删除部门的部门的isParent为0，因为它下已经没有部门了- 定义个int变量，来接收部门的parentId- 定义int类型变量，来存储查询的部门个数declare a int;- 通过前端传的id给select count() into a from t_department where iddid and isParenttrue;- 如果部门数不为0，说明要删除的部门下有部门，不能删！设置参数中的返回值-2为if a!=0 thenset result=-2;else- 如果存在，查询与其关联的

18、员表中在该部门的员数select count() into ecount from t_employee where departmentIddid;- 1if ecount0 thenset result=-1;- 如果要删除的部门没有员else- 查询要删除部门的parentId，赋值给pidselect parentId into pid from t_department where iddid;- 删除指定的部门，且该部门下没有部门delete from t_department where iddid and isParentfalse;- 将受影响的的数赋值给参数的返回值select row_count() into result;- 查询要删除部门的部门还有没有部门（查询要删除的部门还有没有同级部门）select count() into pcount from t_department