CREATE DEFINER=`admin`@`%` PROCEDURE `pro_handleBilling`(IN `beginTime` varchar(100),IN `endTime` varchar(100),OUT `out_msg` varchar(5000))
BEGIN
-- 定义变量
DECLARE found int default true;
DECLARE CA BIGINT;
DECLARE CB DECIMAL(20,2);
DECLARE msgRtn VARCHAR(5000) DEFAULT '';
DECLARE t_error INT DEFAULT 0;-- 事务回滚标志
DECLARE frozenRecords CURSOR FOR SELECT a,b from table_a where create_time>=beginTime and create_time<endTime;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET found=false;-- 异常机制
open frozenRecords;-- 打开游标
-- 此处可处理sql操作
loop_record: LOOP
FETCH frozenRecords INTO CA,CB; -- 将查出的字段匹配到声明的字段中
IF found=false then LEAVE loop_record; end if;-- 跳出循环
-- 开启事务
START TRANSACTION;
-- 事务内的sql
if(t_error=1) then set msgRtn = CONCAT(msgRtn,'Error!'); ROLLBACK;else commit; end if;
END LOOP;
IF msgRtn='' then set msgRtn='成功'; end if;
SET out_msg=msgRtn;
SELECT out_msg;
CLOSE frozenRecords; -- 关闭游标
END
BEGIN
-- 定义变量
DECLARE found int default true;
DECLARE CA BIGINT;
DECLARE CB DECIMAL(20,2);
DECLARE msgRtn VARCHAR(5000) DEFAULT '';
DECLARE t_error INT DEFAULT 0;-- 事务回滚标志
DECLARE frozenRecords CURSOR FOR SELECT a,b from table_a where create_time>=beginTime and create_time<endTime;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET found=false;-- 异常机制
open frozenRecords;-- 打开游标
-- 此处可处理sql操作
loop_record: LOOP
FETCH frozenRecords INTO CA,CB; -- 将查出的字段匹配到声明的字段中
IF found=false then LEAVE loop_record; end if;-- 跳出循环
-- 开启事务
START TRANSACTION;
-- 事务内的sql
if(t_error=1) then set msgRtn = CONCAT(msgRtn,'Error!'); ROLLBACK;else commit; end if;
END LOOP;
IF msgRtn='' then set msgRtn='成功'; end if;
SET out_msg=msgRtn;
SELECT out_msg;
CLOSE frozenRecords; -- 关闭游标
END