數(shù)據(jù)庫系統(tǒng)原理與設計實驗教程 第4版 課件 第5章 數(shù)據(jù)庫編程技術

第1頁第5章數(shù)據(jù)庫編程技術數(shù)據(jù)庫系統(tǒng)原理實驗教程第4版5.1相關知識5.1.1游標5.1.2存儲過程5.1.3觸發(fā)器5.2實驗十二游標與存儲過程5.2.1實驗目的與要求5.2.2實驗案例5.2.3實驗內(nèi)容5.3實驗十三觸發(fā)器5.3.1實驗目的與要求5.3.2實驗案例5.3.3實驗內(nèi)容目錄第3頁5.1相關知識5.1.1游標游標是一種允許用戶訪問單獨的數(shù)據(jù)行的數(shù)據(jù)訪問機制。游標主要用在存儲過程、觸發(fā)器和T-SQL腳本中，使用游標，可以對由SELECT語句返回的結果集記錄進行逐行處理。使用游標必須經(jīng)歷五個步驟：①定義游標：DECLARE②打開游標：OPEN③逐行提取游標集中的行：FETCH④關閉游標：CLOSE⑤釋放游標：DEALLOCATE第4頁1.定義游標語法：DECLAREcursor_nameSCROLLCURSORFORsql_staments[FOR[READONLY|UPDATE{OFcolumn_name_list[,...n]]]其中：·cursor_name：用戶定義的游標名?！ql_staments：定義游標結果集的標準SELECT語句。·FOR：后面的短語定義游標屬性只讀或更新，缺省時為UPDATE。·UPDATE{OFcolumn_name_list}：定義游標內(nèi)可更新的列。如果指定OFcolumn_name_list[,...n]參數(shù)，則只允許修改所列出的列。如果在UPDATE中未指定列的列表，則可以更新所有列。第5頁·READONLY：在UPDATE或DELETE語句的WHERECURRENTOF子句中不能引用游標。該選項替代要更新的游標的默認功能。·SCROLL：指定所有的提取選項(FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE)均可用。如果在DECLARECURSOR中未指定SCROLL，則NEXT是唯一支持的提取選項。注意：①當游標移至尾部，不可以再讀取游標，必須關閉游標然后重新打開游標。②可以通過檢查全局變量@@fetch_status來判斷是否已讀完游標集中所有行。第6頁2.打開游標使用OPEN語句執(zhí)行SELECT語句并生成游標。語法為：OPENcurser_name3.提取游標①逐行提取游標集中的行：FETCHcurser_name[INTO@variable_name[,...n]]②FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar

}|Relative{n|@nvar}][FROM{cursor_name|@cursor_variable_name}[INTO@variable_name[,...n]]第7頁其中：·NEXT：返回緊跟當前行之后的結果行，并且當前行遞增為結果行。如果FETCHNEXT為對游標的第一次提取操作，則返回結果集中的第一行。NEXT為默認的游標提取選項。·PRIOR：返回緊臨當前行前面的結果行，并且當前行遞減為結果行。如果FETCHPRIOR為對游標的第一次提取操作，則沒有行返回并且游標置于第一行之前?！IRST：返回游標中的第一行并將其作為當前行。·LAST：返回游標中的最后一行并將其作為當前行。·ABSOLUTE{n|@nvar}：如果n或@nvar為正數(shù)，返回從游標頭開始的第n行并將返回的行變成新的當前行。如果n或@nvar為負數(shù)，返回游標尾之前的第n行并將返回的行變成新的當前行。如果n或@nvar為0，則沒有行返回。n必須為整型常量且@nvar必須為smallint、tinyint或int?！ELATIVE{n|@nvar}：如果n或@nvar為正數(shù)，返回當前行之后的第n行并將返回的行變成新的當前行。如果n或@nvar為負數(shù)，返回當前行之前的第n行并將返回的行變成新的當前行。如果n或@nvar為0，返回當前行。如果對游標的第一次提取操作時將FETCHRELATIVE的n或@nvar指定為負數(shù)或0，則沒有行返回。n必須為整型常量且@nvar必須為smallint、tinyint或int?！NTO@variable_name[,...n]：把每列中的數(shù)據(jù)轉移到指定的變量中。

第8頁4.關閉游標關閉游標可以釋放某些資源，如游標結果集和對當前行的鎖定，如果重新發(fā)出一個OPEN語句，則該游標結構仍可用于處理。語法為：CLOSEcurser_name5.釋放游標DEALLOCATE語句則完全釋放分配給游標的資源，包括游標名稱。在游標被釋放后，必須使用DECLARE語句重新生成游標。語法為：DEALLOCATEcurser_name6.刪除游標集中當前行語法：DELETEFROMtable_nameWHERECURRENTOFcurser_name注意：從游標中刪除一行后，游標定位于被刪除的游標之后的一行，必須再用FETCH得到該行。第9頁7.更新游標集中當前行語法：UPDATEtable_name

SETcolumn_name=expression[,column_name=expression]WHERECURRENTOFcurser_name第10頁5.1.2存儲過程SQLServer提供了一種方法，它可以將一些固定的操作集中起來由SQLServer數(shù)據(jù)庫服務器來完成，以實現(xiàn)某個任務，這種方法就是存儲過程。存儲過程是經(jīng)過編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務器中SQL語句寫的過程，使用時只要調(diào)用即可。存儲過程的優(yōu)點是：(1)提供了在服務器端快速執(zhí)行SQL語句的有效途徑。(2)降低了客戶機和服務器之間的通信量(3)方便實施企業(yè)規(guī)則。(4)業(yè)務封裝后，對數(shù)據(jù)庫系統(tǒng)提供了一定的安全保證。第11頁創(chuàng)建存儲過程時，需要確定存儲過程的3個組成部分：①所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。②被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲過程的語句。③返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。第12頁1.創(chuàng)建存儲過程語法：CREATEPROCEDUREprocedure_name

[;number][{@parameterdatatype}[OUTPUT]][,...n]ASsql_statement[,...n]其中：·procedure_name：存儲過程的名稱。創(chuàng)建臨時過程，在procedure_name前面加一個編號符，即#procedure_name；創(chuàng)建全局臨時過程，在procedure_name前面加兩個編號符，即##procedure_name。完整的名稱(包括#或##)不能超過128個字符。過程所有者的名稱是可選的。第13頁·number：是可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。例如，名為orders的應用程序使用的過程可以命名為orderproc;1、orderproc;2等。DROPPROCEDUREorderproc語句將除去整個組?！parameter：過程中的參數(shù)，最多可以有2100個參數(shù)。·datatype：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型(包括text、ntext和image)均可以用作存儲過程的參數(shù)。·OUTPUT：表明參數(shù)是輸出參數(shù)，text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關鍵字的輸出參數(shù)可以是游標占位符?！：表示最多可以指定2100個參數(shù)的占位符。·AS：指定過程要執(zhí)行的操作?！ql_statement：過程中的Transact-SQL語句。第14頁2.執(zhí)行存儲過程語法：EXECUTE{procedure_name[;number]|@procedure_name_var}[OUTPUT][,...n]其中：·procedure_name：擬調(diào)用的存儲過程名?！procedure_name_var：局部定義的變量名?！parameter：過程參數(shù)，在CREATEPROCEDURE語句中定義。參數(shù)名稱前必須加上符號@。在以@parameter_name=value格式使用時，參數(shù)名稱和常量不一定按照CREATEPROCEDURE語句中定義的順序出現(xiàn)。但是，如果有一個參數(shù)使用@parameter_name=value格式，則其它所有參數(shù)都必須使用這種格式?！UTPUT：指定存儲過程必須返回一個參數(shù)。使用OUTPUT參數(shù)，參數(shù)值必須作為變量傳遞。在執(zhí)行過程之前，必須聲明變量的數(shù)據(jù)類型并賦值。返回參數(shù)可以是text或image數(shù)據(jù)類型以外的任意數(shù)據(jù)類型。第15頁3.重命名存儲過程語法：

Sp_rename'procedure_name1','procedure_name2'4.刪除存儲過程語法：

DROPPROCEDUREprocedure_name第16頁5.1.3觸發(fā)器觸發(fā)器是一種特殊的存儲過程，當INSERT、DELETE或UPDATE語句修改指定表的一行或多行時，自動執(zhí)行觸發(fā)器。在觸發(fā)器的使用中，系統(tǒng)會自動產(chǎn)生兩張臨時表Deleted和Inserted。用戶不能直接修改這兩個表的內(nèi)容。①Deleted表：存儲在DELETE和UPDATE語句執(zhí)行時所影響的行的拷貝，在DELETE和UPDATE語句執(zhí)行前被作用的行轉移到Deleted表中。②Inserted表：存儲在INSTERT和UPDATE語句執(zhí)行時所影響的行的拷貝，在Insert和UPDATE語句執(zhí)行期間，新行被同時加到Inserted和觸發(fā)器表中。第17頁觸發(fā)器僅在當前DB中生成，觸發(fā)器有3種類型，即插入、刪除和更新。(1)INSERT類型的觸發(fā)器：當對指定表TableName執(zhí)行了插入操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。(2)UPDATE類型的觸發(fā)器：當對指定表TableName執(zhí)行了更新操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。(3)DELETE類型的觸發(fā)器：當對指定表TableName執(zhí)行了刪除操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。第18頁在觸發(fā)器內(nèi)不能使用如下的SQL命令：①所有數(shù)據(jù)庫對象的生成命令，如CREATETABLE、CREATEINDEX等。②所有數(shù)據(jù)庫對象的結構修改命令，如ALTERTABLE、ALTERDATABASE等。③創(chuàng)建臨時保存表。④所有DROP命令。⑤GRANT和REVOKE命令。⑥TRUNCATETABLE命令。⑦LOADDATABASE和LOADTRANSACTION命令。

⑧RECONFIGURE命令。第19頁1.創(chuàng)建觸發(fā)器語法：CREATETRIGGERtrigger_nameONtable_nameFOR<INSERT|UPDATE|DELETE>AS

sql_statement2.刪除觸發(fā)器語法：DROPTRIGGERtrigger_name3.修改觸發(fā)器語法：ALTERTRIGGERtriggernameONtable_nameFOR<INSERT|UPDATE|DELETE>ASsql_statement第20頁5.2實驗十二游標與存儲過程5.2.1實驗目的與要求(1)掌握游標的定義和使用方法。(2)掌握存儲過程的定義、執(zhí)行和調(diào)用方法。(3)掌握游標和存儲過程的綜合應用方法。第21頁5.2.2實驗案例[例5.1]利用游標查詢業(yè)務科員工的編號、姓名、性別、部門和薪水，并逐行顯示游標中的信息。DECLAREcur_empSCROLLCURSORFORSELECTemployeeno,employeename,sex,department,salaryFROMemployeeWHEREdepartment='業(yè)務科'ORDERBYemployeeno

/*定義游標*/OPENcur_emp

/*打開游標*/SELECT'CURSOR內(nèi)數(shù)據(jù)條數(shù)'=@@cursor_rows

/*顯示游標內(nèi)記錄的個數(shù)*/FETCHNEXTFROMcur_emp

/*逐行提取游標中的記錄*/WHILE(@@FETCH_status<>-1)

/*判斷FETCH語句是否執(zhí)行成功*/BEGINSELECT'cursor讀取狀態(tài)'=@@FETCH_status/*顯示游標的讀取狀態(tài)*/FETCHNEXTFROMcur_emp

/*提取游標下一行信息*/ENDCLOSEcur_emp

/*關閉游標*/DEALLOCATEcur_emp

/*釋放游標*/第22頁

本例中，@@cursor_rows是返回連接上最后打開的游標中當前存在的合格行的數(shù)量。具體參數(shù)信息見表5-1所示。第23頁@@FETCH_status是返回被FETCH語句執(zhí)行的最后，而不是任何當前被連接打開的游標的狀態(tài)。具體參數(shù)見表5-2所示。第24頁[例5.2]利用游標查詢業(yè)務科員工的編號、姓名、性別、部門和薪水，并以格式化的方式輸出游標中的信息。DECLARE@emp_nochar(8),@emp_namechar(10),@sexchar(1),@deptchar(4)DECLARE@salarynumeric(8,2),@textchar(100)/*用戶自定義的幾個變量*/DECLAREemp_curSCROLLCURSORFORSELECTemployeeNo,employeeName,sex,department,salaryFROMEmployeeWHEREdepartment='業(yè)務科'ORDERBYemployeeNo/*定義游標*/SELECT@text='========業(yè)務科員工情況列表==========='PRINT@textSELECT@text='編號

姓名

性別

部門

薪水'PRINT@textSELECT@text='----------------------------------'PRINT@text/*按照用戶要求格式化輸出相關信息*/OPENemp_cur

/*打開游標*/第25頁FETCHemp_curINTO@emp_no,@emp_name,@sex,@dept,@salary/*提取游標中的信息傳遞并分別給內(nèi)存變量*/WHILE(@@FETCH_status=0)/*判斷是否提取成功*/BEGINSELECT@text=@emp_no+''+@emp_name+''+@sex+''+@dept+''+convert(char(10),@salary)/*給@text賦字符串值*/PRINT@text/*打印字符串值*//*提取游標中的信息傳遞并分別給內(nèi)存變量*/FETCHemp_curinto@emp_no,@emp_name,@sex,@dept,@salaryENDCLOSEemp_cur

/*關閉游標*/DEALLOCATEemp_cur

/*釋放游標*/本例中，主要結合SELECT和PRINT命令將創(chuàng)建游標后逐行提取游標的信息以格式化的方式輸出，提高了腳本的可讀性

第26頁[例5.3]不帶參數(shù)的存儲過程：利用存儲過程計算出’E2020002’業(yè)務員的銷售總金額。①創(chuàng)建存儲過程CREATEPROCEDUREsales_tot1ASSELECTsum(orderSum)FROMOrderMasterWHEREsalerNo=’E2020002’②執(zhí)行存儲過程EXECsales_tot1上述操作只能統(tǒng)計業(yè)務員’E2020002’的銷售業(yè)績，執(zhí)行此存儲過程不能統(tǒng)計任意一個業(yè)務員的銷售業(yè)績。第27頁[例5.4]帶輸入?yún)?shù)的存儲過程：統(tǒng)計某業(yè)務員的銷售總金額。①創(chuàng)建存儲過程CREATEPROCEDUREsales_tot2@e_no

char(8)ASSELECTsum(orderSum)FROMOrderMasterWHEREsalerNo=@e_no②執(zhí)行存儲過程EXECsales_tot2'E2020003'

注：

程序中使用@符號表示一個變量來指定參數(shù)名稱，且每個過程的參數(shù)僅用于該過程本身。上述操作只要在執(zhí)行存儲過程時添加輸入?yún)?shù)(即被統(tǒng)計的業(yè)務員的編號)就能統(tǒng)計任一業(yè)務員的銷售業(yè)績。問題：任意一個業(yè)務員的銷售總金額如何被其他用戶/程序方便調(diào)用呢？

第28頁[例5.5]帶輸入/輸出參數(shù)的存儲過程：統(tǒng)計某業(yè)務員的銷售總金額并返回其結果。①創(chuàng)建存儲過程CREATEPROCEDUREsales_tot3@E_nochar(8),@p_tot

intOUTPUTASSELECT@p_tot=sum(orderSum)FROMOrderMasterWHEREsalerNo=@E_no②執(zhí)行存儲過程DECLARE@tot_amt

intEXECsales_tot3'E2020003',@tot_amtOUTPUTSELECT銷售總額=@tot_amt上述操作可以統(tǒng)計任一員工的銷售業(yè)績并能實現(xiàn)其結果的調(diào)用。第29頁[例5.6]帶通配符參數(shù)的存儲過程(模糊查找)：統(tǒng)計所有姓陳的員工的銷售業(yè)績并輸出他們姓名和所在部門。①創(chuàng)建存儲過程CreateProcedureemp_name

@E_name

varchar(10)ASSELECTa.EmployeeName,a.department,ssumFROMEmployeea,(SELECTSalerNo,ssum=sum(OrderSum)FROMOrderMasterGROUPBYSalerNo)bWHEREa.EmployeeNo=b.SalerNoANDa.EmployeeNameLIKE@E_name②執(zhí)行存儲過程EXECemp_name

@E_name='陳%'第30頁[例5.7]重命名存儲過程：將存儲過程sales_tot2改名為sale_tot。

Sp_rename‘sales_tot2’,‘sale_tot’[例5.8]刪除存儲過程：將存儲過程sale_tot刪除。DROPPROCEDUREsale_tot第31頁[例5.9]游標和存儲過程的綜合應用：請使用游標和循環(huán)語句編寫一個存儲過程emp_tot，根據(jù)業(yè)務員姓名，查詢該業(yè)務員在銷售工作中的客戶信息及每一客戶的銷售記錄，并輸出該業(yè)務員的銷售總金額。第32頁①創(chuàng)建存儲過程CREATEPROCEDUREemp_tot@v_emp_namechar(10)ASBEGINDECLARE@sv_emp_namevarchar(10),@v_custnamevarchar(10),@p_totintDECLARE@sumint,@countint,@order_novarchar(10)SELECT@sum=0,@count=0DECLAREget_totCURSORFORSELECTEmployeeName,CustomerNo,b.OrderNo,OrderSumFROMEmployeea,OrderMasterbWHEREa.EmployeeName=@v_emp_nameANDa.EmployeeNo=b.SalerNoOPENget_totFETCHget_totINTO@sv_emp_name,@v_custname,@order_no,@p_tot第33頁WHILE(@@FETCH_status=0)

BEGIN

SELECT業(yè)務員=@sv_emp_name,客戶=@v_custname,訂單編號=@order_no,訂單金額=@p_totSELECT@sum=@sum+@p_totSELECT@count=@count+1FETCHget_totINTO@sv_emp_name,@v_custname,@order_no,@p_totENDCLOSEget_totDEALLOCATEget_totIF@count=0SELECT0ELSESELECT業(yè)務員銷售總金額=@sumEND第34頁②執(zhí)行存儲過程

EXECemp_tot'張小娟'本例中，先建立一個游標用于臨時儲存業(yè)務員的基本銷售信息，包括：業(yè)務員姓名、客戶編號、訂單編號、訂單銷售金額；再利用游標能逐行提取的功能，提取游標中每一記錄，同時輸出這些信息；最后統(tǒng)計其相應定單金額的總額，并輸出訂單總額。第35頁5.2.3實驗內(nèi)容在訂單數(shù)據(jù)庫OrderDB中請完成以下實驗內(nèi)容：

(1)根據(jù)訂單明細表中的數(shù)據(jù)，利用游標修改OrderMaster表中orderSum的值。(2)創(chuàng)建存儲過程，要求：按第2章員工表定義中的CHECK約束自動產(chǎn)生員工編號。該過程的輸入?yún)?shù)為員工入職的年份，輸出參數(shù)是自動生成的員工編號，該編號滿足第2章員工表定義中的CHECK約束，且后三位流水號等于表中與入職年份相同的員工編號最大值加1，如：輸入?yún)?shù)為2020，且員工表中該年度最大的編碼是E2020005，自動產(chǎn)生的編號為E20200006；如果該入職年份沒有其他員工，則流水號為001。第36頁

(3)

創(chuàng)建存儲過程，要求將大客戶(銷售數(shù)量位于前5名的客戶)中熱銷的前3種商品的銷售信息按如下格式輸出：=============大客戶中熱銷的前種商品的銷售信息===========商品編號

商品名稱

總銷售金額P20200003三星-Galaxy-A949381.00P20200001vivo-X939173.80P20200002中興AXON天機7(A2017)27891.00

第37頁(4)請使用游標和循環(huán)語句創(chuàng)建存儲過程proSearchCustomer，輸入?yún)?shù)為客戶編號，根據(jù)客戶編號查找該客戶的名稱、住址、總訂單金額以及所有與該客戶有關的商品銷售信息，并按商品分組輸出，制作日期取系統(tǒng)的當前日期，輸出格式如下：===================客戶訂單表====================---------------------------------------------------------------------------------客戶名稱：

興隆股份有限公司

客戶地址：

天津市

總金額：

29986.00--------------------------------------------------------------------------------商品編號

總數(shù)量

平均價格

P2020000142798.00P2020000322599.00P2020000543399.00--------------------------------------------------------------------------------報表制作人

張小娟

制作日期

2022-07-08

(5)請利用游標嵌套和循環(huán)語句創(chuàng)建存儲過程proInvoice，輸入?yún)?shù)有兩個，一個是定單的開始時間，一個是定單的結束時間，要求根據(jù)輸入的時間范圍，輸出每個定單的發(fā)票信息，包括：客戶名稱、定單日期、發(fā)票號碼、業(yè)務員名稱、定單總金額及定單明細信息等，發(fā)票打印日期取系統(tǒng)的當前日期，輸出格式如下：業(yè)務員銷售時間范圍為:2020-03-01----2020-10-19

==============================通用機打發(fā)票==============================-----------------------------------------------------------------------------------------------------------------------客戶名稱：興隆股份有限公司

定購日期：2020-03-01發(fā)票號碼：I000000006-----------------------------------------------------------------------------------------------------------------------商品名稱

數(shù)量

單價

金額vivo-X942798.0011192.00TCL-D55A630U13399.003399.00----------------------------------------------------------------------------------------------------------------------商品類數(shù):2商品數(shù)量:5合計:14591.00----------------------------------------------------------------------------------------------------------------------定單銷售員：張露

發(fā)票打印日期：2022-07-11----------------------------------------------------------------------------------------------------------------------

==============================通用機打發(fā)票==============================------------------------------------------------------------------------------------------------------------------------客戶名稱：五一商廈

定購日期：2020-03-02發(fā)票號碼：I000000007------------------------------------------------------------------------------------------------------------------------商品名稱

數(shù)量

單價

金額vivo-X922798.005596.00中興AXON天機7(A13099.003099.00三星-Galaxy-A932599.007797.00-------------------------------------------------------------------------------------------------------------------------商品類數(shù):3商品數(shù)量:6合計:16492.00------------------------------------------------------------------------------------------------------------------------定單銷售員：張小娟

發(fā)票打印日期：2022-07-11------------------------------------------------------------------------------------------------------------------------第39頁5.3實驗十三觸發(fā)器5.3.1實驗目的與要求(1)掌握觸發(fā)器的創(chuàng)建和使用方法。(2)掌握游標和觸發(fā)器的綜合應用方法。第40頁5.3.2實驗案例[例5.10]刪除觸發(fā)器：編寫一個允許用戶一次只刪除一條記錄的觸發(fā)器。

CREATETRIGGERTr_EmpONEmployeeFORDELETEAS/*對表Employee定義一個刪除觸發(fā)器*/DECLARE@row_cnt

int/*定義變量@row_cnt，用于跟蹤Deleted表中記錄的個數(shù)*/SELECT@Row_Cnt=Count(*)FROMDeletedIf@row_cnt>1/*判斷Deleted表中記錄的個數(shù)是否大于1*/BEGINPRINT‘此刪除操作可能會刪除多條人事表數(shù)據(jù)!!!'ROLLBACKTRANSACTION/*如果Deleted表中記錄的個數(shù)大于1，事務回滾*/END第41頁分析：本例中，觸發(fā)器約束了用戶只能對Employee這張表刪除一次刪除一條記錄。我們可驗證觸發(fā)器的作用效果。驗證過程如下：(1)DELETEFROMEmployeeWHEREsex='F'在(1)執(zhí)行后，結果可能出現(xiàn)二種情況：①系統(tǒng)提示：“外鍵約束沖突”錯誤。②系統(tǒng)提示：“此刪除操作可能會刪除多條人事表數(shù)據(jù)!!!”。第①種情況，由于Employee表與其它表建立了外鍵約束關系，在刪除表中元組時必須滿足參照完整性約束的要求。只有刪除外鍵約束，在執(zhí)行刪除操作時才能激活觸發(fā)器。第②中情況，由于解除了外鍵約束后，刪除操作激活觸發(fā)器，但由于刪除的元組多于一個，所以出現(xiàn)正確系統(tǒng)提示信息。第42頁[例5.11]更新觸發(fā)器：請使用游標和循環(huán)語句為OrderDetail表建立一個更新觸發(fā)器updateorderdetail，要求當用戶修改定單明細表中某個商品的數(shù)量或單價時自動修改定單主表中的訂單金額。分析：本例中，Deleted和Inserted表結構與OrderDetail表結構相同。如果用戶修改了銷售明細表中某個貨品的數(shù)量或單價時，Deleted表記載了更新前信息，Inserted表記載了更新后信息，本例正是利用這兩張表結合游標將正確的訂單金額修改到定單主表中。用戶同樣可以用UPDATE命令修改OrderDetail從而驗證觸發(fā)器的作用。第43頁CREATETRIGGERupdatesaleitemONOrderDetailFORUPDATEAS/*對表Employee定義一個更新觸發(fā)器*/IfUPDATE(quantity)ORUPDATE(price)/*判斷對指定列quantity或price的更新*/BEGIN/*定義兩個內(nèi)存變量用于跟蹤游標中訂單編號和商品編號的值*/DECLARE@ordernoint,@productnochar(5)

/*Deleted表中數(shù)據(jù)信息存入到一個游標結果集中*/

DECLAREcur_orderdetailCURSORFORSELECTorderno,productnoFROMDeletedOPENcur_orderdetail/*打開游標*/

BEGINTRANSACTION/*事務開始*//*提取游標中信息并傳遞給變量@orderno,@productno*/FETCHcur_orderdetailINTO@orderno,@productno第44頁WHILE(@@fetch_status=0)/*判斷如果提取成功*/BEGIN/*修改ordermaster中訂單金額的值*/UPDATEordermasterSETordersum=ordersum-D.quan