mysql 存储过程 返回从今天起1个月日历 mysql存储过程返回集合

1. 存储过程和函数都是事先在数据库中经过执行一段sql语句的集合，存储过程没有返回值，而函数必须有返回值，使用存储过程可以减少数据在数据库和应用服务器之间的传输，简化开发人员的工作。
2. 创建存储过程

CREATE  PROCEDURE pro_name()
  BEGIN
    ---sql语句
  END;

     2.1  调用存储过程

call pro_name();

   2.2 删除存储过程

DROP PROCEDURE IF EXISTS pro_name ；

 3.存储过程语法

 3.1 变量

--DECLARE定义变量,SET进行赋值,也可以用select into赋值

CREATE PROCEDURE PRO_TEST()
BEGIN 
   DECLARE NUM INT DEFAULT 2;
   SET NUM =NUM+3;
   SELECT COUNT(1) INTO NUM FROM TABLE_NAME;
END;

3.2 if条件判断

--if条件判断demo

CREATE PROCEDURE pro_demo() 
BEGIN
 DECLARE height INT DEFAULT 170;
 DECLARE description VARCHAR(50);
IF
	height >= 180 THEN SET description = '偏高';
ELSEIF 
  height >= 170 AND height < 180 THEN SET description = '标准';
ELSE 
  SET description = '偏低';
END IF;
SELECT description;
END;

3.3 传递参数

-- in作为输入参数(默认参数);out作为输出参数;inout可以作为输出参数，也可以作为输入参数
-- @description 代表这个会话是有效的，@@description 代表是系统变量

DROP PROCEDURE IF EXISTS pro_demo;
CREATE PROCEDURE pro_demo(in height int , out description varchar(50)) 
BEGIN
IF
	height >= 180 THEN SET description = '偏高';
ELSEIF 
  height >= 170 AND height < 180 THEN SET description = '标准';
ELSE 
  SET description = '偏低';
END IF;
END;

call  pro_demo(171,@description);
select @description;

3.4 case 结构

--case语句结构
DROP PROCEDURE IF EXISTS pro_demo;
CREATE PROCEDURE pro_demo(in height int ) 
BEGIN
DECLARE result varchar(50);
CASE
  WHEN height >= 180  THEN SET result = '偏高'; 
  WHEN height >= 170 AND height < 180 THEN SET result = '标准';
  WHEN height<170 THEN SET result = '偏低';
END CASE;
select concat('输入的是：',height,' 身高为：',result) as conmment;
END;

3.5 循环结构

-- while循环满足条件就循环
DROP PROCEDURE IF EXISTS pro_demo;
CREATE PROCEDURE pro_demo( m INT ) 
BEGIN
   DECLARE total INT DEFAULT 0;
   DECLARE num INT DEFAULT 1;
 WHILE 
    num <= m DO
		SET total = total + num;
    SET num = num + 1;
END WHILE;
SELECT total;
END ;
-- repeat循环满足条件就退出
DROP PROCEDURE IF EXISTS pro_demo;
CREATE PROCEDURE pro_demo( m INT ) 
BEGIN
   DECLARE total INT DEFAULT 0;
 REPEAT
	 SET total = m + total;
	 SET m = m-1;
   UNTIL m=0 
END REPEAT;
SELECT total;
END ;
-- loop和leave循环语句
DROP PROCEDURE IF EXISTS pro_demo;
CREATE PROCEDURE pro_demo( m INT ) 
BEGIN
   DECLARE total INT DEFAULT 0;
	 label: LOOP
	 SET total = m + total;
	 SET m = m-1;
	IF m=0 THEN
		 LEAVE label; 
	END IF; 
END LOOP label;
SELECT total;
END ;

3.6 游标

-- 游标用来存储查询结果集的数据类型，可以在循环中对结果集进行处理
-- 声明游标
DECLARE cursor_name CURSOR FOR select_statement ;
-- 打开游标
OPEN cursor_name;
-- fetch游标
FETCH cursor_name INTO var_name ...
-- 关闭游标
CLOSE cursor_name ;

drop procedure if exists temp_export; 
create procedure temp_export()  
begin
    declare temp_code varchar(30);  # 声明变量
    declare temp_time varchar(10);
		declare temp_pro varchar(10);
	  declare flag int default 0;
		declare result_sql varchar(50000) default '';     
    # 这是重点，定义一个游标来记录sql查询的结果
    declare s_list cursor for select code ,time_attribute ,region from big_temp_data   group by code,time_attribute,region;
    # 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1
    declare continue handler for not found set flag=1;
    open s_list;  # 打开游标
        while flag <> 1 do
				# 将游标中的值赋给定义好的变量，实现for循环的要点
        fetch s_list into temp_code, temp_time,temp_pro;
				
				  if result_sql <> '' then
          set result_sql = concat(result_sql," union all ","( select * from  big_temp_data  where code = '",temp_code,
					"' and time_attribute = '",temp_time,"'and region = '",temp_pro,"'  order by time desc limit 0,2 )");
				  else 
				  set result_sql = concat("(select * from  big_temp_data  where code = '",temp_code,"' and time_attribute = '",temp_time,"'   
					    and region = '",temp_pro,"'  order by time desc limit 0,2 )");
					end if;
					  set @sql = result_sql ;
        end while;
      close s_list;  # 关闭游标	   
	           PREPARE stmt FROM @sql; 
	 				   EXECUTE stmt ;
				     deallocate prepare stmt;
end ;

call temp_export(); # 调用

4：存储函数

-- while循环满足条件就循环
DROP PROCEDURE IF EXISTS pro_demo;
CREATE FUNCTION pro_demo( m INT ) 
RETURNS INT	
BEGIN
   DECLARE total INT DEFAULT 0;
   DECLARE num INT DEFAULT 1;
 WHILE 
    num <= m DO
		SET total = total + num;
    SET num = num + 1;
END WHILE;
RETURN total;
END ;
select pro_demo(100)