mysql 存储过程实例 (日期以小时递增 while loop循环嵌套 随机数生成)
DELIMITER $$drop procedure if exists proc_test$$create procedure proc_test()begindeclare id int; --对象iddeclare done int; --循环结束的标志declare in_dateTime date; --循环递增的起始时间declare tempVal int; --随机数declarecursor_test cursor for select cfldID from cfield; --建立游标，获取所有的cfield的idDECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; --设置循环结束的标志open cursor_test; --打开游标cursor_loop:loop fetch cursor_test into id; if done =1 thenleave 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;
