1.创建表 t_bank
create table `t_bank` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`bank_name` varchar(50) DEFAULT '' COMMENT '银行名称',
`branch_name` varchar(50) DEFAULT '' COMMENT '支行名称',
`valid` int(1) DEFAULT '1' COMMENT '1.有效,0.无效',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='数据表';
2.创建备份表 t_bank_copy
created table `t_bank_copy` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`bank_name` varchar(50) DEFAULT '' COMMENT '银行名称',
`branch_name` varchar(50) DEFAULT '' COMMENT '支行名称',
`valid` int(1) DEFAULT '1' COMMENT '1.有效,0.无效',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='复制表';
3.创建过程 createdBankData
CREATE PROCEDURE `createdBankData`()
begin
//临时变量
DECLARE temp_bank_name varchar(50);
DECLARE temp_branch_name varchar(50);
DECLARE i int(100) DEFAULT 0;
//循环体
loop_one: loop
//统计数据跳出循环
set i = i+1;
SET temp_bank_name = concat('银行',i);
SET temp_branch_name = concat('分公司',i);
//插入语句
insert into t_bank (bank_name,branch_name,valid)
values (temp_bank_name,temp_branch_name,'1');
if i = 10 then
leave loop_one;
end if;
end loop;
// 存储过程之间的调用,存储过程中调用另一个过程
// call copyBankData();
end
4.创建过程 copyBankData
CREATE PROCEDURE `copyBankData`()
BEGIN
declare temp_bank_name varchar(50) DEFAULT '';
declare temp_branch_name varchar(50) DEFAULT '';
//定义标识位
declare done int default 0;
//定义游标
declare bank_list CURSOR for select bank_name,branch_name from t_bank;
// 若没有数据返回,程序继续并将done设置为1
declare continue handler for not found set done = 1;
open bank_list;
// 方式1,使用whlie进行循环遍历
while done != 1 do
fetch bank_list into temp_bank_name,temp_branch_name;
if done != 1 then
insert into t_bank_copy (bank_name,branch_name,valid)
values (temp_bank_name,temp_branch_name,'1');
end if;
end while;
// 方式2,使用repeat进行循环遍历
repeat
fetch bank_list into temp_bank_name,temp_branch_name;
if done != 1 then
insert into t_bank_copy (bank_name,branch_name,valid)
values (temp_bank_name,temp_branch_name,'1');
end if;
until done = 1
end repeat;
//方式3,loop
list_loop :loop
fetch bank_list into temp_bank_name,temp_branch_name;
if done = 1 then
leave list_loop;
end if;
insert into t_bank_copy (bank_name,branch_name,valid)
values (temp_bank_name,temp_branch_name,'1');
end loop;
close bank_list;
END
5.过程 tempTest
CREATE PROCEDURE `tempTest`()
BEGIN
//变量分类: 局部变量、用户变量、会话变量和全局变量,下面定义的用户变量
set @temp_one = '111';
set @temp_two = '222';
set @executeSql = "insert into t_bank VALUES(3,@temp_one,@temp_two,'1')";
//预处理
PREPARE stmt FROM @executeSql;
EXECUTE stmt;
//释放资源
DEALLOCATE PREPARE stmt;
END