6.存储过程

一、存储过程介绍

SQL执行流程中存在的问题

如果我们需要重复多次执行相同的SQL，SQL执行都需要通过链接传递到MySQL，并且需要编译和执行

如果我们需要执行多个SQL指令，并且第二个SQL指令需要第一个SQL指令的执行结果作为参数

1.1、存储过程

将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，当客户端需要执行这个功能时，不用编写SQL指令，直接通过封装的SQL指令的名字完成调用即可，客户端直接调用SQL指令集进行调用。

1.2、存储过程优点

1.SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性

2.存储过程经过编译器创建并保存在数据库中，执行过程无需惊醒重复的编译操作，提升了SQL执行的效率

3.存储过程中SQL指令之间存在逻辑关系，支持流程控制语句（分支循环），可以实现更加复杂的业务

1.3、存储过程缺点

1.存储过程是根据不同的数据库进行编译、创建并存储在数据库中，当我们需要切换到其他的数据库产品时，需要重新编写对于新数据库的存储过程

2.存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题。

3.在互联网项目中，如果需要数据库的高并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂业务交给了数据库进行处理）

二、存储过程创建

存储过程就是将能够完成特定功能的SQL指令进行封装

# 创建一个存储过程实现加法运算，在其他语言中，方法或者函数是有参数和返回值的
# 存储过程中也是有输入参数和输出参数的
create procedure proc_test1(in a int,in b int,out c int)
begin
	set c = a+b;
end;


# 语法
create PROCEDURE <proc_name>(IN/OUT args)
begin
	# SQL
end;

三、调用存储过程

# 调用存储过程通过call关键字
set @m = 0;
call proc_test1(3,2,@m);
select @m from dual;

四、存储过程中变量的使用

存储过程中分为两种，局部变量和用户变量

局部变量：定义在存储过程中的变量，只能在存储过程的内部使用

# eg
# 无论是局部变量还是用户变量都使用set关键字修改值
CREATE PROCEDURE proc_test2(IN a INT,OUT r INT)
BEGIN
	DECLARE x int DEFAULT 0;
	DECLARE y int DEFAULT 0;
	set x = a*a;
	set y = a/2;
	set r = x+y;
END;

用户变量：相当于全局变量

用户变量会存储在mysql数据库的数据字典中，在用户的开发过程中，尽量少使用用户变量，用户变量过多会导致程序不易理解，难以维护

# 调用存储过程
set @m = 0;
call proc_test2(3,@m);
select @m from dual;

# 用INTO关键字进行赋值
CREATE PROCEDURE proc_test3(OUT r INT)
BEGIN
	# 将查询到的结果复制给r 
	SELECT count(stu_num) INTO r FROM students;
END;

call proc_test3(@m);
select @m from dual;

五、存储过程的参数

MYSQL存储过程的参数一共有三种：IN/OUT/INPUT

输入参数

# 创建存储过程，添加学生信息
CREATE PROCEDURE proc_test4(IN snum VARCHAR(8),IN sname VARCHAR(20), IN gender VARCHAR(2),IN age INT,IN cid INT)
BEGIN
	INSERT INTO students(stu_num,stu_name,stu_gender,stu_age,cid) VALUES(snum,sname,gender,age,cid);
END;

CALL proc_test4('20188888','laowang','w',35,3);

输出参数

CREATE PROCEDURE proc_test5(IN snum VARCHAR(8),OUT sname VARCHAR(20))
BEGIN
	SELECT stu_name INTO sname FROM students WHERE stu_num=snum;
END;

set @name = '';
CALL proc_test5('20188888',@name);
SELECT @name FROM DUAL;

输入输出参数

这个参数尽量少用会降低代码可读性

CREATE PROCEDURE proc_test6(INOUT str VARCHAR(20))
BEGIN
	SELECT stu_name INTO str FROM students WHERE stu_num=str;
END;

set @name2 = '';
CALL proc_test5('20188888',@name2);
SELECT @name2 FROM DUAL;

六、分支语句

• if-then-else

  CREATE PROCEDURE proc_test7(INOUT str VARCHAR(20))
  BEGIN
  	IF str='' THEN
  		# 	
  	ELSE
  		# 
  	END IF
  END;
  
  

• case

  CREATE PROCEDURE proc_test7(IN a Int)
  BEGIN
  	CASE a
  		WHEN a=1 THEN
  		
  		WHEN a=2 THEN
  			
  		ELSE
  			
  	END CASE;
  END;
  

七、循环语句

• while

  CREATE PROCEDURE proc_test7(IN a Int)
  BEGIN
  	DECLARE i int;
  	set i = 0;
  	while i<a do
  		# SQL
  		set i = i+1;
  	END WHILE;
  END;
  

• repeat

  CREATE PROCEDURE proc_test8(IN a Int)
  BEGIN
  	DECLARE i int;
  	set i = 0;
  	REPEAT
  		# SQL
  		set i = i+1;
  	UNTIL i>a END REPEAT;
  END;
  

• loop

  CREATE PROCEDURE proc_test9(IN a Int)
  BEGIN
  	DECLARE i int;
  	set i = 0;
  	myloop:LOOP
  		# SQL
  		set i = i+1;
  		if i=a THEN
  			LEAVE myloop;
  		END IF;
  	END LOOP;
  END;
  

八、存储过程管理

8.1、查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库中，只能在当前的数据库中调用此存储过程

# 根据数据库名查询当前数据库中的存储过程
SHOW PROCEDURE STATUS WHERE db='db_test2';

# 查询存储过程的创建细节
SHOW CREATE PROCEDURE db_test2.proc_test3;

8.2、修改存储过程

修改存储过程的特征特性

ALTER PROCEDURE <proc_name> 特征1[，特征2，特征3]

8.3、删除存储过程

DROP PROCEDURE [ IF EXISTS ] <过程名>

九、游标

游标可以用来依次去除查询结果集中的每一条数据----逐条读取查询结果集中的记录

#查询一条数据的实现
create PROCEDURE pro_1(IN id int,OUT result VARCHAR(200))
BEGIN
	DECLARE bname VARCHAR(20);
	DECLARE bgender VARCHAR(2);
	DECLARE bage INT;
	SELECT stu_name,stu_gender,stu_age INTO bname,bgender,bage FROM students WHERE id=stu_num;
	# 按照断线拼接
	set result = CONCAT('-',bname,bgender,bage);
END


set @re = '';
CALL pro_1('20181114',@re);
SELECT @re from dual;

# 申明游标的语法
DECLARE <cursor_name> CURSOR FOR <select_statement>;
# 打开游标
OPEN <cursor_name>

测试游标

create PROCEDURE pro_2(OUT result VARCHAR(2000))
BEGIN
	DECLARE bname VARCHAR(200);
	DECLARE bgender VARCHAR(2);
	DECLARE bage INT;
	DECLARE num INT;
	DECLARE i INT;
	DECLARE str varchar(1000);
	# 申明游标
	declare Cursor_1 cursor for select stu_name,stu_gender,stu_age FROM students; 
	# 打开游标
	open Cursor_1;
--  	SELECT count(1) INTO num FROM students;
	set num = 2;
	set i = 0;
	while i<num do
		# 提取游标当前指向的数据，游标自动下移
		FETCH Cursor_1 into bname,bgender,bage;
		set i=i+1;
		set str = CONCAT_ws('-',bname,bgender,bage);
		set result = CONCAT_ws(',',result,str);
	end while;
	# 关闭游标
	CLOSE Cursor_1;
END

set @re = '';
CALL pro_2(@re);
SELECT @re from dual;