sql server存儲過程 觸發(fā)器

第9章存儲過程

9?1存儲過程概述

9.2創(chuàng)建管理簡單存儲過程

9.3創(chuàng)建管理帶參的存儲過程

9.4其他存儲過程

1

數(shù)據(jù)摩復(fù)）

恢復(fù)模式也）:

備份類型隹）:

備份組件

◎數(shù)據(jù)庫（2）

。文件和文件組⑥）

0在以下天數(shù)后量）：天

O在⑥：

目標(biā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.存儲過程概念

存儲過程是為了實現(xiàn)某個特定任務(wù)，由一組預(yù)先編譯好的

SQL語句組成，將其放在服務(wù)器上，由用戶通過指定存儲過程

的名字來執(zhí)行的一種數(shù)據(jù)庫對象。

.存儲過程類型

2系統(tǒng)存儲過程存儲以SP_為前綴，是由SQL

■系統(tǒng)存儲過程VI

Server2005自己創(chuàng)建、管理和使用的一種

■擴展存儲過程特殊的存儲過程，不能對其進行修改或刪除。

■用戶自定義存儲過程如Sp_helpdb、Sp_renamedb等。

由用戶自行創(chuàng)建的存儲過程，可以輸入?yún)?shù)、

向客戶端返回表格或結(jié)果、消息等，也可以

返回輸出函數(shù)。

第9章存儲過程

9.1存儲過程概述

9.2創(chuàng)建管理簡單存儲過程

9.3創(chuàng)建管理帶參的存儲過程

9.4其他存儲過程

7

9.2創(chuàng)建管理簡單的存儲過程

921無參存儲過程的創(chuàng)建

9.2.2無參存儲過程的執(zhí)行

9.2.3查看存儲過程

9.2.4修改存儲過程

9.2.5編譯存儲過程

9.2.6刪除存儲過程

8

921無參存儲過程的創(chuàng)建

使用SQL語句創(chuàng)建存儲過程

1）語法格式如下:

CREATEPROC[EDURE]procedure_name

ASsql_statement[...n]

2）語法注釋：

procedure_name:新建存儲過程的名稱，其名稱必須符

合標(biāo)識符命玄規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。

sqlstatement:指存儲過程中的任意數(shù)目和類型的

Transact-SQL語句。

9

921無參存儲過程的創(chuàng)建區(qū)）

例1:在PUBS數(shù)據(jù)庫中，創(chuàng)建一個名

pjsearchorddate的存儲過程，該存儲過程將查詢出sales

表中訂購日期ord_date在1994年以后的記錄信息。

代碼如下：

CREATEPROCpr_searchorddate

AS

SELECT*

FROMsales

WHEREord_date>=T1994-l-lf

GO

10

z

創(chuàng)建存儲過程的注意事項

■只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。

■數(shù)據(jù)庫的所有者可以創(chuàng)建存儲過程，也可以授權(quán)其他用

戶創(chuàng)建存儲過程。

■存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標(biāo)識符命名規(guī)

則。

■不能將CREATEPROCEDURE語句與其它SQL語句

組合到單個批處理中。

■創(chuàng)建存儲過程時，應(yīng)指定所有輸入?yún)?shù)和向調(diào)用過程或

批處理返回的輸出參數(shù)、執(zhí)行數(shù)據(jù)庫操作的編程語句和返

回至調(diào)用過程或批處理以表明成功或失敗的狀態(tài)值。

11

9.2創(chuàng)建管理簡單的存儲過程

9.2.1無參存儲過程的創(chuàng)建

922無參存儲過程的執(zhí)行

9.2.3查看存儲過程

9.2.4修改存儲過程

9.2.5編譯存儲過程

9.2.6刪除存儲過程

12

9.2.2無參存儲過程的執(zhí)行

對存儲在服務(wù)器上的存儲過程，可以使用EXECUTE

命令或其名稱執(zhí)行它，其語法格式如下：

EXEC[UTE]procedure_name

對上例的存儲過程pr_searchorddate的執(zhí)行語句如下：

EXECUTEpr_searchorddate

13

9.2創(chuàng)建管理簡單的存儲過程

9.2.1無參存儲過程的創(chuàng)建

9.2.2無參存儲過程的執(zhí)行

923查看存儲過程

9.2.4修改存儲過程

9.2.5編譯存儲過程

9.2.6刪除存儲過程

14

z

923查看存儲過程、

L使用對象資源管理器查看如M超

2.使用系統(tǒng)存儲過程查看存儲過程信息

在SQLServer中，可以使用sp_helptext、

sp_depends、sp_help等系統(tǒng)存儲過程來查看存儲過程的

不同信息。

1）使用sp_helptext查看存儲過程的文本信息.

其語法格式為：sp_helptext存儲過程名

2）使用sp_depends查看存儲過程的相關(guān)性.

其語法格式為：sp_depends存儲過程名

3）使用sp_help查看存儲過程的一般信息.

其語法格式為：sp_help存儲過程名

15

9.2創(chuàng)建管理簡單的存儲過程

9.2.1無參存儲過程的創(chuàng)建

9.2.2無參存儲過程的執(zhí)行

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創(chuàng)建管理簡單的存儲過程

9.2.1無參存儲過程的創(chuàng)建

9.2.2無參存儲過程的執(zhí)行

9.2.3查看存儲過程

9.2.4修改存儲過程

9.2.5編譯存儲過程

9.2.6刪除存儲過程

19

925編譯存儲過程

在我們使用了一次存儲過程后，可能會因為某些熟

必須向表中新增加數(shù)據(jù)列或者為表新添加索引，從而改變了

數(shù)據(jù)庫的邏輯結(jié)構(gòu)。這時，、需要對存儲過程進行重新編譯，

SQLServer提侯三種重新編譯存偏過超的方法：

1、在建立存儲過程時設(shè)定重新編譯

語法格式：CREATEPROCEDUREprocedure_name

WITHRECOMPILE-

ASsql_statement

2、在執(zhí)行存儲過程前設(shè)定重編譯

語法格式：EXECUTEprocedure_name

WITHRECOMPILE-

3、通過使用系統(tǒng)存儲過程設(shè)定重編譯

語法格式為：EXECsp_recompileOBJECT

20

9.2創(chuàng)建管理簡單的存儲過程

9.2.1無參存儲過程的創(chuàng)建

9.2.2無參存儲過程的執(zhí)行

9.2.3查看存儲過程

9.2.4修改存儲過程

9.2.5編譯存儲過程

926刪除存儲過程

21

z

926刪除存儲過程1

1.使用SSMS對象資源管理器刪除存儲過程

2.使用DROPPROCEDURE語句刪除存儲過程

DROPPROCEDURE語句可以一次從當(dāng)前數(shù)據(jù)庫中將

個或多個存儲過程或過程組刪除，其語法格式如下:

DROPPROCEDURE存儲過程名稱[,n]

例15:刪除存儲過程pjsearchempl

USEPUBS

GO

DROPPROCEDUREpr_searchempl

GO

22

926刪除存儲過程

例16:建立一個帶參的存儲過程于刪除PUBS凌

sales表中stojid為某某值（如663805）的所有記錄。

實現(xiàn)的T-SQL語句：

USEpubs

GO

CREATEPROCdeletesales

@stor_idchar(4)

AS

DELETEsales

WHEREstorjd=@storjd

GO

23

9.2.6刪除存儲過程

存儲過程的執(zhí)行如下:

Execdeletesales'6380’

24

第9章存儲過程

9.1存儲過程概述

9.2創(chuàng)建管理簡單存儲過程

9.3創(chuàng)建管理帶參的存儲過程

9.4其他存儲過程

25

9.3創(chuàng)建管理帶參的存儲過程

931創(chuàng)建帶參存儲過程的語法

9.3.2創(chuàng)建/執(zhí)行帶輸入?yún)?shù)的存儲過程

9.3.3創(chuàng)建/執(zhí)行帶輸出參數(shù)的存儲過程

9.3.4存儲過程的返回值

26

931創(chuàng)建帶參存儲過程的語法

1）語法格式如下：

CREATEPROC[DURE]procedure_name

[{?parameterdatatype}

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

[WITH{RECOMPILE|ENCRYPTION|

RECOMPILE,ENCRYPTION}]

AS

Sql_statement

27

931創(chuàng)建帶參存儲過程的語法

2）語法注釋：

parameter:存儲過程中的輸入和輸出參數(shù)。

datatype:參數(shù)的數(shù)據(jù)類型。

OUTPUT:表明參數(shù)是返回參數(shù)。該選項的值可以返回

給EXEC[UTE]。

28

9.3創(chuàng)建管理帶參的存儲過程

9.3.1創(chuàng)建帶參存儲過程的語法

932創(chuàng)建/執(zhí)行帶輸入?yún)?shù)的存儲過程

9.3.3創(chuàng)建/執(zhí)行帶輸出參數(shù)的存儲過程

9.3.4存儲過程的返回值

29

燈”譴/熱便即整數(shù)的存儲

例2:在PUBS數(shù)據(jù)庫中創(chuàng)建一個存儲過程pr_search黜曲,

查詢出authors表中state字段為某個州且姓中包含某字符

串的所有的員工信息。

CREATEPROCpr_searchempl

@statechar(2),@strvarchar(40)

As

Select*

Fromauthors

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

30

叫”譴/執(zhí)行帶輸入?yún)?shù)的存儲過

語法格式如下：

[[EXEC[UTE]]

{[@return_status=]{procedure_name}

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

[DEFAULT]][，…n]

[WITHRECOMPILE]

對上例的存儲過程pr_searchempl的執(zhí)行語句如下:

EXECUTEpr_searchempl'CA'Jhi'

31

燈”譴/熱便即整數(shù)的存儲

例3:查詢出northwind數(shù)據(jù)庫中城市值為某某值并_^^

傭時間在某某日期之后的所有員工的基本信息。

實現(xiàn)的T-SQL語句：

USEnorthwind

GO

CREATEPROCp_getEmployee

@citynvarchar(15),

@hiredatedatetime

AS

SELECT*FROMemployeesWHEREcity=@cityAND

hiredate>=@hiredate

GO

32

"2J煙熱紅壁郵力數(shù)的存儲甯了

執(zhí)行帶輸入?yún)?shù)的存儲過程

按位置傳遞參數(shù)值

在執(zhí)行存儲過程的語句中，直接給出參數(shù)的值。當(dāng)有

多個參數(shù)時，給出的參數(shù)的順序與創(chuàng)建執(zhí)行存儲過程的

語句中的參數(shù)的順序一致，即參數(shù)傳遞的順序就是參數(shù)

定義的順序。

通過參數(shù)名傳遞參數(shù)值

在執(zhí)行存儲過程的語句中，使用【參數(shù)名=參數(shù)值】的

形式給出參數(shù)值。其優(yōu)點是參數(shù)可以以任意順序給出。

33

"2J煙熱紅壁郵滲數(shù)的存儲過怪

在輸入?yún)?shù)中使用默認值,

在執(zhí)行存儲過程p_getEmployee時，如果沒有指定

參數(shù)，則系統(tǒng)運行就會出錯；此時如果希望在執(zhí)行時不

給出參數(shù)也能正確運行，

則在創(chuàng)建存儲過程時給輸入?yún)?shù)指定默認值。

34

"2J煙熱紅壁郵滲數(shù)的存儲主

按位置傳遞參數(shù)值

EXECp_getEmployeeilondon,/1994-1-15

通過參數(shù)名傳遞參數(shù)值

EXECp_getEmployee

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

或

EXECp_getEmployee

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

35

"2J悶熱伊郵力數(shù)的存儲過怪一

例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創(chuàng)建管理帶參的存儲過程

9.3.1創(chuàng)建帶參存儲過程的語法

9.3.2創(chuàng)建/執(zhí)行帶輸入?yún)?shù)的存儲過程

933創(chuàng)建/執(zhí)行帶輸出參數(shù)的存儲過程

9.3.4存儲過程的返回值

37

23巨劍建/熱什贊狗釁數(shù)的存儲過X

例5:在PUBS數(shù)據(jù)庫中創(chuàng)建一個存儲過程pr_titleprice,

統(tǒng)計出titles表中pub_id字段為某編號的書籍總價格。

CREATEPROCprjitleprice

@pub_idchar(4),@spricemoneyoutput

As

Select@sprice=sum(price)

Fromtitles

Wherepub_id=@pub_id

存儲過程的執(zhí)行：

declare@ssmoney

execpr_titleprice'0877',@ssoutput

select@ssas總價格

3

933創(chuàng)建/執(zhí)行帶輸出參數(shù)的存儲過

例6：創(chuàng)建一個存儲過程p_getCguntEmplo)fees,

開nothwind藪據(jù)庫員工裹平雇傭日麗卷某某時間之后的

員工的個藪。

實現(xiàn)的T-SQL語句：

USEnorthwind

GO

CREATEPROCp_getCountEmployees

@hiredatedatetime=,1990-1-1\

@countintOUTPUT

AS

SELECT@count=count(*)FROMemployeesWHERE

hiredate>=@hiredate

GO

39

存儲過程的調(diào)用

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

在數(shù)據(jù)庫工具中，架構(gòu)也指描述架構(gòu)或數(shù)據(jù)庫中的對象的

目錄信息。

usebb

ifnotexists

(

select,

fromsys.schemassjoinsys.tablestons.schemajd

=t.schemajd

where='dbo'and='t_WorkerMoney'

)

41

933創(chuàng)建/執(zhí)行帶輸出參數(shù)的存儲過

調(diào)用存儲過程的T-SQL語句:

DECLARE@ecountint

EXECp_getCountEmployees'1993-1-1'Recount

OUTPUT

SELECT，員工個數(shù)為：'+str(@ecount)

GO

42

9.3創(chuàng)建管理帶參的存儲過程

9.3.1創(chuàng)建帶參存儲過程的語法

9.3.2創(chuàng)建/執(zhí)行帶輸入?yún)?shù)的存儲過程

9.3.3創(chuàng)建/執(zhí)行帶輸出參數(shù)的存儲過程

934存儲過程的返回值

43

934存儲過程的返回值

存儲過程在執(zhí)行后都會返回一個整型值。如果執(zhí)行成功,

則返回0;否則返回到?99之間的隨機數(shù)，也可以使用

RETURN語句來指定一個存儲過程的返回值。

44

934存儲過程的返回值

例7：在northwind數(shù)據(jù)庫創(chuàng)建一個存儲過程，返回產(chǎn)品表

中的所有產(chǎn)品的庫存量。

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參數(shù)來返回，可以將上

例的存儲過程改寫為：

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返回的是整數(shù)，output可以返回任何數(shù)據(jù)。

2)如果讓return返回非整數(shù)值，在創(chuàng)建存儲過程時不會

出錯，但是運行存儲過程時將會出錯。

48

存儲過程案例

例8:在PUBS數(shù)據(jù)庫中創(chuàng)建一個用戶表UserMembe7,并

在此表中錄入相應(yīng)記錄，用戶表的表結(jié)構(gòu)如下所示：

列名|數(shù)據(jù)類型|長度|允許空I

用戶名varchar50

?

密碼varchar50

「

性別char2

?

電話varchar50

e_mailvarchar50

原甦住址varchar|二|50

49

存儲過程案例

例9:在PUBS數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程

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

存儲過程案例

執(zhí)行存儲過程語句如下：

Declare@fanint

Exec@fan=selectuser￡aaa7aaa

If@fan=O

Print'用戶名和密碼都正確，

If@fan=1

Print，用戶名不存在，

If@fan=2

Print'用戶名存在，密碼不正確，

52

課堂練習(xí)

在學(xué)生數(shù)據(jù)庫中創(chuàng)建一個學(xué)生用戶表StudentMem唯r,

并在此表中錄入相應(yīng)記錄。

列名|數(shù)據(jù)類型1長度1允許空

學(xué)號char8

密碼varchar20

性別char2y

varchar20?

族系電話

?varchaijjj20

在學(xué)生數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程SelectStudent,

查詢出學(xué)生用戶表StudentMember中是否存在某學(xué)生的信息，

如果不存在，則返回值為-1,否則則查詢該學(xué)生的登錄密碼是

否正確，如不正確，則返回值為-2,否則返回值為0。

53

存儲過程案例

例10:建立一個帶參的存儲過程，用于向PUBS數(shù)據(jù)庫中

的publishers表中添加一條記錄，在添加記錄前先查看是

否存在此出版社編號（pub_id）,如不存在則插入該條記

錄信息，存儲過程返回值為0,如已存在此出版社編號，則

不進行插入記錄操作，存儲過程返回值為測試記錄

為：0001\9清華大學(xué)出版社，「北京，，nul『中

國'。

54

存儲過程案例

實現(xiàn)的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

存儲過程案例

存儲過程的執(zhí)行如下：

DECLARE@resultint

Exec@result=lnsertPublishers'9901','清華大學(xué)

出版社，，，北京，中國，

IF@result=0

PRINT'記錄插入成功，

IF@result=-1

PRINT，出版社編號已存在，記錄重復(fù)！，

IF@result=-9

PRINT，出版社編號違反檢查約束，請重新輸入！，

GO

57

存儲過程案例

例11:在PUBS數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程

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

存儲過程案例

存儲過程的執(zhí)行語句如下：▼

declare@fanint

exec@fan=lnsertllserMember

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

I

if@fan=-1

print'用戶名已經(jīng)存在，

if@fan=O

print'插入數(shù)據(jù)成功'

if@fan=-2

print'插入數(shù)據(jù)失敗'

61

z

課堂練習(xí)

在學(xué)生數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程Insertstudent,

接受學(xué)生的注冊信息，首先查詢出學(xué)生用戶表

StudentMember中是否存在該學(xué)生，如果存在，則返回值為

-1,否則將該信息插入到該表中，如果插入記錄成功則返回

值為0,否則返回值為?2。

62

存儲過程案例

例12：建立一個帶參的存儲過程updatesales,用于修改

PUBS數(shù)據(jù)庫sales表中ord_date的值為某某（4口'1994-9-

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

修改其值為輸入的數(shù)值（如25）o

63

存儲過程案例

USEpubs

GO

CREATEPROCupdatesales

@ord_datedatetime,

@title_idvarchar(6),

@qtysmallint

AS

UPDATEsales

SETqty=@qty

WHEREord_date=?ord_dateANDtitle_id=@title_id

GO一一一一

存儲過程的執(zhí)行如下：

Execupdatesalesc1994-9-14\'BU1032\25

GO

64

存儲過程案例

例13:在pubs數(shù)據(jù)庫中創(chuàng)建一個帶參的存儲過程

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

執(zhí)行存儲過程

Execupdateusermemberiyp,,,456123,,,dc,,

121212,/beijing,

66

z

課堂練習(xí)

在學(xué)生數(shù)據(jù)庫中創(chuàng)建一個帶參數(shù)的存儲過程

UpdateStudent,接受學(xué)生的信息修改，首先查詢出學(xué)生

用戶表StudentMember中是否存在該學(xué)號，并且密碼是

否正確，如果不正確，則返回值為否則修改該學(xué)生的

其他信息（學(xué)號不可以修改）。

67

z

綜合練習(xí)L

例17：建立一個帶參的存儲過程p_getTotal,用于查詢

northwind數(shù)據(jù)庫orderdetails表中某產(chǎn)品的總銷量和總多肖售

金額，并將其值返回。

實現(xiàn)的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

綜合練習(xí)\

存儲過程的執(zhí)行如下：

DECLARE@t_quantityint,@t_moneymoney

EXECp_getTotal10,@t_quantityoutput,@t_money

output

PRINT'該產(chǎn)品總銷量為'+str(@t_quantity)

PRINT，該產(chǎn)品總金額為,+str(@t_money)

GO

69

第9章存儲過程

9.1存儲過程概述

9.2創(chuàng)建管理簡單存儲過程

9.3創(chuàng)建管理帶參的存儲過程

9?4其他存儲過程

70

11.6案例中的存儲過程

1.創(chuàng)建一個加密過程

在student數(shù)據(jù)庫中，創(chuàng)建一個名稱為st_jiami的加密存儲過程，該過程用來查詢

一門課程也沒有選修的學(xué)生的學(xué)號與姓名。創(chuàng)建完成后，執(zhí)行該存儲過程。

USEstudent

GO

--如果存儲過程stjiami存在，將其刪除

IFEXISTS(SELECTnameFROMSYSOBJECTSWHEREname='stjiami'

ANDtype='P')

DROPPROCEDUREstjiami

GO

-建立一個加密的存儲過程

71

11.6案例中的存儲過程

CREATEPROCEDUREstjiami

“加密選項

WITHENCRYPTION

AS

SELECT學(xué)號，姓名FROM學(xué)生

WHERE學(xué)號NOTIN（SELECT學(xué)號FROM課程注冊）

GO

“執(zhí)行stjiami

EXECstjiami

GO

72

11.6案例中的存儲過程

在student數(shù)據(jù)庫中，創(chuàng)建一個帶參數(shù)的存儲過程st_chengjichaxun,該存儲過程用于當(dāng)

輸入任意一個成績時，將從三個表（學(xué)生表、云程注冊表、課程表）中查詢出大于

或等于該成績的學(xué)生學(xué)號、姓名、課程名和課程成績。創(chuàng)建完成后，執(zhí)行該存儲過

程，查詢獲得學(xué)分的學(xué)生（即成績大于或等于60）。

USEstudent

GO

--如果存儲過程st_chengjichaxun存在，將其刪除

IFEXISTS（SELECTnameFROMSYSOBJECTSWHEREname='st_chengjichaxun'

ANDtype='P'）

DROPPROCEDUREst_chengjichaxun

GO

73

11.6案例中的存儲過程

--創(chuàng)建一個帶參數(shù)的存儲過程st_chengjichaxun

CREATEPROCEDUREst_chengjichaxun@chengjitinyint

AS

SELECTA.學(xué)號,A.姓名,C.課程名稱,B.成績FROM學(xué)生ASA

JOIN課程注冊ASBONA.學(xué)號二B.學(xué)號

JOIN課程ASCONB.課程號=C裸程號

WHEREB.成績＞=@chengji

ORDERBYA.學(xué)號

GO

--執(zhí)行st_chengjichaxun,顯示獲的學(xué)分學(xué)生的學(xué)號、姓名、課程名和課程成績

EXECst_chengjichaxun60

GO

74

11.6案例中的存儲過程

在student數(shù)據(jù)庫中，創(chuàng)建一個存儲過程st_dkcjfx,當(dāng)任意輸入一個存在的西

程名稱時，該存儲過程將統(tǒng)計出該門諫程的平均成績、最高成績和最低成

績。(本例題在執(zhí)行存儲過程時，統(tǒng)計的是sqlserver2005課程的平均成

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

USEstudent

GO

--如果存儲過程st_dkcjfx存在，將其刪除

IFEXISTS(SELECTnameFROMSYSOBJECTSWHERE

name=,st_dkcjfx'ANDtype-P')

DROPPROCEDUREst_dkcjfx

GO

75

11.6案例中的存儲過程

--創(chuàng)建存儲過程st_dkcjfx

--定義一個輸入?yún)?shù)kechengming

--定義三個輸出參數(shù)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案例中的存儲過程

--執(zhí)行存儲過程st_dkcjfx

USEstudent

GO

一聲明四個變量，用于保存輸入和輸出參數(shù)

DECLARE@kechengming1varchar(20)

DECLARE@avgchengji1tinyint

DECLARE@maxchengji1tinyint

DECLARE@minchengji1tinyint

--為輸入?yún)?shù)賦值

SELECT@kechengming1-SQLServer20051

--執(zhí)行存儲過程

EXECst_dkcjfx@kechengming1,@avgchengji1OUTPUT,

@maxchengji1OUTPUT,@minchengji1OUTPUT

77

11.6案例中的存儲過程

--顯示結(jié)果▼

SELECT@kechengming1AS課程名稱,@avgchengji1

AS平均成績，

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

GO

78

7.3觸發(fā)器概述_____________V

觸發(fā)器是一種特殊類型的存儲過程.它不同于前面介

紹過的存儲過程。（區(qū)別）觸發(fā)器主要是通過事件進行觸發(fā)而

被執(zhí)行，而存儲過程可以通過存儲過程名字而被直接調(diào)用。

當(dāng)對某一表進行如UPDATE,INSERT,DELETE<*#4^

時,SQLServer就會自動執(zhí)行觸發(fā)器所定義的SQL語句。從

而確保對數(shù)據(jù)的處理必須符合這些SQL語句定義的規(guī)則。

觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能

保證的參照完整性和數(shù)據(jù)的一致性。

除此之外，觸發(fā)器還有其他很多功能：

79

(1)強化約束W

觸發(fā)器能夠?qū)崿F(xiàn)比較復(fù)雜的約束。.

(2)跟蹤變化

觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許數(shù)據(jù)庫

未經(jīng)許可的指定更新和變化。

(3)級聯(lián)運行

觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動地級聯(lián)影響整

個數(shù)據(jù)庫的各項內(nèi)容。例如，某個表上創(chuàng)建了觸發(fā)器，

在另外一個表進行數(shù)據(jù)操作(如刪除、更新、插入)時可

以導(dǎo)致該表上的觸發(fā)器被觸至O

(4)觸發(fā)器可以調(diào)用存儲過程

為了響應(yīng)數(shù)據(jù)庫更新，觸發(fā)器可以調(diào)用一個或多

個存儲過程。由此可見，觸發(fā)器可以解決高級形式的業(yè)

務(wù)規(guī)則。

80

SQLServer2000支持兩種類型的觸發(fā)器：.

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

1.AFTER觸發(fā)器在觸發(fā)操作(INSERT,UPDATE

DELETE)后和處理完任何約束后激發(fā)。且只能在表上定義。

可以為同一操作定義多個觸發(fā)器。可以定義哪一個觸

發(fā)器被最先觸發(fā)，哪一個被最后觸發(fā)，通常使用系統(tǒng)過程

sp_settriggerorder來完成止匕任務(wù)。

sp_settriggerorder'ti_Storage_MoveDetair,'first','insert

I

2.INSTEADOF觸發(fā)器代替觸發(fā)動作進行激發(fā)，并

在處理約束之前激發(fā)。

既可以在表上定義INSTEADOF觸發(fā)器，也可

以在視圖上定義INSTEADOF觸發(fā)器，但對同一操作只能定

義一個INSTEADOF觸發(fā)器。

總結(jié)：通過或可以控制觸發(fā)器何時被觸發(fā)。

afterinsteadof81

只要求掌握after觸發(fā)器。

回顧：

1.存儲過程和觸發(fā)器在執(zhí)行上的區(qū)別。

2.觸發(fā)器分為三類：insert觸發(fā)器,update觸

發(fā)器和delete觸發(fā)器。

82

7.4觸發(fā)器原理（inserted表和deleted表）V

觸發(fā)器被觸發(fā)后，可以產(chǎn)生inserted表或deleted表。

這兩個表是邏輯表,不是存儲在數(shù)據(jù)庫中,而是存儲在

內(nèi)存中，并且這兩個表是由系統(tǒng)管理的，是只讀的，因

此用戶不能向這兩個表寫入內(nèi)容但可以引用表中的數(shù)

據(jù)。

這兩個表是動態(tài)駐留在內(nèi)存中，當(dāng)觸發(fā)器工作完成，這

兩個表也被刪除。

這兩個表的結(jié)構(gòu)總是與該觸發(fā)器作用的表有相同的表

結(jié)構(gòu)。

83

73?1inserted表

根據(jù)執(zhí)行的操作類型（三類操作）的不同，會觸發(fā)不同

的觸發(fā)器T不同的觸發(fā)器會創(chuàng)建不回的邏輯表_____

（inserted表和deleted表），可能只創(chuàng)建一個，也可能兩

個都創(chuàng)建。

下表說明了在進行何種操作時,觸發(fā)器創(chuàng)建哪些表。

觸發(fā)器創(chuàng)建的表：

觸發(fā)器類型—創(chuàng)建inserted表一創(chuàng)建deleted表一

INSERT是否

UPDATE是是

DELETE否是

應(yīng)當(dāng)注意的是：inserted表和deleted表只能被創(chuàng)建

它們的

觸發(fā)器引用inserted表和deleted表的范圍僅限于該

觸發(fā)器。

inserted表響表中插入一條記錄時,相應(yīng)的insert

觸發(fā)器創(chuàng)建一個inserted表,該表映射了與該觸發(fā)器作用

的表的結(jié)構(gòu)。例如當(dāng)用戶向?qū)W生表中插入記錄時，觸發(fā)

器使用學(xué)生表的歹I結(jié)構(gòu)創(chuàng)建inserted表,插入到學(xué)生表

的那些新記錄，相應(yīng)的也出現(xiàn)在inserted表中（重要知識

點）。

85

7.4.2deleted表

當(dāng)執(zhí)行一條DELETE語句時,從表中刪除的每一行都包含I

在deleted表中vH列校口刪除學(xué)生表中的學(xué)號為附記

錄，那么學(xué)生表中找不到該條記錄，但是在deleted

表中可以找到。

update操作相當(dāng)于執(zhí)行了delete操作和insert操作。

被UPDATE語句觸發(fā)的觸發(fā)器創(chuàng)建兩個表：inserted

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

本表中數(shù)據(jù)的“前后”快照。

例如：假設(shè)用戶執(zhí)行下面的Transact-SQL語句：

UPDATEtitlesSETprice=30WHEREtitle=’數(shù)據(jù)結(jié)

構(gòu)'

update操作相當(dāng)于刪除一條舊記錄插入一條新記錄。

語句執(zhí)行時，觸發(fā)器產(chǎn)生inserted表和deleted表。

Deleted表中存放的是數(shù)據(jù)結(jié)構(gòu)那本書的舊記錄，即價

格為20的舊記錄；而inserted表中貝I是數(shù)據(jù)結(jié)廟那本

書的新記錄，即價格為30的新記錄。86

7.5觸發(fā)器的創(chuàng)建

在SQLServer中可以使用EnterpriseManager

和Transac_SQL來創(chuàng)建觸發(fā)器。創(chuàng)建觸發(fā)器應(yīng)該考慮以

下幾個問題：

①CREATETRIGGER語句必須是批處理中的第一個語

句。

②觸發(fā)器的名稱必須遵循標(biāo)識符的命名規(guī)則。

③只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。

④在觸發(fā)器中，下面的T-SQL語句是不能使用的：

ALTERDATABASECREATEDATABASE

DISKINITDISKRESIZE

DROPDATABASELOADDATABASE

LOADLOGRESTOREDATABASE

87

使用Transac_SQL來創(chuàng)建觸發(fā)器

（在客戶端創(chuàng)建，要求用戶擁有此權(quán)限）

觸發(fā)器是一種特殊的存儲過程，它可以在表或者視圖上

創(chuàng)建，當(dāng)對表或者視圖進行三類操作時自動執(zhí)行觸發(fā)器。

創(chuàng)建觸發(fā)器的語法如下：

CREATETRIGGERtrigger-name

ON{table|view}[WITHENCRYPTION]

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

AS

sql_statement

88

7.5.2使用INSERT觸發(fā)器

例1:往學(xué)生表插入一條記錄，顯示插入成功的信息。

createtrigger插入on學(xué)生

forinsert

as

print'恭喜你，插入記錄成功！'

insertinto學(xué)生（學(xué)院，姓名，學(xué)號，班級）

values（'計算機'/劉萍','992001',2）

89

z

1L8觸發(fā)器的創(chuàng)建執(zhí)行綜述

在進行觸發(fā)器的基本操作之前，介紹兩張?zhí)厥獾呐R時表，

分別是inserted表和deleted表。這兩張表都存在于高速

緩存中。用戶可以使用這兩張臨時表來檢測某些修改操作

所產(chǎn)生的效果。例如，可以使用SELECT語句來檢查

INSERT和UPDATE語句執(zhí)行的插入操作是否成功，觸發(fā)

器是否被這些語句觸發(fā)等。但是不允許用戶直接修改

inserted表和deleted表中數(shù)據(jù)。

90

z

11.8觸發(fā)器的創(chuàng)建執(zhí)行

1.觸發(fā)器的基本操作—創(chuàng)建

例：在student數(shù)據(jù)庫中，為“班級”表建立一個名為

del_banji的DELETE觸發(fā)器，其作用是當(dāng)刪