三个表结构：
不废话直接上代码
use Text --数据库名
if exists
(select name from sysobjects where name ='up_getallstudents' and type ='p')
drop procedure up_getallstudents
--编写存储过程up_getallstudents,用于获取学生表students的所有记录
GO
create procedure up_getallstudents
as
select * from T_stu_cou
--调用
exec up_getallstudents
if exists
(select name from sysobjects where name ='up_insertstudent' and type ='p')
drop procedure up_insertstudent
GO
create procedure up_insertstudent
@sid int,
@sname varchar(30),
@sphone bigint
as
begin
insert into T_Stuinfo(stu_id,stu_name,stu_phone)
values
(@sid,@sname,@sphone)
end
--调用
exec up_insertstudent 120910102, 'wwqqq', 12890098733
if object_id('up_delstudentbyname','p') is not null
drop procedure up_delstudentbyname
go
create procedure up_delstudentbyname
@sname varchar(30)
as
begin
delete from T_Stuinfo
where stu_name=@sname
end
--调用
exec up_delstudentbyname 'ww'
select * from T_Stuinfo
if object_id('up_getstuinformationbyname','p') is not null
drop procedure up_getstuinformationbyname
go
create procedure up_getstuinformationbyname
@sname varchar(30)
as
begin
select *
from T_Stuinfo "S",T_course "C",T_stu_cou "CS"
where
"S".stu_id = "CS".stu_id
and
"CS".cou_id="C".cou_id
and
stu_name=@sname
end
--调用
exec up_getstuinformationbyname 'ww'
--2、带默认值的参数
--编写一个存储过程up_insertstuwithdefault，给参数定义默认值，完成学生表students数据的插入
if object_id('up_insertstuwithdefault','p') is not null
drop procedure up_insertstuwithdefault
go
create procedure up_insertstuwithdefault
@sid int,
@sname varchar(30),
@sphone bigint=12347890222
as
begin
insert into T_Stuinfo(stu_id,stu_name,stu_phone)
values
(@sid,@sname,@sphone)
end
--调用
exec up_insertstuwithdefault @sid='120902054' ,@sname='给参数定义默认值'
select * from T_Stuinfo
if object_id('up_getAvgScorebyname','p') is not null
drop procedure up_getAvgScorebyname
go
create procedure up_getAvgScorebyname
@aname varchar(30),
@avgscore int output
as
begin
select
@avgscore=avg(cou_grade) from T_Stuinfo "S", T_stu_cou "CS"
where
"S".stu_id="CS".stu_id and "S".stu_name=@aname
end
--调用
declare @avgscoreresult int --@avgscore可以改成任意变量名
exec up_getAvgScorebyname 'ww',@avgscoreresult output
print @avgscoreresult
后续会继续更新，希望会对大家有帮助