MySQL基础之存储过程（九）

1.变量

1、系统变量： 变量由系统提供，不是用户定义，属于服务器层面。
全局变量
会话变量
2、自定义变量： 变量是用户自定义的，不是由系统提供的。
用户变量
局部变量

一、系统变量
1、查看所有的系统变量

show global|[sessin] variables;

2、查看满足条件的部分系统变量

show global|[sessin] variables like '%char%';

3、查看指定的某个系统变量的值

select @@global|[session].系统变量名 ;

4、为某个具体系统变量赋值

方式一：
set global|[session] 系统变量名=值;
方式二：
set @@global|[session].系统变量名=值;

注意：
如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，默认session。

全局变量：
作用域： 服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接），但是不能跨服务器。

show global variables;
show global variables like '%char%';
select @@global.autocommit;
set @@global.autocommit=0;

会话变量：
作用域：仅仅针对当前会话（连接）有效。

show variables;
show session variables;

show variables like '%char%';
show session variables like '%char%';

#查看指定的某个会话变量
select @@tx_isolation;
select @@session.tx_isolation;

#为某个会话变量赋值
set @@SESSION.tx_isolation='read-UNCOMMITTED';
set SESSION tx_isolation='read-committed';

二、自定义变量
使用步骤：
声明->赋值->使用
1、用户变量
作用域：针对于当前会话有效，同于会话变量的作用域。应用于任何地方。
赋值的操作符：=或 :=

#声明并初始化
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#赋值
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;

select 字段 into @变量名 from 表;
#查看
select @变量名;
案例：
#声明并初始化
set @name='john';
set @name=100;
set @count=1;
#赋值
select count(*) into @count from employees;
#查看
select @count;

2、局部变量
作用域：仅仅在定义它的begin end中有效。应用在begin end中的第一句话。

#声明
declare 变量名 类型;
declare 变量名 类型 default 值;
#赋值
方式一：
set 局部变量名=值;
set 局部变量名:=值;
select @用户变量名:=值;
方式二：
select 字段 into 局部变量名 from 表;
#使用
select 局部变量名;

局部变量于用户变量对比：

2.存储过程和函数

存储过程和函数：类似于Java中的方法。
存储过程： 一组预先编译好的sql语句的集合，理解成批处理语句。
好处：
1.提高了代码的重用性；
2.简化操作；
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。
一、创建存储过程

一、创建语法
create procedure 存储过程名(参数列表)
begin 
	存储过程体（sql语句）
end
注意：
1.参数列表包含三部分：参数模式  参数名  参数类型。
参数模式：
IN:该参数可以作为输入，该参数需要传入值
OUT：该参数可以作为输出，该参数需要返回值
INOUT：既可以作为输入，又可以作为输出
2.如果存储过程体仅仅只有一句话，begin end可以省略，存储过程体中的每条sql语句的结尾要求必须加分号，存daioy储过程的结尾可以使用delimiter 重新设置。

二、调用语法
call 存储过程名(参数列表);
1.空参调用
DELIMITER $
create  PROCEDURE myp1()
begin
	insert into admin(username,`password`)
	values('john','0000'),('lisa','1111'),('emma','2222');
	
end $
#调用
call myp1() $;

2.创建带in模式参数的存储过程
DELIMITER $
create PROCEDURE myp2(in beautyName VARCHAR(20))
begin
	select bo.*
	from boys bo
	RIGHT join beauty b on bo.id=b.boyfriend_id
	where b.name=beautyName;

end $

call myp2('王语嫣') $;

delimiter $
create PROCEDURE myp4(in username VARCHAR(20),in PASSWORD VARCHAR(20))
begin
		DECLARE result int DEFAULT 0;#声明并初始化
		
		select count(*) into result #赋值
		from admin
		where admin.username=username
		and admin.`password`=PASSWORD;
		
		select if(result>0,'成功','失败');#使用
end $

3.创建带Out模式的存储过程
delimiter $
create PROCEDURE myp6(in beautyName VARCHAR(20),out boyName VARCHAR(20),out userCP int)
begin
		select bo.boyName,bo.userCP into boyName,userCP
		from boys bo
		inner join beauty b on bo.id=b.boyfriend_id
		where b.name=beautyName;
end $

call myp6('Angelababy',@bName,@usercp) $

select @bName $
#传入女生id，查询女生姓名和电话

delimiter $
create PROCEDURE test_pro(in id int,out name VARCHAR(20),out phone VARCHAR(20))
begin 
		select b.name,b.phone  into name,phone
		from beauty b
		where b.id=id;
end $

call test_pro(1,@m,@p) $
select @m,@p $
#根据传入的条目数和七时索引，查询记录
delimiter $
create PROCEDURE test_pro2(in startIndex int,in size int)
begin
			SELECT * from beauty limit startIndex,size;

end $

call test_pro2(3,5) $;

4.创建带inout模式参数的存储过程
#传入a,b两个值，最终a,b都翻倍返回
delimiter $
create PROCEDURE myp8(inout a int,inout b int)
begin
		#赋值
		set a=a*2;
		set b=b*2;
end $

#调用
set @m=10$
set @n=20$
call myp8(@m,@n)$
select @m,@n $

二、删除存储过程

语法： drop procedure 存储过程名;

三、查看存储过程的信息

show create procedure 存储过程名;