存储进程SQL ServerITeye - 凯发娱乐

存储进程SQL ServerITeye

2019年03月20日14时47分29秒 | 作者: 凌寒 | 标签: 进程,存储,参数 | 浏览: 2226

1.运用waitfor句子表明等候一分钟后,显现sex表中的信息。

waitfor delay 00:01:00
select * from sex;

 

2.运用waitfor句子表明在13:49:40时刻,显现student表中的信息。

waitfor time 13:49:40
select * from student;

3.根本存储进程,不带参数。

create procedure pro_first
 select * from student;

4.两种存储进程的履行方法。

execute pro_first;
exec pro_first;

5.带参数的存储进程及运转方法,查询操作。

create procedure pro_second
@sex int
 select * from student where ssex=@sex;
exec pro_second @sex=1;

6.带参数的存储进程及运转方法,刺进操作。

if exists(select name from sysobjects where name=pro_third)
drop procedure pro_third
create procedure pro_third
@sno int,@sname varchar(10),@ssex int,@sbirth datetime,@sstate int
 insert into student values(@sno,@sname,@ssex,@sbirth,@sstate);
exec pro_third 66,name,1,19870810,0;

7.带默许参数的存储进程,及三种履行进程,当参数值缺省的时分,用默许参数。

if exists(select name from sysobjects where name=pro_fourth)
drop procedure pro_fourth
create procedure pro_fourth
@ssex int=1,@sstate int=0
 select s.sno,s.sname,sex.sexname,c.cname,g.scgrade 
 from student s,sex,course c,grade g
 where s.sno=g.sno
 and c.cno=g.cno
 and s.ssex=sex.sexno
 and s.ssex=@ssex
 and sstate=@sstate;
exec pro_fourth;
exec pro_fourth @ssex=2;
exec pro_fourth @ssex=3,@sstate=1;

8.带输出参数的存储进程,及履行进程。

运转带输出参数的存数进程,有必要预先声明一个变量以存储输出的值,变量的数据类型应该同输出参数的数据类型相匹配。

if exists(select name from sysobjects where name=pro_fifth) 
drop procedure pro_fifth 
create procedure pro_fifth 
@sno int ,@avg int output 
select @avg=avg(scgrade) 
from student left join grade on student.sno=grade.sno 
where student.sno=@sno 
group by student.sno,student.sname; 
declare @result int 
exec pro_fifth 1,@result output 
print 均匀成果为+cast(@result as varchar(20)); 
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章