《数据库原理及应用2》课件第7章.ppt

第7章存储过程和触发器

7.1存储过程

7.2触发器

7.3本章小结

7.1存储过程

7.1.1存储过程概述

存储过程中独立存在于表之外的数据库对象，由被编译

在一起的一组Transact-SQL语句组成。它可以被客服调用，

也可以被另一个存储过程或触发器调用，它的参数可以被传

递，它的出错代码也可以被检验。存储过程可以使对数据库

的管理，以及显示关于数据库及其用户信息的工作容易得多。

可以在任何可使用SQL语句的场合来使用存储过程，它

具有以下优点：

(1)可以在单个存储过程中执行一系列SQL语句。

(2)可以从自己的存储过程内引用其他存储过程，从而

简化一系列复杂语句。

(3)存储过程在创建时即在服务器上进行编译，所以执

行起来比单个SQL语句快，且能减少网络通信的负担。

7.1.2创建存储过程

要使用存储过程，首先要创建一个存储过程。可以使用

Transact-SQL语言的createprocedure语句，也可以使用企业管

理器或者存储过程创建向导来完成。

1．使用createprocedure语句创建存储过程

createprocedure语句的语法格式为：

【例1】创建一个简单的存储过程procedure1，用于检索

所有学生的成绩记录。

通过下述sql语句执行该存储过程：

use学生成绩管理

/*判断procedure1存储过程是否存在，若存在，则执行它*/

ifexists(selectnamefromsysobjects

wherename=‘procedure1’andtype=‘p’)

execprocedure1/*执行存储过程procedure1*/

go

执行结果如图7-1所示。

图7-1执行结果

2．使用企业管理器创建存储过程

使用企业管理器创建存储过程的操作步骤如下：

图7-2创建存储过程

3．使用向导创建

SQLServer2000还提供了创建存储过程的向导。

【例2】使用向导创建一个存储过程procedure2，对应的

操作步骤如下：

图7-3“选择向导”对话框

图7-4选择数据库

图7-5选择存储过程

图7-6完成创建存储过程

图7-7编辑存储过程

图7-8“编辑存储过程SQL”对话框

7.1.3执行存储过程

执行存储过程使用execute语句，其完整语法格式如下：

[exec[ute]]

[@return_status=]

{procedure_name[;number]|@procedure_name_var}

[[@parameter=]{value|@variable[output]|[default]]

[,…n]

[withrecompile]

7.1.4存储过程的参数

在创建和使用存储过程时，其参数是非常重要的。下面

详细讨论存储过程的参数传递和返回。

1．使用参数

在调用存储过程时，有两种传递参数的方法。第一种是

在传递参数时，使传递的参数和定义时的参数顺序一致，对

于使用默认值的参数可以用default代替。

2．使用默认参数

创建存储过程时，可以为参数提供一个默认值，默认值

必须为常量或者null。

【例3】创建一个存储过程insert_学生表2_2，该存储过

程中包含两个参数，其默认值分别为2013008，外语和北

京。

图7-9执行结果

3．使用返回参数

在创建存储过程时，可以定义返回参数。在执行存储过

程时，可以将结果返回给返回参数。返回参数应用output进

行说明。

【例4】创建一个存储过程average，它返回两个参数

@st_name和@st_avg，分别代表了姓名和平均分。

图7-10执行结果

4．存储过程的返回值

存储过程在执行后都会返回一个整型值。如果执行成功，

则返回0；否则返回-1～-99之间的数值。也可以使用return语

句来指定一个返回值。

【例5】创建的存储过程test_ret根据输入的参数来判断

返回值。

7.1.5存储过程的查看、修改和删除

可以使用sp_helptext存储过程来查看存储过程的定义信

息。

【例6】要查看前面的test_ret存储过程的定义信息，可

以执行下面的SQL语句。

use学生成绩管理

go

execsp_helpte