SQL循环语句

大家好，欢迎来到IT知识分享网。

drop procedure if exists insert_while; delimiter // create procedure insert_while() begin declare i int default 1; while i<10 do insert into test values(i,concat('bookworm',i)); set i=i+1; end while; commit; end // delimiter ; call insert_while(); 

下面是查阅结果:

 mysql> select * from test; +-------------+---------------+ | location_id | location_name | +-------------+---------------+ | 1 | bookworm1 | | 2 | bookworm2 | | 3 | bookworm3 | | 4 | bookworm4 | | 5 | bookworm5 | | 6 | bookworm6 | | 7 | bookworm7 | | 8 | bookworm8 | | 9 | bookworm9 | +-------------+---------------+ 9 rows in set (0.00 sec) 

drop procedure if exists insert_while：如果存在函数insertwhile先删除它 delimiter //：将结束符定义为//在begin和end中会使用;如果不换程序会错误 create procedure：创建存储过程 delimiter ;结束符换回;号 call insert_while(); 执行定义的insert_while函数 

CREATE TABLE `dim_time` ( `TimeKey` int(11) NOT NULL, `Hour` tinyint(4) DEFAULT NULL, `Minute` tinyint(4) DEFAULT NULL, PRIMARY KEY (`TimeKey`) ) 

编写存储过程

drop procedure if exists insertValueIntoDimTime; delimiter // create procedure insertValueIntoDimTime() begin declare hour int default 0; declare min int default 0; while hour < 24 do while min < 60 do insert into dim_time values(hour*100+min,hour,min); set min=min+1; end while; set min = 0; set hour = hour+1; end while; end // delimiter ; 

运行

mysql> call insertValueIntoDimTime; Query OK, 1 row affected (22.12 sec) 

结果

mysql> select count(*) from dim_time; +----------+ | count(*) | +----------+ | 1440 | +----------+ 1 row in set (0.02 sec) mysql> select * from dim_time order by timekey limit 10; +---------+------+--------+ | TimeKey | Hour | Minute | +---------+------+--------+ | 0 | 0 | 0 | | 1 | 0 | 1 | | 2 | 0 | 2 | | 3 | 0 | 3 | | 4 | 0 | 4 | | 5 | 0 | 5 | | 6 | 0 | 6 | | 7 | 0 | 7 | | 8 | 0 | 8 | | 9 | 0 | 9 | +---------+------+--------+ 10 rows in set (0.00 sec) mysql> select * from dim_time order by timekey desc limit 10; +---------+------+--------+ | TimeKey | Hour | Minute | +---------+------+--------+ | 2359 | 23 | 59 | | 2358 | 23 | 58 | | 2357 | 23 | 57 | | 2356 | 23 | 56 | | 2355 | 23 | 55 | | 2354 | 23 | 54 | | 2353 | 23 | 53 | | 2352 | 23 | 52 | | 2351 | 23 | 51 | | 2350 | 23 | 50 | +---------+------+--------+ 10 rows in set (0.00 sec) 

3.2实现

drop procedure if exists date_loop; delimiter // create procedure date_loop() begin declare i int default 1; declare start_date date ; -- 当前日子减去6个月 declare end_date date ; -- 当前天 select date_sub(current_date(),interval 6 month) into start_date; select current_date() into end_date; while start_date < end_date do select start_date; set start_date = date_add(start_date,interval 1 day); end while; commit; end // delimiter ; 

date_sub(object,interval):从指定日期减去指定年，月，日，时，分，秒