mysql存储过程学习

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

mysql存储过程学习

1、申明存储过程

1	CREATE DEFINER = CURRENT_USER PROCEDURE `demo`()

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

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

1
2
3

BEGIN
	#Routine body goes here...
END;

3、变量定义

int类型：

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

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

charset ‘utf8’ 设置编码格式为utf8。
1
DECLARE Mobile VARCHAR(50) default 'hello' charset 'utf8';

4、变量赋值

已定义变量赋值(局部变量)
- 方法一：使用set
  1
  set Mobile='world';
- 方法二：使用select …. into…
  1
  2
  3
  4
  select mobile
  into Mobile
  from students
  where id=1;
未定义变量直接赋值（用户变量）
- 方法一：使用set
  1
  set @email='12345@qq.com';
- 方法二：使用select …. into…
  1
  2
  set @email='';
  select @email :=email from students;

5、条件语句

if-then-else语句

1
2
3

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语句

1	x 1declare var int; 2set var=0;3case var4when 0 then5 select name from students;6when 1 then7 select email from students;8else9 select mobile from students;10end case;mysql

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 语句的意义是离开循环。
1
2
3
4
5
6
7
8
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、游标的使用

定义游标及数据

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

循环游标内数据

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

关闭游标
1
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