MySQL之存储过程

最新推荐文章于 2024-03-28 14:35:10 发布

智慧的猪-

最新推荐文章于 2024-03-28 14:35:10 发布

阅读量760

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_54963748/article/details/128686189

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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;