存储过程-游标

BEGIN
DECLARE
	size INTEGER ( 12 ) DEFAULT 0;

	-- 遍历数据结束标志
DECLARE
	done INT DEFAULT FALSE;
DECLARE
	result CURSOR FOR 			    
        
        //***********替换select语句 *****************
		select count(1) size from  user
       
-- 	将结束标志绑定到游标
DECLARE
	CONTINUE HANDLER FOR NOT FOUND 
	SET done = TRUE;
OPEN result;
read_loop :LOOP
-- 取值 取多个字段
FETCH NEXT FROM result	INTO size;
IF done THEN
	LEAVE read_loop;
END IF;
   //***********替换insert语句 *****************
 insert into  user(  `size`) value(  size);
END LOOP;
CLOSE result;
END

*2.*设置时间执行间隔

BEGIN
DECLARE
	minDate VARCHAR ( 32 ) DEFAULT "";
	DECLARE
	maxDate VARCHAR ( 32 ) DEFAULT "";

DECLARE
	minDate1 VARCHAR ( 32 ) DEFAULT "";
DECLARE
	maxDate1 VARCHAR ( 32 ) DEFAULT "";
DECLARE
	dateTime VARCHAR ( 32 ) DEFAULT "";
		-- 遍历数据结束标志
DECLARE
	done INT DEFAULT FALSE;

DECLARE
	result CURSOR FOR SELECT
	date_add(
	date_sub( DATE_SUB(CURRENT_DATE,INTERVAL 30 DAY), INTERVAL 1 DAY ),
	INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY 
	) DAY 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DATEDIFF(
	DATE_SUB( CURRENT_DATE, INTERVAL 1 DAY ),
	date_sub( DATE_SUB(CURRENT_DATE,INTERVAL 30 DAY), INTERVAL 1 DAY ) )
	
ORDER BY
	help_topic_id;
-- 	将结束标志绑定到游标
DECLARE
	CONTINUE HANDLER FOR NOT FOUND 
	SET done = TRUE;

	OPEN result;
read_loop :
LOOP-- 取值 取多个字段
FETCH NEXT 
FROM
	result INTO dateTime;
IF
	done THEN
	LEAVE read_loop;
END IF;
//需要执行的游标方法
CALL modth ( dateTime );
END LOOP;
CLOSE result;
END

3.执行存储过程

CALL all_add(DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY))