一、存储过程概念
什么是存储过程
一组为了完成特定功能的SQL 语句集。
更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。优点
存储过程是一个预编译的代码块，执行效率比较高
一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
可以一定程度上确保数据安全（可设定只有某些用户才具有对指定存储过程的使用权）
缺点
如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。
可移植性差
很多存储过程不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑框架。
代码可读性差，相当难维护。
要不要用存储过程
个人觉得各组件应该各司其职。MySQL就是存储数据的仓库，逻辑实现还是应该放在业务层。
建议看看别人说的。为什么要用存储过程
二、存储过程使用
存储过程的创建语法：
DELIMITER //
CREATE PROCEDURE 储存过程名([in|out|inout] 参数 datatype)
BEGIN
SQL语句代码快
END
//
DELIMITER ;
几点说明：
- 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”
- begin end 可以在只有一条sql语句的时候省略。
- 每条语句的末尾，都要加上分号 “;”
- 不能在存储过程中使用 “return” 关键字。
- DELIMITER 的意思是，告诉mysql，下面的语句中，语句定界符不再是分号（;），而是双斜线（//）。这里的双斜线可以任意指定，比如指定为|或者///，都是可以的（但不要指定为sql语句中经常出现的逗号或等号等，不然就结束了），如：
mysql> delimiter //
mysql> create procedure simpleproc (out param1 int)
-> begin
-> select count(*) into param1 from t;
-> end
-> //
Query OK, 0 rows affected
mysql> delimiter |
mysql> create procedure simpleproc1 (out param1 int)
-> begin
-> select count(*) into param1 from t;
-> end
-> |
Query OK, 0 rows affected
mysql> delimiter ///
mysql> create procedure simpleproc2 (out param1 int)
-> begin
-> select count(*) into param1 from t;
-> end
-> ///
Query OK, 0 rows affected
还要注意，定义完存储过程之后，要重新将分号（;）指定为语义分隔符。即调用DELIMITER ;
关于参数
in表示向存储过程传递参数，out表示从存储过程返回参数，而inout表示传递参数和返回参数；如果不显式指定in、out、inout，则默认为in。习惯上，对于是in的参数，我们都不会显式指定；
参数只能指定参数类型，不能指定长度；
参数不能指定默认值。
关于注释
/*
这是
多行
注释
*/
declare a int; -- 这是单行 MySQL 注释 （注意 -- 后至少要有一个空格）
if a is null then set a = 0; # 这也是个单行 MySQL 注释
查看已经创建的存储过程：
show procedure status where Db='数据库名';或者：先use 数据库名，再show procedure status
mysql> show procedure status where Db='cpgl';
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| cpgl | hi | PROCEDURE | root@localhost | 2017-05-30 11:39:44 | 2017-05-30 11:39:44 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| cpgl | pr_add | PROCEDURE | root@localhost | 2017-05-30 11:41:42 | 2017-05-30 11:41:42 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set
删除存储过程
drop procedure '存储过程名字';
mysql> drop procedure hi;
Query OK, 0 rows affected
实例
从网上找了几个例子，真正用到的时候，可以参考：包含了事务，参数，嵌套调用，游标，循环等使用
例子1
drop procedure if exists pro_rep_shadow_rs;
delimiter |
----------------------------------
-- rep_shadow_rs
-- 用来处理信息的增加，更新和删除
-- 每次只更新上次以来没有做过的数据
-- 根据不同的标志位
-- 需要一个输出的参数，
-- 如果返回为0，则调用失败，事务回滚
-- 如果返回为1，调用成功，事务提交
--
-- 测试方法
-- call pro_rep_shadow_rs(@rtn);
-- select @rtn;
----------------------------------
create procedure pro_rep_shadow_rs(out rtn int)
begin
-- 声明变量，所有的声明必须在非声明的语句前面
declare iLast_rep_sync_id int default -1;
declare iMax_rep_sync_id int default -1;
-- 如果出现异常，或自动处理并rollback,但不再通知调用方了
-- 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句全部去掉
declare exit handler for sqlexception rollback;
-- 查找上一次的
select eid into iLast_rep_sync_id from rep_de_proc_log where tbl='rep_shadow_rs';
-- 如果不存在，则增加一行
if iLast_rep_sync_id=-1 then
insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');
set iLast_rep_sync_id = 0;
end if;
-- 下一个数字
set iLast_rep_sync_id=iLast_rep_sync_id+1;
-- 设置默认的返回值为0：失败
set rtn=0;
-- 启动事务
start transaction;
-- 查找最大编号
select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;
-- 有新数据
if iMax_rep_sync_id>=iLast_rep_sync_id then
-- 调用
call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);
-- 更新日志
update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';
end if;
-- 运行没有异常，提交事务
commit;
-- 设置返回值为1
set rtn=1;
end;
|
delimiter ;
例子2
drop procedure if exists pro_rep_shadow_rs_do;
delimiter |
---------------------------------
-- 处理指定编号范围内的数据
-- 需要输入2个参数
-- last_rep_sync_id 是编号的最小值
-- max_rep_sync_id 是编号的最大值
-- 无返回值
---------------------------------
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)
begin
declare iRep_operationtype varchar(1);
declare iRep_status varchar(1);
declare iRep_Sync_id int;
declare iId int;
-- 这个用于处理游标到达最后一行的情况
declare stop int default 0;
-- 声明游标
declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;
-- 声明游标的异常处理，设置一个终止标记
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
-- 打开游标
open cur;
-- 读取一行数据到变量
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;
-- 这个就是判断是否游标已经到达了最后
while stop <> 1 do
-- 各种判断
if iRep_operationtype='I' then
insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
elseif iRep_operationtype='U' then
begin
if iRep_status='A' then
insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
elseif iRep_status='B' then
delete from rs0811 where id=iId;
end if;
end;
elseif iRep_operationtype='D' then
delete from rs0811 where id=iId;
end if;
-- 读取下一行的数据
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;
end while; -- 循环结束
close cur; -- 关闭游标
end;
|
delimiter ;
关于游标定义和使用，可以参考：MySql存储过程—游标(Cursor)
游标循环次数不对的问题
在使用游标的时候，最容易出现的问题就是循环次数与实际记录数不一：第一种就是循环次数比实际记录数多一次；第二种情况就是循环次数远小于记录数。
第一种情况的发生，是由于对while do结构（或者有时候使用repeat）以及cursor的性质认识不够导致的，cursor会在找不到记录时（一般是循环完了最后一行，也有可能是循环时的select into 赋值语句结果为空导致）发生NOT FOUND，可以根据这一点来判断循环结束。而while do 结构类似于java中的do while 结构，无论怎样都会先do再判断。上面例子2中的写法可以很好的解决这个问题：在每次循环结尾，执行赋值操作，相当于让cursor的判断提前一步。
第二种情况的发生，一般是select into 赋值语句结果为空（有待研究）。存储过程如何调试
我使用的有两种：
第一种是在call调用存储过程之前，定义全局变量，如SET @test=0；在存储过程的函数体中的某个地方，为该变量赋值，如SET @test=1；在调用完存储过程之后，使用select @test的方式查看之前定义的@test的值。如果存储过程写的没问题，执行到了你写SET @test=1；的地方，则值为1，否则还是初始值0。
第二种则是，在函数体中，使用select var1,var2...这种方式：var1,var2...是你在其中定义的变量，你想在某处查看它们的值是否与你期望的一样，就在该处写上select var1,var2...语句。这样，在控制台调用存储过程时，就会打印出这样变量的值。
下面的存储过程可供参考：
CREATE PROCEDURE copy_nfi_item(nfi_id_from varchar(100), nfi_id_to varchar(100))
COMMENT '复制nfi考核，与业务无关；如果两个部门的nfi考核项相同，可以通过该存储过程快捷复制。'
BEGIN
DECLARE v_item_id varchar(50);
DECLARE v_percent float(11,0);
DECLARE v_first_level_duty varchar(255);
DECLARE v_first_level_duty_weight float(11,0);
DECLARE v_title varchar(255);
DECLARE v_task text;
DECLARE v_task_weight float(11,0);
DECLARE v_plan_begin_date date;
DECLARE v_plan_end_date date;
DECLARE v_complete_date date;
DECLARE v_results text;
DECLARE v_scoring_standard text;
DECLARE v_results_desc text;
DECLARE v_provide_dept varchar(255);
DECLARE v_check_dept varchar(255);
DECLARE v_self_marks float(11,0);
DECLARE v_self_score float(11,2);
DECLARE v_dept_marks float(11,0);
DECLARE v_dept_score float(11,2);
DECLARE v_nonfinancial_score float(11,2);
DECLARE v_president_score float(11,2);
DECLARE v_review_marks float(11,2);
DECLARE v_review_score float(11,2);
DECLARE v_advice text;
DECLARE v_nfi_id varchar(50);
DECLARE v_executor_id varchar(50);
DECLARE v_status int(11);
DECLARE v_step int(11);
DECLARE v_dr bit(1);
DECLARE v_ts datetime;
DECLARE v_improvements text;
DECLARE v_update_ts varchar(50);
DECLARE temp_dept_id_from VARCHAR(200);
DECLARE temp_dept_id_to VARCHAR(200);
DECLARE v_count int(10) DEFAULT 0;
-- 这个用于处理游标到达最后一行的情况
DECLARE stopFlag int default 0;
-- 声明游标：指向数据源
DECLARE cur CURSOR FOR SELECT item.item_id,item.percent,item.first_level_duty,item.first_level_duty_weight,item.title,item.task,item.task_weight,item.plan_begin_date,item.plan_end_date,
item.complete_date,item.results,item.scoring_standard,item.results_desc,item.provide_dept,item.check_dept,item.self_marks,item.self_score,item.dept_marks,item.dept_score,item.review_marks,item.review_score,
item.advice,item.nfi_id,item.status,item.step,item.dr,item.ts,item.executor_id,item.nonfinancial_score,item.president_score,item.improvements FROM nfi_item item WHERE item.nfi_id = nfi_id_from;
-- 声明游标的异常处理，设置一个终止标记
-- 另一种写法：DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;
-- 打开游标
OPEN cur;
/**
使用游标的一个常见问题就是循环次数不对。这样可以正确。
mysql的while与java里的类似，会先进入其中，do之后，再判断。
在循环体的最后使用fetch，是为了让游标提前更进一步，使得stopFlag=1。
*/
-- 读取一行数据到变量
FETCH cur INTO v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements;
-- 判断是否游标已经到达了最后
WHILE stopFlag <> 1 DO
SET v_count = v_count + 1;
-- 测试用，这样的语法会在控制台直接输出变量的值
SELECT v_item_id,v_percent, temp_dept_id_from, temp_dept_id_to, v_count;
-- 几个特殊的变量重新赋值:使用SELECT 表中字段名 INTO 变量名的方式
SELECT n.dept_id INTO temp_dept_id_from FROM nfi n WHERE n.nfi_id = nfi_id_from;
SELECT n.dept_id INTO temp_dept_id_to FROM nfi n WHERE n.nfi_id = nfi_id_to;
IF temp_dept_id_from = v_provide_dept THEN SET v_provide_dept = temp_dept_id_to;
END IF;
SET v_item_id = CONCAT(SUBSTR(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),3),SUBSTR(UUID(),1,8));
SET v_nfi_id = nfi_id_to;
SELECT n.psn_code INTO v_executor_id FROM nfi n WHERE n.nfi_id = nfi_id_to;
-- 执行插入
INSERT INTO nfi_item
(item_id,percent,first_level_duty,first_level_duty_weight,title,task,task_weight,plan_begin_date,plan_end_date,complete_date,
results,scoring_standard,results_desc,provide_dept,check_dept,self_marks,self_score,dept_marks,dept_score,review_marks,review_score,
advice,nfi_id,status,step,dr,ts,executor_id,nonfinancial_score,president_score,improvements)
VALUES(
v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements);
-- 读取一行数据到变量
FETCH cur INTO v_item_id,v_percent,v_first_level_duty,v_first_level_duty_weight,v_title,v_task,v_task_weight,v_plan_begin_date,v_plan_end_date,v_complete_date,v_results,
v_scoring_standard,v_results_desc,v_provide_dept,v_check_dept,v_self_marks,v_self_score,v_dept_marks,v_dept_score,v_review_marks,v_review_score,v_advice,v_nfi_id,v_status,v_step,
v_dr,v_ts,v_executor_id,v_nonfinancial_score,v_president_score,v_improvements;
END WHILE; -- 循环结束
CLOSE cur; -- 关闭游标
END
