Mysql语句编写循环

刺骨的言语ヽ痛彻心扉 2022-12-26 15:25 123阅读 0赞

# 0 总结 #

`Get to the points first. The article comes from LawsonAbs!`

*  updata on 20200506：修改相关格式；在单层循环中，增加`test`的创表语句

--------------------

## 1.单层循环 ##

*  先定义测试表

create table test(location_id int ,location_name varchar(10));

*  1

*  再编写存储过程，**其中涉及到循环的使用**。我们欲通过这个存储过程，来达到往表中插入数据的效果。

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

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13

*  接着检验输出

mysql> select * from test;
    Empty set (0.00 sec)
    
    mysql> call test_loop();
    Query OK, 0 rows affected (0.35 sec)
    
    mysql> select * from test;
    +-------------+---------------+
    | location_id | location_name |
    +-------------+---------------+
    |           1 | hangzhou1     |
    |           2 | hangzhou2     |
    |           3 | hangzhou3     |
    |           4 | hangzhou4     |
    |           5 | hangzhou5     |
    |           6 | hangzhou6     |
    |           7 | hangzhou7     |
    |           8 | hangzhou8     |
    |           9 | hangzhou9     |
    +-------------+---------------+
    9 rows in set (0.00 sec)

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21

发现存储过程调用成功，即循环调用成功。

## 2.双层循环 ##

*  创建表

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

*  1
 *  2
 *  3
 *  4
 *  5
 *  6

*  编写存储过程

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 ;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18

*  运行存储过程

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

*  1
 *  2

*  验证输出

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)

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23
 *  24
 *  25
 *  26
 *  27
 *  28
 *  29
 *  30
 *  31
 *  32
 *  33
 *  34
 *  35
 *  36
 *  37
 *  38
 *  39
 *  40
 *  41

## 3. 日期循环 ##

### 3.1 需求 ###

今天需要从订单库中找出过去六个月的订单中是桌面机的订单，所以需要写一个简单的日期循环，用于读取数据。

### 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 ;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20

## 4.参考文章 ##

*  https://blog.csdn.net/luogan129/article/details/73691286