SQL系列——存储过程
存储过程存储过程基础?什么是存储过程?优点执行效率很高降低网络通信量代码复用安全性?缺点移植性非常差代码可读性差?用途造测试数据数据同步数据挖掘?注意事项可能遇到的问题:使用语法(Mysql)基础用法创建删除调用参数游标的使用基本用法3中循环方式 while, repeat, loop,handler使用示例:常用系统存储过程存储过程基础?什么是存储过程就是写在SQL中的方法,用于完成特定功能的S
存储过程
存储过程基础
?什么是存储过程
就是写在SQL中的方法,用于完成特定功能的SQL语句集合。
?优点
存储过程是预编译的,即创建时编译,而SQL语句是执行一次,编译一次。调用存储过程可以大大减少同数据库的交互次数。
-
降低网络通信量
存储过程执行的时候,只需要call存储过程,而存储过程是sql语句的集合,减少了与数据库的连接次数,降低了数据库的压力。
存储过程就是公用的sql方法。
-
安全性
参数化的存储过程可以防止SQL注入式的攻击,而且可以将Grant、Deny以及Revoke权限应用于存储过程。
?缺点
多平台不兼容(Mysql\Oracle)
实现一个简单的逻辑,代码会非常长。
?用途
可以使用存储过程,往表里造几百万条数据。
两个表之间按照一定的业务逻辑进行数据同步。
?注意事项
数据量大的时候(10万+),一定要做压力测试,有些存储过程在大数据量的情况下才会出现问题。
如果插入或者更新的次数比较多,为了提高效率,可以执行一万次,再commit一次。
如果先插入记录,没有commit,再对这条记录进行更新,会引起死锁。如果先后对同一笔记录进行更新,又没有commit,也会引起死锁。因为后一条语句会等待前一条语句提交。如果出现这种情况,则需要一条条commit。
不要忘记在存储过程里写commit。
可能遇到的问题:
使用语法(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
-
while
#开启游标 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_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
: 不支持。
CONTINUE 与 EXIT 对比
CONTINUE: 发送错误时继续执行后续代码
EXIT: 发生错误时退出当前代码块(可能是子代码块或者main代码块)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
: 不支持。
-
使用示例:
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
常用系统存储过程
系统存储过程,由系统定义,存放在master数据库中,名称以“sp_”开头或”xp_”开头
自定义存储过程,由用户在自己的数据库中创建的存储过程,以”usp_”开头
名称 | 作用 |
---|---|
sp_attach_db | 将数据库附加到服务器上 |
sp_attach_single_file_db | 将只有一个数据文件的数据库附加到当前服务器上 |
sp_changedbowner | 更改当前数据库所有者 |
sp_changeobjectowner | 更改当前数据库中对象的所有者 |
sp_column_privileges | 返回当前环境中单个表的列的特权信息 |
sp_help | 报告有关数据库对象、用户定义数据类型或SQL Server 提供的数据类型的信息 |
sp_helptext | 显示用户定义的默认值,未加密的T-SQL存储过程、用户定义的T-SQL 函数、触发器、计算列、CHECK约束、视图或系统对象 |
sp_rename | 更改用户创建对象的名称、如表、索引、列、别名数据类型 |
sp_renamedb | 更改数据库名称 |
sp_tables | 显示可以在 SELECT 查询语句的FROM 子句中的对象 |
sp_stored_procedures | 列出所有可用的存储过程 |
sp_password | 修改而且只能修改标准登录的密码 |
sp_who | 查看当前用户、会话和进程信息 |
sp_monitor | 显示有关 SQL Server 的统计信息 |
sp_depends | 查看详细信息 |
未完待续…
参考文章:
官方手册:https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html
https://blog.csdn.net/zsx157326/article/details/52197949?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-0.control&spm=1001.2101.3001.4242
https://blog.csdn.net/wuxinwudai/article/details/54999071
https://blog.csdn.net/hatwn/article/details/84714179
https://blog.csdn.net/duduyingya/article/details/119568713
更多推荐
所有评论(0)