存储过程实验

 

1、使用不带参数的存储过程

（1）创建一个存储过程my_proc，查询“学生表”中所有计算机系女生的学号、姓名、性别、年龄和所在院系。

create proc my_proc as

select 学号,姓名,性别,年龄,所在院系

from students

where 性别='女' and 所在院系='计算机'

Return

（2）执行存储过程

exec my_proc

（3）修改存储过程，使其能够查询计算机系女生的所有基本信息

alter proc my_proc as

select *

from students

where 性别='女' and 所在院系='计算机'

Return

 

2、带输入参数的存储过程

（1）创建一个存储过程my_procsex，使其能够查询“学生表”中男学生或女学生的学号、姓名、性别、年龄和所在院系

Create proc my_procsex @sex varchar(128) as

select 学号,姓名,性别,年龄,所在院系

from students

where 性别=@sex

return

（2）执行存储过程

exec my_procsex @sex='男';

exec my_procsex @sex='女';

 

      

3、带输入/输出参数的存储过程

（1）创建一个存储过程my_procage，使其能够根据学生姓名，查询学生年龄。（考虑当学生不存在时给出提示信息）

1)创建存储过程my_procage:

drop proc my_procage

Create proc my_procage @name varchar(80),@age int output

as

if not exists(

       select *

       from students

       where 姓名=@name)

return -1

select @age=年龄

from students

where 姓名=@name

return

 2）执行存储过程

--1.没有张键这个学生

Declare @status int

declare @agedata int

exec @status=my_procage @name='张键',@age=@agedata output

if @status = -1

print '不存在此学生'

else

print '此学生年龄为'+convert(varchar(10),@agedata)

--2.存在李涛这个学生

Declare @status int

declare @agedata int

exec @status=my_procage @name='李涛',@age=@agedata output

if @status = -1

print '不存在此学生'

else

print '此学生年龄为'+convert(varchar(10),@agedata)

    以上存储过程执行的几种不同情况比较了带参输入/输出。由于SQL Server 定义了一张内部状态码表。所以如果存储过程中出现表中定义的错误，则返回一个状态码，客户端随即根据返回的状态码值判断出错原因，打印错误提示信息。

 

4、返回状态值的存储过程

（1）创建一个存储过程my_procstatus，使其能够根据学生姓名，查询学生的选课信息。（如果没有输入学生姓名，返回状态码55；如果输入的学生姓名不存在，则返回状态码-155）

drop proc my_procstatus

create proc my_procstatus @name varchar(80)

as

if @name=''

return 55

else if not exists(

            select *

            from students

            where 姓名=@name)

return -155

else

            select g.课程号

            from students s right outer join grade g on s.学号=g.学号

            where s.姓名=@name

return

（2）执行存储过程：接收存储过程返回的状态码，如果返回的状态码为55则输出提示信息“没有输入名字！！”；如果返回的状态码为-155，则输出“没找到！！”。

Declare @status int

exec @status=my_procstatus @name=''

if @status = 55

print '没有输入名字'

else if @status = -155

print '没找到'

（3）删除存储过程

drop proc my_procstatus

上述过程中，值得注意的是两种返回方式的比较：

    如果输入为空值，则返回给服务器的是status值55；如果输入的值在数据库中无法检索到，则返回给服务器的status是-155。这种方式的好处是返回给服务器的是一个整形值，简单方便且给服务器很小的压力，一个短小的整形状态码代表了一类错误状态。否则返回给服务器的只能是字符串信息。(2)中服务器运行存储过程，如果@name为空，则服务器给客户端返回一个状态码，客户端根据状态码打印相应的提示信息。

5、在存储过程中使用临时表，不指定题目，请理解全局临时表与局部临时表的区别

  创建存储过程temp_proc,查询成绩表grade全部记录，放到局部临时表temp_1和全局临时表temp_2中，从两张临时表读全部记录，分析差别

create proc temp_proc

as

select * into #temp_1

from grade

select * into ##temp_2

from grade

Return

exec temp_proc

 

select * from #temp_1

select * from ##temp_2

这里两种不同的临时表区别如下：

使用临时表
临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。