忘電子工掌出社 第10章存储过程 10.1存储过程的概念 10.2存储过程的程序结构 10.3存储过程的创建 10.4存储过程的管理
第10章 存储过程 10．1存储过程的概念 10．2存储过程的程序结构 10．3存储过程的创建 10．4 存储过程的管理
忘電子工掌出社 10.1存储过程概述 存储过程是 SQL Server服务器上一组预先定义并编译好的 Transact-SQL语句,它可以接受参数、返回状态值和参数值。存 储过程在第一次执行时将进行语法检査与编译,然后将处理好的 版本存在高速缓冲中,用以再次调用。存储过程将提高运行效率, 而且也加强了系统的安全机制。 在 SQL Server的系列版本中存储过程分为两类,系统提供的存储 过程和用户自定义存储过程 使用存储过程应用程序具有以下的优点: 存储过程能够提高程序的执行速度 2.存储过程能够减少网络流量 ·3.存储过程允许标准组件式编程
10.1 存储过程概述 • 存储过程是SQL Server服务器上一组预先定义并编译好的 Transact-SQL语句，它可以接受参数、返回状态值和参数值。存 储过程在第一次执行时将进行语法检查与编译，然后将处理好的 版本存在高速缓冲中，用以再次调用。存储过程将提高运行效率， 而且也加强了系统的安全机制。 • 在SQL Server 的系列版本中存储过程分为两类，系统提供的存储 过程和用户自定义存储过程。 • 使用存储过程应用程序具有以下的优点： • • 1． 存储过程能够提高程序的执行速度 • • 2．存储过程能够减少网络流量 • • 3．存储过程允许标准组件式编程 • 返回目录
密冀出社10.2存储过程的程序结构 存储过程的应用程序包括两部分:一个是存储过程的本身,它存 放并运行在数据库服务器端;另一个是客户端应用程序,它运行 在客户端,对存储过程进行调用。它们有不同的功能: 客户端应用程序的主要功能如下: 定义有关数据结构和主变量,为他们分配并初始化存储空间 ·2)连接数据库。 3)调用存储过程 4)完成事务的提交和回滚。 5)执行 CONNECT RESET语句。 ·服务器端存储过程的主要功能如下: 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存储过程的创建 • 创建存储过程有两种方法，一是使用Transaction-SQL 命令Create Procedure，二是使用图形化管理工具企 业管理器（Enterprise Manager）。 创建存储过程时 需要确定存储过程的三个部分：第一是所有的输入参 数以及传给调用者的输出参数，第二是被执行的针对 数据库的操作语句（包括调用其它存储过程的语句）， 第三是 返回给调用者的状态值，以说明调用是成功还 是失败。 10．3．1使用企业管理器（Enterprise Manager） 创建 存储过程 • 返回目录
電子版独2.用 CREATE PROCEDURE命令创建存储过程 ·通过运用 Create Procedure命令能够创建存储过程,其语法格式 如下: CREATE PROCEDURE procedure_name[i number I [@parameter data_type y I VARYING[=default ouTPUt n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTIONI I FOR REPLICATION As Sql_statement[…n] 各参数的说明如下 procedure_name:是要创建的存储过程的名字。 @ parameter:是存储过程的参数 Data_type:是参数的数据类型。 VARYING:指定由 OUTPU参数支持的结果集,仅应用于游标型 参数 返回目录
• 10．3．2．用CREATE PROCEDURE 命令创建存储过程 • 通过运用Create Procedure 命令能够创建存储过程，其语法格式 如下： • CREATE PROCEDURE procedure_name [ ; number ] • [ { @parameter data_type } • [ VARYING ] [ = default ] [ OUTPUT ] • ] [ ,...n ] • [ WITH{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] • [ FOR REPLICATION ] • AS • Sql_statement [ ...n ] • 各参数的说明如下 • procedure_name：是要创建的存储过程的名字。 • @parameter:是存储过程的参数。 • Data_type：是参数的数据类型。 • VARYING：指定由OUTPUT 参数支持的结果集，仅应用于游标型 参数。 返回目录
紅:表明该参数是一个返回参数,用 OUTPUT参数 可以向调用者返回信息,Text类型参数不能用作 OUTPUT参数 RECOMPILE:指明 SQL Server并不保存该存储过程 的执行计划,该存储过程每执行一次都又要重新编译。 ENCRYPTION:表明 SQL Server加密了 syscomments 表 FOR REPLICATI0N;选项说明所创建的存储过程用于 系统的数据复制,该选项不能与 WITH RECOMPILE选项 同时使用。 AS:指明该存储过程将要执行的动作 Sql statement:是任何数量和类型的包含在存储过 程中的SQL语句。 返回目录
• OUTPUT：表明该参数是一个返回参数，用OUTPUT 参数 可以向调用者返回信息，Text 类型参数不能用作 OUTPUT 参数。 • RECOMPILE：指明SQL Server 并不保存该存储过程 的执行计划，该存储过程每执行一次都又要重新编译。 • ENCRYPTION：表明SQL Server 加密了syscomments 表。 • FOR REPLICATION；选项说明所创建的存储过程用于 系统的数据复制，该选项不能与WITH RECOMPILE 选项 同时使用。 • AS：指明该存储过程将要执行的动作。 • Sql_statement：是任何数量和类型的包含在存储过 程中的SQL 语句。 • 返回目录
出例砖:1】在dcmo数据库中的用户信息表s中(表结构如表5 所示),建立一个名为“ 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 返回目录
• 【例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 返回目录
白電工2】在该存储过程中使用了参数 use users f 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.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存储过程的管理 10.4.1删除存储过程 1.使用企业管理器删除存储过程 2.使用 DRO PPROCEDURE语句删除存储过程 使用 DRO PPROCEDURE语句可以删除存储过程,其语法格式为: DROP PROCEDURE procedure_name 【例10.3】将存储过程 check_pass从数据库中删除。则执行: drop procedure check pass 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 存储过程的管理
密子款行存储过程 执行已创建的存储过程,使用 EXECUTE命令,其语法如下 LEXECUTE] iL@return_statur=] procedure_namel;number] @procedure_name_vary [[@parameter=] fvalue I @variable [oUTPUT]I [DEFAULT]lin] [WITH RECOMPILE] 各参数的含义如下 @ return status:是可选的整型变量,用来存储存储过程向调用 者返回的值 @ procedure name var:是一变量名,用来代表存储过程的名字 其它参数据和保留字的含义与 CREATE PROCEDURE中介绍的一样。 返回目录
• 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 中介绍的一样。 • 返回目录
