CREATE PROCEDURE `test`.`new_procedure` ()
BEGIN
-- 需要定义接收游标数据的变量
DECLARE a CHAR(16);
-- 游标
DECLARE cur CURSOR FOR SELECT i FROM test.t;
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
-- 开始循环
read_loop: LOOP
-- 提取游标里的数据,这里只有一个,多个的话也一样;
FETCH cur INTO a;-- 抓取FETCH的时候,如果cur为null(SELECT i FROM test.t),则会触发SET done = TRUE
-- 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件
INSERT INTO test.t VALUES (a);
END LOOP;
-- 关闭游标
CLOSE cur;
END
在MySQL的存储过程中经常会看到这句话:DECLARE CONTINUE HANDLER FOR NOT FOUND。
它的含义是:若没有数据返回,程序继续,并将变量IS_FOUND设为0 ,这种情况是出现在select XX into XXX from tablename的时候发生的。
-- start by lxm
DROP PROCEDURE IF EXISTS loan_contract_no_rule_init;
CREATE PROCEDURE loan_contract_no_rule_init()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ruleId VARCHAR(32) DEFAULT '1528655117129572354';
DECLARE rule_detail_id_insert VARCHAR(32);
DECLARE rule_vale_insert VARCHAR(512);
-- 定义游标
DECLARE cur CURSOR FOR
SELECT rule_detail_id as rule_detail_id_insert,rule_vale as rule_vale_insert FROM trade_rule_detail_h WHERE rule_key = 'ATTACHMENT_TYPE' AND rule_detail_id IN (SELECT rule_detail_id FROM ent_trade_rule_info t WHERE
1 = 1 AND trade_code = 'REGISTER' AND STATUS = 1 AND prod_id = 'C01P00001' AND institution_id IN ( SELECT ent_id FROM prod_cif.cif_ent_type_ext WHERE ent_type = 9 AND prod_type = '01' ));
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 打开游标
OPEN cur;
-- 提取游标里的数据;
FETCH cur INTO rule_detail_id_insert,rule_vale_insert;
WHILE done<>1 DO
-- 历史快照插入明细
IF (rule_vale_insert = 'SELL_PROTOCOL') THEN
-- 融单协议 >初始化> 融单合同编号
INSERT INTO trade_rule_detail_h(id,rule_detail_id,rule_id,rule_key,rule_status,rule_vale)VALUES
(REPLACE(UUID(),'-',''),rule_detail_id_insert,ruleId,'LOAN_CONTRACT_NO',1,'LOAN_CONTRACT_NO');
ELSEIF (rule_vale_insert = 'LAST_TRADE_BACK') THEN
-- 最后一手贸易背景材料 > 初始化为 > 贸易合同编号
INSERT INTO trade_rule_detail_h(id,rule_detail_id,rule_id,rule_key,rule_status,rule_vale)VALUES
(REPLACE(UUID(),'-',''),rule_detail_id_insert,ruleId,'LOAN_CONTRACT_NO',1,'BACKGROUND_CONTRACT_NO');
ELSEIF (rule_vale_insert ='SELL_PROTOCOL,LAST_TRADE_BACK') THEN
-- 选择了融单协议和最后一手贸易背景材料 > 初始化 > 融单合同编号
INSERT INTO trade_rule_detail_h(id,rule_detail_id,rule_id,rule_key,rule_status,rule_vale)VALUES
(REPLACE(UUID(),'-',''),rule_detail_id_insert,ruleId,'LOAN_CONTRACT_NO',1,'LOAN_CONTRACT_NO');
ELSE
-- 其他无
INSERT INTO trade_rule_detail_h(id,rule_detail_id,rule_id,rule_key,rule_status,rule_vale)VALUES
(REPLACE(UUID(),'-',''),rule_detail_id_insert,ruleId,'LOAN_CONTRACT_NO',1,'');
END IF;
FETCH cur INTO rule_detail_id_insert,rule_vale_insert;
END WHILE;
CLOSE cur;
END;
CALL loan_contract_no_rule_init();
-- end by lxm