SQL系列——存储过程

存储过程基础

？什么是存储过程

就是写在SQL中的方法，用于完成特定功能的SQL语句集合。

？优点

• 执行效率很高

​ 存储过程是预编译的，即创建时编译，而SQL语句是执行一次，编译一次。调用存储过程可以大大减少同数据库的交互次数。

• 降低网络通信量

存储过程执行的时候，只需要call存储过程，而存储过程是sql语句的集合，减少了与数据库的连接次数，降低了数据库的压力。

• 代码复用

存储过程就是公用的sql方法。

• 安全性

参数化的存储过程可以防止SQL注入式的攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

？缺点

• 移植性非常差

多平台不兼容（Mysql\Oracle）

• 代码可读性差

实现一个简单的逻辑，代码会非常长。

？用途

• 造测试数据

可以使用存储过程，往表里造几百万条数据。

• 数据同步

两个表之间按照一定的业务逻辑进行数据同步。

• 数据挖掘

？注意事项

可能遇到的问题：

• 同一个服务器跨库：
  • 1、设置用户权限查询
  • 2、先建立个视图，在视图里跨库连表，再在存储过程中调用视图
• 跨服务器

  目前只能通过编程语言来实现，不能通过底层的存储过程跨服务器编程

使用语法（Mysql）

基础用法

• 创建

  1,参数是可选的 2,参数分为输入参数、输出参数 3,输入参数允许有默认值 

  CREATE DEFINER=`root`@`%` PROCEDURE `update_file_search_procedure`( ) @参数1 数据类型 = 默认值, …… , @参数n 数据类型 OUTPUT 出参 AS SQL语句 BEGIN #Routine body goes here... END 

• 删除

  DROP PROCEDURE 存储过程名 

• 一种是参数按次序调用，一种是按参数名
  

  调用

  EXEC 过程名 参数值1，参数值2，....，@a output EXEC 过程名 参数1=参数值1，参数2=参数值2.... 参数有默认值时： exec 过程名 #都用默认值 exec 过程名 参数值1， #第二个用默认值 exec 过程名 参数2=参数值2.... #第一个用默认值 #设置变量 declare @out int #有出参 exec usp_pp @canshu= @out output #如果是按次序调用可写成 exec usp_pp @out output #打印参数值 print @out 

• 参数分为局部参数@、全局参数@@或者没有@, 可以设置默认值
  

  参数

  DECLARE 参数名 参数类型; DECLARE a INT; DECLARE a INT default 0; 

游标的使用

• 基本用法

#游标的使用 遍历表 #声明游标 DECLARE cursor_name CURSOR FOR #游标处理语句 select cloumn1, cloumn2 from table_name #开启游标 open cursor_name; DECLARE @变量1 变量类型 DECLARE @变量2 变量类型 CLOSE cursor_name --关闭游标 

• 3中循环方式 while, repeat, loop

  • #开启游标 OPEN cursor_name #先将游标中的数据存入到变量中 FETCH cursor_name into 变量1,变量2; #当a不等于1的时候执行内容操作 while a<>1 do ... #逻辑操作 #再在循环中将游标中的值传入到变量中 FETCH cursor_name into 变量1,变量2; end while #释放游标 CLOSE cursor_name; 

  • repeat

    #打开游标 open cursor_name; #进入循环 REPEAT FETCH userCur into uid; #赋值给变量 IF NOT DONE THEN #当done!=1继续循环 .....#搞事情 END IF; UNTIL done END REPEAT; #直到done=1时结束循环 #释放游标 CLOSE cursor_name; 

  • loop

    #开启游标 OPEN cursor_name; #定义loop循环 loop_name: loop #将游标中的cloum1,cloum2 、给到变量1, 变量2 FETCH cursor_name INTO 变量1, 变量2; # update执行的条件 IF b <4 THEN ...#搞事情 END IF; IF b>=7 THEN #停止LOOP 循环 LEAVE loop_name; END IF; #停止LOOP 循环 END LOOP loop_name; #释放游标 CLOSE cursor_name; 

  • DECLARE … HANDLER

    该DECLARE … HANDLER语句指定处理一个或多个条件的处理程序。如果出现这些条件之一，则*statement*执行指定的。 *statement*可以是简单的语句，例如，也可以是使用and 编写的复合语句。 SET *var_name* = *value*``BEGIN``END

    DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action: { CONTINUE | EXIT | UNDO } condition_value: { mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION } 

    处理程序声明必须出现在变量或条件声明之后。

    该*handler_action*值指示处理程序在执行处理程序语句后采取的操作：

    • CONTINUE: 当前程序继续执行。
    • EXIT: 对BEGIN … END声明处理程序的复合语句执行终止 。即使条件发生在内部块中也是如此。
    • UNDO： 不支持。

    CONTINUE 与 EXIT 对比

    DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action: { CONTINUE | EXIT | UNDO } condition_value: { mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION } 

    该DECLARE … HANDLER语句指定处理一个或多个条件的处理程序。如果出现这些条件之一，则*statement*执行指定的。 *statement*可以是简单的语句，例如，也可以是使用and 编写的复合语句。 SET *var_name* = *value*``BEGIN``END

    处理程序声明必须出现在变量或条件声明之后。

    该*handler_action*值指示处理程序在执行处理程序语句后采取的操作：

    • CONTINUE: 当前程序继续执行。
    • EXIT: 对BEGIN … END声明处理程序的复合语句执行终止 。即使条件发生在内部块中也是如此。
    • UNDO： 不支持。

  while

使用示例：

create definer = root@`%` procedure update_circle_recommend_procedure() BEGIN #定义参数 DECLARE args varchar(35); #声明游标 DECLARE circle_info CURSOR FOR select cloumn1 , ... from table_name where ...; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; #开启游标 open circle_info; #定义循环 read_loop read_loop : LOOP FETCH circle_info into args; IF done THEN LEAVE read_loop; END IF; if updateFlag >= 1 then update ... else insert into ... end if; END LOOP read_loop; #释放游标 CLOSE circle_info; END 

常用系统存储过程

未完待续…

参考文章：

官方手册：https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

https://blog.csdn.net/zsx/article/details/?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultbaidujs_baidulandingword~default-0.control&spm=1001.2101.3001.4242

https://blog.csdn.net/wuxinwudai/article/details/

https://blog.csdn.net/hatwn/article/details/

https://blog.csdn.net/duduyingya/article/details/