算9章存储过程与触发舞 本章学习目标 0通过创建、修改、删除存储过程与触发器，了解存销过程与触发器的基本概念，理 解存储过程和触发器的作用： Q通过对实例的存储过程与触发器的管理，掌探存储过程与触发器的使用方法和操作 技巧 0通过对常用系统存储过程的使用，了解存储过程的类吸： 本章知识要点 。存储过程与触发器的概念、功能、类型： ◇创建，修改，副除存储过程和触发墨的Transact-SL语句： ◇在存储过程中定义和使用输入。输出参数，以及禁止和启用触发器。 本章操作技能 △能够使用对象资源管理器和SL编辑器创建存储过程与触发墨： △能够草握#储过程调用和触发器董活的方法： △能够实施存储过程和触发器的应用管理。 7.1存储过程概述 S风L.S©r应川操作中。存储过程和触发器扮演相当重要的角色，基于其预编译并存 储在S网L.Ser数据库中的特性，不仅能提高应用效率，确保一致性，更能提高系统执行 速度，同时，使用触发器来完成业务规则，达到简化程序设计的目的。本章将介绍存储过程 和触发器的作用，并讨论使用对象货源管理器和Transact-S语句创建、修改、刷除存储 过程和触发器的方法。 7.1.1什么是存储过程 当开发一个应用程序时，为了易于修政和扩充方便，我们经常会将负责不同功能的语句 集中起来而且按照用递分别鞋立做置，以便能够反复调用，而这些鞋立放置且拥有不月功能 的语句。即是“过程”(Procedure).SQL Server的存销过程包含一些Transact SqL语句 并以特定的名称存储在数据库中（存储过程也是一种数据库对象）.可以在存储过程中声明变 量、有条件执行以及其他各膜强大的程序设计功能。 S如LSer的存销过程与其他程序设计语言的过程类似，同样能按下列方式运行： ()它陵够包含执行各项数据库燥作的语句，并且可以调用其地的存销过程
第 9 章 存储过程与触发器 本章学习目标 ◎ 通过创建、修改、删除存储过程与触发器，了解存储过程与触发器的基本概念，理 解存储过程和触发器的作用； ◎ 通过对实例的存储过程与触发器的管理，掌握存储过程与触发器的使用方法和操作 技巧； ◎ 通过对常用系统存储过程的使用，了解存储过程的类型； 本章知识要点 ◇ 存储过程与触发器的概念、功能、类型； ◇ 创建、修改、删除存储过程和触发器的 Transact-SQL 语句； ◇ 在存储过程中定义和使用输入、输出参数，以及禁止和启用触发器。 本章操作技能 △ 能够使用对象资源管理器和 SQL 编辑器创建存储过程与触发器； △ 能够掌握存储过程调用和触发器激活的方法； △ 能够实施存储过程和触发器的应用管理。 7.1 存储过程概述 SQL Server 应用操作中，存储过程和触发器扮演相当重要的角色，基于其预编译并存 储在 SQL Server 数据库中的特性，不仅能提高应用效率，确保一致性，更能提高系统执行 速度。同时，使用触发器来完成业务规则，达到简化程序设计的目的。本章将介绍存储过程 和触发器的作用，并讨论使用对象资源管理器和 Transact-SQL 语句创建、修改、删除存储 过程和触发器的方法。 7.1.1 什么是存储过程 当开发一个应用程序时，为了易于修改和扩充方便，我们经常会将负责不同功能的语句 集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能 的语句，即是“过程”(Procedure)。SQL Server 的存储过程包含一些 Transact SQL 语句 并以特定的名称存储在数据库中(存储过程也是一种数据库对象)。可以在存储过程中声明变 量、有条件执行以及其他各项强大的程序设计功能。 SQL Server 的存储过程与其他程序设计语言的过程类似，同样能按下列方式运行： ⑴ 它能够包含执行各项数据库操作的语句，并且可以调用其他的存储过程
的能够接受输入参数并以输出参数的形式将多个数据值返回给调用程序(Ca11i眼 Procedure)或批处理Batch), (由向调用程序或批处理返回一个状者值，以表明成功成失败（以及失败的原因）。 存储过程Stored Procedure)是一组为了完成转定功逢的S语句集，经编译后存储在 数据库中。用户通过指定存储过程的名学并给出参数（如果该存储过程箭有参数米执行它， 7.1,2存储过程的好处 1.允许模块化的程序设计 存储过程一旦创建完成并存储于数据库中，即可在应用程序中反复调用，因此利用存储 过程完成某些例行操作是最恰当不过了。一般来说，我们将存钻过程的创建和维护操作交由 专人负责，由于各个用于完成特定操作的存储过程均粒立故置，因此根本不需相心当修政存 储过程时会影响到应用程序的程序代码。 此外，通过在存储过程中编写业务逐辑和策略。不仅可让不月的应用程序共享，同时可 要求所有的客户端使用相同的存储过程从面达到数据访付和更新的一致性。 2更快的执行速度 当执行批处理和Transact-S刘L,程序代列时，SS0eT必须先检查语法是否正确，接 着进行编译、优化然后再执行它，因此如果所要执行的Transact SQL程序代码非常虎大， 执行前的处理过程将会耗费一些时间。 对存储过程而言，当它们创建时就己经检查过语法的正确性、编译并如以优化，因此当 执行存储过程时，可以立即直接执行，自然速度会比较快。顾名思义，存储过程就是顶先编 保和优化并存储于数据岸中的过程。 更重要的是，存储过程在它弟一次执行后会在内存中保图，因此以后的调用并不需再将 存储过程从磁盘中装载。然面如果从客户端传送Transact SQL语句到后璃的SQL.Server 执行，则每次执行时都必颈重新编译和优化，速度当然比较慢。 及有效降低网络流量 假设某一项操作需要数百行的Transact S观程序代码完成，如果从客户编将这数百行 的Transact S程序代码传运到后瑞的SgLS0rveT执行，则在网络上传输的将是程序代码 的数千或数万个字符：但是如果事先将这数百行的Tsa心tSqL程序代码编写成一个存储 在Sq.Sner数据岸中的存储过程，则贝需从客户端调用该存储过程的名称即可执行它， 此时在网络上传输的仅仅是存储过程名称的几个字符。 显而易见。使用存储过程。所造成的网络流量比较小。因此使用存储过程较好
⑵ 能够接受输入参数并以输出参数的形式将多个数据值返回给调用程序(Ca11ing Procedure)或批处理(Batch)。 ⑶ 向调用程序或批处理返回一个状态值，以表明成功或失败(以及失败的原因)。 存储过程(Stored Procedure)是一组为了完成特定功能的 SQL 语句集，经编译后存储在 数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 7.1.2 存储过程的好处 1. 允许模块化的程序设计 存储过程一旦创建完成并存储于数据库中，即可在应用程序中反复调用，因此利用存储 过程完成某些例行操作是最恰当不过了。一般来说，我们将存储过程的创建和维护操作交由 专人负责，由于各个用于完成特定操作的存储过程均独立放置，因此根本不需担心当修改存 储过程时会影响到应用程序的程序代码。 此外，通过在存储过程中编写业务逻辑和策略，不仅可让不同的应用程序共享，同时可 要求所有的客户端使用相同的存储过程从而达到数据访问和更新的一致性。 2. 更快的执行速度 当执行批处理和 Transact-SQL 程序代码时，SQL Server 必须先检查语法是否正确、接 着进行编译、优化然后再执行它，因此如果所要执行的 Transact SQL 程序代码非常庞大， 执行前的处理过程将会耗费一些时间。 对存储过程而言，当它们创建时就己经检查过语法的正确性、编译并加以优化，因此当 执行存储过程时，可以立即直接执行，自然速度会比较快。顾名思义，存储过程就是预先编 译和优化并存储于数据库中的过程。 更重要的是，存储过程在它第一次执行后会在内存中保留，因此以后的调用并不需再将 存储过程从磁盘中装载。然而如果从客户端传送 Transact SQL 语句到后端的 SQL Server 执行，则每次执行时都必须重新编译和优化，速度当然比较慢。 3. 有效降低网络流量 假设某一项操作需要数百行的 Transact SQL 程序代码完成，如果从客户端将这数百行 的 Transact SQL 程序代码传送到后端的 SQL Server 执行，则在网络上传输的将是程序代码 的数千或数万个字符；但是如果事先将这数百行的 Transact_SQL 程序代码编写成一个存储 在 SQL Server 数据库中的存储过程，则只需从客户端调用该存储过程的名称即可执行它， 此时在网络上传输的仅仅是存储过程名称的几个字符。 显而易见，使用存储过程，所造成的网络流量比较小，因此使用存储过程较好
4.较好的安全机制 对于存储过程，我们可以设置事些用户有权执行它。这样，瓷可达到较完善的安全控制 和管理。例如！不希望某一位用户有权直接访问某个表，但是又必须要求他针对该表执行特 定的操作，这时可以将该位用户所能针对表执行的操作编写成一个存储过程。并赋予也执行 该存储过程的权限，如此一米，虽然这位用户设没有权直接访问表，仍然能通过执行存储过程 米完成所需的操作。 7.1.3存储过程类罩 】.系统存储过程 存储过程在运算时生成执行方式，其后再运行时执行速度很快。S风，S0T不仅提供 了用户白定文存锦过程的功能，而且也提保了许多可作为工具使用的系统存储过程。 系统存储过程(Systen Stored Procedures)主要存储在国ster量据库中并以sp为前 银，并且系统存销过程主要是从系统表中获取信息，从而为系统管理员管理Sq,Ser提 供支转。通过系统存储过程，SQL Server中的许多管理性或信息性的活动（如了解数据库对 象、数据库信息)都可以被顺利有效地完成.尽管这些系统存储过程被成在国st®r数据岸中， 但是仍可以在其它数据库中对其进行调用，在调用时不经在存储过程名前加上数据库名。而 且当创建一个新数据库时，一些系统存储过程会在新数据库中被白动创建。 系统存储过程所能完成的操作多达千百项。例如。提债帮助的系饶存储过程有： sp_helpsql是示关于S如L语句、存储过程和其他主题的信息：sp_help提供关于存销过程或 其也数据库对象的报告：s弹helptext显示存储过程和其他对象的文本：s知_depends列举引 用或依赖指定对象的所有存储过程。事实上我们在前面的学习中就己使用过不少系统存储过 程，例如，s单_1 ables取得数据库中关于表和视图的相关信息：spre4e山更改数据库的 名称等。 Sq,S©er系统存储过程是为用户提供方便的，它们使川户可以根容易地从系统表提 取信息、管理数据库，并执行涉及更新系统表的其他任务 系统存储过程是在安装过程中在国st:数据岸中创建，由系统管理员用有。所有系统 存储过程的名字均以s即开始。 如果过程以s单_开头，又在当前数据库中找不到，SQL Server就在aster数据库中寻 找。以sp前最命名的存储过程中引用的表如果不能在当前数据岸中解析出来，将在s1 数据库查找。 当系饶存储过程的参数是保留字成对象名，且对象名由数据库成拥有者名字限定时整个
4. 较好的安全机制 对于存储过程，我们可以设置哪些用户有权执行它。这样，就可达到较完善的安全控制 和管理。例如：不希望某一位用户有权直接访问某个表，但是又必须要求他针对该表执行特 定的操作。这时可以将该位用户所能针对表执行的操作编写成一个存储过程，并赋予他执行 该存储过程的权限，如此一来，虽然这位用户没有权直接访问表，仍然能通过执行存储过程 来完成所需的操作。 7.1.3 存储过程类型 1. 系统存储过程 存储过程在运算时生成执行方式，其后再运行时执行速度很快。SQL Server 不仅提供 了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。 系统存储过程(System Stored Procedures)主要存储在 master 数据库中并以 sp_为前 缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理 SQL Server 提 供支持。通过系统存储过程，SQL Server 中的许多管理性或信息性的活动(如了解数据库对 象、数据库信息)都可以被顺利有效地完成。尽管这些系统存储过程被放在 master 数据库中， 但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而 且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。 系统存储过程所能完成的操作多达千百项。例如，提供帮助的系统存储过程有： sp_helpsql 显示关于 SQL 语句、存储过程和其他主题的信息；sp_help 提供关于存储过程或 其他数据库对象的报告；sp_helptext 显示存储过程和其他对象的文本；sp_depends 列举引 用或依赖指定对象的所有存储过程。事实上我们在前面的学习中就己使用过不少系统存储过 程，例如，sp_tables 取得数据库中关于表和视图的相关信息；sp_renamedb 更改数据库的 名称等。 SQL Server 系统存储过程是为用户提供方便的，它们使用户可以很容易地从系统表提 取信息、管理数据库，并执行涉及更新系统表的其他任务。 系统存储过程是在安装过程中在 master 数据库中创建，由系统管理员拥有。所有系统 存储过程的名字均以 sp_开始。 如果过程以 sp_开头，又在当前数据库中找不到，SQL Server 就在 master 数据库中寻 找。以 sp_前缀命名的存储过程中引用的表如果不能在当前数据库中解析出来，将在 master 数据库查找。 当系统存储过程的参数是保留字或对象名，且对象名由数据库或拥有者名字限定时整个
名字必须包含在单引号中。一个用户可以在所有数据库中执行一个系统存储过程的许可权， 否则在任何数据库中都不使执行该系统存储过程。 2本地存储过程 本地存储过程(Local Stored Procedures)也就是用户白行创建并存储在用户数据库中 的存储过程。事实上我们一般所说的存储过程指的就是本地存储过程。 用户创建的存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息） 的存储过程。 3临时存储过程 性时存储过程(Temporary Stored Procedures)可分为下列两种： ()木地临时存储过程 不论爆一个数据库是当前数据库，如果在创建存储过程时，以井字号(#)作为其名称的 第一个字符，则该存储过程将成为一个存放在te即山中的本地临时存储过程（例如：C家EA正 PCCE距Estud_proc一)，本地峰时存储过程只有创建它并连接的用户能够执行它，而 一旦这位用户断开与SCL Server的连接（也就是注销S测L Server),本地临时存储过程会自 动到除，当然，这位用户也可以在连接期间用PCED配命令副除他所制建的本地临 时存储过程。 由于本地临时存储过程的适用范围仅限于创建它的连接，因此不需担心其名称会和其他 连接所采用的名称相同。 (②全同临时存储过程 不论哪一个数据库是当前数据库，只要所创建的存储过程名称是以两个并字号《种)开 头，则该存储过程将成为一个存款在tp北中的全局临时存储过程（例如：C距ATEP ROCEDURE 共stud_proc-）。全局临时存储过程一旦创建，以后连接到SOL Server的任何用户都能够 执行它，而且不需要特定的权限。 当创建全局备时存铺过程的用户断开与SQL Sever的连接，SQL Server将检查是否有 其他用户正在执行该全局峰时存储过程，如果没有。便立即将全局临时存储过程刷除！如果 有，SQL Server会让这些正在执行中的操作维续进行，（是不允许任何用户再执行全局路 时存储过程，等到所有未完成的操作执行完毕后，全同临时存储过程会自动削除。 由于全同临时存过程能够被所有的连接使用，因此必须注意其名称不能和其他连接所 采用的名称相同。 不论创建的是本地临时存储过程还是全局临时存储过程，贝要SQL.Server一停止运行
名字必须包含在单引号中。一个用户可以在所有数据库中执行一个系统存储过程的许可权， 否则在任何数据库中都不能执行该系统存储过程。 2. 本地存储过程 本地存储过程(Local Stored Procedures)也就是用户自行创建并存储在用户数据库中 的存储过程。事实上我们一般所说的存储过程指的就是本地存储过程。 用户创建的存储过程是由用户创建并能完成某一特定功能(如查询用户所需数据信息) 的存储过程。 3. 临时存储过程 临时存储过程(Temporary Stored Procedures)可分为下列两种： ⑴ 本地临时存储过程 不论哪一个数据库是当前数据库，如果在创建存储过程时，以井字号(#)作为其名称的 第一个字符，则该存储过程将成为一个存放在 tempdb 中的本地临时存储过程(例如：CREATE PROCEDURE #stud_proc…)。本地临时存储过程只有创建它并连接的用户能够执行它，而且 一旦这位用户断开与 SQL Server 的连接(也就是注销 SQL Server)，本地临时存储过程会自 动删除，当然，这位用户也可以在连接期间用 DROP PROCEDURE 命令删除他所创建的本地临 时存储过程。 由于本地临时存储过程的适用范围仅限于创建它的连接，因此不需担心其名称会和其他 连接所采用的名称相同。 ⑵ 全局临时存储过程 不论哪一个数据库是当前数据库，只要所创建的存储过程名称是以两个井字号(##)开 头，则该存储过程将成为一个存放在tempdb中的全局临时存储过程(例如：CREATEP ROCEDURE ##stud_proc…)。全局临时存储过程一旦创建，以后连接到 SQL Server 的任何用户都能够 执行它，而且不需要特定的权限。 当创建全局临时存储过程的用户断开与 SQL Sewer 的连接，SQL Server 将检查是否有 其他用户正在执行该全局临时存储过程，如果没有，便立即将全局临时存储过程删除；如果 有，SQL Server 会让这些正在执行中的操作继续进行，但是不允许任何用户再执行全局临 时存储过程，等到所有未完成的操作执行完毕后，全局临时存储过程会自动删除。 由于全局临时存储过程能够被所有的连接使用，因此必须注意其名称不能和其他连接所 采用的名称相同。 不论创建的是本地临时存储过程还是全局临时存储过程，只要 SQL Server 一停止运行
它们将不复存在。 4.运程存储过程 在SQL Server中，远程存储过程像enote Stored Procedures)是位于远程服务器上的 存储过程，通常，我们可以使用分布式查面和TE命令执行一个远程存储过程。 反扩展存储过程 扩展存储过程(Extended Stored Procedures)使用户可以使用外部程序语言黑与的存储 过程。显而易见，通过扩展存销过程我们可以%补SQL Server不是之处并按需要自行大幅 扩展其功能。扩展存储过程在使川和执行上与一般的存储过程完全相同。可以将参数传递给 扩展存储过程，扩展存储过程也挂够返日结果和状态值。 为了区别，扩展存储过程的名称通常以即开头。扩展存储过程是以动态鼓接岸L】 的形式存在，能让SQL Server动态装载和执行，扩展存储过程一定要存放在系统数据库 aster中。 7.2创建和执行存储过程 在SQL Server中创建一个存储过程有两种方法：一种是使用Transaction-SQL命令 CREATE PROCEDURE,另一种是使用对象资源管理墨。用Transaction一S创建存储过程是一 种较为快速的方法，（对于初学者，使用对象资源管理器更易理解，更为简单。 7,2,1创建和执行不错参数的存储过程 .使用对象资源管理器创建存储过 (a)单击“开始”按细，选释“程序”→“Microsoft5 5QL Server2006”→“S知L5ever anageaent Studio'”·“对象资源管理器”. (的分别展开“数据岸”、欲创建的数据库“student、“可编程性”，“存储过程”. (围右击“存储过程”项。在弹出菜单中选择“新建存储过程”选项，打开“SL编粗 器”存储过程模板文件，如图7-2所示。 图7-2存储过程模板 ()在“SQ.编朝器”存储过程模板文件中的相应位置填入存储过程的正文内容，也可 以单击“S编辑署”工具栏上的“指定核板参数的值”按组。弹出如图T-3所示“指定核 板参数的值”对话框。输入模板相关的参数植。然后单击“确定”更新存储过程中的值。 图7-3“指定模板参数的值”对话框 )单击“S编辑器”工具栏上的“分析”按钮。检查语法是否正确。 向单击“SL编辑器”工具栏上的“执行”按扭，创建这一存储过程
它们将不复存在。 4. 远程存储过程 在 SQL Server 中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的 存储过程，通常，我们可以使用分布式查询和 EXECUTE 命令执行一个远程存储过程。 5. 扩展存储过程 扩展存储过程(Extended Stored Procedures)使用户可以使用外部程序语言编写的存储 过程。显而易见，通过扩展存储过程我们可以弥补 SQL Server 不足之处并按需要自行大幅 扩展其功能。扩展存储过程在使用和执行上与一般的存储过程完全相同。可以将参数传递给 扩展存储过程，扩展存储过程也能够返回结果和状态值。 为了区别，扩展存储过程的名称通常以 xp_开头。扩展存储过程是以动态链接库(DLLs) 的形式存在，能让 SQL Server 动态装载和执行。扩展存储过程一定要存放在系统数据库 master 中。 7.2 创建和执行存储过程 在 SQL Server 中创建一个存储过程有两种方法：一种是使用 Transaction-SQL 命令 CREATE PROCEDURE，另一种是使用对象资源管理器。用 Transaction-SQL 创建存储过程是一 种较为快速的方法，但对于初学者，使用对象资源管理器更易理解，更为简单。 7.2.1 创建和执行不带参数的存储过程 1. 使用对象资源管理器创建存储过程 ⑴ 单击“开始”按钮，选择“程序”→“Microsoft SQL Server 2005”→“SQL Server Management Studio”→“对象资源管理器”。 ⑵ 分别展开“数据库”、欲创建的数据库“student”、“可编程性”、“存储过程”。 ⑶ 右击“存储过程”项，在弹出菜单中选择“新建存储过程”选项，打开“SQL 编辑 器”存储过程模板文件，如图 7-2 所示。 图 7-2 存储过程模板 ⑷ 在“SQL 编辑器”存储过程模板文件中的相应位置填入存储过程的正文内容，也可 以单击“SQL 编辑器”工具栏上的“指定模板参数的值”按钮，弹出如图 7-3 所示“指定模 板参数的值”对话框，输入模板相关的参数值。然后单击“确定”更新存储过程中的值。 图 7-3 “指定模板参数的值”对话框 ⑸ 单击“SQL 编辑器”工具栏上的“分析”按钮，检查语法是否正确。 ⑹ 单击“SQL 编辑器”工具栏上的“执行”按钮，创建这一存储过程
(们单击“标准”工具栏上的“保存”按钮，候存创建存储过程的S网代码。 2使用SL命令创建存储过程 创让不替参数的存储过程的命◆格式， CREATE PROC [EDURE]procedure_nane AS sql_statenents 其中，rocedure_nam指明所创建的存储过程的名字，sgl_stat0nts为在存储过程 中需要执行的数据库操作。 【示例7.I】针对教师基本信息表teacher_info,创建一个名称为teacher_procl的存 储过程。该存储过程的功能是从数据表tach©r_nfo中查询所有男教师的倍息， USE student CO CREATE PROCEDUKE teacher_proc1 AS SELECT.FROM teacher_info WHERE gender·'男 CO 欲要执行己创建的存储过程，可以使用XBTE命◆。 执行存储过程的命令格式： [EXEC[UTE]] [@return_status=]iprocedure_name I @procedure_name_var) [[paraneter=](value variable [OUTPUT][DEFALLT])[.n]][WITH ECOMPILE司 其中，各个参数意义如下： ①r©turn_status是一个可选的整型变量，保存存储过程的返国状老， ②procedure_ae是叔调用的存储过程名称，procedure_nae_rar是局部定义变量 名，代表存储过程名称。 parameter是过程参数，在CREATE PROCEDURE语句中定义. ④vale是过程中参数的值，variable是用来保存参数威者返回参数的变量。 回0UTT指定存储过程必须返回一个参数，EF机LT根据过程的定义，提供参数的默 认植 @n表示可以指定一个或者多个paraneter、vale成tvariable
⑺ 单击“标准”工具栏上的“保存”按钮，保存创建存储过程的 SQL 代码。 2. 使用 SQL 命令创建存储过程 创建不带参数的存储过程的命令格式： CREATE PROC[EDURE] procedure_name AS sql_statements 其中：procedure_name 指明所创建的存储过程的名字，sql_statements 为在存储过程 中需要执行的数据库操作。 【示例 7.1】针对教师基本信息表 teacher_info，创建一个名称为 teacher_proc1 的存 储过程，该存储过程的功能是从数据表 teacher_info 中查询所有男教师的信息。 USE student GO CREATE PROCEDURE teacher_proc1 AS SELECT * FROM teacher_info WHERE gender = '男' GO 欲要执行已创建的存储过程，可以使用 EXECUTE 命令。 执行存储过程的命令格式： [EXEC[UTE]] [＠return_status=]{procedure_name | ＠procedure_name_var} [[ ＠ parameter=]{value | ＠ variable [OUTPUT] | [DEFAULT]} [, … n]][WITH RECOMPILE] 其中，各个参数意义如下： ① @return_status 是一个可选的整型变量，保存存储过程的返回状态。 ② procedure_name 是拟调用的存储过程名称，@procedure_name_var 是局部定义变量 名，代表存储过程名称。 ③ @parameter 是过程参数，在 CREATE PROCEDURE 语句中定义。 ④ value 是过程中参数的值，@variable 是用来保存参数或者返回参数的变量。 ⑤ OUTPUT 指定存储过程必须返回一个参数，DEFAULT 根据过程的定义，提供参数的默 认值。 ⑥ n 表示可以指定一个或者多个@parameter、value 或@variable
⑦IT用RECOMPILE强制编译存储过程. 【示例7.2】在S编辑器中编写S0L命令执行数据库student中的存销过程 teacher_procl. USE student 00 EXECUTE teacher_procl 00 在“SgL编辑器”中运行上面的衡令，在结果窗格中将返目载师信息表teacher_.info 中的所有男教师的信息。可以看出其结果符合存储过程中定义的查询语句。 及创建存储过程的步骤及注意事项 在创建存储过程中，用户需要考虑一些列准则。 ()不陵将C范4T正DCE语句与其他SqL语句组合到单个数处理中，每一个批处理 就是一个0语句段. 因为CREATE PROCEDURE命令和SE命◆位于同一个量处理之中。下列程序必颈如下写 才能顺利执行。 USE student 00 CREATE PROCEDUKE stud_procl AS SELECT FROM stud_info 在S子句之后且在下一个批处理之前的语句将被视为是存储过程所要包含的程序代 码。例如，假设要一次创建两个存储过程。下面的写法将是错误的 CREATE PROCEDURE stud_procl AS SELECT stud_id,name,birthday,gender FROM stud_info 00 CREATE PROCEDUKE stud_proc2 AS SELECT stud_id,nane,address,telcode FRO stud_info (②创建存储过程是有权限的，其默认权限属于数据库所有者，其他用户如果希望获得
⑦ WITH RECOMPILE 强制编译存储过程。 【示例 7.2】在 SQL 编辑器中编写 SQL 命令执行数据库 student 中的存储过程 teacher_proc1。 USE student GO EXECUTE teacher_proc1 GO 在“SQL 编辑器”中运行上面的命令，在结果窗格中将返回教师信息表 teacher_info 中的所有男教师的信息。可以看出其结果符合存储过程中定义的查询语句。 3. 创建存储过程的步骤及注意事项 在创建存储过程中，用户需要考虑一些列准则。 ⑴ 不能将 CREATE PROCEDURE 语句与其他 SQL 语句组合到单个批处理中，每一个批处理 就是一个 GO 语句段。 因为 CREATE PROCEDURE 命令和 USE 命令位于同一个批处理之中。下列程序必须如下写 才能顺利执行。 USE student GO CREATE PROCEDURE stud_proc1 AS SELECT * FROM stud_info 在 AS 子句之后且在下一个批处理之前的语句将被视为是存储过程所要包含的程序代 码。例如，假设要一次创建两个存储过程，下面的写法将是错误的： CREATE PROCEDURE stud_proc1 AS SELECT stud_id,name,birthday,gender FROM stud_info GO CREATE PROCEDURE stud_proc2 AS SELECT stud_id,name, address, telcode FROM stud_info ⑵ 创建存储过程是有权限的，其默认权限属于数据库所有者，其他用户如果希望获得
创建存储过程的权限，必须通过数据库所有者授权。值得注意的是，应尽量避免存德过程的 使用者和所涉及表的所有者不是同一个人的情况。 (因由于存储过程是一个数据库对象，其名称必须遵守标识符规则，在命名用户白定义 的存储过程时应避免使用s印前摄，以免和弱统存储过程混滑， ()尽量不要使川伤时存储过程。以道免t©即db上造成的对系统表货源的争夺，从而梦 致影响系统的执行性能。 (贝能在当前数据岸中创建属于当前数据岸的存储过程。根据可使用的内存，存储过 程的最大尺寸被限制为12，存储过程还可以嵌套，最多嵌套至32领。 7.2,2创建和执行带参数的存德过程 由于不带参数的存销过程的创建是非常简单，所以采用直接创建的办法。但是，对于创 建比较复杂的存储过程，当存储过程时，需要确定存储过程的三个组成部分： ()所有的输入参数以及传给调用者的编出参数 (的被执行的针对量据库的操作语句包括调用其它存储过程的语句： 围运回给调用者的状态值以指明调用是成功还是失败。 建议按理如下四个步囊创建存储过程。 ()输写Transact_SQL语句 例如【示例7.2】中的SgL语句SELECT幸FROM teacher_.1 nfo WHE能gender="男 (的测试Transact_SQl语句 执行编写的5语句，确认结果符合要求。 )如果返回结果符合要求，则按照存储过程的语法创建其存储过程。 ()在服务器上执行存储过程以验证该存锦过程的正确性。 可以将数据值传递给存储过程，面存储过程用来接收传递进来数据值的参数就是所谓的 “输入参数”(Input Paraneters),创建带参数的存绪过程的命令格式 CREATE PROC [EDUIRE]procedure_nane [[@parameter data_type)[-DEFAULT][OUTPUT]][..n] [VITH (RECOMPILE ENCRYPTION RECOMIPILE.ENCRYPTION] AS sql_statenents 其中： ①proced如re_nae为所创建的存储过程的名字。 ②@arameter用来蛋定输入利和输出参数的名称，参最的名称是以字符@开头，而且
创建存储过程的权限，必须通过数据库所有者授权，值得注意的是，应尽量避免存储过程的 使用者和所涉及表的所有者不是同一个人的情况。 ⑶ 由于存储过程是一个数据库对象，其名称必须遵守标识符规则，在命名用户自定义 的存储过程时应避免使用 sp_前缀，以免和系统存储过程混淆。 ⑷ 尽量不要使用临时存储过程，以避免 tempdb 上造成的对系统表资源的争夺，从而导 致影响系统的执行性能。 ⑸ 只能在当前数据库中创建属于当前数据库的存储过程。根据可使用的内存，存储过 程的最大尺寸被限制为 128MB，存储过程还可以嵌套，最多嵌套至 32 级。 7.2.2 创建和执行带参数的存储过程 由于不带参数的存储过程的创建是非常简单，所以采用直接创建的办法。但是，对于创 建比较复杂的存储过程，当存储过程时，需要确定存储过程的三个组成部分： ⑴ 所有的输入参数以及传给调用者的输出参数； ⑵ 被执行的针对数据库的操作语句包括调用其它存储过程的语句； ⑶ 返回给调用者的状态值以指明调用是成功还是失败。 建议按照如下四个步骤创建存储过程。 ⑴ 编写 Transact_SQL 语句 例如【示例 7.2】中的 SQL 语句 SELECT * FROM teacher_info WHERE gender = '男' ⑵ 测试 Transact_SQL 语句 执行编写的 SQL 语句，确认结果符合要求。 ⑶ 如果返回结果符合要求，则按照存储过程的语法创建其存储过程。 ⑷ 在服务器上执行存储过程以验证该存储过程的正确性。 可以将数据值传递给存储过程，而存储过程用来接收传递进来数据值的参数就是所谓的 “输入参数”(Input Parameters)。创建带参数的存储过程的命令格式： CREATE PROC[EDURE] procedure_name [{＠parameter data_type} [=DEFAULT][OUTPUT]][ ,…n] [WITH{RECOMPILE | ENCRYPTION| RECOMPILE, ENCRYPTI○N}] AS sql_ statements 其中： ① procedure_name 为所创建的存储过程的名字。 ② ＠parameter 用来指定输入和输出参数的名称，参数的名称是以字符＠开头，而且
其名称必類符合SQL Server的命名规则。data_type用米指定输入参数的数据类型。输入 参数可以是任何的数据类型（包括text、ntext、i国ge和用户定义数据类生）， 当然，输入参数的数据类型也就决定了这一输入参数所能接收数据植的类型和范围，例 如，输入参数的数据类型是11nt,则它只能接收-32,76832,767之饲的整数值，将不 符合此条件的数据值传递给它都会发生错误。 DFLT用来番定输入参数的欧认值。它是一个常量成N山，在创建存储过程时如果给 出了默认值，那么在执行该存他过程时，如果没有向具有默认值的参数传递参数值时，则具 有默认值的参数就可以使用它们的默认值。UTUT表明参数是返回参数。 @IT用0WPE读定执行计划不保存在过程高速缓存中，每次执行时都重新编译它。 ITH ENCRYPTION对含有CREATE PROCEDURE正文的sysC0ents项进行加密。 ④sql_stateneats:在存储过程中需要执行的数据库操作。 1.具有输入参数的存储过程 【示例7,3】针对教师基本信息表teacher_.info,创建一个名称为teacher_proc2的存 储过程，执行存储过程将完成向数据表teacher_info中插入一条记录，新记录的值由参数 提供。 USE student 0国 CREATE PROCEDURE teacher_proc2 (eno char(6),enan varchar (8),esex char(2).eaxe int,etitle char (10),etel varchar(12).Esala decinal(7).enun char (10)) AS INSERT INTO teacher_info VALUES (@no,@nan,@sex,age.@title,@tel,sala, nu国 00 执行带有输入参数的存储过程有两件种方法：一是使用参数名传送参量值，二局按位置传 送参数值： ()使用参数名传送参数值 在执行存储过程的语句中，通过语句@paraneter_.nne=yae给出参数的传递值。当 存储过程含有多个输入参数时，参数值可以以任意顺序指定，对于允许空值和具有默认值的 输入参数可以不给出参数的传递值。语法命令如下
其名称必须符合 SQL Server 的命名规则。data_type 用来指定输入参数的数据类型。输入 参数可以是任何的数据类型(包括 text、ntext、image 和用户定义数据类型)。 当然，输入参数的数据类型也就决定了这一输入参数所能接收数据值的类型和范围。例 如，输入参数的数据类型是 smallint，则它只能接收-32,768~32,767 之间的整数值，将不 符合此条件的数据值传递给它都会发生错误。 DEFAULT 用来指定输入参数的默认值，它是一个常量或 NULL。在创建存储过程时如果给 出了默认值，那么在执行该存储过程时，如果没有向具有默认值的参数传递参数值时，则具 有默认值的参数就可以使用它们的默认值。OUTPUT 表明参数是返回参数。 ③ WITH RECOMPILE 决定执行计划不保存在过程高速缓存中，每次执行时都重新编译它。 WITH ENCRYPTION 对含有 CREATE PROCEDURE 正文的 syscomments 项进行加密。 ④ sql_statements：在存储过程中需要执行的数据库操作。 1. 具有输入参数的存储过程 【示例 7.3】针对教师基本信息表 teacher_info，创建一个名称为 teacher_proc2 的存 储过程，执行存储过程将完成向数据表 teacher_info 中插入一条记录，新记录的值由参数 提供。 USE student GO CREATE PROCEDURE teacher_proc2 (@no char(6), @nam varchar(8), @sex char(2), @age int, @title char(10), @tel varchar(12), @sala decimal(7), @num char(10)) AS INSERT INTO teacher_info VALUES(@no, @nam, @sex, @age, @title, @tel, @sala, @num) GO 执行带有输入参数的存储过程有两种方法：一是使用参数名传送参数值，二是按位置传 送参数值。 ⑴ 使用参数名传送参数值 在执行存储过程的语句中，通过语句＠parameter_name=value 给出参数的传递值。当 存储过程含有多个输入参数时，参数值可以以任意顺序指定，对于允许空值和具有默认值的 输入参数可以不给出参数的传递值。语法命令如下：
[[EXEC[UTE]]procedure_nane [eparameter_nane value][,.n] 其中各参数的意义为：procedure_nae为存储过程名：@paraneter_.nae为输入参数 名：alue为传递给输入参量的值。 【示例7.4】使用参数名传送参数值的方法来执行存储过程teacher_proc2,完成向数 据表teacher_info中插入一条记录. USE student 00 CECUTE teacher_proc2no'010l0g',nar’李铁培'，sex'男'， nge=49,tit1e-'高讲'，te1-'0203430129g',sa1a=250.0.ur'0401010207 为了说明使用参数名传递参数值时可以以任意顺序指定参数值，将两个参数的次序选行 了顾倒。此外由于目的地的查询值与该参数的默认值一致，所以也没有对该输入参数的参数 值进行指定。 心按位置传送参数值 在执行存储过程的语句中，不参照被传递的参数面直接给出参数的传递值。当存销过程 含有多个输入参数时。传递值的顺序必领与存储过程中定义的输入参数的顺序相一致。 语法命令如下： [EXEC[UTE]]proc_nane [valuel,value2,] 其中各参数的意义为：proe_nam为存储过程名：aluel,alue2.为传递给各输入参 数的值，按位置传送参数值时，也可以忽略允许空值和具有默认值的参数，但是不修因此破 环输入参数的指定次序。因此，在一个含有四个输入参数的存储过程中，用户可以题略第三 和第四个参数，但无法在忽略第三个参数的情况下而指定第四个参数的输入值。 【示例T.5】使用按位置传送参数值的方法来执行teacher_proc2存储过程，实现向数 据表teacher_info中插入一条记录。具体命令知下， USE student CO EXEC teacher_proc2‘01010g',·李格铁‘，·女”，48，”讲师 ','02034367719',2500,'0401010108 这里由于使用的是按位置传送参数值的方法，所以不可以将参数的次序进行额倒。此外
[[EXEC[UTE]] procedure_name [@parameter_name = value][ ,…n] 其中各参数的意义为：procedure_name 为存储过程名；＠parameter_name 为输入参数 名；value 为传递给输入参数的值。 【示例 7.4】使用参数名传送参数值的方法来执行存储过程 teacher_proc2，完成向数 据表 teacher_info 中插入一条记录。 USE student GO EXECUTE teacher_proc2 @no ='010108', @nam=' 李 铁 烙 ', @sex=' 男 ', @age=49,@title='高讲', @tel='02034301299', @sala=250.0, @num='0401010207' 为了说明使用参数名传递参数值时可以以任意顺序指定参数值，将两个参数的次序进行 了颠倒，此外由于目的地的查询值与该参数的默认值一致，所以也没有对该输入参数的参数 值进行指定。 ⑵ 按位置传送参数值 在执行存储过程的语句中，不参照被传递的参数而直接给出参数的传递值。当存储过程 含有多个输入参数时，传递值的顺序必须与存储过程中定义的输入参数的顺序相一致。 语法命令如下： [EXEC[UTE]] proc_name [value1,value2,…] 其中各参数的意义为：proc_name 为存储过程名；value1,value2,…为传递给各输入参 数的值。按位置传送参数值时，也可以忽略允许空值和具有默认值的参数，但是不能因此破 坏输入参数的指定次序。因此，在一个含有四个输入参数的存储过程中，用户可以忽略第三 和第四个参数，但无法在忽略第三个参数的情况下而指定第四个参数的输入值。 【示例 7.5】使用按位置传送参数值的方法来执行 teacher_proc2 存储过程，实现向数 据表 teacher_info 中插入一条记录。具体命令如下： USE student GO EXEC teacher_proc2 '010109', ' 李烙铁 ', ' 女 ', 48,' 讲 师 ','02034367719',250.0,'0401010108' 这里由于使用的是按位置传送参数值的方法，所以不可以将参数的次序进行颠倒。此外