MYSQL使用存储过程和游标向数据表批量添加指定的数据

lsysafe 2020-04-21 22:31:44

24503

本文链接：https://blog.csdn.net/lsysafe/article/details/105660292

版权

MYSQL数据库版本:5.7.23

第一个存储过程

表tb_a记录app和应用的对应关系,有三个字段,id为自动生成,app_id为应用id,server_id为服务器id

添加了服务器以后,新的服务器和app的对应关系,和已有的某些旧服务器一致,此时可通过存储过程批量添加新机器和app的对应关系

向tb_a表写入数据,有两列数据写入,id自动生成,数据里面的server_id列为新的服务器id,app_id这一列参照的旧服务器已有的列

drop procedure if exists spappserver;  
CREATE PROCEDURE spappserver(in _oldsid INTEGER,in _newsid INTEGER)  

insert into `tb_a` (app_id,server_id)

select app_id,_newsid from `tb_a` where server_id=_oldsid

用旧服务器和新服务器作为参数,执行存储过程,旧服务器id为15,新服务器id为40

call spappserver(15,40)

第二个存储过程

表tb_c记录了配置文件存放的路径和文件名等信息,其中app_server_id关联了tb_a的id字段,这样就能将配置文件和app及服务器的信息对应起来

那么添加了新的服务器的话,需要将和新的服务器相关的配置文件路径的信息登记到tb_c,这些信息除了app_server_id,其余字段也和已有的某些旧服务器一致,因为配置文件的数量太多,手工添加不太现实,同样通过写存储过程实现,这个存储过程使用了两层游标嵌套的方式

drop procedure if exists spconf;  
CREATE PROCEDURE spconf(in _oldsid INTEGER,in _newsid INTEGER)  
BEGIN  
    declare _appid INTEGER;
    declare _newappsvrid INTEGER;
    declare done int default false;  
    declare cur cursor for select id,app_id from `tb_a` where server_id=_newsid;  
    declare continue HANDLER for not found set done = true;  
    open cur;  
    read_loop:loop  
    fetch cur into _newappsvrid,_appid;  
    if done then  
        leave read_loop;  
    end if;  
    begin  
        declare _oldappsvrid INTEGER;    
        declare done1 int default false;  
        declare cur1 cursor for select id from `tb_a` where app_id=_appid and server_id=_oldsid;  
        declare continue HANDLER for not found set done1 = true;   
        open cur1;  
        read1_loop:loop  
        fetch cur1 into _oldappsvrid;  
        if done1 then  
            leave read1_loop;  
        end if;  
        insert into `tb_c`  (file_name,file_path,app_server_id,line_path)
        select file_name,file_path,_newappsvrid,line_path from `tb_c`
        where app_server_id=_oldappsvrid;    
        
        end loop;  
        close cur1;    
    end;  
 
    end loop;  
    close cur;  
END;

如果某个字段比如file_path和原来的纪录不同的时候,使用replace关键字,根据规律替换,如:

insert into `tb_c` (file_name,file_path,app_server_id,line_path)
select file_name,(replace(file_path,'testunc','produnc')),_newappsvrid,line_path from `tb_c`
where app_server_id=_oldappsvrid;

调用存储过程: call spconf(15,40)

注意,某些版本的MYSQL新建存储过程的时候,需要在前后添加特定的格式才不会报错

delimiter

存储过程的定义