sql server存储过程 触发器

第9章存储过程

9・1存储过程概述

9.2创建管理简单存储过程

9.3创建管理带参的存储过程

9.4其他存储过程

1

数据摩复）

恢复模式也）:

备份类型隹）:

备份组件

◎数据库（2）

。文件和文件组⑥）

0在以下天数后量）：天

O在⑥：

目标

：D651F26

备份到:@磁盘（I）S1南印

：D651F26\Adn»inistrator

令连接雇性

就绪

2

setselect

同时对多个变量同时赋值不支持支持

表达式返回多个值时出错将返回的

最后一个值赋给变量

表达式未返回值变量被赋null值变量保持

原值

3

declare@addrvarchar(128)

set@addr=(selectaddrfromchinadbal)

/////////////////////

declare@addrvarchar(128)

select@addr=addrfromchinadbal

print@addr

///////////////////////

declare@addrvarchar(128)

set@addr='初始值'

set@addr=(selectaddrfromchinadbalwhereuserid=4)

print@addr

/////////////////////////

declare@addrvarchar(128)

set@addr='初始值'

select@addr=addrfromchinadbalwhereuserid=4

print@addr

////////////////

declare@addrvarchar(128)

set@addr='初始值'

select@addr=(selectaddrfromchinadbalwhereuserid=4)

print@addr

4

Go的使用技巧

execsp_addtypetaihang,'smllint’,

'notnull'

DECLARE@ffloat,@cnchar(8)

createtable

5

9.1存储过程概述

1.存储过程概念

存储过程是为了实现某个特定任务，由一组预先编译好的

SQL语句组成，将其放在服务器上，由用户通过指定存储过程

的名字来执行的一种数据库对象。

.存储过程类型

2系统存储过程存储以SP_为前缀，是由SQL

■系统存储过程VI

Server2005自己创建、管理和使用的一种

■扩展存储过程特殊的存储过程，不能对其进行修改或删除。

■用户自定义存储过程如Sp_helpdb、Sp_renamedb等。

由用户自行创建的存储过程，可以输入参数、

向客户端返回表格或结果、消息等，也可以

返回输出函数。

第9章存储过程

9.1存储过程概述

9.2创建管理简单存储过程

9.3创建管理带参的存储过程

9.4其他存储过程

7

9.2创建管理简单的存储过程

921无参存储过程的创建

9.2.2无参存储过程的执行

9.2.3查看存储过程

9.2.4修改存储过程

9.2.5编译存储过程

9.2.6删除存储过程

8

921无参存储过程的创建

使用SQL语句创建存储过程

1）语法格式如下:

CREATEPROC[EDURE]procedure_name

ASsql_statement[...n]

2）语法注释：

procedure_name:新建存储过程的名称，其名称必须符

合标识符命玄规则，且对于数据库及其所有者必须唯一。

sqlstatement:指存储过程中的任意数目和类型的

Transact-SQL语句。

9

921无参存储过程的创建区）

例1:在PUBS数据库中，创建一个名

pjsearchorddate的存储过程，该存储过程将查询出sales

表中订购日期ord_date在1994年以后的记录信息。

代码如下：

CREATEPROCpr_searchorddate

AS

SELECT*

FROMsales

WHEREord_date>=T1994-l-lf

GO

10

z

创建存储过程的注意事项

■只能在当前数据库中创建存储过程。

■数据库的所有者可以创建存储过程，也可以授权其他用

户创建存储过程。

■存储过程是数据库对象，其名称必须遵守标识符命名规

则。

■不能将CREATEPROCEDURE语句与其它SQL语句

组合到单个批处理中。

■创建存储过程时，应指定所有输入参数和向调用过程或

批处理返回的输出参数、执行数据库操作的编程语句和返

回至调用过程或批处理以表明成功或失败的状态值。

11

9.2创建管理简单的存储过程

9.2.1无参存储过程的创建

922无参存储过程的执行

9.2.3查看存储过程

9.2.4修改存储过程

9.2.5编译存储过程

9.2.6删除存储过程

12

9.2.2无参存储过程的执行

对存储在服务器上的存储过程，可以使用EXECUTE

命令或其名称执行它，其语法格式如下：

EXEC[UTE]procedure_name

对上例的存储过程pr_searchorddate的执行语句如下：

EXECUTEpr_searchorddate

13

9.2创建管理简单的存储过程

9.2.1无参存储过程的创建

9.2.2无参存储过程的执行

923查看存储过程

9.2.4修改存储过程

9.2.5编译存储过程

9.2.6删除存储过程

14

z

923查看存储过程、

L使用对象资源管理器查看如M超

2.使用系统存储过程查看存储过程信息

在SQLServer中，可以使用sp_helptext、

sp_depends、sp_help等系统存储过程来查看存储过程的

不同信息。

1）使用sp_helptext查看存储过程的文本信息.

其语法格式为：sp_helptext存储过程名

2）使用sp_depends查看存储过程的相关性.

其语法格式为：sp_depends存储过程名

3）使用sp_help查看存储过程的一般信息.

其语法格式为：sp_help存储过程名

15

9.2创建管理简单的存储过程

9.2.1无参存储过程的创建

9.2.2无参存储过程的执行

9.2.3查看存储过程

924修改存储过程

9.2.5编译存储过程

9.2.6删除存储过程

16

z

9.2.4修改存储过程

修改存储过程语法格式为:

ALTERPROC[DURE]procedure_name

[{©parameterdata_type}

[=default][OUTPUT][,...n]

[WITH{RECOMPILE|ENCRYPTION|

RECOMPILE,ENCRYPTION}]

AS

Sql_statement

17

z

9.2.4修改存储过程

例14:修改存储过程prjsearchempL查询出authors表

state字段为某个州的员工信息。

其程序清单如下:

ALTERPROCpr_searchempl

@statechar(2)

As

Select*

Fromauthors

Wherestate=@state

GO

18

9.2创建管理简单的存储过程

9.2.1无参存储过程的创建

9.2.2无参存储过程的执行

9.2.3查看存储过程

9.2.4修改存储过程

9.2.5编译存储过程

9.2.6删除存储过程

19

925编译存储过程

在我们使用了一次存储过程后，可能会因为某些熟

必须向表中新增加数据列或者为表新添加索引，从而改变了

数据库的逻辑结构。这时，、需要对存储过程进行重新编译，

SQLServer提侯三种重新编译存偏过超的方法：

1、在建立存储过程时设定重新编译

语法格式：CREATEPROCEDUREprocedure_name

WITHRECOMPILE-

ASsql_statement

2、在执行存储过程前设定重编译

语法格式：EXECUTEprocedure_name

WITHRECOMPILE-

3、通过使用系统存储过程设定重编译

语法格式为：EXECsp_recompileOBJECT

20

9.2创建管理简单的存储过程

9.2.1无参存储过程的创建

9.2.2无参存储过程的执行

9.2.3查看存储过程

9.2.4修改存储过程

9.2.5编译存储过程

926删除存储过程

21

z

926删除存储过程1

1.使用SSMS对象资源管理器删除存储过程

2.使用DROPPROCEDURE语句删除存储过程

DROPPROCEDURE语句可以一次从当前数据库中将

个或多个存储过程或过程组删除，其语法格式如下:

DROPPROCEDURE存储过程名称[,n]

例15:删除存储过程pjsearchempl

USEPUBS

GO

DROPPROCEDUREpr_searchempl

GO

22

926删除存储过程

例16:建立一个带参的存储过程于删除PUBS凌

sales表中stojid为某某值（如663805）的所有记录。

实现的T-SQL语句：

USEpubs

GO

CREATEPROCdeletesales

@stor_idchar(4)

AS

DELETEsales

WHEREstorjd=@storjd

GO

23

9.2.6删除存储过程

存储过程的执行如下:

Execdeletesales'6380’

24

第9章存储过程

9.1存储过程概述

9.2创建管理简单存储过程

9.3创建管理带参的存储过程

9.4其他存储过程

25

9.3创建管理带参的存储过程

931创建带参存储过程的语法

9.3.2创建/执行带输入参数的存储过程

9.3.3创建/执行带输出参数的存储过程

9.3.4存储过程的返回值

26

931创建带参存储过程的语法

1）语法格式如下：

CREATEPROC[DURE]procedure_name

[{©parameterdatatype}

[=default][OUTPUT][,...n]

[WITH{RECOMPILE|ENCRYPTION|

RECOMPILE,ENCRYPTION}]

AS

Sql_statement

27

931创建带参存储过程的语法

2）语法注释：

parameter:存储过程中的输入和输出参数。

datatype:参数的数据类型。

OUTPUT:表明参数是返回参数。该选项的值可以返回

给EXEC[UTE]。

28

9.3创建管理带参的存储过程

9.3.1创建带参存储过程的语法

932创建/执行带输入参数的存储过程

9.3.3创建/执行带输出参数的存储过程

9.3.4存储过程的返回值

29

灯”谴/热便即整数的存储

例2:在PUBS数据库中创建一个存储过程pr_search黜曲,

查询出authors表中state字段为某个州且姓中包含某字符

串的所有的员工信息。

CREATEPROCpr_searchempl

@statechar(2),@strvarchar(40)

As

Select*

Fromauthors

Wherestate=@stateandau_lnamelike'%'+@str+'%'

30

叫”谴/执行带输入参数的存储过

语法格式如下：

[[EXEC[UTE]]

{[@return_status=]{procedure_name}

[[@parameter=]{value|@variable[OUTPUT]|

[DEFAULT]][，…n]

[WITHRECOMPILE]

对上例的存储过程pr_searchempl的执行语句如下:

EXECUTEpr_searchempl'CA'Jhi'

31

灯”谴/热便即整数的存储

例3:查询出northwind数据库中城市值为某某值并_^^

佣时间在某某日期之后的所有员工的基本信息。

实现的T-SQL语句：

USEnorthwind

GO

CREATEPROCp_getEmployee

@citynvarchar(15),

@hiredatedatetime

AS

SELECT*FROMemployeesWHEREcity=@cityAND

hiredate>=@hiredate

GO

32

"2J烟热红壁邮力数的存储甯了

执行带输入参数的存储过程

按位置传递参数值

在执行存储过程的语句中，直接给出参数的值。当有

多个参数时，给出的参数的顺序与创建执行存储过程的

语句中的参数的顺序一致，即参数传递的顺序就是参数

定义的顺序。

通过参数名传递参数值

在执行存储过程的语句中，使用【参数名=参数值】的

形式给出参数值。其优点是参数可以以任意顺序给出。

33

"2J烟热红壁邮渗数的存储过怪

在输入参数中使用默认值,

在执行存储过程p_getEmployee时，如果没有指定

参数，则系统运行就会出错；此时如果希望在执行时不

给出参数也能正确运行，

则在创建存储过程时给输入参数指定默认值。

34

"2J烟热红壁邮渗数的存储主

按位置传递参数值

EXECp_getEmployeeilondon,/1994-1-15

通过参数名传递参数值

EXECp_getEmployee

@city='london',@hiredate="1994-1-1,

或

EXECp_getEmployee

@hiredate="1994-1-1',@city='london'

35

"2J闷热伊邮力数的存储过怪一

例4：对存储过程p_getEmployee进行修改，指定城市默认

值为London，一指定雇佣日期为1990年1月1日。

USEnorthwind

GO

ALTERPROCp_getEmployee

@citynvarchar(15)=,london,,

@hiredatedatetime=,1990-1-1J

AS

SELECT*FROMemployeesWHEREcity=@cityAND

hiredate>=@hiredate

GO

EXECp_getEmployee

36

9.3创建管理带参的存储过程

9.3.1创建带参存储过程的语法

9.3.2创建/执行带输入参数的存储过程

933创建/执行带输出参数的存储过程

9.3.4存储过程的返回值

37

23巨剑建/热什赞狗衅数的存储过X

例5:在PUBS数据库中创建一个存储过程pr_titleprice,

统计出titles表中pub_id字段为某编号的书籍总价格。

CREATEPROCprjitleprice

@pub_idchar(4),@spricemoneyoutput

As

Select@sprice=sum(price)

Fromtitles

Wherepub_id=@pub_id

存储过程的执行：

declare@ssmoney

execpr_titleprice'0877',@ssoutput

select@ssas总价格

3

933创建/执行带输出参数的存储过

例6：创建一个存储过程p_getCguntEmplo)fees,

开nothwind薮据库员工裹平雇佣日丽卷某某时间之后的

员工的个薮。

实现的T-SQL语句：

USEnorthwind

GO

CREATEPROCp_getCountEmployees

@hiredatedatetime=,1990-1-1\

@countintOUTPUT

AS

SELECT@count=count(*)FROMemployeesWHERE

hiredate>=@hiredate

GO

39

存储过程的调用

sp_helppr_check_manager

go

sp_helptextpr_check_manager

go

sp_dependspr_check_manager

llllllllllllllllllllllllllllllllllllllllll

sp_helppr_check_manager

EXECsp_helptextpr_check_manager

EXECsp_dependspr_check_manager

llllllllllllllllllllllllllllllllllllllllllll

40

在数据库工具中，架构也指描述架构或数据库中的对象的

目录信息。

usebb

ifnotexists

(

select,

fromsys.schemassjoinsys.tablestons.schemajd

=t.schemajd

where='dbo'and='t_WorkerMoney'

)

41

933创建/执行带输出参数的存储过

调用存储过程的T-SQL语句:

DECLARE@ecountint

EXECp_getCountEmployees'1993-1-1'Recount

OUTPUT

SELECT，员工个数为：'+str(@ecount)

GO

42

9.3创建管理带参的存储过程

9.3.1创建带参存储过程的语法

9.3.2创建/执行带输入参数的存储过程

9.3.3创建/执行带输出参数的存储过程

934存储过程的返回值

43

934存储过程的返回值

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

则返回0;否则返回到・99之间的随机数，也可以使用

RETURN语句来指定一个存储过程的返回值。

44

934存储过程的返回值

例7：在northwind数据库创建一个存储过程，返回产品表

中的所有产品的库存量。

createprocprjier

as

begin

declare@fanhuizhiint

select@fanhuizhi=sum(unitsinstock)

fromproducts

return@fanhuizhi

end

45

934存储过程的返回值

接受这个返回值必须要用变量来接收declare

@jieshouzhiint

exec@jieshouzhi=pr_lier

print@jieshouzhi

46

934存储过程的返回值

上例中返回的值也可以用output参数来返回，可以将上

例的存储过程改写为：

createprocpr_lier_2

@fanhuizhiintoutput

as

begin

select@fanhuizhi=sum(unitsinstock)

fromproducts

end

47

z

934存储过程的返回值

接收output的返回值也必须要用变量，如:

declare@jieshouzhiint

execpr_lier_2@jieshouzhioutput

print@jieshouzhi

注意：

1)return返回的是整数，output可以返回任何数据。

2)如果让return返回非整数值，在创建存储过程时不会

出错，但是运行存储过程时将会出错。

48

存储过程案例

例8:在PUBS数据库中创建一个用户表UserMembe7,并

在此表中录入相应记录，用户表的表结构如下所示：

列名|数据类型|长度|允许空I

用户名varchar50

✓

密码varchar50

「

性别char2

✓

电话varchar50

e_mailvarchar50

原甦住址varchar|二|50

49

存储过程案例

例9:在PUBS数据库中创建一个带参数的存储过程

SelectUser,查询出用户表UserMember中是否存在某

用户，如果不存在，则返回值为1,否则则查询该用户的

密码是否正确，如不正确，则返回值为2,否则返回值为

0。

50

存储过程案例

createprocselectuser

@usernamevarchar(20),@passvarchar(20)

as

if@usernamenotin(select用户名fromusermember)

return(1)

elseifexists(select*fromusermember

where用户名=@usernameand密码=@pass)

return(O)

elsereturn(2)

51

z

存储过程案例

执行存储过程语句如下：

Declare@fanint

Exec@fan=selectuser£aaa7aaa

If@fan=O

Print'用户名和密码都正确，

If@fan=1

Print，用户名不存在，

If@fan=2

Print'用户名存在，密码不正确，

52

课堂练习

在学生数据库中创建一个学生用户表StudentMem唯r,

并在此表中录入相应记录。

列名|数据类型1长度1允许空

学号char8

密码varchar20

性别char2y

varchar20✓

族系电话

►varchaijjj20

在学生数据库中创建一个带参数的存储过程SelectStudent,

查询出学生用户表StudentMember中是否存在某学生的信息，

如果不存在，则返回值为-1,否则则查询该学生的登录密码是

否正确，如不正确，则返回值为-2,否则返回值为0。

53

存储过程案例

例10:建立一个带参的存储过程，用于向PUBS数据库中

的publishers表中添加一条记录，在添加记录前先查看是

否存在此出版社编号（pub_id）,如不存在则插入该条记

录信息，存储过程返回值为0,如已存在此出版社编号，则

不进行插入记录操作，存储过程返回值为测试记录

为：0001\9清华大学出版社，「北京，，nul『中

国'。

54

存储过程案例

实现的T・SQL语句：

USEpubs

GO

CREATEPROCInsertPublishers

@Pub_ldchar(4),

@pub_namevarchar(40),

@cityvarchar(20),

@statechaK2),

©countryvarchar(30)

55

存储过程案例、一

AS

IFEXISTS(SELECT*FROMpublishersWHERE

pubjd=@pub_id)

Return-1

ELSE

BEGIN

INSERTPublishers(PubJd,pub_name,city,state,

country)一一

VALUES(@PubJd,@pub_name,@city,@state,@country)

IF@@ERROR=O—

RETURN0

ELSE

RETURN-9

END

GO

存储过程案例

存储过程的执行如下：

DECLARE@resultint

Exec@result=lnsertPublishers'9901','清华大学

出版社，，，北京，中国，

IF@result=0

PRINT'记录插入成功，

IF@result=-1

PRINT，出版社编号已存在，记录重复！，

IF@result=-9

PRINT，出版社编号违反检查约束，请重新输入！，

GO

57

存储过程案例

例11:在PUBS数据库中创建一个带参数的存储过程

InsertUserMember,接受用户的注册信息，首先查询出用

户表UserMember中是否存在该用户，如果存在，则返回值

为否则将该信息插入到该表中，如果插入记录成功则返

回值为0,否则返回值为・2。

58

存储过程案例

createprocInsertUserMember

@usernamevarchar(50),@pwdvarchar(50),

@sexchar(2尸男9,@phonevarchar(50)=null,

@emailvarchar(50)=null,@address

varchar(50)=null

as

ifexists(select*fromusermemberwhere用户名

=@username)

return-1

59

存储过程案例

else

begin

insertintousermember

values(@username,@pwd,@sex,@phone,

@email,@address)

if@@error=0

return0

else

return-2

end

go

60

存储过程案例

存储过程的执行语句如下：▼

declare@fanint

exec@fan=lnsertllserMember

4cc,/cc,,default/86822555,/a@126.com,,，长春

I

if@fan=-1

print'用户名已经存在，

if@fan=O

print'插入数据成功'

if@fan=-2

print'插入数据失败'

61

z

课堂练习

在学生数据库中创建一个带参数的存储过程Insertstudent,

接受学生的注册信息，首先查询出学生用户表

StudentMember中是否存在该学生，如果存在，则返回值为

-1,否则将该信息插入到该表中，如果插入记录成功则返回

值为0,否则返回值为・2。

62

存储过程案例

例12：建立一个带参的存储过程updatesales,用于修改

PUBS数据库sales表中ord_date的值为某某（4口'1994-9-

14，）并且titlejd为某某值（如BU1032,）的qty字段值，

修改其值为输入的数值（如25）o

63

存储过程案例

USEpubs

GO

CREATEPROCupdatesales

@ord_datedatetime,

@title_idvarchar(6),

@qtysmallint

AS

UPDATEsales

SETqty=@qty

WHEREord_date=®ord_dateANDtitle_id=@title_id

GO一一一一

存储过程的执行如下：

Execupdatesalesc1994-9-14\'BU1032\25

GO

64

存储过程案例

例13:在pubs数据库中创建一个带参的存储过程

updateusermember,接受用户的信息修改，首先查询出

用户表usermemeber中是否存在该用户名，并且密码是否

正确，如果不正确，则返回（・1）,否则可以修改用户的

信息（用户名不可以修改）。

65

Createprocupdateusermember

@usernamevarchar(50),@pwdvarchar(50),@sexchar(2),

©telephonevarchar(50),@addressvarchar(50)

as

Ifexists(select*fromusermemberwhere用户名

=@usernameand密码=@pwd)

Updateusermember

Set性别=@sex,电话=@telephone，^><^£iiE=@address

Where用户名=@username

Elsereturn(-1)

Go

执行存储过程

Execupdateusermemberiyp,,,456123,,,dc,,

121212,/beijing,

66

z

课堂练习

在学生数据库中创建一个带参数的存储过程

UpdateStudent,接受学生的信息修改，首先查询出学生

用户表StudentMember中是否存在该学号，并且密码是

否正确，如果不正确，则返回值为否则修改该学生的

其他信息（学号不可以修改）。

67

z

综合练习L

例17：建立一个带参的存储过程p_getTotal,用于查询

northwind数据库orderdetails表中某产品的总销量和总多肖售

金额，并将其值返回。

实现的T・SQL语句：

USEnorthwind

GO

CREATEPROCp_getTotal

@prod_idint,@total_quantityINTOUTPUT,@total_money

moneyOUTPUT

AS

SELECT@total_quantity=sum(quantity),

@total_money=sum(quantity*unitprice*(1-discount))

FROM[orderdetails]WHEREproductid=@prod_id

GO

z

综合练习\

存储过程的执行如下：

DECLARE@t_quantityint,@t_moneymoney

EXECp_getTotal10,@t_quantityoutput,@t_money

output

PRINT'该产品总销量为'+str(@t_quantity)

PRINT，该产品总金额为,+str(@t_money)

GO

69

第9章存储过程

9.1存储过程概述

9.2创建管理简单存储过程

9.3创建管理带参的存储过程

9・4其他存储过程

70

11.6案例中的存储过程

1.创建一个加密过程

在student数据库中，创建一个名称为st_jiami的加密存储过程，该过程用来查询

一门课程也没有选修的学生的学号与姓名。创建完成后，执行该存储过程。

USEstudent

GO

--如果存储过程stjiami存在，将其删除

IFEXISTS(SELECTnameFROMSYSOBJECTSWHEREname='stjiami'

ANDtype='P')

DROPPROCEDUREstjiami

GO

-建立一个加密的存储过程

71

11.6案例中的存储过程

CREATEPROCEDUREstjiami

“加密选项

WITHENCRYPTION

AS

SELECT学号，姓名FROM学生

WHERE学号NOTIN（SELECT学号FROM课程注册）

GO

“执行stjiami

EXECstjiami

GO

72

11.6案例中的存储过程

在student数据库中，创建一个带参数的存储过程st_chengjichaxun,该存储过程用于当

输入任意一个成绩时，将从三个表（学生表、云程注册表、课程表）中查询出大于

或等于该成绩的学生学号、姓名、课程名和课程成绩。创建完成后，执行该存储过

程，查询获得学分的学生（即成绩大于或等于60）。

USEstudent

GO

--如果存储过程st_chengjichaxun存在，将其删除

IFEXISTS（SELECTnameFROMSYSOBJECTSWHEREname='st_chengjichaxun'

ANDtype='P'）

DROPPROCEDUREst_chengjichaxun

GO

73

11.6案例中的存储过程

--创建一个带参数的存储过程st_chengjichaxun

CREATEPROCEDUREst_chengjichaxun@chengjitinyint

AS

SELECTA.学号,A.姓名,C.课程名称,B.成绩FROM学生ASA

JOIN课程注册ASBONA.学号二B.学号

JOIN课程ASCONB.课程号=C裸程号

WHEREB.成绩＞=@chengji

ORDERBYA.学号

GO

--执行st_chengjichaxun,显示获的学分学生的学号、姓名、课程名和课程成绩

EXECst_chengjichaxun60

GO

74

11.6案例中的存储过程

在student数据库中，创建一个存储过程st_dkcjfx,当任意输入一个存在的西

程名称时，该存储过程将统计出该门谏程的平均成绩、最高成绩和最低成

绩。(本例题在执行存储过程时，统计的是sqlserver2005课程的平均成

绩、最高成绩和最低成绩。)

USEstudent

GO

--如果存储过程st_dkcjfx存在，将其删除

IFEXISTS(SELECTnameFROMSYSOBJECTSWHERE

name=,st_dkcjfx'ANDtype-P')

DROPPROCEDUREst_dkcjfx

GO

75

11.6案例中的存储过程

--创建存储过程st_dkcjfx

--定义一个输入参数kechengming

--定义三个输出参数avgchengji,maxchengji和minchengji,用于接受平均成绩，最高

成绩和最低最绩

CREATEPROCEDUREst_dkcjfx

@kechengmingvarchar（20）,@avgchengjitinyintOUTPUT,

@maxchengjitinyintOUTPUT,@minchengjitinyintOUTPUT

AS

SELECT@avgchengji=AVG（成绩）,@maxchengji二MAX（成绩）,@minchengji二MIN（成

绩）

FROM课程注册WHERE课程号IN

（SELECT课程号FROM课程WHERE课程名^=@kechengming）

GO

76

11.6案例中的存储过程

--执行存储过程st_dkcjfx

USEstudent

GO

一声明四个变量，用于保存输入和输出参数

DECLARE@kechengming1varchar(20)

DECLARE@avgchengji1tinyint

DECLARE@maxchengji1tinyint

DECLARE@minchengji1tinyint

--为输入参数赋值

SELECT@kechengming1-SQLServer20051

--执行存储过程

EXECst_dkcjfx@kechengming1,@avgchengji1OUTPUT,

@maxchengji1OUTPUT,@minchengji1OUTPUT

77

11.6案例中的存储过程

--显示结果▼

SELECT@kechengming1AS课程名称,@avgchengji1

AS平均成绩，

@maxchengji1AS最高成绩,@minchengji1AS最低成绩

GO

78

7.3触发器概述_____________V

触发器是一种特殊类型的存储过程.它不同于前面介

绍过的存储过程。（区别）触发器主要是通过事件进行触发而

被执行，而存储过程可以通过存储过程名字而被直接调用。

当对某一表进行如UPDATE,INSERT,DELETE<*#4^

时,SQLServer就会自动执行触发器所定义的SQL语句。从

而确保对数据的处理必须符合这些SQL语句定义的规则。

触发器的主要作用就是其能够实现由主键和外键所不能

保证的参照完整性和数据的一致性。

除此之外，触发器还有其他很多功能：

79

(1)强化约束W

触发器能够实现比较复杂的约束。.

(2)跟踪变化

触发器可以侦测数据库内的操作，从而不允许数据库

未经许可的指定更新和变化。

(3)级联运行

触发器可以侦测数据库内的操作，并自动地级联影响整

个数据库的各项内容。例如，某个表上创建了触发器，

在另外一个表进行数据操作(如删除、更新、插入)时可

以导致该表上的触发器被触至O

(4)触发器可以调用存储过程

为了响应数据库更新，触发器可以调用一个或多

个存储过程。由此可见，触发器可以解决高级形式的业

务规则。

80

SQLServer2000支持两种类型的触发器：.

AFTERBXW1NSTEAD19FBO^-------------七

1.AFTER触发器在触发操作(INSERT,UPDATE

DELETE)后和处理完任何约束后激发。且只能在表上定义。

可以为同一操作定义多个触发器。可以定义哪一个触

发器被最先触发，哪一个被最后触发，通常使用系统过程

sp_settriggerorder来完成止匕任务。

sp_settriggerorder'ti_Storage_MoveDetair,'first','insert

I

2.INSTEADOF触发器代替触发动作进行激发，并

在处理约束之前激发。

既可以在表上定义INSTEADOF触发器，也可

以在视图上定义INSTEADOF触发器，但对同一操作只能定

义一个INSTEADOF触发器。

总结：通过或可以控制触发器何时被触发。

afterinsteadof81

只要求掌握after触发器。

回顾：

1.存储过程和触发器在执行上的区别。

2.触发器分为三类：insert触发器,update触

发器和delete触发器。

82

7.4触发器原理（inserted表和deleted表）V

触发器被触发后，可以产生inserted表或deleted表。

这两个表是逻辑表,不是存储在数据库中,而是存储在

内存中，并且这两个表是由系统管理的，是只读的，因

此用户不能向这两个表写入内容但可以引用表中的数

据。

这两个表是动态驻留在内存中，当触发器工作完成，这

两个表也被删除。

这两个表的结构总是与该触发器作用的表有相同的表

结构。

83

73・1inserted表

根据执行的操作类型（三类操作）的不同，会触发不同

的触发器T不同的触发器会创建不回的逻辑表_____

（inserted表和deleted表），可能只创建一个，也可能两

个都创建。

下表说明了在进行何种操作时,触发器创建哪些表。

触发器创建的表：

触发器类型—创建inserted表一创建deleted表一

INSERT是否

UPDATE是是

DELETE否是

应当注意的是：inserted表和deleted表只能被创建

它们的

触发器引用inserted表和deleted表的范围仅限于该

触发器。

inserted表响表中插入一条记录时,相应的insert

触发器创建一个inserted表,该表映射了与该触发器作用

的表的结构。例如当用户向学生表中插入记录时，触发

器使用学生表的歹I结构创建inserted表,插入到学生表

的那些新记录，相应的也出现在inserted表中（重要知识

点）。

85

7.4.2deleted表

当执行一条DELETE语句时,从表中删除的每一行都包含I

在deleted表中vH列校口删除学生表中的学号为附记

录，那么学生表中找不到该条记录，但是在deleted

表中可以找到。

update操作相当于执行了delete操作和insert操作。

被UPDATE语句触发的触发器创建两个表：inserted

表和deleted表。deleted表和inserted表分另4包含基

本表中数据的“前后”快照。

例如：假设用户执行下面的Transact-SQL语句：

UPDATEtitlesSETprice=30WHEREtitle=’数据结

构'

update操作相当于删除一条旧记录插入一条新记录。

语句执行时，触发器产生inserted表和deleted表。

Deleted表中存放的是数据结构那本书的旧记录，即价

格为20的旧记录；而inserted表中贝I是数据结庙那本

书的新记录，即价格为30的新记录。86

7.5触发器的创建

在SQLServer中可以使用EnterpriseManager

和Transac_SQL来创建触发器。创建触发器应该考虑以

下几个问题：

①CREATETRIGGER语句必须是批处理中的第一个语

句。

②触发器的名称必须遵循标识符的命名规则。

③只能在当前数据库中创建触发器。

④在触发器中，下面的T-SQL语句是不能使用的：

ALTERDATABASECREATEDATABASE

DISKINITDISKRESIZE

DROPDATABASELOADDATABASE

LOADLOGRESTOREDATABASE

87

使用Transac_SQL来创建触发器

（在客户端创建，要求用户拥有此权限）

触发器是一种特殊的存储过程，它可以在表或者视图上

创建，当对表或者视图进行三类操作时自动执行触发器。

创建触发器的语法如下：

CREATETRIGGERtrigger-name

ON{table|view}[WITHENCRYPTION]

FOR{[DELETE][,][INSERT][,][UPDATE]}

AS

sql_statement

88

7.5.2使用INSERT触发器

例1:往学生表插入一条记录，显示插入成功的信息。

createtrigger插入on学生

forinsert

as

print'恭喜你，插入记录成功！'

insertinto学生（学院，姓名，学号，班级）

values（'计算机'/刘萍','992001',2）

89

z

1L8触发器的创建执行综述

在进行触发器的基本操作之前，介绍两张特殊的临时表，

分别是inserted表和deleted表。这两张表都存在于高速

缓存中。用户可以使用这两张临时表来检测某些修改操作

所产生的效果。例如，可以使用SELECT语句来检查

INSERT和UPDATE语句执行的插入操作是否成功，触发

器是否被这些语句触发等。但是不允许用户直接修改

inserted表和deleted表中数据。

90

z

11.8触发器的创建执行

1.触发器的基本操作—创建

例：在student数据库中，为“班级”表建立一个名为

del_banji的DELETE触发器，其作用是当删