表 m_sp 中一个字段sp_id,关联另一个m_sp_k 表,B表是后期加的,导致m_sp_k 表中没有关联A表的历史数据,如下存储过程便是解决所述问题。
查询结果循环遍历,结果值作为新的参数插入新表中。
create procedure proc_tmp()
BEGIN
declare done int default 0;
declare relationId VARCHAR(32);
declare idCur cursor for select A.sp_id from m_sp as A;
declare continue handler for not FOUND set done = 1;
open idCur;
/* 循环开始 */
REPEAT
fetch idCur into relationId;
if not done THEN
insert into m_sp_k(s_k_id, sp_id, s_k_key, s_k_sec, s_k_time, s_k_sta, s_k_tp)
values(REPLACE (UUID(), '-', ''), relationId, REPLACE (UUID(), '-', ''), REPLACE (UUID(), '-', ''), NOW(), 0, 0);
end if;
until done end repeat;
close idCur;
END