mysql 根据select结果进行循环操作，并写入临时表

数据库中有一个表：如下

SELECT id, descs FROM table_learn WHERE id>0 and id<4;

结果如下：

下面是一个demo，可以直接按照这个结果循环操作：

delimiter //
drop procedure if exists sp_name //
create procedure sp_name()
begin
DECLARE done INT DEFAULT FALSE;
DECLARE v_id int;
DECLARE v_descs varchar(100);

#declare cursor
DECLARE cur1 CURSOR FOR SELECT id, descs
FROM table_learn
WHERE id>0 and id<4;

#declare handle
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

drop table if exists tmp_table;
create temporary table tmp_table(id int, descs varchar(100));

#open cursor
OPEN cur1;

#starts the loop
the_loop: LOOP

#get the values of each column into our variables
FETCH cur1 INTO v_id,v_descs;
IF done THEN
LEAVE the_loop;
END IF;

#Do some post processing
IF v_id IS NOT NULL THEN
SET v_descs = "haha";
END IF;

#Insert it
INSERT INTO tmp_table (id, descs)
VALUES (v_id, v_descs);

END LOOP the_loop;

CLOSE cur1;
select * from tmp_table;
end;

//
delimiter ;
call sp_name();

更多相关文章

随机推荐