存储过程SQL Server
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
as
select * from student;
4.两种存储过程的执行方式。
execute pro_first;
exec pro_first;
5.带参数的存储过程及运行方式，查询操作。
create procedure pro_second
@sex int
as
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
go
create procedure pro_third
@sno int,@sname varchar(10),@ssex int,@sbirth datetime,@sstate int
as
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
go
create procedure pro_fourth
@ssex int=1,@sstate int=0
as
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
go
create procedure pro_fifth
@sno int ,@avg int output
as
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));
页:
[1]