数据库应用技术：第11章 存储过程与触发器

第11章存储过程与触发器

本章主要介绍SQLServer2005中存储过程的概念、类型、常用系统存储过程以及如何

创建存储过程；另外介绍了触发器的类型以及创建和使用。

11.1存储过程

存储过程是由Transact-SQL命令编写的过程,这个过程经编译和优化后存储在数据库服

务器中，使用时只要调用即可。

11.1.1存储过程的功能及优势

MicrosoftSQLServer中的存储过程与其他编程语言中的过程类似，可以实现以下功能。

>接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。

>包含用于在数据库中执行操作（包括调用其他过程）的编程语句。

>向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。

可以使用Transact-SQL语言的EXECUTE命令运行存储过程。存储过程与函数不同，

存储过程不返回取代其名称的值，也不能直接在表达式中使用。

在SQLServer中使用存储过程而不使用存储在客户端计算机本地的Transact-SQL程序

有以下优势。

>存储过程已在服务器注册。

>存储过程具有安全特性（例如权限）和所有权链接，且具有可以附加到存储过程的证

书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。

>存储过程可以加强应用程序的安全性。参数化存储过程有助于保护应用程序不受

SQLInjection攻击。

>存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多

次。这样可以改进应用程序的可维护性，并允许应用程序统一访问数据库。

>存储过程可以减少网络通信流量。一个需要数百行Transact-SQL代码的操作可以通

过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。

11.1.2存储过程类型

MicrosoftSQLServer2005中有多种可用的存储过程。

1.用户定义的存储过程

用户定义的存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参

数、向客户端返回表格或标量结果和消息、调用数据定义语言①DL）和数据操作语言（DML）

的命令，然后返回输出参数。在SQLServer2005中，用户定义的存储过程有Transact-SQL

或CLR两种类型。

•2•数据库应用技术

（l）Transact-SQL存储过程

Transact-SQL存储过程是指保存的Transact-SQL语句集合，可以接受和返回用户提供的

参数。存储过程中可能包含根据客户端应用程序提供的信息在一个或多个表中插入新行所需

的语句。存储过程也可能从数据库向客户端应用程序返回数据。例如，电子商务Web应用

程序可以使用存储过程实现根据联机用户指定的搜索条件返回有关特定产品的信息。

（2）CLR存储过程

CLR存储过程是指对Microsoft.NETFramework公共语言运行时CLR方法的引用，可

以接受和返回用户提供的参数。它们在.NETFramework程序集中是作为类的公共静态方法

实现的。

2.扩展存储过程

扩展存储过程是指MicrosoftSQLServer的实例可以动态加载和运行的DLL,允许使用

编程语言（例如C语言）创建自己的外部例程。扩展存储过程直接在SQLServer的实例的地址

空间中运行，可以使用SQLServer扩展存储过程API完成编程。

3.系统存储过程

SQLServer2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过

程称为系统存储过程。例如，sys.sp_changedbowner就是一个系统存储过程。从物理意义上

讲，系统存储过程存储在源数据库中，并且带有sp_前缀。从逻辑意义上讲，系统存储过程

出现在每个系统定义数据库和用户定义数据库的sys构架中。在SQLServer2005中，可将

GRANT>DENY和REVOKE权限应用于系统存储过程。

SQLServer支持在SQLServer和外部程序之间提供一个接口，以实现各种维护活动的

系统存储过程。这些扩展存储程序使用xp-前缀。

11.1.3常用系统存储过程

下面介绍几种常用的系统存储过程。

1.sp_help

报告有关数据库对象（sys.sysobjects兼容视图中列出的所有对象）、用户定义数据类型或

SQLServer2005提供的数据类型的信息。语法格式如下。

sp_help[[@objname=]'name']

sp_help过程仅在当前数据库中查找对象。

如果未指定name,则sp_help将列出当前数据库中所有对象的对象名称、所有者和对象

类型。sp_helptrigger提供有关触发器的信息。

[@objname=]'name'：sysobjects类型或systypes表中任何用户定义数据类型的某个对

象的名称。name的数据类型为nvarchar（776）,默认值为NULL。不能接受数据库名称。

返回代码值为0（成功）或1（失败）。

返回的结果集取决于name是否已指定、何时指定以及属于何种数据库对象。

该系统存储过程的使用权限为具有public角色的成员身份。

•如果执行不带参数的sp-help,则返回当前数据库中现有的所有类型对象的汇总信

息，即Name（对象名称）、所有者（对象所有者）及Objectjype（对象类型）。

【例11.11返回有关所有对象的信息。以下示例将列出有关teaching数据库中每个对象

的信息。

USEteaching

GO

EXECsp_help

GO

执行结果如图11.1所示。

二）结果I二消息I

NameOwner|Object_type|

1：sysconsUaintsdboview

2syssegmentsdboview

3coursedbousertable

4S_Cdbousertable

5studentdbousertable

6$y$column$dbosystemtable

_7__syscommentsdbosystemtable

8sysdependsdbosystemtable

Ieimacrci’ccrlkr»cigiamFnhlo

jUsejtype|Storagetype|Length|Prec|Scale|Nullable|Defaukname|Rulename|Collation|

,查询已成功执行.；“ec303（8.0RTM）CTEC303\ajeeky（52）|teaching|00:00:0132行

图11』teaching数据库中每个对象的信息

•如果name是SQLServer数据类型或用户定义数据类型，则sp-help将返回结果集

包括Type_name（数据类型名称）、Storage_type（SQLServer类型名称）、长度（数据类型的物理

长度（以字节为单位））、Prec（精度（数字总位数））、小数位数（小数点右边的数字位数）、

Nullable（指示是否允许NULL值：“是"或“否"）、Default_name（绑定到此类型的默认值

的名称）、Rule」1ame（绑定到此类型的规则的名称）、排序规则（数据类型的排序规则）。

【例11.2】返回数据类型信息。以下示例将列出有关teaching数据库中smallint数据类

型的信息。

USEteaching

GO

EXECsp_helpsmallint

GO

执行结果如图11.2所示。

•4•数据库应用技术

图11.2teaching数据库中smalIint数据类型的信息

•如果name是数据库对象而不是数据类型，则sp_help将根据指定的数据库对象的

类型返回结果集，同时还包括Name（表名）、所有者（表所有者）、Type（表类型）及

Created_datetime（表的创建日期）。根据指定的数据库对象，sp_help将返回其他结果集。

如果name是系统表、用户表或视图，则sp_help将返回结果集，结果集包括的内容及

含义见表11」至表11.8。但是，不会为视图返回说明数据文件在文件组中位置的结果集。

令返回的有关列对象的结果集

表11.1有关列对象的结果集

列名数据类型说明

Column_namenvarchar(128)列名

Typenvarchar(128)列数据类型

Computedvarchar(35)指示是否计算列中的值：“是”或“否”

Lengthint以字节为单位的列长度

Precchar(5)列精度

Scalechar(5)列小数位数

Nullablevarchar(35)指示是否允许列中包含NULL值：“是"或"否"

TrimTrailingBlanksvarchar(35)剪裁尾随空格，返回Yes或No

FixedLenNullInSourcevarchar(35)仅为保持向后兼容性

Collationsysname列的排序规则，对于非字符数据类性为NULL

令针对标识列返回的结果集

表11.2针对标识列返回的结果集

列名数据类型说明

Identitynvarchar(128)其数据类型被声明为标识的列名

Seednumeric标识列的起始值

Incrementnumeric用于此列中的值的增量

复制登录名（如sqlrepl）试图在表中插入数据时，

NotForReplicationint

不强制使用IDENTITY属性：1=True：0=False

令针对各列返回的结果集

表11.3针对各列返回的结果集

列名数据类型说明

RowGuidColsysname全局唯一标识符列的名称

令针对文件组返回的结果集

表11.4针对文件组返回的结果集

列名数据类型说明

数据所在的文件组：主要文件组、

Data_located_on_filegroupnvarchar(128)

次要文件组或事务日志文件组

令针对索引返回的结果集

表11.5针对索引返回的结果集

列名数据类型说明

index_namesysname索引名

index_descriptionvarchar(210)索引的说明

index_keysnvarchar(2078)要生成索引的列的列名

令针对约束返回的结果集

表11.6针对约束返回的结果集

列名数据类型说明

constrain_typenvarchar(146)约束的类型

constraint_namenvarchar(128)约束的名称

指示DELETE操作是：NoAction,CASCADE

delete_actionnvarchar(9)

还是N/A。仅适用于FOREIGNKEY约束

指示UPDATE操作是：NoAction,Cascade还

update_actionnvarchar(9)

是N/Ao仅适用于FOREIGNKEY约束

指示是否启用约束：EnabledsDisabled或N/A。

status_enabledvarchar(8)

仅适用于CHECK和FOREIGNKEY约束

指示约束是否用于复制。仅适用于CHECK和

Status_fbr_replicationvarchar(19)

FOREIGNKEY约束

构成约束的列的名称。对于默认值和规则而言，

constrain_keysnvarchar(2078)

则为定义默认值或规则的文本

令针对执行引用的对象返回的结果集

•6•数据库应用技术

表11.7针对执行引用的对象返回的结果集

列名数据类型说明

Tableisreferencedbyviewsnvarchar(516)标识引用表的其他数据库对象

今针对存储过程、函数或扩展存储过程返回的结果集

表11.8针对存储过程、函数或扩展存储过程返回的结果集

列名数据类型说明

Parameter_namenvarchar(128)存储过程参数名

Typenvarchar(128)存储过程参数的数据类型

Lengthsmallint最大物理存储长度（以字节为单位）

Precint精度，即数字总位数

Scaleint小数点右边的数字位数

Param_ordersmallint参数的顺序

Parameter_namenvarchar(128)存储过程参数名

【例11.3］返回有关单个对象的信息。以下示例将显示有关student表的信息。

USEteaching

GO

EXECsp_help'student,

GO

执行结果如图11.3所示。

二）结果I」消息I

Name|Owner|Type|Createddatetime

1studentjdbousertable2006-06-2517:50:31700

Column_nameType|Computed|Length|Prec|Scale|NullableTrimTrailingBlanks|FixedLenNullnSourceCollation

|SNO

1charno4nononoChine$e_PRC_CI_AS

2SNAMEncharno20no(n/a)(n/a)Chine$e_PRC_CI_AS

3SEXcharno1yesnoyesChinese_PRC_CI_AS

4AGEsmaliintno250yes(n/a)(n/a)NULL

IIdentity|Seed|Increment|NotForReplication|

1No^entitycolumnde(ined.jNULLNULLNULL-

____RowGui比ol]

J___iNorowguidcolcolumndefined,i

IData_tocated_on_filegroup|

1|jPRIMARY

index一name|index_descriptionIr)dex_key5|

1PK_student：clustered,unique,primarykeylocatedonPRIMARYSNO

con$traint_typeconstrain5ame|delete_action卬date_action$talu$_enabledstatusJor_replicat»oncon$tfdint_key$

1CHECKoncolumnSEXCK_$tudent(n/a)(n/a)Enabledl$_For_ReplicationQSEXHF'ORISEXh'M')

2_CHECKoncolumnAGECK_Table_2(n/a)(n/a)Enabledls_For_Replication0AGE]>=(18)AND(AG...

2-lPRIMARYKEY(dust...PK_student(n/a)(n/a)(n/a)(n/a)SNO

|Tableis【efeiencedbyviews|

②查询已成功执行.CTEC-YZM(9.0RTM)CTEC-YZM\Administrator(53)t.aching00:00:0212行

图11.3teaching数据库中student表的信息

2.sp_helpdb

报告有关指定数据库或所有数据库的信息。语法格式如下。

sp_helpdb[[@dbname=]'name*]

[@dbname=「name，：要报告其信息的数据库的名称。name的数据类型为sysname,

没有默认值。如果未指定name,则sp_helpdb将报告sys.databases目录视图中所有数据库的

信息。

返回代码值为0（成功）或1（失败）。

sp_helpdb存储过程结果集如表11.9所示。

表11.9sp_he1pdb存储过程结果集

列名数据类型说明

namesysname数据库名称

db_sizenvarchar(13)数据库总计大小

ownersysname数据库所有者，例如sa

dbidsmallint数据库ID

creatednvarchar(11)数据库创建的日期

以逗号分隔的值列表，这些值是当前在数据库上设置

statusnvarchar(600)的数据库选项的值

只有启用布尔值选项时，才将这些选项列出。非布尔

•8・数据库应用技术

选项及其对应值以option_name=value的形式列出

compatibilityjeveltinyint数据库兼容级别：60、65、70、80或90

结果集中的status列报告数据库中已设置为ON的选项，并非所有的数据库选项都由

status列报告.若要查看当前数据库选项设置的完整列表，可以使用sys.databases目录视图。

如果指定name,便会有显示指定数据库的文件分配的结果集，如表11.10所示。

表II.10指定name的结果集

列名数据类型说明

namenchar(128)逻辑文件名

fileidsmallint文件ID

filenamenchar(260)操作系统文件名(物理文件名称)

文件所属的文件组

filegroupnvarchar(128)

NULL=文件为日志文件，它决不是文件组的一部分

sizenvarchar(18)文件大小(MB)

文件大小可达到的最大值。此字段中的UNLIMITED

maxsizenvarchar(18)

值表示文件可以一直增长到磁盘已满

文件的增量，表示每次需要新的空间时给文件增加的

growthnvarchar(18)

空间大小

文件用法，对于数据文件，该值为dataonly，，对于日

usagevarchar(9)

志文件，该值为'logonly，

该系统存储过程使用权限为，当指定单个数据库时，需要具有数据库中的public角色成

员身份。当没有指定数据库时，需要具有master数据库中的public角色成员身份。

如果无法访问数据库，那么sp_helpdb将显示相应的错误消息。

【例11.4】返回有关单个数据库的信息。以下示例显示有关teaching数据库的信息。

EXECsp_helpdb'teaching'

GO

图11.4teaching数据库信息

【例11.51返回有关所有数据库的信息•以下示例显示运行在SQLServer服务器上所

有数据库的信息。

EXECsp_helpdb

GO

执行结果如图11.5所示。

二]结果I」消息I

namedb_$ueownerdbidcreatedstatus8mpabbilityjevel|

1AdvenlureV/orks165.94MBCTEC-YZMSAdmimstrata6Apr202006Status-ONLINE,Updateability-READ.WRITE,UserAcc..90

2AdventureWorksDW70.50MBCTEC-VZHVAdmirtstratoc5Apr202006Status=ONLINE,Updateabilily=READ_WRITE,UserAcc..90

3master6.50MBsa1Apr82003Status=ONLINEzUpdateability=READ_WRITE/UserAcc...90

4model3.19MBsa3Apr82003Status=ONLINEzUpdateability=READ_WRITE,UserAcc...90

5m$db9.44MB4Oct142005Stalus«ONLINE,Updateability«READ_WRITE,UserAcc...90

6teaching8.00MBNTAUTHORITYSSYSTEM8Aug272006S»atus«ONLINE.Updateability-READ.WRITE,UserAcc.90

7teaching_new8.00MBNTAUTHORITYKSYSTEM7Aug272006Status=ONLINEzUpdateability=READ_WRITEzUserAcc...90

8tempdb8.50MBsa2Aug292006Status=ONLINE,Updateability=READ_WRITE,UserAcc...90

CTEC-YZM(9.0RTM)CTEC-YZB\Administrator(53)t<achinC00:00:018行

图11.5所有数据库信息

3.sp_helpfile

返回与当前数据库关联的文件的物理名称及属性。使用此存储过程可以确定附加到服务

器或从服务器分离的文件名。语法格式如下。

sp_helpfile[[@filename=J'name'J

[@filename=]'name'：是当前数据库中任意文件的逻辑名称。name的数据类型为

sysname,默认值为NULL。如果未指定name,则返回当前数据库中所有文件的属性。

返回代码值为0（成功）或1（失败）。

sp_helpfile存储过程结果集如表11.11所示。

表11.11sp_helpfiIe存储过程结果集

列名数据类型说明

namesysname逻辑文件名

fileidsmallint文件的数字标识符。如果指定了name,则不返回该标识符

filenamenchar(260)物理文件名

文件所属的文件组

filegroupsysname

NULL二文件为日志文件，它决不是文件组的一部分

sizenvarchar(15)文件大小（MB）

文件大小可达到的最大值，此字段中的UNLIMITED值表示

maxsizenvarchar(15)

文件可以一直增长到磁盘充满

文件的增量，表示每次需要新空间时为文件增加的空间大小

growthnvarchar(15)

0=文件的大小是固定的，不会增长

对于数据文件，该值为'dataonly"而对于日志文件，该值为

usagevarchar(9)

'logonly'

该系统存储过程使用权限为要求具有public角色的成员身份。

【例11.6]以下示例返回有关teaching中的文件的信息。

USEteaching

•10.数据库应用技术

GO

EXECsp.helpfile

GO

执行结果如图11.6所示。

□结果

namefiieidfilenamefilegroifisizemaxsizegrowthusage

1jteaching_data1E:\ProgramFitesXMicrosoftSQLServef\MSSQL.1\MSS...PRIMARY3072KBUnlimited2048KBdataonly

2teachingjog2E:\ProgramFile$\Micro$oftSQLServer\MSSQL,1\MSS..NULL1024KB2147483648KB10%logonly

3teaching_由3EAPfogramFile式MicrosoftSQLServef\MSSQL.1\MSS...PRIMARY2048KBUnlimited1024KBdataonly

4teaching_log14E:\ProgramFiies\Micro$oftSQLSefver\MSSQL.1\MSS...NULL2048KB2147483848KB1024KBlogonly

ill◎查询已成功执行.CTEC-YZH(9.0RTM)CTEC-YZH\A4»inistr»tor63)teachin;00:00:00

图11.6teaching数据库文件信息

4.sp_rename

在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、歹！J、别名数据类型

或Microsoft.NETFramework公共语言运行时(CLR)用户定义类型。语法格式如下。

sp_rename[@objname=]'object_name',[@newname=]'new_name'

[,[@objtype=]'object_type'】

只能更改当前数据库中的对象名称或数据类型名称。大多数系统数据类型和系统对象的

名称都不能更改。

每当重命名PRIMARYKEY或UNIQUE约束时，sp_rename都会自动重命名关联的索

引。如果重命名的索引与PRIMARYKEY约束关联，则sp_rename也会自动重命名该

PRIMARYKEY约束。

更改对象名的任一部分都可能破坏脚本和存储过程。建议不要使用此语句来重命名存储

过程、触发器、用户定义函数或视图，而是删除该对象，然后使用新名称重新创建该对象。

重命名诸如表或列等对象将不会自动重命名对该对象的引用。必须手动修改引用已重命

名对象的任何对象。例如，如果重命名表列，并且触发器中引用了该列，则手动必须修改触

发器以反映新的列名。

•[@objname=]'object_name's用户对象或数据类型的当前限定或非限定的名称。

如果要重命名的对象是表中的列，则object_name的格式必须是table.columiio如果要重命名

的对象是索引，则object_name的格式必须是table.index。

只有在指定了合法的对象时才使用引号。如果提供了完全限定名称，包括数据库名称，

则该数据库名称必须是当前数据库的名称。objecjname的数据类型为nvarchar(776),无默

认值。

•[@newname=]'new_name'：指定对象的新名称。new_name必须是名称的一部分，

并且必须遵循标识符的规则。new_name的数据类型为sysname,无默认值。

注意：触发器名称不能以#或##开头。

•[@objtype=],object_type'：指定要重命名的对象类型。object_type的数据类型为

varchar（13）,默认值为NULL,可取如表11.12所示的值之一。

表11.12object_type的数据类型值

值说明

COLUMN要重命名的列

DATABASE用户定义数据库。重命名数据库时需要此对象类型

INDEX用户定义索引

在sys.objects中跟踪的类型的项目。例如，OBJECT

OBJECT可用于重命名约束（CHECK、FOREIGNKEY、

PRIMARY/UNIQUEKEY）,用户表和规则等对象

通过执行CREATETYPE或sp_addtype添加别名数

USERDATATYPE

据类型或CLR用户定义类型

返回代码值为0（成功）或非零数字（失败）。

该系统存储过程的使用权限为，若要重命名对象、列或索引，则需要对该对象具有

ALTER权限。若要重命名用户类型，则需要对该类型具有CONTROL权限。若要重命名数

据库，需要具备sysadmin或dbcreator固定服务器角色的成员身份。

【例11.7]重命名表。以下示例将student表重命名为stu。

USEteaching

GO

EXECsp_rename'student',*stu'

GO

【例11.8】重命名列。以下示例将student表中的SNO重命名为SID。

USEteaching

GO

EXECsp_rename'student.SNO1,'SID\'COLUMN'

GO

【例11.9]重命名索引。以下示例将PK_stu命nt索引重命名为PK_stu。

USEteaching

GO

EXECsp_rename'student.PK_student','P^stu','INDEX1

GO

5.sp_renamedb

更改数据库的名称。语法格式如下。

sp_renamedb[@dbname=1'old_name',[@newname=]'new_name'

•[@dbname=]'1：数据库的当前名称。old_name的数据类型为sysname,

无默认值。

•[@newname=]*new_name*：数据库的新名称。new_name必须遵循有关标识符的

规则。new_name的数据类型为sysname,无默认值。

返回代码值为0（成功）或非零数字（失败）。

该系统存储过程的使用权限为，具有sysadmin或dbcreator固定服务器角色的成员资格。

•12•数据库应用技术

【例11.10】以下示例先创建Accounting数据库，然后将该数据库的名称更改为Financial,

再查询sys.databases目录视图以确认数据库的新名称。

USEmaster

GO

CREATEDATABASEAccounting

GO

EXECsp_renamedbN'Accounting',N'Financial'

GO

SELECTname,database__id,modified_date

FROMsys.databases

WHEREname=N'Financial'

GO

6.sp_databases

列出驻留在SQLServer2005DatabaseEngine实例中的数据库或可以通过数据库网关访

问的数据库。语法格式如下。

sp_databases

所返回的数据库名称可以作为USE语句的参数，用来更改当前数据库上下文。

返回代码值为无。

sp_databases存储过程结果集如表11.13所示。

表11.13sp_databases存储过程结果集

列名数据类型说明

数据库的名称。在数据库引擎中，此列表示存

DATABASE_NAMEsysname

储在sys.databases口录视图中的数据库名称

DATABASE_SIZEint数据库的大小（以KB计）

REMARKSvarchar(254)对于数据库引擎，此字段始终返回NULL

该系统存储过程的使用权限为：需要对架构的SELECT权限。

11.11］以下示例显示如何执行sp_databaseso

EXECsp_databases

GO

执行结果如图11.7所示。

二1转果I」消息I

|DATABMENAME|DATABASE一SIZE|REMARKS|

1J；AdventureWorks169920NULL

2AdventiMeWorksDW72192NULL

3master6656NULL

4model3264NULL

5m$曲9664NULL

Eteachng8192NULL

7teaching_new8192NULL

8tempcfc8704NULL

,查询已成功执行.icTEC-YZM(9.0RTM)CTEC-YZM\Administrator©3)|teaching00:00:00g行

图11.7驻留实例中的数据库信息

7.sp_Ubles

返回可在当前环境中查询的对象列表。这些对象是可以在FROM子句中出现的任何对

象。语法格式如下。

sp_tables[[@table_name=J'name']

[,[@table_owner=]'owner']

[,I@table_qualifier=J'qualifier']

[,[@table_type=]"'type'"]

[,l@fUsePattern=]'fUsePattern'J

•[@table_name=]'name'：用来返回目录信息的表。name的数据类型为

nvarchar（384）,默认值为NULL。支持通配符模式匹配。

•[@table_owner=],owner'：用于返回目录信息的表的所有者。owner的数据类型

为nvarchar（384）,默认值为NULL。支持通配符模式匹配。如果未指定所有者，则遵循基础

DBMS的默认表可见性规则。

在SQLServer中，如果当前用户拥有一个具有指定名称的表，则返回该表的列。如果

未指定所有者，且当前用户未拥有指定名称的表，则该过程查找由数据库所有者拥有的具有

指定名称的表。如果有，则返回该表的列。

•[@table_qualifier=]'qualifier'：表限定符的名称。qualifier的数据类型为sysname,

默认值为NULL。在SQLServer中，此列表示数据库名称。

•[,[@ta