Mysql

存储过程

• declare 和 set的区别？

1. declare : 此关键字声明的变量只能用于 begin end 之间。
2. set : 一个@表示会话变量，只在当前会话有效；两个@表示全局变量
3. 调用存储过程时 declare 变量会被初始化为 null ， 而 set 变量只需初始化一次，之后的值都是上一次会话计算的结果。

• 获取范围内时间算法

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2020-05-01' and '2020-05-05' 

效果：

• 一个简单的存储过程

BEGIN
	#循环结束标志 1 时结束循环
	DECLARE v_finished INTEGER DEFAULT 0;
	#游标里的数据循环赋值给 v_summary
	DECLARE v_summary varchar(100) DEFAULT "";
	#声明游标 DECLARE cursor_name CURSOR FOR SELECT_statement;
	DECLARE s_cursor CURSOR FOR SELECT summary FROM posts;
	#异常处理
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
	#打开游标
	OPEN s_cursor;
	#开始循环
	get_summary:LOOP
	#依次处理游标
	FETCH s_cursor INTO v_summary;
	#循环结束条件
	IF v_finished = 1 THEN 
		LEAVE get_summary;
	END IF;
	-- build  list
	SET summary_list  = CONCAT(v_summary,";",summary_list );
	#结束循环
	END LOOP get_summary;
	#关闭游标
	CLOSE s_cursor;

END

表结构：