MySQL循环处理查询结果
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;
参考:
