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

直接上代码：

?

[
DELIMITER?$$??

1. drop?procedure?if?exists?proc_test$$??
2. create?procedure?proc_test()??
3. begin??
4. ????declare?id?int;??–对象id ??
5. ????declare?done?int;?–循环结束的标志 ??
6. ????declare?in_dateTime?date;?–循环递增的起始时间 ??
7. ????declare?tempVal?int;?–随机数 ??
8. ??????
9. ????declare?cursor_test?cursor?for?select?cfldID?from?cfield;?–建立游标，获取所有的cfield的id ??
10. ????DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?done=1;?–设置循环结束的标志 ??
11. ??
12. ????open?cursor_test;?–打开游标 ??
13. ????cursor_loop:loop???
14. ????????fetch?cursor_test?into?id;???
15. ????????if?done?=1?then??
16. ????????????leave?cursor_loop;??
17. ????????end?if;??
18. ????????–设置起始时间值 ??
19. ????????set?in_dateTime?=?‘2012-11-30?00:00:00’;??
20. ????????set?@in_dateTime?=?in_dateTime;??
21. ????????–while递增循环，每次增加一个小时 ??
22. ????loop_while:?while?@in_dateTime?<?‘2012-11-30?23:00:00’?do??
23. ????????–生成随机数 ??
24. ????????SELECT?FLOOR(18?+?(RAND()?*?7))?into?tempVal;??
25. ????????set?@tempVal?=?tempVal;??
26. ????????insert?into?cdacq(cfldID,HTime,MTime,LTime,FanState,FanPowerState,SupplyTemp,RoomTe