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