mysql存储过程遍历游标

深藏阁楼爱情的钟 2022-12-29 14:27 186阅读 0赞

CREATE DEFINER=`root`@`%` PROCEDURE `update_xxljob`( out  out_result int(8))
    BEGIN
         
    
    		
    		#结束标识定义
    		DECLARE edone INT DEFAULT 0 ;
    		 
    	 
    		#结果集
    		declare result_date VARCHAR(5000) DEFAULT '';
    	
     
    		
    		########替换变量开始-------------------
    		#定义游标临时变量-------------------开始
     
     
        DECLARE index_merchant_id VARCHAR (36);
     
    		
    		#定义游标临时变量===================结束
    		#声明游标 ----------------开始
    		#声明游标cursor_name（cursor_name是个多行结果集） 
        DECLARE cursor_name CURSOR FOR 
    		
    		select DISTINCT u_id  from sys_u  where unit_top_id = 4 ;
     
    
    		#声明游标==================结束
    		########替换变量结束===================
    	
    		#设置终止标志
    		#DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; 
    		DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;
    
     
    				#重置游标 
    				set  edone = 0; 
    					
    					
    					#二级循环开始
    					#打开游标 
    					OPEN cursor_name;
     					 
    										#遍历游标
    										while edone <> 1 do
    													 #######替换变量开始------------------------------
    														#获取游标当前指针的记录，读取一行数据并传给变量a,b 
    														FETCH cursor_name INTO  index_merchant_id;
    														 
    											  
    											#sql开始
    									  INSERT INTO   tem_xxljob (  id_code  )   	SELECT id    from xxl_job_info where  status=1 and FIND_IN_SET(index_merchant_id,    SUBSTRING(executor_param, LOCATE( 'unis',  executor_param ),100 ) );
    										set result_date  =  concat(result_date,',',index_merchant_id);
    													
    											#sql结束		
    								  
    								   
    						 
    								 #结束游标变成了1,游标只要有值就不会走end whil
    								 end while;
    	 
    									
    						#关闭游标 
    						CLOSE cursor_name;
    					 #二级循环结束
    					 
     
    	  SELECT  edone;
    		--  赋值结果,先查询再赋值
    		SELECT result_date;
    		set out_result=result_date;
     
    END