sql_server_2008_存储过程与触发器资源-CSDN文库

sql_server_2008

存储过程与触发器

3星 · 超过75%的资源需积分: 15 33 浏览量 2011-09-30 13:25:57 上传评论收藏 934KB PDF 举报

资源推荐

资源详情

资源评论

第八章存储过程与触发器

在 SQL Server 2008 中存储过程和触发器是两个重要的数据库对象。使用存储过程，可

以将 Transact-SQL 语句和控制流语句预编译到集合并保存到服务器端，它使得管理数据库

、

显示关于数据库及其用户信息的工作更为容易。

而触发器是一种特殊类型的存储过程，在用户使用一种或多种数据修改操作来修改指定

表中的数据时被触发并自动执行，通常用于实现复杂的业务规则，更有效地实施数据完整性。

本章学习目标

 了解存储过程的作用及类型

 掌握存储过程的创建及应用

 熟悉存储过程的管理

 了解触发器的作用及分类

 熟悉各种类型触发器的创建

 了解嵌套、递归触发器

 熟悉触发器的管理

8.1

8.1 认识存储过程

Transact-SQL

语句是应用程序与

SQL Server

数据库之间的主要编程接口，大量的时间将

花费在

Transact-SQL

语句和应用程序代码上。在很多情况下，许多代码被重复使用多次，每

次都输入相同的代码不但繁琐，更由于在客户机上的大量命令语句逐条向

SQL Server

发送将

降低系统运行效率。因此，

SQL Server

提供了一种方法，它将一些固定的操作集中起来由

SQL

Server

数据库服务器来完成，应用程序只需调用它的名称，将可实现某个特定的任务，这种

方法就是存储过程。

下面将详细介绍存储过程的概念、特点、创建、执行等内容。

8.1.1

8.1.1 存储过程概述

SQL Server 中 T-SQL 语言为了实现特定任务而将一些需要多次调用的固定的操作编写成

子程序并集中以一个存储单元的形式存储在服务器上，由 SQL Server 数据库服务器通过子程

序名来调用它们，这些子程序就是存储过程。

存储过程是一种数据库对象，存储在数据库内，可由应用程序通过一个调用执行，而且

允许用户声明变量、有条件执行，具有很强的编程功能。存储过程可以使用 EXECUTE 语句

来运行。

在 SQL Serve r 中使用存储过程而不使用存储在客户端计算机本地的 T-SQ L 程序有以下几

个方面的好处。

 加快系统运行速度存储程序只在创建时进行编译，以后每次执行存储过程都不需再

重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。

 封装复杂操作当对数据库进行复杂操作时（如对多个表进行更新，删除时），可用

存储过程将此复杂操作封装起来与数据库提供的事务处理结合一起使用。

 实现代码重用可以实现模块化程序设计，存储过程一旦创建，以后即可在程序中调

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

 增强安全性可设定特定用户具有对指定存储过程的执行权限而不具备直接对存储

过程中引用的对象具有权限。可以强制应用程序的安全性，参数化存储过程有助于保护应用程

序不受 SQL 注入式攻击。

 减少网络流量因为存储过程存储在服务器上，并在服务器上运行。一个需要数百行

T-SQ L 代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码

，

这样就可以减少网络流量。

8.1.2

8.1.2 存储过程的分类

存储过程是一个被命名的存储在服务器上的 Transact-SQL 语句的集合，是封装重复性工

作的一种方法，它支持用户声明的变量、条件执行和其他强大的编程功能。

在 SQL Server 2008 中存储过程可以分为两类：系统存储过程、用户存储过程和扩展性存储

过程。

1 ．系统存储过程

系统存储过程是由 SQL Server 系统提供的存储过程，可以作为命令执行各种操作。

系统存储过程主要用来从系统表中获取信息，为系统管理员管理 SQL Server 提供帮助

，

为用户查看数据库对象提供方便。例如，执行

SP_HELPTEXT

系统存储过程可以显示规则、默

认值、未加密的存储过程、用户函数、触发器或视图的文本信息；执行

sp_depends

系统存

储过程可以显示有关数据库对象相关性的信息；执行

sp_rename

系统存储过程可以更改当前

数据库中用户创建对象的名称。

SQL Server

中许多管理工作是通过执行系统存储过程来完成

的，许多系统信息也可以通过执行系统存储过程而获得。

系统存储过程定义在系统数据库

master

中，其前缀是

sp_

。在调用时不必在存储过程前

加上数据库名。

．用户存储过程

用户存储过程是指用户根据自身需要，为完成某一特定功能，在用户数据库中创建的存

储过程。用户创建存储过程时，存储过程名的前面加上 “ ## ” ，是表示创建全局临时存储过

程。在存储过程名前面加上 “ # ” ，是表示创建局部临时存储过程。局部临时存储过程只能在

创建它的会话中可用，当前会话结束时除去。全局临时存储过程可以在所有会话中使用，即

所有用户均可以访问该过程。它们都在 tempdb 数据库上。

存储过程可以接受输入参数、向客户端返回表格或者标量结果和消息、调用数据定义语

言（ DDL ）和数据操作语言（ DML ），然后返回输出参数。在 SQL Server 2008 中，用户定义

的存储过程有两种类型： Transact-SQL 或者 CLR ，如表 8-1 所示。

表 8-1 用户定义存储过程的两种类型

存储过程类型说明

Transact-SQL

Transact-SQL 存储过程是指保存的 Transact-SQL 语句集合，可以接

受和返回用户提供的参数。存储过程也可能从数据库向客户端应用程

序返回数据。

CLR

CL R 存储过程是指对 Microsoft .NET Framewor k 公共语言运行时方

法的引用，可以接受和返回用户提供的参数。他们在 .NET Framewor k

程序集中是作为类的公共静态方法实现的

3 ．扩展存储过程

扩展存储过程以在 SQL Server 环境外执行的动态链接库（ DLL ， Dynamic-Link Librar-ies

）

来实现。扩展存储过程通过前缀 “ xp_ ” 来标识，它们以与存储过程相似的方式来执行。

8.2

8.2 使用存储过程

在使用存储过程之前，首先需要创建一个存储过程，这可以通过

T-SQL

语句

CREATE PR

OCEDURE

来完成。在使用的过程中，包括对存储过程的执行、查看和修改以及删除操作。

8.2.1

8.2.1 创建存储过程

在

SQL Server 2008kh ,

可以使用

T-SQL

语句

CREATE PROCEDURE

来创建存储过程。在创建

存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过程或批

处理时以示成功或失败的状态值、捕获和处理潜在错误时的错误处理语句等。

需要强调的是，必须具有 CREATE PROCEDURE 权限才能创建存储过程，存储过程是架构

作用域中的对象，只能在本地数据库中创建存储过程。

1 ．创建存储过程的规则

在设计和创建存储过程时，应该满足一定的约束和规则。只有满足了这些约束和规则才

能创建有效的存储过程。

 CREATE PROCEDURE 定义自身可以包括任意数量和类型的 SQL 语句，但表 8-2 中的语

句除外。因为不能在存储过程的任何位置使用这些语句。

表 8-2 CREATE PROCEDURE 定义中不能出现的语句

CREATE AGGREGATE CREATE RULE

CREATE DEFAULT CREATE SCHEMA

CREATE 或 ALTER FUNCTION CREATE 或 ALTER TRIGGER

CREATE

或

ALTER PROCEDURE CREATE.

或

ALTER VIEW

SET PARSEONLY SET SHOWPLAN_ALL

SET SHOWPLAN_TEXT SET SHOWPLAN_XML

USE Database_name

 可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。

 可以在存储过程内引用临时表。

 如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过

程后，临时表将消失。

 如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个

存储过程创建的所有对象，包括临时表在内。

 如果执行对远程 SQL Server 2008 实例进行更改的远程存储过程，则不能回滚这些更

改，而且远程存储过程不参与事务处理。

 存储过程中的参数的最大数目为 2100 。

 存储过程中的局部变量的最大数目仅受可用内存的限制。

 根据可用内存的不同，存储过程最大可达 128MB 。

2 ．存储过程的语法

使用 CREATE PROCEDURE 语句创建存储过程的语法如下。

CREATE PROCDURE procedure_name[;number]

[{@parameter data_type}

[VARYING][=default][OUTPUT]][, … n]

[WITH

{ RECOMPILE

ENCRYPTION

RECOMPILE , ENCRYPTION}]

[FOR REPLICATION]

AS sql_statement[ … n]

其主要参数含义含义如下：

 P rocedure_name 新存储过程的名称。过程名称在架构中必须唯一，可在 procedure_

name 前面使用一个数字符号 “ # ” 来创建局部临时过程，使用两个数字符号 “ # ” 来创建全局临

时过程。对于 CLR 存储过程，不能指定临时名称。

 ;number 是可选的整数，用来对同名的过程分组。使用一个 DROP PROCEDURE 语句

可将这些分组过程一起删除。如果名称中包含分隔标识符，则数字不应该包含在标识符中 ; 只应

在 procedure_name 前使用分隔符。

 @parameter 过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参

数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时

为每个声明的参数提供值，如果指定了 FOR REPLICATION ，则无法声明参数。

 D ata_type 参数的数据类型。所有数据类型均可以用作存储过程的参数。不过 curso r

数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor ，则还必须指定 VARYING 和 OU

TPUT 关键字。对于 CLR 存储过程，不能指定 char,varchar,text,next,image,cursor 和 table 作为参数

。

如果参数的数据类型为 CLR 用户定义类型，则必须对此类型有 EXECUTE 权限。

 Default 参数的默认值。如果定义了 dafault 值，则无须指定此参数的值即可执行过程

。

默认值必须是常量或 NULL 。如果过程使用带 like 关键字的参数，则可包含下列通配符： % 、 _ 、 []

、

[^] 。

 O utput 指示参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。使用 O

UTPUT 参数将值返回给过程的调用方。除非是 CLR 过程，否则 text,ntext 和 image 参数不能用作

OUTPUT 参数。 OUTPUT 关键字的输出参数可以为游标占位符， CLR 过程除外， <sql_statement>

要包含在过程中的一个或多个 T-SQL 语句中。

3 ．使用图形工具创建

除了直接编写 T-SQL 创建外， SQL Server 2008 还提供了一种简便的方法，使用 SQL Se

rver Management Studio 工具。操作步骤如下：

（ 1 ）打开 SQL Server Management Studio 窗口，连接到【 BookDateBase 】数据库。

（ 2 ）依次展开【服务器】｜【数据库】｜【 BookDateBase 】 | 【可编程性】节点。

（ 3 ）从列表中右击【存储过程】节点选择【新建存储过程】命令，然后将出现如图 8-

1 所示的显示 CREATE PROCEDURE 语句的模板，可以修改要创建的存储过程的名称，然后加

入存储过程所包含的 SQL 语句。

剩余31页未读，继续阅读

评论收藏

内容反馈

zhaoli1122

2011-11-29

很好的资源，内容比较详细，主要内容有：在SQL Server 2008 中存储过程和触发器是两个重要的数据库对象。使用存储过程，可以将Transact-SQL 语句和控制流语句预编译到集合并保存到服务器端，它使得管理数据库、显示关于数据库及其用户信息的工作更为容易。而触发器是一种特殊类型的存储过程，在用户使用一种或多种数据修改操作来修改指定表中的数据时被触发并自动执行，通常用于实现复杂的业务规则，更有效地实施数据完整性。本章学习目标
wu_min

2017-03-04

一般，是哪本书籍截取的内容... ?
huanying102218

2012-08-07

内容有点偏少~~