1.insert_num
将查询结果循环,并传入到下一个存储过程
-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除
DROP PROCEDURE IF EXISTS insert_num;
-- 创建存储过程
CREATE PROCEDURE insert_num()
BEGIN
-- 定义变量
DECLARE s INT DEFAULT 0;
--注意定义的变量最好不要和表中的一样,避免一些问题
DECLARE merchantId VARCHAR ( 32 );
DECLARE cardTemplateId VARCHAR ( 32 );
DECLARE orderId VARCHAR ( 32 );
DECLARE parentId VARCHAR ( 32 );
DECLARE orderNum VARCHAR ( 32 );
DECLARE productIds text;
-- 定义游标,并将sql结果集赋值到游标中
DECLARE report CURSOR FOR
SELECT merchant_id,
card_template_id,
CONCAT(card_order_id, '1'),
card_order_id parent_id,
order_num,
product_ids
FROM oms_order_card
WHERE oms_order_card.del_flag = 1
AND oms_order_card.parent_id = '0'
AND oms_order_card.card_template_id != '';
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE
CONTINUE HANDLER FOR NOT FOUND SET s = 1;
-- 打开游标
OPEN report;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
FETCH report INTO merchantId, cardTemplateId, orderId, parentId, orderNum, productIds;
-- 当s不等于1,也就是未遍历完时,会一直循环
WHILE
s <> 1 DO
-- 执行业务逻辑
-- SELECT merchantId, cardTemplateId, orderId, parentId, orderNum, productIds;
-- 执行存储过程 insert_num_handle
CALL insert_num_handle(merchantId, cardTemplateId, orderId, parentId, orderNum, productIds);
-- 将游标中的值再赋值给变量,供下次循环使用
FETCH report INTO merchantId, cardTemplateId, orderId, parentId, orderNum, productIds;
-- 当s等于1时表明遍历以完成,退出循环
END WHILE;
-- 关闭游标
CLOSE report;
END;
2.insert_num_handle
处理 productIds 字段,根据”,”分割,并传入到下一个存储过程
-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除
DROP PROCEDURE IF EXISTS insert_num_handle;
-- 创建存储过程
CREATE PROCEDURE insert_num_handle(IN merchantId VARCHAR ( 32 ), IN cardTemplateId VARCHAR (32),
IN orderId VARCHAR (32), IN parentId VARCHAR (32), IN orderNum VARCHAR (32),
IN productIds text)
BEGIN
-- 定义变量
DECLARE s INT DEFAULT 0;
DECLARE numScop VARCHAR ( 1024 );
-- 定义游标,并将sql结果集赋值到游标中
DECLARE report CURSOR FOR
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(productIds, ',', help_topic_id + 1), ',', - 1) AS num_scop
FROM mysql.help_topic
WHERE help_topic_id < (LENGTH(productIds) - LENGTH(REPLACE(productIds, ',', '')) + 1);
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE
CONTINUE HANDLER FOR NOT FOUND SET s = 1;
-- 打开游标
OPEN report;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
FETCH report INTO numScop;
-- 当s不等于1,也就是未遍历完时,会一直循环
WHILE
s <> 1 DO
-- 执行业务逻辑
-- SELECT merchantId, cardTemplateId, orderId, parentId, orderNum, productIds, SUBSTRING_INDEX( numScop, '-', 1 ) result;
-- 执存储过程insert_num_exec
CALL insert_num_exec(merchantId, cardTemplateId, orderId, parentId, orderNum, productIds);
-- 将游标中的值再赋值给变量,供下次循环使用
FETCH report INTO numScop;
-- 当s等于1时表明遍历以完成,退出循环
END WHILE;
-- 关闭游标
CLOSE report;
END;
3.insert_num_exec
INSERT INTO
-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除
DROP PROCEDURE IF EXISTS insert_num_exec;
-- 创建存储过程
CREATE PROCEDURE insert_num_exec(IN merchantId VARCHAR ( 32 ), IN cardTemplateId VARCHAR (32), IN orderId VARCHAR (32),
IN parentId VARCHAR (32), IN orderNum VARCHAR (32), IN numScop VARCHAR ( 1024 ))
BEGIN
INSERT INTO oms_order_gift_card_num_record
SELECT UUID_SHORT() id,
merchantId merchant_id,
orderId order_id,
parentId parent_id,
orderNum order_num,
2 `type`,
cardTemplateId card_template_id,
sys_card_serial_num,
card_num
FROM cpm_merchant_gift_card
WHERE del_flag = 1
AND merchant_id = merchantId
AND card_template_id = cardTemplateId
AND card_serial_num >= RIGHT ( SUBSTRING_INDEX( numScop
, '-'
, 1 )
, 8 ) + 0
AND card_serial_num <= RIGHT ( SUBSTRING_INDEX( numScop
, '-'
, - 1 )
, 8 ) + 0;
END;