mysql 存储过程实例 (日期以小时递增 while loop循环嵌套 随机数生成)

直接上代码：

 

drop procedure if exists proc_test$$
create procedure proc_test()
begin
    declare id int;  --对象id
    declare done int; --循环结束的标志
    declare in_dateTime date; --循环递增的起始时间
    declare tempVal int; --随机数

    declare cursor_test cursor for select cfldID from cfield; --建立游标，获取所有的cfield的id
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; --设置循环结束的标志

    open cursor_test; --打开游标
    cursor_loop:loop
        fetch cursor_test into id;
        if done =1 then
            leave cursor_loop;
        end if;
        --设置起始时间值
        set in_dateTime = '2012-11-30 00:00:00';
        set @in_dateTime = in_dateTime;
        --while递增循环，每次增加一个小时
    loop_while: while @in_dateTime < '2012-11-30 23:00:00' do
        --生成随机数
        SELECT FLOOR(18 + (RAND() * 7)) into tempVal;
        set @tempVal = tempVal;
        insert into cdacq(cfldID,HTime,MTime,LTime,FanState,FanPowerState,SupplyTemp,RoomTemp,AcqTime,Alt) values(id,10000,10000,10000,'84','0',42,@tempVal,@in_dateTime,'');

        set @in_dateTime = date_add(@in_dateTime, interval '01:00:00' hour_second); --时间增加一小时
    end while loop_while; --结束while循环

    end loop cursor_loop;  --结束loop循环
    close cursor_test; --关闭游标

end  $$ --存储过程结束
DELIMITER;

DELIMITER $$
drop procedure if exists proc_test$$
create procedure proc_test()
begin
	declare id int;  --对象id
	declare done int; --循环结束的标志
	declare in_dateTime date; --循环递增的起始时间
	declare tempVal int; --随机数
	
	declare	cursor_test cursor for select cfldID from cfield; --建立游标，获取所有的cfield的id
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; --设置循环结束的标志

	open cursor_test; --打开游标
	cursor_loop:loop 
		fetch cursor_test into id; 
		if done =1 then
			leave cursor_loop;
		end if;
		--设置起始时间值
		set in_dateTime = '2012-11-30 00:00:00';
		set @in_dateTime = in_dateTime;
		--while递增循环，每次增加一个小时
	loop_while: while @in_dateTime < '2012-11-30 23:00:00' do
	 	--生成随机数
		SELECT FLOOR(18 + (RAND() * 7)) into tempVal;
		set @tempVal = tempVal;
		insert into cdacq(cfldID,HTime,MTime,LTime,FanState,FanPowerState,SupplyTemp,RoomTemp,AcqTime,Alt) values(id,10000,10000,10000,'84','0',42,@tempVal,@in_dateTime,'');
		
		set @in_dateTime = date_add(@in_dateTime, interval '01:00:00' hour_second); --时间增加一小时
	end while loop_while; --结束while循环
		
	end loop cursor_loop;  --结束loop循环
	close cursor_test; --关闭游标
		
end  $$ --存储过程结束
DELIMITER;