存储过程概念
存储过程(stored procedure):是在大型关系型数据库中,一组为了完成特定功能的sql语句集。它存储在数据库中,一次编译永久有效(提高效率),用户通过指定存储过程名字并给出参数(如果该过程有参数)来执行它,存储过程是数据库的一个重要对象,针对sql编程而言。
与函数的区别
存储过程和函数的目的都是为了可以重复执行的操作数据sql语句的集合。
- 标识符不同,函数的标识符是function过程为procedure
- 函数中有返回值,且必须返回,过程没有返回值。
- 过程无返回值类型,不能讲结果直接赋值给变量,函数有返回值类型,调用时,结果要把返回值赋值给变量。
- Select 函数名 过程不能。
1.存储过程操作
创建过程
基本语法:
Create procedure 过程名字(参数列表)
Begin
过程体
End
结束符
如果过程中只有一条指令,那么可以省略begin和end
create PROCEDURE my_pro1() select * from my_student;
查看过程
Show procedure status;
调用过程
Call 过程名字(参数列表);
删除过程
Drop procedure 过程名字;
求1-100和的存储过程
delimiter $$
create procedure my_pro2()
begin
DECLARE i int DEFAULT 1; --声明局部变量 给出默认值
set @sum=0; --声明会话变量 用来存储和
while i<101 do --开启循环 求结果
set @sum=@sum+i;
set i=i+1;
end while; --结束循环
select @sum; --查询结果
end
$$
delimiter ;
call my_pro2();
2.存储过程参数
存储过程允许提供参数,存储过程的参数也和函数一样,需要指定其参数,但是存储过程对参数还有额外的要求,自己的参数分类。
In
标识参数从外部传入到过程里面使用,可以是直接的数据,也可以是保存数据的变量。
Out
标识参数从过程里面把数据保存到变量中,交给外部使用,传入的值必须是变量,如果说传入的out变量本身在外部有值,那么在进入过程之后,第一件事就是被清空。变为null
Inout
数据可以从外部传入到内部使用,同时内部操作,之后又会讲数据返回给外部。
案例
--创建3个变量
set @n1=1;
set @n2=2;
set @n3=3;
select @n1,@n2,@n3;
--创建过程
delimiter $$
create PROCEDURE my_pro999(in int_1 int,out int_2 int,inout int_3 int)
begin
select int_1,int_2,int_3; --查看三个传入变量进入过程的数据值
set int_1=10; --修改传入内部的参数的值
set int_2=100;
set int_3=1000;
select int_1,int_2,int_3; --查询修改完毕后的内部的参数的值
select @n1,@n2,@n3; --在过程内部查询外部的会话变量
set @n1='a'; --在过程内部修改外部会话变量的值
set @n2='b';
set @n3='c';
select @n1,@n2,@n3; --在过程内部查询修改后的外部会话变量的值 过程没有结束
end
$$
delimiter ;
call my_pro999(@n1,@n2,@n3);
select @n1,@n2,@n3;
分析结果
查看三个传入变量进入过程的数据值
由于int_2参数类型是out,所以进入过程内部被清空
查询修改完毕后的内部的参数的值
在过程内部可以修改传入参数的数值,正常可以修改。
在过程内部查询外部的会话变量
在过程内部可以访问外部会话变量,值没有问题
在过程内部查询修改后的外部会话变量的值 过程没有结束
在过程内部可以修改会话变量,查询修改后的变量,没有问题,过程没有结束,也就没有向外赋值的操作。
@n1的值是a,是因为我们在过程内部对外部的会话变量进行了修改。
@n2的值是100,是因为我们在过程内部对该参数的值进行了修改,并且其参数类型是out,所以过程结果之后会把内部修改的值传入外部。
@n3的值是1000,是因为我们在过程内部对该参数的值进行了修改,并且其参数类型是inout,所以过程结果之后会把内部修改的值传入外部。