CREATE DEFINER=`mycommcrm`@`%` PROCEDURE `insert_dispatch_contract_setp2`(in beginDate varchar(30))
BEGIN
-- 合同号
declare contractCode varchar(100) default '';
-- 开始时间
DECLARE start_time datetime DEFAULT now();
-- 遍历数据结束标志
declare ifEnd int DEFAULT false;
DECLARE jjl_contract_curs cursor for (
-- 查询新增数据
select contract_code from jjl_dispatch_contract where update_date >= STR_TO_DATE(beginDate,'%Y-%m-%d %H:%i:%s')
);
-- 将结束标志绑定到游标
declare CONTINUE HANDLER FOR not found SET ifEnd = true;
-- 打开游标
open jjl_contract_curs;
-- 循环
read_loop: LOOP
-- 提取游标数据
fetch jjl_contract_curs into contractCode;
-- 判断游标是否结束
IF ifEnd THEN
LEAVE read_loop;
END IF;
-- 主收费代码-临时变量赋值(业务逻辑)
SELECT @primaryPayProject:=pay_serial_name FROM jjl_contract_pay_code
WHERE contract_code = contractCode A
-- 更新表
update jjl_dispatch_contract
set
primary_pay_project = @primaryPayProject,
update_date = now()
where contract_code = contractCode;
-- 关闭循环
end Loop;
-- 关闭游标
close jjl_contract_curs;
END
Mysql存储过程-游标遍历中子查询的临时变量赋值
最新推荐文章于 2024-07-03 03:27:16 发布