MySQL之存储过程

1、基本介绍

1.1、介绍存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是很有好处的。
简单来说就是把很多SQL语句进行封装处理然后同时进行执行。

1.2、特点

封装、复用SQL语句的代码可以进行封装和复用
可以接收参数可以返回数据存储过程可以接收参数可以返回结果
减少网络交互提升效率不用发送多个sql语句只需要发送存储过程就行

1.3、基本语法

1.3.1、delimiter

控制台中mysql以;进行结尾我们在使用上面的创建方法时就会出错,所以使用 DELIMITER $来进行修改结束的处理最后语句结束使用DELIMITER ;修改回来

1.3.1、创建存储过程

#创建p1存储过程
CREATE PROCEDURE p1()
BEGIN 
   SELECT * FROM student;
END;

控制台中

# 控制台中mysql以;进行结尾我们在使用上面的创建方法时就会出错,所以使用 DELIMITER $$来进行修改
# 结束的处理最后语句结束使用DELIMITER ;修改回来
DELIMITER $
CREATE PROCEDURE p1()
BEGIN 
   SELECT * FROM student;
END$
# 修改回来
DELIMITER ;

1.3.2、调用存储过程

# 调用p1存储过程
CALL p1();

运行结果

1.3.3、查看存储过程

# 查看存储过程
SHOW CREATE PROCEDURE p1;
# 查看系统表中的存储过程
SELECT * from information_schema.ROUTINES WHERE ROUTINE_SCHEMA = "表名";

1.3.4、删除存储过程

#删除存储过程
DROP PROCEDURE if EXISTS p1;

2、变量

2.1、系统变量

2.1.1、查询(会话、全局、模糊、精确)

全局变量GLOBAL
会话变量SESSION

# 查询全局变量(GLOBAL) 和 会话变量 (SESSION)
show GLOBAL VARIABLES;
show SESSION VARIABLES;
# 事务提交的变量(模糊匹配查看)
show GLOBAL VARIABLES like 'auto%';
# 精确查找
SELECT @@autocommit;

2.1.2、设置系统变量

# 设置系统变量 autocommit = 0
set SESSION autocommit = 0;
set SESSION autocommit = 1;

2.2、用户定义变量

用户定义变量 根据需要自己定义的变量用户变量不需要提前声明在使用的时候直接用"@变量名"使用就可以

2.2.1、用户变量赋值(普通赋值SQL查询赋值)

# 赋值
set @testname = 'zzz';
set @testage := 21;
# 定义多个赋值
set @testname := 'zzz',@testage := 21;
# SQL 查询结果定义给变量
SELECT count(*) into @testcount from student;

2.2.2、查询用户自定义变量

# 使用SELECT @ 来进行查询
SELECT @testname,@testage,@testcount;

2.3、局部变量

根据需要定义在局部生效的变量访问之前需要DECLARE声明。
可用作存储过程内的局部变量和输入参数局部变量的范围是在其内声明的 BEGIN …END

# 创建存储过程然后进行执行的处理
CREATE PROCEDURE p2()
BEGIN 
# 定义变量然后进行赋值然后进行输出
	 DECLARE stu_count int DEFAULT 0;
   SELECT count(*) into stu_count FROM student;
	 SELECT stu_count;
END;
# 运行存储过程
call p2();

3、常用语句

3.1、if语句

判断age如果大于18就是成年否则就是未成年

CREATE PROCEDURE p3()
BEGIN 
# 定义age变量
    DECLARE stu_age int DEFAULT 20;
		DECLARE stu_type VARCHAR(10);
		
    if stu_age >= 18 then
		  set stu_type := '成年';
		else  
		  set stu_type := '未成年';
		end if;
		# 输出
		SELECT stu_type;
END;

call p3();

3.2、参数(in,out,inout)

in 输入参数
out 输出参数可以作为返回值
inout 既可以作为输入参数又可以作为输出参数

CREATE PROCEDURE p1([in/out/INOUT 参数名 参数类型])
BEGIN 
   SQL语句
END;

还是这个案例判断age如果大于18就是成年否则就是未成年

# 修改上面的处理
# stu_age为输入值stu_type为输出值
CREATE PROCEDURE p4(in stu_age int,out stu_type VARCHAR(10))
BEGIN 	
    if stu_age >= 18 then
		  set stu_type := '成年';
		else  
		  set stu_type := '未成年';
		end if;
END;
# 赋值给result
call p4(20,@result);
# 取result这个值
SELECT @result;

inout 即是输入又是输出,把age*2

# inout 即是输入又是输出,把age*2
CREATE PROCEDURE p5(INOUT stu_age DOUBLE)
BEGIN 	
   set stu_age :=  stu_age  * 2;
END;
# 先进行赋值然后在传递然后在输出
set @stuage = 25.0;
call p5(@stuage);
SELECT @stuage;

3.3、case判断

根据age来进行判断 0~18 未成年19~60青年其他老年人

# case 根据age来进行判断
CREATE PROCEDURE p6(in stu_age int)
BEGIN 
   DECLARE result VARCHAR(10);
   case 
	   WHEN stu_age >= 0 and stu_age<= 18 then set result := "未成年";
		 WHEN stu_age >= 19 and stu_age<= 60 then set result := "青年";
		 else set result := "老年人";
	 end case;
	 # 字符串拼接处理
	 SELECT CONCAT("您输入的年龄为",stu_age,"所属分类为",result);
END;

call p6(55);

3.4、while循环

求1累加到n

# while循环 求1累加到n
CREATE PROCEDURE p7(in n int)
BEGIN 
   DECLARE total int DEFAULT 0;
	 WHILE n > 0 do 
			set total := total + n;
	    set n := n-1;
	 end WHILE;
	 SELECT total;
END;

call p7(10);

3.5、repeat循环

有条件的循环控制语句 条件写在until的后面满足条件就退出循环
我们还是进行1~n的累加处理

# repeat有条件的循环控制语句  条件写在until的后面满足条件就退出循环
CREATE PROCEDURE p8(in n int)
BEGIN 
   DECLARE total int DEFAULT 0;
	 REPEAT
	 set total := total + n;
	 set n := n-1;
UNTIL n <= 0 
END REPEAT;
	 SELECT total;
END;

call p8(10);

运行结果

3.6、loop循环

计算从1~n的累加处理

# loop 计算从1累加到n
CREATE PROCEDURE p9(in n int)
BEGIN 
# 定义total变量
   DECLARE total int DEFAULT 0;
	 # 循环调用sum
	 sum: LOOP
	 set total := total + n;
	 set n := n-1;
	 # 如果小于0在调用sum
	IF n<0 THEN
		LEAVE sum; 
	END IF; 
END LOOP sum;

	 SELECT total;
END;
#执行
call p9(10);

运行结果

3.7、存储函数

还是进行1~n的处理

CREATE function fun1(n int)
RETURNS int DETERMINISTIC
BEGIN
 DECLARE total int DEFAULT 0;
 WHILE n > 0 DO
	set total := total +  n;
	set n := n-1;
END WHILE;
RETURN total;
end;

SELECT fun1(10);

3.8、游标

游标是用来存储查询结果集的数据类型在存储过程和函数中可以使用游标对结果集进行循环的处理使用游标包括游标的声明、open、fetch、close

  声明游标   declare 游标名称 cursor for SQL语句
  打开游标   open 游标名称
  获取游标记录 FETCH 游标名称 into 变量;
  关闭游标   close 游标名称

我们把用户表中age < 指定数的数据存到另外一张表中把name存到另外一张表中

	# 游标 是用来存储查询结果集的数据类型在存储过程和函数中可以使用游标对结果集进行循环的处理。
	CREATE PROCEDURE p10(in a int)
	BEGIN 
		 # 先声明变量
		 DECLARE uname VARCHAR(100);
		 # 创建游标
		 DECLARE u_cursor cursor for select `name` from student where age < a;
		 
		 # 创建另外一张表
		 DROP TABLE if EXISTS tb_user;
		 CREATE TABLE if not EXISTS tb_user(
		 id int PRIMARY KEY auto_increment,
		 `name` varchar(100)
		);
		# 打开游标
		open u_cursor;
	WHILE true DO
		FETCH u_cursor into uname;
		INSERT INTO tb_user VALUES(NULL,uname);
	END WHILE;
	# 关闭游标
		CLOSE u_cursor;
	END;
# 运行存储过程
call p10(60);
#删除存储过程
DROP PROCEDURE if EXISTS p10;

运行结果age < 60的数据都存到了tb_user表中
原表

结果表