MySQL系列(九)：存储过程

 

一、概念

存储过程简称过程(procedure)，是一种用来处理数据的方式，存储过程是一种没有返回值的函数。

存储过程(procedure)概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。

在封装的语句体里面，可以进行sql编程，可以同if/else 、case、while等语句结构。

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

• 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。
• 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
• 存储过程可以重复使用，可减少数据库开发人员的工作量。
• 安全性高，可设定只有某些用户才具有对指定存储过程的使用权

二、创建存储过程

通常情况下，";"表示SQL语句结束，同时向服务器提交并执行。

但是存储过程中有很多SQL语句，每一句都要以分号隔开，这时候我们就需要使用其他符号来代替向服务器提交的命令。通过delimiter命令更改语句结束符。

[delimiter @@]
create procedure 过程名字([参数列表])
begin

    -- 过程体

end
[@@]
[dilimiter ;]

存储过程的参数分为输入参数(in)、输出参数(out)、输入输出参数(inout)，默认是输入参数。

如果存储过程中就一条语句,begin和end可以省略。

• 存储过程中，可有各种编程元素，变量、流程控制、函数调用等
• 还可以有，增删改查等各种mysql语句
• 其中select（或show、或desc）会作为存储过程执行后的"结果集"返回
• 形参可以设定数据的"进出方向"

-- 创建存储过程
create procedure pro1() select * from student;

-- 创建存储过程
delimiter $$
create procedure pro2()
begin
    select * from goods;
end
$$
delimiter ;

三、查看存储过程

-- 查看所有过程
show procedure status [like 'pattern'];

-- 查看过程创建语句
show create procedure 过程名;

四、调用存储过程

call 存储过程()

select是不能访问存储过程的。

-- 调用过程
call pro1();

五、修改与删除存储过程

不能直接修改，只能先删除，后新增。

删除存储过程：

drop procedure [if exists] 存储过程名

-- 删除过程
drop procedure pro1;

六、存储过程参数

函数的参数需要指定数据类型，存储过程比函数更严格。

存储过程还有自己的类型限定：

• in：数据只是从外部传入给内部使用(值传递)，可以是数值也可以是变量
• out：只允许过程内部使用(不用外部数据)，给外部使用的(引用传递: 外部的数据会被先清空才会进入到内部)只能是变量
• inout：外部可以在内部使用，内部修改也可以给外部使用，典型的引用传递，只能传变量

create procedure 过程名(in 形参名字 数据类型, out 形参名字 数据类型, inout 形参名字 数据类型)

• out和inout类型的参数必须传入变量，而不能是数值，二者都是引用传递
• 存储过程对于变量的操作(返回)是滞后的，是在存储过程调用结束的时候，才会重新将内部修改的值赋值给外部传入的全局变量
• 在存储过程调用结束之后，系统会将局部变量返回给全局变量(out和inout)

输入参数(in、默认)存储过程：

delimiter $$
create procedure pro_1(n int)
begin

declare i int default 1;
declare s int default 0;
while i<=n do
    set s = s + i;
    set i = i + 1;
end while;
select s;

end$$
delimiter ;

 输出参数(out)存储过程：

-- 创建存储过程
delimiter $$
create procedure pro_2(in n int,out res int)
begin

    set res = n * n;

end$$

-- 调用
set @res = 0;
call pro_2(100,@res);
select @res$$

-- 还原语句结束符
delimiter ;

输入输出参数(inout)存储过程：

-- 创建存储过程
delimiter $$;

create procedure pro_3(inout n int)
begin

    set n = n * n;

end$$

-- 调用
set @a = 20;
call pro_3(@a);
select $a$$

delimiter ;

 

转载于:https://my.oschina.net/programs/blog/1789529