MySql CURSOR+LOOP循环-使用小实例

来源：互联网发布：vscode js代码提示编辑：程序博客网时间：2024/06/07 18:07

实例说明

有两个表stu_info、stu_info_bak，表结构完全一致，建表脚本如下：

CREATE TABLE `stu_info` (  `stuId` varchar(20) NOT NULL COMMENT '学号',  `stuName` varchar(20) NOT NULL COMMENT '姓名',  `phone` varchar(11) DEFAULT NULL COMMENT '手机号码',  `idNumber` varchar(20) DEFAULT NULL COMMENT '身份证号码',  PRIMARY KEY (`stuId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息';

CREATE TABLE `stu_info_bak` (  `stuId` varchar(20) NOT NULL COMMENT '学号',  `stuName` varchar(20) NOT NULL COMMENT '姓名',  `phone` varchar(11) DEFAULT NULL COMMENT '手机号码',  `idNumber` varchar(20) DEFAULT NULL COMMENT '身份证号码',  PRIMARY KEY (`stuId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息备份';

stu_info表中有几条数据，stu_info_bak中没有数据，现在想要把stu_info中的数据全部备份到stu_info_bak表中。

处理脚本

delimiter $$drop procedure if exists `proc_copy_stu_info` $$CREATE PROCEDURE `proc_copy_stu_info`()BEGIN    DECLARE done INT DEFAULT FALSE;    DECLARE v_stuId VARCHAR(20);    DECLARE v_stuName VARCHAR(20);    DECLARE v_phone VARCHAR(11);    DECLARE v_idNumber VARCHAR(20);    DECLARE cur CURSOR FOR SELECT stuId,stuName,phone,idNumber FROM stu_info;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;    OPEN cur;    read_loop : LOOP        FETCH cur INTO v_stuId,v_stuName,v_phone,v_idNumber;        IF done THEN            LEAVE read_loop;        END IF;        SET @sql_insert = CONCAT("insert into stu_info_bak(stuId,stuName,phone,idNumber) VALUES ('",v_stuId,"','",v_stuName,"','",v_phone,"','",v_idNumber,"')");        PREPARE sqlli FROM @sql_insert;            EXECUTE  sqlli;        COMMIT;    END LOOP read_loop;    CLOSE cur;END$$delimiter;call `proc_copy_stu_info`();commit;

阅读全文

1 0