### 实现从表中查询所有学生基本信息的存储过程（存储过程名称一定要为 proc_student_info

1
2
3
4
5

create procedure proc_student_info
as
Begin
    select * from student
End

1

exec proc_student_info

创建一个带参数的存储过程，输出指定学号的学生信息；

1
2
3
4
5
6

create procedure proc_sno
@sno_input varchar(30)
as
Begin
    select * from student where sno = @sno_input
End

1

exec proc_sno '1001'

创建一个带参数的存储过程，根据指定参数增加学生信息，如果学生编号已经存在则不能增加(调用此存储过程时，会依次填充各个字段值，请注意 insert 时，参数顺序与表字段的顺序一致)；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

create procedure proc_add
@sno varchar(100),
@sname varchar(100),
@sex varchar(100),
@birthday date,
@discipline varchar(100),
@school varchar(100)
as
Begin
    if exists(select * from student where sno = @sno)
        print 'Already have a primary key '+@sno
    else
        insert into student values(@sno,@sname,@sex,@birthday,@discipline,@school)
End

1
2
3
4

exec proc_add '1004','HMM','female','2019-6-2','English','national school'
go
exec proc_student_info
go

创建一个带参数的存储过程，删除指定学号的学生信息。若成功，则输出 successfully deleted ；若没有该学号，则输出 No such student

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

create procedure student_del
@sno varchar(100)
as
Begin
    if exists (select * from student where sno = @sno)
    Begin
        delete from student where sno = @sno
        print 'successfully deleted'
    End
    else
    print 'No such student'
End

1
2
3
4

go
exec student_del '1001'
go
exec proc_student_info