第 10章 存储过程
10,1存储过程的概念
10,2存储过程的程序结构
10,3存储过程的创建
10,4 存储过程的管理
10.1 存储过程概述
存储过程是 SQL Server服务器上一组预先定义并编译好的
Transact-SQL语句,它可以接受参数,返回状态值和参数值 。 存储过程在第一次执行时将进行语法检查与编译,然后将处理好的版本存在高速缓冲中,用以再次调用 。 存储过程将提高运行效率,
而且也加强了系统的安全机制 。
在 SQL Server 的系列版本中存储过程分为两类,系统提供的存储过程和用户自定义存储过程 。
使用存储过程应用程序具有以下的优点：
1,存储过程能够提高程序的执行速度
2,存储过程能够减少网络流量
3,存储过程允许标准组件式编程
返回目录
10,2存储过程的程序结构
存储过程的应用程序包括两部分：一个是存储过程的本身,它存放并运行在数据库服务器端；另一个是客户端应用程序,它运行在客户端,对存储过程进行调用 。 它们有不同的功能：
客户端应用程序的主要功能如下,
1) 定义有关数据结构和主变量,为他们分配并初始化存储空间 。
2) 连接数据库 。
3) 调用存储过程 。
4) 完成事务的提交和回滚 。
5) 执行 CONNECT RESET语句 。
服务器端存储过程的主要功能如下：
1） 接受客户端应用程序传送的信息 。
2） 作为与客户端应用程序相同的事务在数据库服务器上运行 。
3） 向客户端应用程序返回服务器运行结果 。
返回目录
10,3存储过程的创建
创建存储过程有两种方法,一是使用 Transaction-SQL
命令 Create Procedure,二是使用图形化管理工具企业管理器 （ Enterprise Manager） 。 创建存储过程时需要确定存储过程的三个部分：第一是所有的输入参数以及传给调用者的输出参数,第二是被执行的针对数据库的操作语句 （ 包括调用其它存储过程的语句 ）,
第三是 返回给调用者的状态值,以说明调用是成功还是失败 。
10,3,1使用企业管理器 （ Enterprise Manager） 创建存储过程
返回目录
10,3,2,用 CREATE PROCEDURE 命令创建存储过程
通过运用 Create Procedure 命令能够创建存储过程,其语法格式如下：
CREATE PROCEDURE procedure_name[ ; number ]
[ { @parameterdata_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH{ RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION} ]
[ FOR REPLICATION ]
AS
Sql_statement[,..n ]
各参数的说明如下
procedure_name：是要创建的存储过程的名字 。
@parameter:是存储过程的参数 。
Data_type：是参数的数据类型 。
VARYING：指定由 OUTPUT 参数支持的结果集,仅应用于游标型参数 。 返回目录
OUTPUT：表明该参数是一个返回参数,用 OUTPUT 参数可以向调用者返回信息,Text 类型参数不能用作
OUTPUT 参数 。
RECOMPILE：指明 SQL Server 并不保存该存储过程的执行计划,该存储过程每执行一次都又要重新编译 。
ENCRYPTION：表明 SQL Server 加密了 syscomments
表 。
FOR REPLICATION；选项说明所创建的存储过程用于系统的数据复制,该选项不能与 WITH RECOMPILE 选项同时使用 。
AS：指明该存储过程将要执行的动作 。
Sql_statement：是任何数量和类型的包含在存储过程中的 SQL 语句 。
返回目录
【 例 10.1】 在 demo数据库中的用户信息表 users中 （ 表结构如表 5.1
所示 ）,建立一个名为,check_pass”的存储过程,用于检索
password=”123”的信息 。
use users
if exists select name from sysobjects//如果存储过程已经存在,则将其删除
where name=’check_pass’ and type=’p’
drop procedure check_pass
go
create procedure check_pass //建立存储过程
as
select Username,Email,Resume
from users
Where Password=’123’
go
返回目录
【 例 10.2】 在该存储过程中使用了参数
use users
if exists select name from sysobjects//如果存储过程已经存在,则将其删除
where name=’check_pass’ and type=’p’
drop procedure check_pass
go
use users
go
create procedure check_pass //建立存储过程
@Password varchar 10
as
select Username,Email,Resume
from users
Where Password=@Password
go 返回目录
10,4,1 删除存储过程
1,使用企业管理器删除存储过程
2,使用 DRO PPROCEDURE语句删除存储过程使用 DRO PPROCEDURE语句可以删除存储过程,其语法格式为：
DROP PROCEDURE procedure_name
【 例 10.3】 将存储过程 check_pass 从数据库中删除 。 则执行,
drop procedure check_pass
go
返回目录
10.4 存储过程的管理
10.4.2 执行存储过程
执行已创建的存储过程,使用 EXECUTE命令,其语法如下：
[EXECUTE]
{[@return_statur=]
{procedure_name[;number]| @procedure_name_var}
[[@parameter=] {value | @variable [OUTPUT] | [DEFAULT]
[,? n]
[WITH RECOMPILE]
各参数的含义如下
@return_status：是可选的整型变量,用来存储存储过程向调用者返回的值 。
@procedure_name_var：是一变量名,用来代表存储过程的名字 。
其它参数据和保留字的含义与 CREATE PROCEDURE 中介绍的一样。
返回目录
【 例 10.4】 该存储过程被用来将两个字符串连接成一个字符串并将结果返回
‘ 创建存储过程
create procedure strconnect @str1 varchar 20,@str2
varchar 20,@connect varchar 40 output
as
select @connect=@str1 + @str2
如果我们提供三个字符串来执行这一存储过程,我们将看不到字符串相加的结果,虽然 Select 语句用来对 result 变量赋值,但 result
结果并没有显示 。 执行以下语句：
declare @result varchar 40
execute strconnect 'I am',' John',' string'
select 'The result'=@result
则其运行结果为
The result
NULL
1 row s affected 返回目录
若增加 OUTPUT 保留字到 EXECUTE 语句中,便可显示返回参数
result 的值,OUTPUT要求参数值被作为一个变量传送,而不是作为一个常量 。 下面的例子说明 @result 变量来存放由存储过程
strconnect 通过 @connect 返回给调用者的结果值,从而使 SQL
Server 能够显示出存储过程的返回值 。
【 例 10.5】
declare @result varchar 40
execute strconnect 'I am','John ',@result output
select 'The result'=@result
运行结果为
The result
I am John
1 row s affected
返回目录
10,1存储过程的概念
10,2存储过程的程序结构
10,3存储过程的创建
10,4 存储过程的管理
10.1 存储过程概述
存储过程是 SQL Server服务器上一组预先定义并编译好的
Transact-SQL语句,它可以接受参数,返回状态值和参数值 。 存储过程在第一次执行时将进行语法检查与编译,然后将处理好的版本存在高速缓冲中,用以再次调用 。 存储过程将提高运行效率,
而且也加强了系统的安全机制 。
在 SQL Server 的系列版本中存储过程分为两类,系统提供的存储过程和用户自定义存储过程 。
使用存储过程应用程序具有以下的优点：
1,存储过程能够提高程序的执行速度
2,存储过程能够减少网络流量
3,存储过程允许标准组件式编程
返回目录
10,2存储过程的程序结构
存储过程的应用程序包括两部分：一个是存储过程的本身,它存放并运行在数据库服务器端；另一个是客户端应用程序,它运行在客户端,对存储过程进行调用 。 它们有不同的功能：
客户端应用程序的主要功能如下,
1) 定义有关数据结构和主变量,为他们分配并初始化存储空间 。
2) 连接数据库 。
3) 调用存储过程 。
4) 完成事务的提交和回滚 。
5) 执行 CONNECT RESET语句 。
服务器端存储过程的主要功能如下：
1） 接受客户端应用程序传送的信息 。
2） 作为与客户端应用程序相同的事务在数据库服务器上运行 。
3） 向客户端应用程序返回服务器运行结果 。
返回目录
10,3存储过程的创建
创建存储过程有两种方法,一是使用 Transaction-SQL
命令 Create Procedure,二是使用图形化管理工具企业管理器 （ Enterprise Manager） 。 创建存储过程时需要确定存储过程的三个部分：第一是所有的输入参数以及传给调用者的输出参数,第二是被执行的针对数据库的操作语句 （ 包括调用其它存储过程的语句 ）,
第三是 返回给调用者的状态值,以说明调用是成功还是失败 。
10,3,1使用企业管理器 （ Enterprise Manager） 创建存储过程
返回目录
10,3,2,用 CREATE PROCEDURE 命令创建存储过程
通过运用 Create Procedure 命令能够创建存储过程,其语法格式如下：
CREATE PROCEDURE procedure_name[ ; number ]
[ { @parameterdata_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [,...n ]
[ WITH{ RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION} ]
[ FOR REPLICATION ]
AS
Sql_statement[,..n ]
各参数的说明如下
procedure_name：是要创建的存储过程的名字 。
@parameter:是存储过程的参数 。
Data_type：是参数的数据类型 。
VARYING：指定由 OUTPUT 参数支持的结果集,仅应用于游标型参数 。 返回目录
OUTPUT：表明该参数是一个返回参数,用 OUTPUT 参数可以向调用者返回信息,Text 类型参数不能用作
OUTPUT 参数 。
RECOMPILE：指明 SQL Server 并不保存该存储过程的执行计划,该存储过程每执行一次都又要重新编译 。
ENCRYPTION：表明 SQL Server 加密了 syscomments
表 。
FOR REPLICATION；选项说明所创建的存储过程用于系统的数据复制,该选项不能与 WITH RECOMPILE 选项同时使用 。
AS：指明该存储过程将要执行的动作 。
Sql_statement：是任何数量和类型的包含在存储过程中的 SQL 语句 。
返回目录
【 例 10.1】 在 demo数据库中的用户信息表 users中 （ 表结构如表 5.1
所示 ）,建立一个名为,check_pass”的存储过程,用于检索
password=”123”的信息 。
use users
if exists select name from sysobjects//如果存储过程已经存在,则将其删除
where name=’check_pass’ and type=’p’
drop procedure check_pass
go
create procedure check_pass //建立存储过程
as
select Username,Email,Resume
from users
Where Password=’123’
go
返回目录
【 例 10.2】 在该存储过程中使用了参数
use users
if exists select name from sysobjects//如果存储过程已经存在,则将其删除
where name=’check_pass’ and type=’p’
drop procedure check_pass
go
use users
go
create procedure check_pass //建立存储过程
@Password varchar 10
as
select Username,Email,Resume
from users
Where Password=@Password
go 返回目录
10,4,1 删除存储过程
1,使用企业管理器删除存储过程
2,使用 DRO PPROCEDURE语句删除存储过程使用 DRO PPROCEDURE语句可以删除存储过程,其语法格式为：
DROP PROCEDURE procedure_name
【 例 10.3】 将存储过程 check_pass 从数据库中删除 。 则执行,
drop procedure check_pass
go
返回目录
10.4 存储过程的管理
10.4.2 执行存储过程
执行已创建的存储过程,使用 EXECUTE命令,其语法如下：
[EXECUTE]
{[@return_statur=]
{procedure_name[;number]| @procedure_name_var}
[[@parameter=] {value | @variable [OUTPUT] | [DEFAULT]
[,? n]
[WITH RECOMPILE]
各参数的含义如下
@return_status：是可选的整型变量,用来存储存储过程向调用者返回的值 。
@procedure_name_var：是一变量名,用来代表存储过程的名字 。
其它参数据和保留字的含义与 CREATE PROCEDURE 中介绍的一样。
返回目录
【 例 10.4】 该存储过程被用来将两个字符串连接成一个字符串并将结果返回
‘ 创建存储过程
create procedure strconnect @str1 varchar 20,@str2
varchar 20,@connect varchar 40 output
as
select @connect=@str1 + @str2
如果我们提供三个字符串来执行这一存储过程,我们将看不到字符串相加的结果,虽然 Select 语句用来对 result 变量赋值,但 result
结果并没有显示 。 执行以下语句：
declare @result varchar 40
execute strconnect 'I am',' John',' string'
select 'The result'=@result
则其运行结果为
The result
NULL
1 row s affected 返回目录
若增加 OUTPUT 保留字到 EXECUTE 语句中,便可显示返回参数
result 的值,OUTPUT要求参数值被作为一个变量传送,而不是作为一个常量 。 下面的例子说明 @result 变量来存放由存储过程
strconnect 通过 @connect 返回给调用者的结果值,从而使 SQL
Server 能够显示出存储过程的返回值 。
【 例 10.5】
declare @result varchar 40
execute strconnect 'I am','John ',@result output
select 'The result'=@result
运行结果为
The result
I am John
1 row s affected
返回目录
