首页 > 数据库技术 > 详细

数据库(八)

时间:2014-11-07 20:35:17      阅读:157      评论:0      收藏:0      [点我收藏+]

上一节小练习:

bubuko.com,布布扣

存储过程 

    存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数来执行它。

bubuko.com,布布扣

bubuko.com,布布扣

bubuko.com,布布扣

bubuko.com,布布扣

注:return要放在if里或者放到最下,否则会影响下面代码执行,因为遇到return就会中断存储过程

 

--------带语句带参数的存储过程
alter proc fourproc
@name varchar(20)
as
begin
declare @jscode int,@kecheng varchar(20)
select @jscode=bianhao,@kecheng=kecheng from jiaoshibiao where xingming=@name

declare @count int

if @kecheng=‘语文‘
begin
select @count=count(*) from fenshubiao where xuehao in (
select xuehao from xueshengxinxi where yuwenbianhao=(select bianhao from jiaoshibiao where xingming=@name)
) and yuwenfenshu>=80
end
if @kecheng=‘数学‘
begin
select @count=count(*) from fenshubiao where xuehao in (
select xuehao from xueshengxinxi where shuxuebianhao=(select bianhao from jiaoshibiao where xingming=@name)
) and shuxuefenshu>=80
end
if @kecheng=‘英语‘
begin
select @count=count(*) from fenshubiao where xuehao in (
select xuehao from xueshengxinxi where yingyubianhao=(select bianhao from jiaoshibiao where xingming=@name)
) and yingyufenshu>=80
end

if @count>=3
begin
print ‘达标‘
end
else
begin
print ‘不达标‘
end

end
go
exec fourproc ‘老王‘

 

练习:

-----输入学号,判断学生结业、优秀、不结业、
create proc fiveproc
@xuehao int
as
begin
declare @yuwenfenshu int,
@shuxuefenshu int,
@yingyufenshu int,
@zongfen int

select @yuwenfenshu=COUNT(*)from fenshubiao where xuehao=@xuehao and yuwenfenshu>=60
select @shuxuefenshu=COUNT(*)from fenshubiao where xuehao=@xuehao and shuxuefenshu>=60
select @yingyufenshu=COUNT(*)from fenshubiao where xuehao=@xuehao and yingyufenshu>=60
set @zongfen=@yuwenfenshu+@shuxuefenshu+@yingyufenshu
if @zongfen=3
begin
print ‘优秀‘
end
if @zongfen=2
begin
print ‘结业‘
end
if @zongfen=1
begin
print ‘不结业‘
end
if @zongfen=0
begin
print ‘重修‘
end
end
go
exec fiveproc 201106001

数据库(八)

原文:http://www.cnblogs.com/mxx0426/p/4082214.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!