前言
大家好,我是小郭,今天我们来学习存储过程,在几年前的系统中存储过程随处可见,他的优势就是提高数据库的安全性和数据的完整性,我们可以利用存储过程完成一系列的流程而无需在代码中反复跳转,但是他的缺点也暴露了出来,开发与维护的难度时间越长难度越大,我们来看看他的神奇之处。
一张图了解存储过程的内容
1. 什么是存储过程?
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时,只需调用“CALL存储过程名字”即可自动完成。
2. 存储过程的优势与劣势
优势
-
封装性
-
可增强 SQL 语句的功能和灵活性
-
可减少网络流量
-
高性能
-
提高数据库的安全性和数据的完整性
-
使数据独立
劣势
-
难以调试存储过程
-
开发与维护不容易
-
可移植性差
3. 含义
声明存储过程,是我们书写内部实现的入口,它就是一个我们封装起来的方法,写完调用就可以了。
// 声明存储过程
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE ccc ([proc_parameter[,...]])
[characteristic ...] routine_body
//IN入参 OUT出参 param_name参数名称 type参数类型
proc_parameter:
[ IN | OUT | INOUT ] param_name type
// 执行存储过程特性
characteristic:
COMMENT 'string'
| LANGUAGE SQL //Sql语句组成
| [NOT] DETERMINISTIC //NOT 结果不确定,相同输入可能得到不同输出
// CONTAINS SQL 子程序包含SQL语句
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
//存储过程开始和结束符号
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
实战
实战的代码也很简单,主要就是嵌套的循环,这其中也猜了许多的坑。
为了方便看内容是否正确,可以通过select xx来输出变量结果。
create
procedure update_xxx(IN Id varchar(50))
begin
DECLARE code varchar(40);
DECLARE done boolean default false;
DECLARE edone boolean default false;
/*查询企业code*/
DECLARE list CURSOR FOR select ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
open list;
posLoop:LOOP
fetch list into medinsCode;
select medinsCode;
if (done) then
leave posLoop;
else
set edone = false;
begin
DECLARE specialList CURSOR FOR select ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = true;
open specialList;
specialLoop:LOOP
fetch specialList into xxx;
if edone then
LEAVE specialLoop;
elseif (pubonlnStas = 1 and minPricStas = 0) then
update ...
elseif (pubonlnStas = 1 and minPricStas = 1) then
update ...
elseif (pubonlnStas = 0) then
update ...
end if;
end LOOP specialLoop;
close specialList;
end;
begin
DECLARE otherList CURSOR FOR select ...;
SET done = false;
open otherList;
otherLoop:LOOP
fetch otherList into xxx;
if done then
LEAVE otherLoop;
elseif (pubonlnStas = 1) then
update ...
elseif (pubonlnStas = 0) then
update ...
end if;
end LOOP otherLoop;
close otherList;
end;
update ...
end if;
end LOOP posLoop;
close list;
end;
4. 实战踩坑处理
- [24000][1326] Cursor is not open 错误原因:游标未打开
specialLoop:LOOP
fetch specialList into rpupMcsRsltAsgnId
open specialList;
...
解决:要注意游标的顺序,先开启游标在进行循环
open specialList;
specialLoop:LOOP
fetch specialList into rpupMcsRsltAsgnId
5. 总结
写到这里想必大家已经对存储过程有了了解,我在这里主要介绍了存储过程的概念和实战,在某些场景下,存储过程为我们提供了许多的遍历,但我们也看到他缺点也是很大的问题,是我们在实战的时候需要特别处理的。