第10章存储过程概论

第10章存储过程

关键词：

存储过程创建存储过程执行存储过程存储过程的参数查看存储过程修改存储过程删除存储过程

学习要求：

本章主要阐述了存储过程的创建和使用方法。并且全面地、系统地介绍了存储过程的概念、存储过程的优点；系统存储过程的特点及用途；创建存储过程的方法；查看、修改和删除存储过程的方法。重点分析了存储过程的创建和使用方法。学习和掌握本章，是对SQL Server 2000数据库的灵活运用。

10.1 概述

１、存储过程——是为实现某个特定任务而编写的一段代码。

２、存储过程的特点：

● 可以包含一条或多条Transact-SQL语句。

● 可以接受输入参数并可以返回输出值。

● 一个存储过程可以调用另一个存储过程。

● 会返回执行情况的状态代码给调用它的程序。

３、存储过程的优点：

● 实现模块化编程。一个存储过程可以被多个用户共享和重用。

● 加快程序的运行速度。第一次执行后的存储过程会在缓冲区中创建查询树，使得第二次执行时不用进行预编译。

● 可以减少网络流量。存储过程存储在服务器上，只有触发执行存储过程的命令和返回结果才在网络上传输。

● 可以提高数据库安全性。可以只授予用户访问存储过程的权限，而不授予其直接修改数据表的权限。

３、存储过程的分类：

● 系统存储过程。由系统自动创建，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。

● 用户自定义存储过程。由用户创建并能完成某一特定功能的存储过程。

10.2 创建存储过程

１、使用CREATE PROCEDURE语句创建存储过程

格式：CREATE PROC[EDURE ] procedure_name [； number]

[ {@parameter data_type}

[VARYING ][ = default][OUTPUT]

][，…n]

[WITH

{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}]

[FOR REPLICATION]

AS sql_statement […n ]

其中各参数含义如下：

● procedure_name：新存储过程的名称。

● number：对同名的过程分组。

● @parameter：过程中的参数。可以声明一个或多个参数。存储过程最多可以有2100个参数。

● data_type：参数的数据类型。

● VARYING：指定作为输出参数支持的结果集(由存储过程动态构造，内容可以变化)。仅适用于游标参数。

● default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。

● OUTPUT：表明参数是返回参数。该选项的值可以返回给EXE[UTE]。使用OUTPUT参数可将信息返回给调用过程。

● RECOMPILE：表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。

● ENCRYPTION：表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。

● FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。

● sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。

创建存储过程时应该注意下面几点：

● 存储过程的最大大小为128MB。

● 用户定义的存储过程只能在当前数据库中创建(临时过程除外，临时过程总是在tempdb中创建)。

● 在单个批处理中，CREATE PROCEDURE语句不能与其他Transact-SQL语句组合使用。

● 存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程。嵌套的最大深度不能超过32层。

● 存储过程如果创建了临时表，则该临时表只能用于该存储过程，而且当存储过程执行完毕后，临时表自动被删除。

● 创建存储过程时，“sq_statement”不能包含下面的Transact-SQL语句：

SET SHOWPLAN_TEXT、SET SHOWMAN_ALL、CREATE VIEW、CREATE DEFAULT、CREATE RULE、CREATE PROCEDURE和CREATE TRIGGER。

例1：创建用于检索所有学生的成绩记录的存储过程stud_degree。

USE school

--判断stud_degree存储过程是否存在，若存在，则删除

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ’stud_degree’ AND type =’P’)

DROP PROCEDURE stud_degree

GO

USE school

GO

--创建存储过程stud_degree

CREATE PROCEDURE stud_degree /* 无参过程 */

AS

SELECT student.sno，student.sname，ame，score.degree

FROM student，course，score

WHERE student.sno=score.sno AND o=o

ORDER BY student.sno

GO

通过下述SQL语句执行该存储过程：

USE school

--判断stud_degree存储过程是否存在，若存在，则执行它

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ’stud_degree’ AND type =’P’)

EXEC stud_degree

GO

例2：创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在student表中查询此学生的信息。

USE school

--判断stud_info存储过程是否存在，若存在，则删除

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ’stud_info’ AND type =’P’)

DROP PROCEDURE stud_info

GO

USE school

GO

--创建存储过程stud_info

CREATE PROCEDURE stud_info

@s_no char(5) /* 有参过程·形参 */

AS

SELECT *

FROM student

WHERE sno=@s_no

GO

通过下述SQL语句执行该存储过程：

USE school

--判断stud_info存储过程是否存在，若存在，则执行它

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ’stud_info’ AND type =’P’)

EXEC stud_info ‘105’/*实参*/

GO

例3：创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在student表中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。

USE school

--判断stud_age存储过程是否存在，若存在，则删除