mysql存储过程双层遍历(游标)

超、凢脫俗 2022-12-11 13:52 247阅读 0赞

CREATE DEFINER=`root`@`%` PROCEDURE `in_begin_date`(IN in_begin_date VARCHAR ( 20 ),IN in_end_date VARCHAR ( 20 ))
    BEGIN
         
    
    		
    		#结束标识定义
    		DECLARE edone INT DEFAULT 0 ;
    		
    		#定义每一天
    		declare index_date date;
    		#结果集
    		declare result_date VARCHAR(5000) DEFAULT '';
    		#结果集2
    		declare result_param VARCHAR(5000) DEFAULT '';
    		
    		########替换变量开始-------------------
    		#定义游标临时变量-------------------开始
        DECLARE a INT;
        DECLARE b VARCHAR (36);
    		
    		
    		#定义游标临时变量===================结束
    		#声明游标 ----------------开始
    		#声明游标cursor_name（cursor_name是个多行结果集） 
        DECLARE cursor_name CURSOR FOR SELECT
            age,
            c_name
        FROM
            class;
    		#声明游标==================结束
    		########替换变量结束===================
    	
    		#设置终止标志
    		#DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; 
    		DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;
    
    		#把结果数量赋值给某个变量
    		#select count(*) into count_x from class; 
    		
    		#初始化开始时间
    		set index_date= in_begin_date ;
    		
    		#时间循环开始
    		while index_date <= in_end_date  	do 
    	
    				#重置游标 
    				set  edone = 0;
    					
    					
    					#二级循环开始
    					#打开游标 
    					OPEN cursor_name;
    
    								#######替换变量开始------------------------------
    								#获取游标当前指针的记录，读取一行数据并传给变量a,b 
    								FETCH cursor_name INTO a, b;
    											#遍历游标
    											while edone <> 1 do
    											
    											#sql开始
    									 
    													INSERT INTO class_back ( age ,c_name,create_date) VALUES (a, b,index_date);
    													
    											#sql结束		
    										set result_param   =  concat(result_param,',',a,'+',b,'+',index_date);
    								FETCH cursor_name INTO a, b;
    								############替换变量结束=============================
    								end while;
    		 
    									
    						#关闭游标 
    						CLOSE cursor_name;
    					 #二级循环结束
    					 
    				 
    				 
    	  #设置时间向前推进一天
    		set index_date =  date_sub(index_date, INTERVAL -1 DAY) ;
    		
    		set result_date  =  concat(result_date,',',index_date);
    		
        end while;
    		SELECT  result_date;
    		SELECT  result_param;
    END

代码段 小部件

\[Dàimǎ duàn xiǎo bùjiàn\]

Snippet widget