mysql游标循环表与存储过程传参

电玩女神 2023-06-25 01:58 11阅读 0赞

drop procedure if exists proc_tmp;
    create procedure proc_tmp(in cnt int)
    BEGIN
    /*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
    declare done int default 0;  /*用于判断是否结束循环*/
    declare zoneCode varchar(100); /*用于存储结果集S_S的记录（因为我这里S_S的记录只有一列且为bigint类型）*/
    
    /*定义游标*/
    declare zoneCur cursor for select code from demo_zone where code!='Q07';
    /*定义 设置循环结束标识done值怎么改变 的逻辑*/
    declare continue handler for not FOUND set done = 1; /*done = true;亦可*/
    
    open zoneCur;  /*打开游标*/
    
    /* 循环开始 */
    REPEAT
    /* 如果要fetch多列应该这样写，fetch idCur into rowId, rowName 但是注意rowId和rowName要先declare，且declare cur时也要select两列，且这两列和rowId、rowName对应 */
    fetch zoneCur into zoneCode;  /*这部分可以看看书，还可以fetch多列（假设结果集S_S的记录不是单列的话）*/
    if not done THEN  /*数值为非0，MySQL认为是true*/
    
    INSERT INTO `hznet_demo`.`sys_station`(`id`, `name`, `type`, `stationid`, `remark`, `longitude`, `latitude`, `enable_control`, `stationsts`, `stationpic`, `code`, `svnaddress`) 
    select 10000000+@rowNum:=@rowNum + 1, temp.`name`, temp.`type`, 10000000+@rowNum:=@rowNum, temp.`remark`, temp.`longitude`+0.1, temp.`latitude`+0.1, temp.`enable_control`, temp.`stationsts`, temp.`stationpic`, temp.`code`, temp.`svnaddress`
    from sys_station a,(SELECT @rowNum:=(select count(1) from sys_station)) b,
    (select `name`, `type`, `remark`, `longitude`, `latitude`, `enable_control`, `stationsts`, `stationpic`, `code`, `svnaddress`
    from sys_station a, demo_station_config c
    where a.stationid=c.stationid and a.stationid not in (0,1,2,3) 
    and c.zone_code=zoneCode limit 1) temp limit cnt;
    
    end if;
    until done end repeat;
    
    close zoneCur;  /*关闭游标*/
    END
    /* 循环结束 */
    
    -- call proc_tmp(40);