mysql之存储过程和存储函数

王凱旋

已于 2022-09-28 10:08:28 修改

阅读量624

点赞数

文章标签： mysql 数据库 sql

于 2022-09-27 21:25:14 首次发布

本文链接：https://blog.csdn.net/weixin_43183850/article/details/127079006

版权

文章目录

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合

可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可

可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。

减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

创建存储过程

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])  //  create procedure p1（）
BEGIN
	---sql语句
END ;

调用存储过程

//调用存储过程
CALL 名称 ([ 参数 ]);
call p1();

查看存储过程的创建语句

show create procedure p1;

删除存储过程

drop procedure if exists p1;

在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

系统变量：系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

查看系统变量

show session variables ;   // 查看所有系统变量

show global variables like 'auto%'; // 可以通过LIKE模糊匹配方式查找变量

select @@session.autocommit; // 查看指定名称的系统变量，用两个@@

设置系统变量

set session autocommit = 1;

set global autocommit = 0;

用户定义变量: 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接。

设置用户变量并赋值且查询

set @var_name := 'itcast'; // 赋值尽量使用:= ， 虽然也可使用=

SELECT @var_name ;

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

声明局部变量：DECLARE 变量名变量类型 [default … ] ;

赋值：SELECT 字段名 INTO 变量名 FROM 表名 … ;

举例

create procedure p2()
begin
	declare stu_count int default 0;
	select count(*) into stu_count from student;  //给声明的变量赋值
	select stu_count;
end;

call p2();

if

基本语法

create procedure p3() 
begin
	declare score int default 58; 
	declare result varchar(10); 
	if score >= 85 then 
		set result := '优秀'; 
	elseif score >= 60 then 
		set result := '及格'; 
	else 
		set result := '不及格'; 
	end if; 
	select result; 
end; 

call p3();

参数

参数的类型，主要分为以下三种：IN、OUT、INOUT

IN ：该类参数作为输入，也就是需要调用时传入值 （默认）
OUT ：该类参数作为输出，也就是该参数可以作为返回值
INOUT ：既可以作为输入参数，也可以作为输出参数

create procedure p4(in score int, out result varchar(10)
begin
	---
end;

//定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
                    
select @result;
                    
 ---------------------------------------------------------------------------
                    
//变量既是入参，又是出参
create procedure p5(inout score double)
begin
 set score := score * 0.5;
end;
                    
set @score := 198;
                    
call p5(@score);
                    
select @score;

case

语法1：

语法2：

根据传入的月份，判定月份所属的季节（要求采用case结构）

create procedure p6(in month int)
begin
	declare result varchar(10);
	case
		when month >= 1 and month <= 3 then
			set result := '第一季度';
		when month >= 4 and month <= 6 then
			set result := '第二季度';
		when month >= 7 and month <= 9 then
			set result := '第三季度';
		when month >= 10 and month <= 12 then
			set result := '第四季度';
		else
			set result := '非法参数';
	end case ;
		
	select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
end

call p6(16);

while

计算从1累加到n的值，n为传入的参数值

// A. 定义局部变量, 记录累加之后的值; 
// B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环

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(100);

repeat

计算从1累加到n的值，n为传入的参数值。

// A. 定义局部变量, 记录累加之后的值;
// B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环

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);
call p8(100);

loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

LOOP可以配合一下两个语句使用:

leave ：配合循环使用，退出循环。
iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

计算从1累加到n的值，n为传入的参数值。

A. 定义局部变量, 记录累加之后的值;
B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx

create procedure p9(in n int)
begin
	declare total int default 0;
	sum:loop
		if n<=0 then
			leave sum;
		end if;
			set total := total + n;
			set n := n - 1;
	end loop sum;
	select total;
end;
call p9(100);