实验八(存储过程 )

实验8.1 创建病执行存储过程

创建存储过程 Pro_Qsinf：通过学生学号来查询学生的姓名，年龄，姓名

/*创建存储过程Pro_Qsinf*/
create procedure Pro_Qsinf
	@sno_in char(8) ='S2',
	@sname_out char(10) output,
	@sage_out int output,
	@dept_out char(10) output
  as
  begin 
	set nocount on;
	select 
	@sname_out=SN,
	@sage_out=AGE,
	@dept_out =DEPT
	FROM Student WHERE SNO = @sno_in
  END 
GO

如下图:

/*查询存储过程*/
use testbase2
go
declare @sno_in char(8),
	    @sname_out char(8),
	    @sage_out int,
	    @sdept_out char(10)
exec Pro_Qsinf default,
		@sname_out output,
		@sage_out output,
		@sdept_out output
print @sname_out
print @sage_out
print @sdept_out
select @sno_in='S5'
exec Pro_Qsinf @sno_in,
		@sname_out output,
		@sage_out output,
		@sdept_out output
print @sname_out
print @sage_out
print @sdept_out
go

创建存储过程 Pro_Qscore

/*创建存储过程Pro_Qscore*/
create procedure Pro_Qscore
		@sname_in char(8),
		@cname_in char(10),
		@score_out tinyint output
as select @sname_in= sc.CNO from Student,C,SC
where Student.SNO =SC.SNO and c.CNO = sc.CNO and SN=@sname_in 

go
declare @sname_in char(8),
		@cname_in char(8),
		@score_out tinyint
select @sname_in ='王青山'
select @cname_in = '计算机'
exec Pro_Qscore @sname_in,@cname_in,@score_out output
print rtrim(@sname_in)+'='+ltrim(str(@score_out))
go

---

实验8.2 修改存储过程