MySQL的存储过程(procedure)，也叫存储程序。它是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。可以通过存储过程的名称对其进行调用。
存储过程跟触发器有点类似，都是一组SQL集，但存储过程是主动调用的，且功能比触发器更加强大，触发器是某种SQL行为后自动触发的。
应用场景：
当用多种语言编写应用程序，或应用程序在不同平台上运行且需要执行相同的数据库操作时。
当安全极为重要时。比如，银行对某些数据库操作使用存储过程。
优点
存储过程的sql语句已经预编译过了，因此再来调用它，运行的速度比较快。
存储过程可以包含流程控制语句、逻辑判断和数据库查询语句，隐藏了处理逻辑，实现了代码的封装。
1. 创建和调用存储过程
基本语法：
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
存储过程可以带参数，参数的形式为：[ IN | OUT | INOUT ] param_name type
IN 表示参数是输入参数，参数的值必须在调用存储过程时指定，如果在存储过程内部改变了该参数的值，并不会改变存储过程外部的该参数的值。默认是输入参数。
OUT 表示参数是输出参数，参数的值不能在调用存储过程时指定，如果在存储过程内部改变了该参数的值，也会改变存储过程外部的该参数的值。
INOUT 表示参数是输入输出参数，参数的值可以在调用存储过程时指定，如果在存储过程内部改变了该参数的值，也会改变存储过程外部的该参数的值。
示例1：不带参数的存储过程
mysql -uroot -p123456 test
# 连接MySQL，并选择test数据库
\d //
# 将sql语句的定界符改为//，默认的分割符是分号。 也可以写成 delimiter //
create procedure p1()
begin
set @i=1;
while @i<=5 do
select @i;
set @i=@i+1;
end while;
end
//
这样，就创建好了一个存储过程p1，调用方式如下：
\d ;
# 将定界符还原为分号
call p1();
# 调用p1，就会执行存储过程里的sql语句
示例2：带输入参数的存储过程
delimiter //
CREATE PROCEDURE p2(IN p_in int)
begin
select p_in;
set p_in=5;
select p_in;
end
//
调用方式如下：
delimiter ;
set @p_in=2;
call p2(@p_in);
示例3：带输出参数的存储过程
delimiter //
CREATE PROCEDURE p3(OUT p_out int)
begin
select p_out;
set p_out=5;
select p_out;
end
//
调用方式如下：
delimiter ;
SET @p_out=3;
call p3(@p_out);
示例4：带输入输出参数的存储过程
delimiter //
CREATE PROCEDURE p4(INOUT p_inout int)
begin
select p_inout;
set p_inout=5;
select p_inout;
end
//
调用方式如下：
delimiter ;
SET @p_inout=4;
call p4(@p_inout);
说明：
存储过程可以带参数，也可以没有参数。如果声明了参数，无论是输入参数、输出参数还是输入输出参数，调用时就必须传递。多个参数之间用逗号分隔。
简单来说，输入参数就是供存储过程内部使用，就算在内部改变它的值，也不会影响外部;输出参数在存储过程内部不能获得它的初始值，但能改变它的值，并影响外部;输入输出参数可在存储过程内部获得它的初始值，也可改变它的值，并影响外部。
2. 变量
2.1 声明局部变量
语法：?DECLARE var_name[,...] type [DEFAULT value]
声明局部变量时，如果没有DEFAULT子句，初始值为NULL。
局部变量的作用范围在它被声明的BEGIN ... END块内。
delimiter //
CREATE PROCEDURE p5()
begin
DECLARE p_a int unsigned default 1;
DECLARE p_b varchar(20) default 'test';
select p_a;
select p_b;
end
//
2.2 变量赋值
给变量赋值，有两种方式：
SET语句
delimiter //
CREATE PROCEDURE p6()
begin
DECLARE p_a int unsigned default 1;
DECLARE p_b varchar(20) default 'test';
set p_a = 200;
set p_b = 'hello';
select p_a;
select p_b;
end
//
SELECT ... INTO语句
delimiter //
CREATE PROCEDURE p7()
begin
DECLARE p_a int unsigned default 1;
DECLARE p_b varchar(20) default 'test';
SELECT id,name INTO p_a,p_b FROM test.t1 LIMIT 1;
select p_a;
select p_b;
end
//
2.3 用户变量
用户变量的形式为：?@var_name
用户变量与当前的连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接中的所有用户变量将自动释放。
设置用户变量的一种方式是执行SET语句：
语法：?SET @var_name = expr [, @var_name = expr] ...
对于SET，可以使用=或:=作为分配符。分配给每个变量的值可以为整数、小数、字符串或者NULL值。
SET @a=1,@b:=2.5;
SET @c:=@a+@b;
select @a,@b,@c;
也可以用SQL语句代替SET来为用户变量分配一个值。此时，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较运算符。
SET @t1=0, @t2=0, @t3=0;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
说明：
用户变量以@开头。
用户变量名不区分大小写。
不要滥用用户变量。
2.4 系统变量
系统变量主要是和服务器运行有关的变量，具体可参考MySQL手册。
3. 修改存储过程
基本语法：
ALTER PROCEDURE sp_name ...
4. 查看存储过程
查看所有的存储过程：
show procedure status;
或
select name from mysql.proc;
查看指定数据库里的存储过程：
show procedure status where db='test';
或
select name from mysql.proc where db='test';
查看存储过程的详细信息:
show create procedure p1;
5. 删除存储过程
drop procedure if exists p1;
6. 存储过程和存储函数的区别
存储过程和存储函数统称为存储例程(stored routine)，两者的语法比较类似。
存储函数限制比较多，存储过程的限制相对就比较少。 存储过程实现的功能要复杂一点，而存储函数实现的功能针对性比较强。
存储过程使用 PROCEDURE 关键字，存储函数使用 FUNCTION 关键字。
存储过程的参数有三种类型(in|out|inout)，存储函数的参数就一种类型。
存储过程的返回值可以有多个，存储函数的返回值只能有一个。
存储过程只能通过call语句进行调用，而存储函数可以直接在SQL语句中使用，和其他内置函数一样。
下面是一个创建和使用存储函数的例子：
delimiter //
create function diy_max(a int, b int) returns int
begin
declare c int;
if a>=b then set c=a;
else set c=b;
end if;
return c;
end
//
delimiter ;
select diy_max(3,7);
如对本文有疑问， 点击进行留言回复！！
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火，普罗米修斯的崛起
我要评论
网友评论