1.存储过程是什么?
在工作中经常会遇到重复性工作,这时候就可以用SQL把过程存储起来。下次遇到一样的问题时候,就不需要重新写一遍纯SQL,就可以直接调用。提高了工作效率。
【优点】:提高效率
【缺点】:扩展功能不方便
2.存储过程类型:
1)无参数的存储过程
delimiter//
create procedure p1()
Begin
<SQL 语句>;
end//
delimiter;
<SQL语句>是重复使用的SQL语句。
【栗子】:
delimiter//
create procedure p1()
Begin
select * from table;
insert into table(name,sub_time) values ("xxx",now());
End//
delimiter;
下次需要使用这个存储过程时候就可以用以下脚本来调用sql,不需要重新写一遍SQL
call 存储过程名称()
【栗子】:
call p1();
(2)有参数存储过程
前面无参数的存储过程名称后面是(),括号里面没有参数
delimiter//
create procedure 存储过程名称(参数1,参数2,....)
begin
<SQL语句>
end;//
delimiter
【栗子】:
delimiter//
create procedure getNum(num varchar(100))
Begin
select Name
from student
where studentnumber=num;
end;//
delimiter
栗子中,studentnum对应的num就是参数变量。因为每个业务场景下需要获取的studentnum有可能不一样,有可能今天是找0001,明天是找0002号。所以在这里num是参数。
而create procedure后面的getNum是存储过程的名称。后面括号里面的num varchar(100)就是参数的定义。num为参数,varchar(100)则是参数的类型定义。字符串类型。
如果现在要查询studentnum=0001的学生姓名,就可以利用存储过程来获取。
call getNum(0001);
3)默认参数的存储过程
前面的一般由参数的存储过程只含参数类型和名字,方便调取使用。
而默认参数的存储过程。可以接受参数共有三类:
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
in 传入参数:参数初始值在存储过程钱被指定为默认值,在存储过程中修改该参数的值不能被返回。
set @ num=0;
delimiter//
create procedure p2 (in num int)
Begin
select num; --初始值已被定义为0
set num=1;
select num;---此处把num设为1
end;//
delimiter;
--in 参数 调用
call p2 (@ num)
select num;---num仍然是0,不能返回1;
out输出参数:参数初始值为空,该值可在存储过程内部被改变,并可返回
set @ num=0;
create procedure p3 (out num int)
begin
select num;--初始值为null
set num=1;
select num;--初始值为1
end;
--out 参数 调用
call p3 (@num);--num参数可返回1
inout输入输出参数:参数初始值在存储过程钱被指定为默认值,并且可在存储过程中被改变和在调用完毕后可被返回。跟out输出参数的区别在于,应用out参数时,初始值被定义为null;应用inout参数时,初始值被定义为0。
set @num=0;
create procedure p4(inout num int)
begin
select num;--初始值被定义为0;
set num=1;
select num;--初始值被定义为1;
end;
--in 参数 调用
call p4(@num);
select num;--num值返回1
3.调用存储过程
--无参数
call p()
--有参数,全in
call p(1,2)
--有参数,有in,out,inout
set @t1=0;
set @t2=3;
call p(1,2,@t1,@t2)
4.删除/更改存储过程
--修改存储过程
alter procedure proc_name
--删除存储过程
drop procedure proc_name;
5.查看存储过程
1)使用sys.sql_modules查看存储过程的定义
sys.sql_modules为系统视图,通过该视图可以查看数据库中存储过程。
select * from sys.sql_modules
2)使用object_definition查看存储过程的定义
返回指定对象定义的Transact_sql源文本。Oject_definition(object_id)
select OBJECT_DEFINITION(309576141)
3)使用sp_helptext 查看存储过程的定义
sphelptext 'Proc_Stu'