这里写的是SqlServer 2005的存储过程 数据库都差不多 供参考! create database svse go use svse go create table useinfo ( useid int identity(100,1) primary key, usename varchar(30), useage int ) create table score ( scoreid int identity(200,1) primary key, english int, math int, chinese int, useid int references useinfo (useid) ) --插入存储过程 create proc proc_insert @usename varchar(30), @useage int, @english int, @math int, @chinese int, @useid int as insert into useinfo values(@usename,@useage) insert into score values(@english,@math,@chinese,@useid) go execute proc_insert '王玮',24,99,99,99,100 execute proc_insert '刘获',24,100,99,98,101 execute proc_insert '邵振',23,98,96,95,102 execute proc_insert '胡康',24,40,50,60,103 --查询存储过程 create proc proc_select as select * from useinfo select * from score go execute proc_select --无参求平均分 drop proc proc_avg create proc proc_avg as declare @avg_score float select @avg_score=avg((chinese+english+math)/3) from score print'考试平均成绩:' + convert(varchar(50),@avg_score) go execute proc_avg --带参的平均分 drop proc proc_avg1 create proc proc_avg1 @avgnum int = 0 as declare @avg_score float select @avg_score=avg((chinese+english+math)/3) from score print'考试平均分数:'+ convert(varchar(50),@avg_score + @avgnum) print'优秀学员的名单:' select usename 姓名,useage 年龄,english 英语,math 数学,chinese 语文 from useinfo u,score s where (chinese+english+math)/3>86 and u.useid = s.useid go execute proc_avg1 execute proc_avg1 2 --输出带参数的存储过程 --使用输出参数,创建存储过程时在参数后面需要使用关键字“output”,调用时也需要在变量后跟随“output”关键字 drop proc proc_avg create proc proc_avg @num int=0, @cnt int output as declare @avg_score float select @avg_score=avg((chinese+english+math)/3)from score print'考试平均成绩:'+convert(varchar(30),@avg_score) print'优秀线:'+convert(varchar(30),@avg_score + @num) print'优秀学员的名单' select u.usename 姓名,u.useage 年龄,s.english 英语,s.math 数学,s.chinese 语文 from useinfo u,score s where((chinese+english+math)/3)>80 + @num and u.useid = s.useid select @cnt=count(*)from score where((chinese+english+math)/3)>40+@num go declare @cnt int execute proc_avg 2,@cnt output print'优秀学员的人数:'+convert(varchar(10),@cnt) go --raiserror 返回用户定义的错误信息 drop proc proc_raiserror create proc proc_raiserror @num int=0, @cnt int output as declare @avg_score float select @avg_score=avg((chinese+english+math)/3)from score print'学员的品均分:'+convert(varchar(20),@avg_score) if(not((@avg_score+@num)between 0 and 100)) begin raiserror('不在1--100之间,重新设置上浮分数',15,2) return --立即返回 结束存储过程的执行 end print'优秀分数:'+convert(varchar(20),@avg_score+@num) print'优秀学员的名单' select usename,useage,chinese,english,math from useinfo u,score s where u.useid>102 and u.useid = s.useid select @cnt=count(*) from score where((chinese+english+math)/3)>80 +@num go declare @cnt int execute proc_raiserror 2,@cnt output print'优秀学员人数'+convert(varchar(30),@cnt) go --------练习4 drop proc proc_4 create proc proc_4 @name varchar(20)='*', @number int=0 as --select @number =count(*) from useinfo where usename=@name if @name='*' begin select * from useinfo u,score s where u.useid = s.useid end else begin select @number =count(*) from useinfo where usename=@name if @number>0 begin print'学生存在:'+convert(varchar(20),@number) select * from useinfo u,score s where u.useid = s.useid and @name=u.usename end else begin print'学生不存在:'+convert(varchar(20),@number) end end go execute proc_4 go --------------------------------------------------------------- create table u ( uid int primary key, uname varchar(20) ) --添加存储过程 create proc proc_add @uid int, @uname varchar(20) as insert into u values(@uid,@uname) go execute proc_add 111,'wangwei' execute proc_add 112,'liuhuo' ---删除存储过程 drop proc proc_delete create proc proc_delete @id int as delete from u where uid=@id go execute proc_delete 111 select * from u --更新存储过程 drop proc proc_update create proc proc_update @uname varchar(40), @uid int as update u set uname=@uname where uid=@uid go execute proc_update 'xuexue',112 --查询存储过程 create proc proc_select1 @uid int as select * from u where uid=@uid go execute proc_select1 112