《數(shù)據(jù)庫原理及應(yīng)用2》課件第7章

第7章存儲過程和觸發(fā)器7.1存儲過程7.2觸發(fā)器7.3本章小結(jié)

7.1存儲過程

7.1.1存儲過程概述

存儲過程中獨(dú)立存在于表之外的數(shù)據(jù)庫對象，由被編譯在一起的一組Transact-SQL語句組成。它可以被客服調(diào)用，也可以被另一個存儲過程或觸發(fā)器調(diào)用，它的參數(shù)可以被傳遞，它的出錯代碼也可以被檢驗(yàn)。存儲過程可以使對數(shù)據(jù)庫的管理，以及顯示關(guān)于數(shù)據(jù)庫及其用戶信息的工作容易得多。可以在任何可使用SQL語句的場合來使用存儲過程，它具有以下優(yōu)點(diǎn)：

(1)可以在單個存儲過程中執(zhí)行一系列SQL語句。

(2)可以從自己的存儲過程內(nèi)引用其他存儲過程，從而簡化一系列復(fù)雜語句。

(3)存儲過程在創(chuàng)建時即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來比單個SQL語句快，且能減少網(wǎng)絡(luò)通信的負(fù)擔(dān)。7.1.2創(chuàng)建存儲過程

要使用存儲過程，首先要創(chuàng)建一個存儲過程?？梢允褂肨ransact-SQL語言的createprocedure語句，也可以使用企業(yè)管理器或者存儲過程創(chuàng)建向?qū)硗瓿伞?/p>

1．使用createprocedure語句創(chuàng)建存儲過程

createprocedure語句的語法格式為：

【例1】創(chuàng)建一個簡單的存儲過程procedure1，用于檢索所有學(xué)生的成績記錄。通過下述sql語句執(zhí)行該存儲過程：

use學(xué)生成績管理

/*判斷procedure1存儲過程是否存在，若存在，則執(zhí)行它*/

ifexists(selectnamefromsysobjects

wherename=‘procedure1’andtype=‘p’)

execprocedure1/*執(zhí)行存儲過程procedure1*/

go

執(zhí)行結(jié)果如圖7-1所示。圖7-1執(zhí)行結(jié)果

2．使用企業(yè)管理器創(chuàng)建存儲過程

使用企業(yè)管理器創(chuàng)建存儲過程的操作步驟如下：圖7-2創(chuàng)建存儲過程

3．使用向?qū)?chuàng)建

SQLServer2000還提供了創(chuàng)建存儲過程的向?qū)А?/p>

【例2】使用向?qū)?chuàng)建一個存儲過程procedure2，對應(yīng)的操作步驟如下：圖7-3“選擇向?qū)А睂υ捒驁D7-4選擇數(shù)據(jù)庫圖7-5選擇存儲過程圖7-6完成創(chuàng)建存儲過程圖7-7編輯存儲過程圖7-8“編輯存儲過程SQL”對話框7.1.3執(zhí)行存儲過程

執(zhí)行存儲過程使用execute語句，其完整語法格式如下：

[exec[ute]]

[@return_status=]

{procedure_name[;number]|@procedure_name_var}

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

[,…n]

[withrecompile]7.1.4存儲過程的參數(shù)

在創(chuàng)建和使用存儲過程時，其參數(shù)是非常重要的。下面詳細(xì)討論存儲過程的參數(shù)傳遞和返回。

1．使用參數(shù)

在調(diào)用存儲過程時，有兩種傳遞參數(shù)的方法。第一種是在傳遞參數(shù)時，使傳遞的參數(shù)和定義時的參數(shù)順序一致，對于使用默認(rèn)值的參數(shù)可以用default代替。

2．使用默認(rèn)參數(shù)

創(chuàng)建存儲過程時，可以為參數(shù)提供一個默認(rèn)值，默認(rèn)值必須為常量或者null。

【例3】創(chuàng)建一個存儲過程insert_學(xué)生表2_2，該存儲過程中包含兩個參數(shù)，其默認(rèn)值分別為'2013008'，'外語'和'北京'。圖7-9執(zhí)行結(jié)果

3．使用返回參數(shù)

在創(chuàng)建存儲過程時，可以定義返回參數(shù)。在執(zhí)行存儲過程時，可以將結(jié)果返回給返回參數(shù)。返回參數(shù)應(yīng)用output進(jìn)行說明。

【例4】創(chuàng)建一個存儲過程average，它返回兩個參數(shù)@st_name和@st_avg，分別代表了姓名和平均分。圖7-10執(zhí)行結(jié)果

4．存儲過程的返回值

存儲過程在執(zhí)行后都會返回一個整型值。如果執(zhí)行成功，則返回0；否則返回-1～-99之間的數(shù)值。也可以使用return語句來指定一個返回值。

【例5】創(chuàng)建的存儲過程test_ret根據(jù)輸入的參數(shù)來判斷返回值。7.1.5存儲過程的查看、修改和刪除

可以使用sp_helptext存儲過程來查看存儲過程的定義信息。

【例6】要查看前面的test_ret存儲過程的定義信息，可以執(zhí)行下面的SQL語句。

use學(xué)生成績管理

go

execsp_helptexttest_ret

go

執(zhí)行結(jié)果如圖7-11所示。圖7-11執(zhí)行結(jié)果圖7-12“存儲過程屬性”對話框

7.2觸發(fā)器

7.2.1觸發(fā)器概述

觸發(fā)器在insert、update或delete語句對表或視圖進(jìn)行修改時會被自動執(zhí)行。觸發(fā)器可以查詢其他表，并可以包含復(fù)雜的Transact-SQL語句。一個表可以有多個觸發(fā)器。7.2.2創(chuàng)建觸發(fā)器

在創(chuàng)建觸發(fā)器前，應(yīng)該考慮到下列問題：

(1)?createtrigger語句必須是批處理中的第一個語句。該批處理中隨后的其他所有語句將被解釋為createtrigger語句定義的一部分。

(2)創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。

(3)觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標(biāo)識符的命名規(guī)則。

(4)雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。

(5)雖然不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時表。觸發(fā)器不應(yīng)引用系統(tǒng)表，而應(yīng)使用信息架構(gòu)視圖。

(6)在含有用delete或update操作定義的外鍵的表中，不能定義insteadof和insteadofupdate觸發(fā)器。

(7)雖然truncatetable語句沒有類似where子句(用于刪除行)的delete語句，但它并不會引發(fā)delete觸發(fā)器，因?yàn)閠runcatetable語句沒有記錄。

(8)?writetext語句不會引發(fā)insert或update觸發(fā)器。創(chuàng)建觸發(fā)器時需要指定下面的選項(xiàng)：

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

(2)需定義觸發(fā)器的表。

(3)觸發(fā)器將何時激發(fā)。

(4)激活觸發(fā)器的數(shù)據(jù)修改語句。有效選項(xiàng)為insert，update或delete。多個數(shù)據(jù)修改語句可激活同一個觸發(fā)器。例如，觸發(fā)器可由insert或update語句激活。

(5)執(zhí)行觸發(fā)操作的編程語句。

1．使用Transact-SQL語句創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器可以使用createtrigger語句，其語法格式如下：

【例7】創(chuàng)建一個觸發(fā)器，在插入、修改和刪除記錄時，都會自動顯示表中的內(nèi)容。

2．使用企業(yè)管理器

使用企業(yè)管理器創(chuàng)建觸發(fā)器的操作步驟如下：圖7-13管理觸發(fā)器快捷菜單圖7-14“觸發(fā)器屬性”對話框7.2.3inserted表和deleted表

在觸發(fā)器執(zhí)行的時候，會產(chǎn)生兩個臨時表：inserted表和deleted表。它們的結(jié)構(gòu)和觸發(fā)器所在的表的結(jié)構(gòu)相同，SQLServer2000自動創(chuàng)建和管理這些表。可以使用這兩個臨時的駐留內(nèi)存的表測試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件；然而，不能直接對表中的數(shù)據(jù)進(jìn)行更改。

【例8】下面的例子說明了inserted表和deleted表的作用。7.2.4使用觸發(fā)器

1．insert和update觸發(fā)器

當(dāng)向表中插入或者更新記錄時，insert或者update觸發(fā)器被執(zhí)行。一般情況下，這兩種觸發(fā)器常用來檢查插入或者修改后的數(shù)據(jù)是否滿足要求。

【例9】下面創(chuàng)建的trig2觸發(fā)器可用來檢查插入的c1是否在1～108之間。

2．delete觸發(fā)器

delete觸發(fā)器通常用于下面的情況：

(1)防止那些確實(shí)要刪除，但是可能會引起數(shù)據(jù)一致性問題的情況，一般用在有外部鍵記錄時。

(2)用于級聯(lián)刪除操作。

【例10】學(xué)生成績管理數(shù)據(jù)庫中，學(xué)生表包含學(xué)生基本數(shù)據(jù)，而成績表包含學(xué)生的成績，當(dāng)刪除學(xué)生表中的學(xué)生記錄時，應(yīng)該同時刪除成績表中對應(yīng)的成績記錄。實(shí)現(xiàn)該功能的觸發(fā)器如下。此時，要刪除學(xué)生表中的記錄：

delete學(xué)生表where學(xué)號='213006'

則學(xué)生表中對應(yīng)的記錄也被刪除。如果使用select語句來查詢成績表，將看到其中學(xué)號為'213006'的兩個記錄已經(jīng)被刪除。7.2.5修改觸發(fā)器

修改觸發(fā)器可以使用altertrigger語句,其語法格式如下：7.2.6刪除觸發(fā)器

除了使用企業(yè)管理器刪除觸發(fā)器外，也可以使用droptrigger語句來刪除觸發(fā)器。其語法格式如下：

droptrigger{trigger}[,…n]

其中，“trigger”是要刪除的觸發(fā)器名稱，而n是表示可以指定多個觸發(fā)器的占位符。

【例11】要刪除trig1觸發(fā)器，則可以執(zhí)行下面的SQL語句。

droptriggertrig17.2.7嵌套觸發(fā)器

如果一個觸發(fā)器在執(zhí)行操作時引發(fā)了另一個觸發(fā)器，而這個觸發(fā)器又接著引發(fā)下一個觸發(fā)器，如此等等。這些觸發(fā)器就是嵌套觸發(fā)器。觸發(fā)器可嵌套至32層，可