存储过程

存储过程是存储在服务器上的例行程序及过程。调用时可以给存储过程传递参数，也可以从存储过程返回数据。

基本概念

   存储过程是指经过预先编译的SQL语句的集合，可以以一种可执行的形式永久的存储在数据库中。   前面介绍的SQL语句是交互执行的，及当数据库中有SQL语句发出时，DBMS检查其语法，并对其进行语法分析，转化为数据库内部的可执行形式。但是对于存储过程则不同，他可以把多条(或一条)SQL语句组织在一起形成一个独立的逻辑单元存储于数据库中。由于存储过程是预先编译好的SQL语句，所以，需要时只需调用该过程就可以完成相应的操作。   存储过程类似于程序设计中的过程和函数，主要体现在以下几个方面。   (1)存储过程可以接受参数，并以输出参数的形式返回多个参数给调用存储过程的过程或批处理。   (2)存储过程可以包含对数据库进行查询，修改等的编程语句，也可以调用其他的存储过程。   (3)可以返回执行存储过程的状态值以反映存储过程的执行情况。   但是，存储过程与通常的函数不同。存储过程不能直接用过程名返回数据，也不能直接在表达式中使用。例如，不能用类似@a1=sp_procedure的方式使用存储过程。但是，存储过程可以使用变量的形式来返回参数。   存储过程有以下优点。   (1)运行速度快。当客户程序需要访问服务器上的数据时，一般要经过以下几个步骤：查询语句发送到服务器，服务器编译SQL语句，优化产生查询计划，数据库引擎执行查询，执行结果发回客户程序。对于存储在客户本地的SQL程序，每次执行该程序时，对于程序中的每条语句都要经过以上几个步骤。而存储过程在创建时就被编译和优化，当存储过程第一次执行时，SQL Server为其产生查询计划并保存在内存中，以后再次调用该存储过程时则不必进行编译和优化产生查询计划，这能大大的改善系统的性能。再有大量批处理的SQL要重复执行时，使用存储过程可以极大地提高运行效率。   (2)模块化编程，增强代码的重用性和共享性。存储过程经过一次建立，可以被许多用户重用和共享，从而增强了代码的重用性和共享性，进而提高应用开发的质量和效率。用户还可以独立于应用程序而对存储过程进行修改，可以按照功能模块不同，设计不同的存储过程以供使用。   (3)减少网络通信量。存储在服务器上的存储过程中可以包含大量的SQL语句，但存储过程作为一个独立的单元使用，在调用时只需要一条语句就可以实现，这就避免了多条语句通过网络上传到服务器，从而大大减少了网络上的数据传输。   (4)保证系统的安全性。可以设置用户通过存储过程对数据库中的数据进行访问，但不允许用户直接使用SQL语句对数据进行访问。这样，既可以满足用户对相关数据的访问，又不允许用户直接访问存储过程中涉及的表，从而保证了系统的安全性。   在SQL Server中，存储过程有两种类型：系统提供的存储过程和用户自定义的存储过程。   系统存储过程存放在master数据库中并以sp为前缀，主要是从系统表中获取信息，从而为系统管理员SQL Server提供支持。用户也可以在其他数据库中对其进行调用，在调用时不必在存储过程前加上数据库名，而且在创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。   用户自定义存储过程是由用户创建并能完成某一特定功能的存储过程。对于用户创建的存储过程，尽量不要以sp_作为其名称的前缀，因为SQL Server在执行存储过程时是以下顺序查找以sp_为名称查找的   (1)在master数据库中查找   (2)如果在存储过程前指定了数据库和所有者，则查找基于指定数据库和所有者的存储过程。   (3)对于没有指定数据库和所有者的存储过程，以DBO作为所有者查找。

创建和执行存储过程

1.创建存储过程

创建存储过程的T-SQL语句的一般格式

create proc[edure] procedure_name   [@parameter data_type [=default] [OUTPUT]] [,...]   [WITH ENCRYPTION]   AS sql_statement;

其中procedure_name:存储过程名，存储过程的命名必须符合有关数据库对象命名规范parameter:存储过程中定义的输入输出参数。data_type:参数的数据类型。default:定义参数的默认值。OUTPUT:指出该参数是一个返回调用过程的参数，即输出参数。WITH ENCRYPTION:加密存储过程。sql_statement:在存储过程中要执行的T-SQL语句.

eg:在学生-课程数据库中创建一个存储过程，查看”张明”同学的有关基本信息和选修课的情况。

create procedure student_courseas  select Student.Sno,Sname,Cname,Grade  from Student,Course,SC  where Student.Sno=SC.Sno and Course.Cno=SC.Sno and Sname='张明';

如果存储过程以存在，可以使用以下语句删除：

if exists(select name from sysobjects where name='student_course' and type='p')   drop procedure student_course;

2.执行存储过程

存储过程的执行可以使用execute语句，格式如下:

[exe[ute]] [@return_status=] <procedure_name>[[@parameter=] {value | @variable [OUTPUT]} [,....n]];其中,@return_status:是一个可选的整形变量，保存存储过程的返回状态。这个变量在execute语句使用之前，必须在批处理，调用的存储过程或函数中声明。procedure_name:被调用执行的存储过程的名称。@parameter:是存储过程的参数，在create procedure语句中定义。@variable:用来保存输入参数或者输出参数值的变量。OUTPUT:指定存储过程必须返回一个参数，其值送入输出参数。

eg:调用上例存储过程:

execute student_course;

3.通过存储过程传递参数

eg:存储过程只能对某个特定学生的有关信息进行查询。要是该存储过程具有通用性，即可以查询任意学生的相关信息，那么学生的姓名就应该是可变的，即将学生姓名为参数把值传递给存储过程。

create procedure student_course1@StudentName varchar(10)as   select Student.Sno,Sname,Cname,Grade   from Student,Course,SC   where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Sname=@StudentName;

调用该存储过程的方法为:

execute student_course1 '张明';或execute student_course1 @StudentName='张明';

eg:创建一个存储过程用于向学生表中插入记录。

create procedure student_insert@xh char(6),@name varchar(8),@sex char(2),@age int,@sdep char(10)as     insert into Student    values(@xh,@name,@sex,@age,@sdep);

调用该存储过程:

execute student_insert '950002','李明','男',20,'CS';

eg:创建一个存储过程，若没有给出学生姓名，则返回所有学生的有关情况。

create procedure student_course2@StudentName varchar(10)=nullas   if @StudentName is null   begin      select Student.Sno,Sname,Cname,Grade      from Student,Course,SC      where Student.Sno=SC.Sno and Course.Cno=SC.Cno   end   else   begin      select Student.Sno,Sname,Cname,Grade      from Student,Course,SC      where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Sname=@StudentName   end

eg:创建一个存储过程，返回选修每门课程的学生人数。

create procedure student_count@CourseName varchar(20),@StudentSum int outputas   select @StudentSum=COUNT(*)   from Course,SC   where Course.Cno=SC.Cno and Cname=@CourseNamedeclare @studentNum int;execute student_count '数据库原理及应用',@studentNum output;select 'The result is:',@studentNum;

eg:创建一个存储过程，输出学生的基本情况。

create procedure student_query@xh char(6),@name varchar(8) output,@sex char(2) output,@age int outputas    select @name=Sname,@sex=Ssex,@age=Sage    from Student    where Sno=@xh;declare @name varchar(8);declare @sex char(2);declare @age int;execute student_query '950001',@name output,@sex output,@age output;

存储过程的修改和删除

修改存储过程

第一种方法是删除该存储过程；第二种是alter procedure语句修改

alter proc[edure] procedure_name  [@parameter data_type [=default] [output]] [,...]  [with encryption]  as     sql_statement;

删除存储过程

drop procedure procedure_name;