MySQL-存储过程学习

MySQL-存储过程学习

​ 进行Java Web 项目的时候，经常需要执行数据库查询，有时对一些表数据进行统计，特别是统计分析的时候，原有的SQL执行会比较慢，而且每次点击都会比较慢，如今使用存储过程，可以将那些不需要实时展示的统计数据统计并存储在统计结果表里面，最终查询时直接查询统计结果表，提高查询结果速度。

其实实现此效果的方式有很多，如java也可以通过定时任务一段时间统计结果，重新添加到统计结果表中。

个人认为，非必要不使用存储过程。java的扩容比mysql的容易得多。

1、申明存储过程

CREATE DEFINER = CURRENT_USER PROCEDURE `demo`()

2、存储过程开始和结束符号

所有的内容SQL语句都是写在BEGIN END 里面。

BEGIN
	#Routine body goes here...
END;

3、变量定义

• int类型：

  变量名 num，类型 int，无符号， 默认值 4000000。（默认值可以不设置，即 default 4000000可去掉）

  DECLARE num int unsigned default 4000000;
  

• varchar类型：

  变量名Mobile ，类型VARCHAR，长度50，默认值 hello。（默认值可以不设置，即 default ‘hello’ 可去掉）

• 编码格式

  charset ‘utf8’ 设置编码格式为utf8。

  DECLARE Mobile  VARCHAR(50) default 'hello' charset 'utf8';
  

4、变量赋值

• 已定义变量赋值(局部变量)

  • 方法一：使用set

    set Mobile='world';
    

  • 方法二：使用select …. into…

    select mobile 
    into  Mobile 
    from students 
    where id=1;
    

• 未定义变量直接赋值（用户变量）

  • 方法一：使用set

    set @email='12345@qq.com';
    

  • 方法二：使用select …. into…

    set @email='';
    select @email :=email from students;
    

5、条件语句

• if-then-else语句

  if startyear>2016 then 
  	select name from students;
  end if;
  

  if startyear>2016 then 
  	select name from students;
  else 
  	select email from students;
  end if;
  

• case语句

  declare var int;
  set var=0;
  case var when 0 then  select name from students;
  when 1 then select email from students;
  else select mobile from students;
  end case;
  

6、循环语句

• while ···· end while

  declare var int;  
  set var=0;
  while var<3 do
  	select name from students where id=var;
  	set var=var+1;
  end while;
  

• repeat···· end repea

  操作后判断循环条件，while是先判断循环条件在执行循环体。

  declare var int;  
  set var=0;
  repeat  
  	select name from students where id=var;
  	set var=var+1;
  until var>2
  end repeat;
  

• loop ·····endloop

  loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

  declare var int;  
  set var=0;
  LOOP_LABLE:loop 
  	set var=var+1;
  	if var>2 then
  		leave LOOP_LABLE;
  	end if;
  end loop;
  

7、游标的使用

1. 定义游标及数据

-- mobile变量接收游标中的值
DECLARE Mobile  VARCHAR(50) ; 
DECLARE UpdateAuthorCursor CURSOR for
		SELECT name from students;

1. 设置游标内容执行完毕标志

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

1. 打开游标

open UpdateAuthorCursor;

1. 循环游标内数据

   posLoop:LOOP  
   	--判断是否结束循环
   	if done then 
   		leave posLoop;
   	end if;
   	--取出游标中的数据，并保存在变量中
   	FETCH  UpdateAuthorCursor into Mobile;  
   		update students set mobile=Mobile where id=0;
   	end LOOP;
   

2. 关闭游标

   close UpdateAuthorCursor;
   

8、示例

-- 创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `merge2019`()
BEGIN
-- 定义变量
DECLARE  yearnum int DEFAULT year(CURRENT_DATE);
 DECLARE Mobile  VARCHAR(50)  ;
 DECLARE Content VARCHAR(3200)  ;
 DECLARE Taskid VARCHAR(255)  ;
 DECLARE Sendtime VARCHAR(255) ;

	
-- 	创建游标
	DECLARE done INT DEFAULT FALSE;
	DECLARE UpdateAuthorCursor CURSOR for
		SELECT b.CONTENT,b.MOBILE ,
		case when b.TASKID is not null then b.TASKID else '' end as TASKID ,
		DATE_FORMAT(b.SENDTIME,'%Y-%m-%d') as sendtime
		FROM jx_sendresult_2019_1 b;		
-- 设置游标循环结束标志
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
  OPEN UpdateAuthorCursor;
  -- 循环游标内容
	read_loop: LOOP
	 FETCH UpdateAuthorCursor INTO Content, Mobile, Taskid, Sendtime;
	-- 设置循环结束标识
	   IF done THEN
      LEAVE read_loop;
    END IF;
    -- 执行SQL操作
		select count(1) from tbl_smresult;
 			  UPDATE  tbl_smresult_2019_2
								SET  MSGID = Taskid 
                 WHERE   SM_CONTENT= Content 
 								and DESTADDR= Mobile
 								and MSGID =''
 								and DATE_FORMAT(SENDTIME,'%Y-%m-%d')=Sendtime
								limit 1;
 				COMMIT;
	  END LOOP;
	  -- 关闭游标
	CLOSE  UpdateAuthorCursor;
	-- 结束存储过程
END