存储过程

一、存储过程定义

定义：存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

二、存储过程的有点

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

1、存储过程允许标准组件式编程

存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。

2、存储过程能够实现较快的执行速度

如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。

3、存储过程减轻网络流量

对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。

4、存储过程可被作为一种安全机制来充分利用

系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

三、存储过程的使用

采用一个分页的例子

USE [OnlineExamSystem]
GO
/****** Object:  StoredProcedure [dbo].[teacher_view_exam_report]    Script Date: 04/26/2014 18:56:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[teacher_view_exam_report]     
    (@page_size int, --参数
    @exam_name varchar(45),
    @exam_description varchar(45),
    @from_time datetime,
    @to_time datetime,
    @start_number int) 
as
begin
if (OBJECT_ID('tempdb.#report.#report') is not null) --判断表是否为空，采用的是临时表
    drop table #report
else
begin
create table #report --创建临时表
(
    id int Identity(1,1) not null primary key,
    exam_id int not null,
    description varchar(45),
    name varchar(45) not null,
    total_score int not null,
    pass_score int not null,
    quantity int not null,
    duration int not null,
    examinee_count int not null,
    average_score int not null,
    total_pass int not null
)
end
insert into #report
select
exam.exam_id,
exam.description, 
exam.name, exam.total_score,exam.pass_score, exam.quantity, exam.duration, 
COUNT(*) as examinee_count,AVG(score.grade) as average_score,
(select COUNT( score.username) from score where score.grade > exam.pass_score and exam.exam_id = score.exam_id) as total_pass 
from exam , score 
where
exam.exam_id = score.exam_id 
and
DATEDIFF ([second], @from_time, exam.create_time) > 0
and
DATEDIFF ([second], exam.create_time, @to_time) > 0
and exam.name like '%'+@exam_name+'%'
and exam.description like '%'+@exam_description+'%'
group by exam.name, exam.total_score,exam.quantity,exam.duration, 
exam.pass_score,exam.exam_id,exam.description,exam.create_time
end

select top (@page_size)  --查询分页数据
#report.id,
#report.exam_id,
#report.name,
#report.total_score,
#report.pass_score,
#report.quantity,
#report.duration,
#report.examinee_count,
#report.average_score,
#report.total_pass,
(select COUNT(*) from #report) as total_count,
#report.description
from #report 
where #report.id>(@start_number)
group by
#report.id,
#report.exam_id,
#report.name,
#report.total_score,
#report.pass_score,
#report.quantity,
#report.duration,
#report.examinee_count,
#report.average_score,
#report.total_pass, 
#report.description
GO

执行一个存储过程

exec teacher_view_exam_report;

修改存储过程

alter proc teacher_view_exam_report
as
select * from student;