SQL Sever-存储过程

--1.不带参数的存储过程
use jwt
go
if exists(select * from sysobjects where name='pr_stu1' and type='P')
drop procedure pr_stu1 --检查是否存在pr_stu1，存在则删除
go

create procedure pr_stu1 --创建存储过程
as select * from S

exec pr_stu1   --执行存储过程

--2.带输入参数的存储过程
--2.1输入学号，找到该同学的信息
use jwt
go
if exists(select * from sysobjects where name='pr_stu2' and type='P')
drop procedure pr_stu2
go

create procedure pr_stu2 @stuid char(7)
as select * from S
where SN0=@stuid

exec pr_stu2 'S1' --或者 exec pr_stu2 @stuid='S1'

--2.2输入学号或姓名，找到该同学信息
use jwt
go
if exists(select * from sysobjects where name='pr_stu3' and type='P')
drop procedure pr_stu3
go

create procedure pr_stu3 @stuid char(7),@stuname char(8)
as select * from S
where SN0=@stuid or SNAME=@stuname

exec pr_stu3 'S1','' --或者 exec pr_stu3 'S1','jwt'    ''中不记得可以不填

--3.带输出参数的存储过程
--3,1输入一个学号，输出该同学的姓名
use jwt
go
if exists(select * from sysobjects where name='pr_stu4' and type='P')
drop procedure pr_stu4
go

create procedure pr_stu4 @stuid char(7),@stuname char(8) output
as select @stuname=SNAME from S
where SN0=@stuid
go

declare @sname_out char(8) --声明变量 上面的@stuname是局部变量 这个@sname_out相当于实参
exec pr_stu4  'S1',@sname_out output--执行存储过程
print 'S1同学的姓名为：'+@sname_out

--3.2输入一个学号，输出该同学的姓名和性别
use jwt
go
if exists(select * from sysobjects where name='pr_stu5' and type='P')
drop procedure pr_stu5
go

create procedure pr_stu5 @stuid char(7),@stuname char(8) output ,@stusex char(3) Output
as select @stuname=SNAME,@stusex=SEX from S
where SN0=@stuid
go

declare @sname_out char(8),@ssex_Out char(3) 
exec pr_stu5  'S1',@sname_out output,@ssex_out output --执行存储过程
print 'S1同学的姓名为：'+@sname_out+'性别为：'+@ssex_out

--4.使用存储过程实现向学生信息表插入一条记录的操作
use jwt
go
if exists(select * from sysobjects where name='insert_StuInfo' and type='P')
drop procedure insert_StuInfo --检查是否存在insert_SC，存在则删除
go

create procedure insert_StuInfo 
@stusno char(10),@stuname char(8),@stusex char(8),@stuage char(8),@stunum char(8)
as 
    insert into StuInfo(学号,姓名,性别,年龄,电话号码)
    values(@stusno ,@stuname ,@stusex ,@stuage,@stunum )
    exec insert_StuInfo '3120170906219','jwtaa','女','18','10000'
    select * from StuInfo --查看表

--5.在学生信息表中，修改所给的学号相同的记录，用存储过程实现
use jwt
go
if exists(select * from sysobjects where name='update_S' and type='P')
drop procedure update_S 
go

create procedure update_S @stusno char(7)
as 
    update S
    set SNAME='jjwt'
    where SN0=@stusno
go

--6.在学生信息表中，删除和所给的学号相同的记录，用存储过程实现
use jwt
go
if exists(select * from sysobjects where name='delete_S' and type='P')
drop procedure delete_S 
go

create procedure delete_S 
@stusno char(7)
as 
    delete S
    where SN0=@stusno
go

--7.使用存储过程实现向学生成绩表插入一条记录的操作
use jwt
go
if exists(select * from sysobjects where name='insert_SC' and type='P')
drop procedure insert_SC --检查是否存在insert_SC，存在则删除
go

create procedure insert_SC 
@stusno char(7),@stucno char(8),@stugrade char(8)
as 
    insert into SC(SNO,CNO,GRADE)
    values(@stusno ,@stucno ,@stugrade)
go

--8.在学生信息表中，删除和所给的学号，课程号都相同的记录，用存储过程实现
use jwt
go
    if exists(select * from sysobjects where name='delete_SC' and type='P')
    drop procedure delete_SC
go

create procedure delete_SC 
@stusno char(7),@stucno char(7)
as 
    delete SC
    where SNO=@stusno and CNO=@stucno
go