MySQL——存储过程1（介绍、基本语法、变量）

MySQL存储过程是实现经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

1、存储过程介绍

简单一点来说就是：用户多次操作数据库，每执行一次就要进行网络传输。这对于数据库来说是不安全的，而且可能对数据库造成威胁。那么优化手段就是将多条语句封装在数据库里面（p1）中，在应用程序中执行这个逻辑（也就是调用p1）就可以对数据库进行操作啦

1.2、存储过程特点

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

1.2.2、可以接收参数，也可以返回数据：在存储过程中，可以传递参数，也可以接收返回值

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

2、存储过程基本语法

2.1、创建

create procedure p1(参数列表)
begin
    SQL语句
end;

示例

----存储过程基本语法
--创建
CREATE PROCEDURE p1()

BEGIN
    #SQL语句
    SELECT count(*) from tb_user;
END;

注意：在命令行创建的时候，因为命令行看见分号；就直接认为语句结束了，所以需要在执行上述语句之前先加上delimiter $$。

#让sql语句以$$结束
delimiter $$

------命令行
CREATE PROCEDURE p1()
BEGIN
SELECT count(*) from tb_user;
END$$

执行结束之后，记得改回来哦。delimiter ； 

2.2、调用

call p1();

2.3、查看

方法一：查询指定数据库的存储过程及状态

select * from information_schema.ROUTINES WHERE Routine_schema='t1';

方法二：查询某个存储过程的定义

#查看创建sql语句
SHOW CREATE PROCEDURE p1;

2.4、删除

drop procedure if exists p1;

3、存储过程—变量

MySQL中变量分为三种类型：系统变量、用户自定义变量、局部变量

3.1、系统变量

系统变量，顾名思义，系统，那就是系统自己定义的，不是用户定义的，属于服务器层面的。关于系统变量，又分为全局变量（global）和会话变量（session）

1）查看系统变量（不指定的话默认是session）

a、#查看所有系统变量

#查看所有系统变量
show session VARIABLES;

show global VARIABLES;

b、模糊查询系统变量

SHOW SESSION VARIABLES LIKE 'auto%';

SHOW GLOBAL VARIABLES LIKE 'auto%';

c、查看某一个系统变量名，查看指定变量的值

SELECT @@autocommit; #不指定session、global就是默认session

SELECT @@session.autocommit;
SELECT @@global.autocommit;

2）设置系统变量

#设置系统变量——会话变量
SET SESSION autocommit=0;
insert into tb_user(id,name) values (32,'Tom');#发现没有插入，原因就是自动提交关闭了
commit;  #会提交插入语句，插入成功



#记得改回来
SET SESSION autocommit=1;



#设置系统变量——全局变量
SET GLOBAL autocommit=0;
#当服务器重启之后会重新设置为1，如果想要一直不变的话，就要去/etc/my.cnf里面配置

注意：

全局变量（global）：全局变量针对于所有的会话。也就是说，当你开启两个会话的时候，另一个会话内容也会因为此会话发生变化

会话变量（session）：会话变量只是针对于单个会话。也就是说此会话执行的语句只在该会话有效，在另外一个会话中就不生效了。

3.2、用户自定义变量

用户自定义变量，顾名思义就是用户根据自己的需要自己定义变量。特点就是用户不需要提前声明，在使用的时候直接用“@变量名”即可。其作用域为当前会话，其他会话不生效。

注意：系统变量和用户自定义变量其实也就是，系统变量是两个@，用户自定义变量是一个@

3.2.1、赋值

方法一：

SET @myname='t1';
set @myage:=10;
set @mygender:='男', @phone:='12423465';

方法二：

select @myname1:='t2';

#将sql查询的结果赋值给某一个变量
select count(*) into @mycount from tb_user;

3.2.2、使用

SELECT @myname,@myage, @mygender,@myphone;
SELECT @myname1,@mycount

注意：用户定义的变量无须对其进行声明或者初始化，只不过获得的值之null

#不用声明
select @abc;

3.3、局部变量

局部变量，也即是说根据需要定义的局部生效的变量，访问之前，需要使用declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的Begin...End之间，超出范围就没有用了。

3.3.1、声明

 #如果有默认值的，用default指定

declare 变量名 变量类型 [defualt...]

变量类型就是数据库字段类型：int、bigint、char、varchar、data、time等

3.3.2、赋值

set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名...;

示例：

CREATE PROCEDURE p2()
BEGIN

		DECLARE stu_count int DEFAULT(0);
		#SET stu_count:=100;
		select count(*) into stu_count from tb_user;
		SELECT stu_count;
END; 

CALL p2();