Mysql变量、存储过程、存储函数笔记

变量

一、系统变量

变量有系统提供，不是用户定义，属于服务器层面，可以通过show global|session variables查看,注意：如果是全局级别，则需要加global,如果是回话级别，则需要加session,如果不写，默认为session

1.全局变量

针对于所有的会话连接有效，服务器重启后会重置为修改前的

2.会话变量

仅仅针对于当前会话

# 查看所有系统变量
show global|session variables;

# 查看满足条件的部分系统变量
show global|session variables like '%char%'

# 查看指定的某个系统变量值
select @@global|session .[system_variables]

# 为某个系统变量赋值
# 方式1
set global|session system_variables=value
# 方式2
set @@global|session system_varibales=value

# 查看所有的会话变量
show session variables;

二、自定义变量

用户自定义的，不是有系统默认提供的；使用步骤 声明->赋值->使用（查看，比较，运算等）

1.用户变量

针对于当前会话（连接）有效，同于会话变量的作用域,应用在任何地方，

#声明并初始化
set @user_variables=value
set @user_variables:= value
select @user_variables:=value

# 赋值（更新用户变量的值）
# 方式一 同声明并初始化
# 方式二 ：通过select into 
select field into user_variables from table_name

#使用
select user_variable

2.局部变量

作用域仅仅在定义它的begin end块中有效

#声明
declare 变量名 类型

#赋值
declare 变量名 类型 default value
set user_variables=value
set user_variables:= value
select @user_variables:=value
select field into user_variables from table_name

# 使用
select user_variables

存储过程

一组预先编译好的sql语句的集合，理解成批处理语句，优点：1.提高代码重用性；2.简化操作；3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

# 1.创建语法
# 参数列表包含三部分（参数模式 参数名 参数类型）
# 如果body中只有一条语句，可以省略begin...end
# body中的每条sql语句结尾要求必须加分号（;）
# 存储过程结尾也可以使用delimiter重新设置（delimiter 结束标记），如：delimiter $
#create procedure 存储过程名(arguementList)
begin
	body(一组合法的sql语句)
end

# 调用语法
call 存储过程名（实参列表）

参数模式

IN:该参数可以作为输入，也就是该参数需要调用方传入值

OUT:该参数可以作为输出（即返回值）

INOUT:既可以用作输入，也可以用作返回

案列

select * FROM user_info;

# 定义结束符
delimiter $
create PROCEDURE mypl()
BEGIN
	insert into user_info(username,user_sex,user_age) value('咱三','男',14),('咱四','女',14);
end $

#调用无参存储过程
call mypl();

# 创建待IN模式参数的存储过程
create procedure test2(in uname varchar(50))
BEGIN
	select * from user_info where username=uname;
end

call test2('战三')

#创建带out模式参数的存储过程
create PROCEDURE test3(in username varchar(50),out age int)
BEGIN
	select u.user_age into age from user_info u WHERE u.username=username;
END
call test3('战三',@age);
select @age

#创建带INOUT模式的存储过程
# 传入a,b；将a,b都翻倍返回
create PROCEDURE test4(INOUT a int,inout b int)
begin
	set a = a*2;
	set b = b*2;
END

set @a = 2;
set @b = 3;
call test4(@a,@b);
select @a,@b

存储过程删除

drop procedure 过程名

查看存储过程的信息

show create procedure 过程名

存储函数

类似于存储过程，有且仅有一个返回结果

创建语法

create function 函数名（参数列表） returns 返回类型

begin

​ body(函数体)

end

参数列表包含两部分：（参数名 参数类型）

函数体必须有return语句，如果没有会报错，仅有一行语句时可省略begin…end

调用语法

select 函数名（参数列表）

案列演示

# 无参有返回
# 创建无参函数时，如果出现1418错误，说明我们开启了bin-log,可以修改全局变量
# set global log_bin_trust_function_creators = true,否则必须为函数加入参数


set global log_bin_trust_function_creators = true


delimiter $
create function testFun1() RETURNS INT
begin
	#申明变量并设置初始值
	DECLARE total INT DEFAULT 0;
	select count(*) into total from user_info;#赋值
	return total;
end $

select testFun1();

## 创建有参构造函数

delimiter $
create function testFun2(username VARCHAR(50)) returns int
BEGIN
		DECLARE age int DEFAULT 0;
		select user_age	into age from user_info u WHERE u.username=username;
		return age;
end $

SELECT testFun2('战三')
    
## 删除函数
drop function testFun2；

#### 查看函数的信息
show create function testFun2