SQL Server教程（第6版）(2008版) 課件 第7章 存儲(chǔ)過程、用戶定義函數(shù)和觸發(fā)器

第7章

存儲(chǔ)過程、用戶定義函數(shù)和觸發(fā)器——存儲(chǔ)過程01存儲(chǔ)過程的優(yōu)點(diǎn)和類型存儲(chǔ)過程的優(yōu)點(diǎn)存儲(chǔ)過程的類型存儲(chǔ)過程的優(yōu)點(diǎn)和類型1.存儲(chǔ)過程的優(yōu)點(diǎn)使用存儲(chǔ)過程的優(yōu)點(diǎn)如下。（1）存儲(chǔ)過程在服務(wù)器端運(yùn)行，執(zhí)行速度快。（2）存儲(chǔ)過程執(zhí)行一次后，就駐留在高速緩沖存儲(chǔ)器中，在以后的操作中，只需從高速緩沖存儲(chǔ)器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行，從而提高了系統(tǒng)性能。（3）使用存儲(chǔ)過程可以完成所有數(shù)據(jù)庫(kù)操作，并可通過編程方式控制對(duì)數(shù)據(jù)庫(kù)信息訪問的權(quán)限，確保數(shù)據(jù)庫(kù)的安全。（4）自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過程可以在SQLServer啟動(dòng)時(shí)自動(dòng)執(zhí)行，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手工操作，極大方便了用戶的使用，可以自動(dòng)完成一些需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過程的優(yōu)點(diǎn)和類型2.存儲(chǔ)過程的類型在SQLServer中有下列幾種類型的存儲(chǔ)過程。（1）系統(tǒng)存儲(chǔ)過程。是由SQLServer提供的存儲(chǔ)過程，可以作為命令執(zhí)行。系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)數(shù)據(jù)庫(kù)master中，其前綴是“sp_”。例如，常用的顯示系統(tǒng)對(duì)象信息的sp_help，為檢索系統(tǒng)表的信息提供了方便、快捷的方法。（2）擴(kuò)展存儲(chǔ)過程。擴(kuò)展存儲(chǔ)過程是指在SQLServer環(huán)境之外，使用編程語(yǔ)言（如C++語(yǔ)言）創(chuàng)建的外部例程形成的動(dòng)態(tài)鏈接庫(kù)（DLL）。使用時(shí)，先將DLL加載到SQLServer系統(tǒng)中，并且按照使用系統(tǒng)存儲(chǔ)過程的方法執(zhí)行。擴(kuò)展存儲(chǔ)過程在SQLServer實(shí)例地址空間中運(yùn)行。但因?yàn)閿U(kuò)展存儲(chǔ)過程不易撰寫，而且可能會(huì)引發(fā)安全性問題，所以Microsoft公司可能會(huì)在未來的SQLServer中刪除這一功能，本書將不詳細(xì)介紹擴(kuò)展存儲(chǔ)過程。（3）用戶存儲(chǔ)過程。在SQLServer中，用戶存儲(chǔ)過程可以使用T-SQL語(yǔ)言編寫，也可以使用CLR方式編寫。02存儲(chǔ)過程的創(chuàng)建與執(zhí)行創(chuàng)建存儲(chǔ)過程存儲(chǔ)過程的執(zhí)行舉例存儲(chǔ)過程的創(chuàng)建與執(zhí)行1．創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程的語(yǔ)句是CREATEPROCEDURE或CREATEPROC，兩者同義。語(yǔ)法格式如下。CREATEPROCEDURE|PROC過程名@參數(shù)數(shù)據(jù)類型[OUTPUT] AS SQL語(yǔ)句塊 /*存儲(chǔ)過程體*/說明：（1）過程名：用于指定存儲(chǔ)過程的名稱，必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及所在架構(gòu)必須唯一。這個(gè)名稱應(yīng)當(dāng)盡量避免與系統(tǒng)內(nèi)置函數(shù)同名，否則會(huì)發(fā)生錯(cuò)誤。另外，也應(yīng)當(dāng)盡量避免使用“sp_”作為前綴，此前綴由SQLServer指定系統(tǒng)存儲(chǔ)過程。（2）@參數(shù)：為存儲(chǔ)過程的形參，@符號(hào)作為第一個(gè)字符來指定參數(shù)名。參數(shù)名必須符合標(biāo)識(shí)符規(guī)則。創(chuàng)建存儲(chǔ)過程時(shí)，可聲明一個(gè)或多個(gè)參數(shù)。（3）數(shù)據(jù)類型：用于指定形參的數(shù)據(jù)類型，形參可為SQLServer支持的任何類型，但游標(biāo)類型只能用于OUTPUT參數(shù)。

（4）OUTPUT：指示參數(shù)為輸出參數(shù)，輸出參數(shù)可以從存儲(chǔ)過程返回信息。（5）SQL語(yǔ)句：代表過程體包含的T-SQL語(yǔ)句，存儲(chǔ)過程體中可以包含一條或多條T-SQL語(yǔ)句，除了DCL、DML與DDL命令外，還能包含過程式語(yǔ)句，如變量的定義與賦值、流程控制語(yǔ)句等。存儲(chǔ)過程的創(chuàng)建與執(zhí)行2．存儲(chǔ)過程的執(zhí)行語(yǔ)法格式如下。EXECUTE|EXEC 過程名|@過程名變量 [參數(shù)值,…|@參數(shù)名=值,…]說明：（1）過程名：要調(diào)用的存儲(chǔ)過程或用戶定義標(biāo)量函數(shù)的完全或者不完全限定名稱。“組號(hào)”用于調(diào)用已定義的一組存儲(chǔ)過程中的某一個(gè)。（2）@過程名變量：局部定義的變量名，保存存儲(chǔ)過程或用戶定義函數(shù)的名稱。（3）@參數(shù)名：為CREATEPROCEDURE或CREATEFUNCTION語(yǔ)句中定義的參數(shù)名，“值”為實(shí)參。如果省略“@參數(shù)名”，則后面的實(shí)參順序要與定義時(shí)參數(shù)的順序一致。存儲(chǔ)過程的創(chuàng)建與執(zhí)行3．舉例1）設(shè)計(jì)簡(jiǎn)單的存儲(chǔ)過程【例7.1】從xscj數(shù)據(jù)庫(kù)的3個(gè)表中查詢所有學(xué)生課程的成績(jī)和學(xué)分。（1）創(chuàng)建存儲(chǔ)過程語(yǔ)句如下：USExscjGOCREATEPROCEDUREstudent_info AS SELECTa.學(xué)號(hào),姓名,課程名,成績(jī),t.學(xué)分 FROMxsbaINNERJOINcjbb ONa.學(xué)號(hào)=b.學(xué)號(hào)INNERJOINkcbt ONb.課程號(hào)=t.課程號(hào)

GO說明：該存儲(chǔ)過程不使用任何參數(shù)。（2）執(zhí)行存儲(chǔ)過程語(yǔ)句如下。EXECUTEstudent_info如果該存儲(chǔ)過程是批處理中的第一條語(yǔ)句，直接寫出過程名即可。student_info存儲(chǔ)過程的創(chuàng)建與執(zhí)行2）使用帶參數(shù)的存儲(chǔ)過程【例7.2】從xscj數(shù)據(jù)庫(kù)的3個(gè)表中查詢某學(xué)生指定課程的成績(jī)和學(xué)分。該存儲(chǔ)過程接收與傳遞參數(shù)精確匹配的值。語(yǔ)句如下：CREATEPROCEDUREstudent_info1@namechar(8),@cnamechar(16) AS SELECTa.學(xué)號(hào),姓名,課程名,成績(jī),t.學(xué)分 FROMxsbaINNERJOINcjbb ONa.學(xué)號(hào)=b.學(xué)號(hào)INNERJOINkcbt ONb.課程號(hào)=t.課程號(hào)

WHEREa.姓名=@nameANDt.課程名=@cnameGO存儲(chǔ)過程的創(chuàng)建與執(zhí)行執(zhí)行存儲(chǔ)過程student_info1，語(yǔ)句如下。

EXECUTEstudent_info1'王林','計(jì)算機(jī)導(dǎo)論'執(zhí)行結(jié)果如圖。以下命令的執(zhí)行結(jié)果與上面的相同。EXECUTEstudent_info1@name='王林',@cname='計(jì)算機(jī)導(dǎo)論'或者：DECLARE@procchar(20)SET@proc='student_info1'EXECUTE@proc@name='王林',@cname='計(jì)算機(jī)導(dǎo)論'存儲(chǔ)過程的創(chuàng)建與執(zhí)行3）使用帶OUPUT參數(shù)的存儲(chǔ)過程【例7.3】創(chuàng)建一個(gè)存儲(chǔ)過程kc_insert，作用是向kcb表中插入一行數(shù)據(jù)。插入成功，則輸出1，否則輸出0。CREATEPROCEDUREkc_insert@kchchar(3),@kcmchar(16),@xfint,@yesbitOUTPUT AS BEGIN SET@yes=0 INSERTINTOkcb(課程號(hào),課程名,學(xué)分)VALUES(@kch,@kcm,@xf) SET@yes=1 END接下來執(zhí)行存儲(chǔ)過程kc_insert來查看結(jié)果。語(yǔ)句如下。DECLARE@myyesbitEXECdbo.kc_insert'401','就業(yè)指導(dǎo)',2,@myyesOUTPUTSELECT@myyes執(zhí)行結(jié)果顯示“修改成功”。存儲(chǔ)過程的創(chuàng)建與執(zhí)行4）使用帶有通配符參數(shù)的存儲(chǔ)過程【例7.4】從3個(gè)表的連接中返回指定學(xué)生的學(xué)號(hào)、姓名、所選課程名及該課程的成績(jī)。該存儲(chǔ)過程在參數(shù)中使用了模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。語(yǔ)句如下。CREATEPROCEDUREst_info@namevarchar(30)='李%' AS SELECTa.學(xué)號(hào),a.姓名,c.課程名,b.成績(jī) FROMxsbaINNERJOINcjbb ONa.學(xué)號(hào)=b.學(xué)號(hào)INNERJOINkcbc ONc.課程號(hào)=b.課程號(hào)

WHERE姓名LIKE@nameGO執(zhí)行存儲(chǔ)過程，代碼如下。EXECUTEst_info /*參數(shù)使用默認(rèn)值*/或者：EXECUTEst_info'王%' /*傳遞給@name的實(shí)參為'王%'*/存儲(chǔ)過程的創(chuàng)建與執(zhí)行5）使用OUTPUT游標(biāo)參數(shù)的存儲(chǔ)過程OUTPUT游標(biāo)參數(shù)用于返回存儲(chǔ)過程的局部游標(biāo)?！纠?.5】在xscj數(shù)據(jù)庫(kù)中的xsb表上聲明并打開一個(gè)游標(biāo)。語(yǔ)句如下。CREATEPROCEDUREst_cursor@st_cursorCURSORVARYINGOUTPUT AS SET@st_cursor=CURSOR FORWARD_ONLYSTATIC FOR SELECT* FROMxsb OPEN@st_cursor說明：VARYING指定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲(chǔ)過程動(dòng)態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變，僅適用于游標(biāo)參數(shù)。如果指定參數(shù)的數(shù)據(jù)類型為CURSOR，則必須同時(shí)指定VARYING和OUTPUT關(guān)鍵字。存儲(chǔ)過程的創(chuàng)建與執(zhí)行在如下的批處理中，聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述存儲(chǔ)過程，并將游標(biāo)賦值給局部游標(biāo)變量，然后通過該游標(biāo)變量讀取記錄。語(yǔ)句如下。DECLARE@MyCursorCURSOREXECst_cursor@st_cursor=@MyCursorOUTPUT /*執(zhí)行存儲(chǔ)過程*/FETCHNEXTFROM@MyCursorWHILE(@@FETCH_STATUS=0)BEGIN FETCHNEXTFROM@MyCursorENDCLOSE@MyCursorDEALLOCATE@MyCursor存儲(chǔ)過程的創(chuàng)建與執(zhí)行6）使用WITHENCRYPTION選項(xiàng)WITHENCRYPTION選項(xiàng)用于對(duì)用戶隱藏存儲(chǔ)過程的文本?！纠?.6】創(chuàng)建加密過程，使用sp_helptext系統(tǒng)存儲(chǔ)過程獲取加密過程的信息，然后嘗試直接從syscomments表中獲取該過程的信息。語(yǔ)句如下：CREATEPROCEDUREencrypt_thisWITHENCRYPTION AS SELECT*FROMxsb通過系統(tǒng)存儲(chǔ)過程sp_helptext可顯示規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義函數(shù)、觸發(fā)器或視圖的文本。執(zhí)行如下語(yǔ)句。EXECsp_helptextencrypt_this結(jié)果集為提示信息“對(duì)象'encrypt_this'的文本已加密”。03存儲(chǔ)過程的修改存儲(chǔ)過程的修改使用ALTERPROCEDURE命令可修改已存在的存儲(chǔ)過程并保留以前賦予的許可。語(yǔ)法格式如下。ALTER PROCEDURE|PROC 過程名 @參數(shù)數(shù)據(jù)類型[OUTPUT] AS SQL語(yǔ)句用ALTERPROCEDURE更改后，存儲(chǔ)過程的權(quán)限和啟動(dòng)屬性保持不變。【例7.7】對(duì)存儲(chǔ)過程student_info1進(jìn)行修改，將第一個(gè)參數(shù)改成學(xué)生的學(xué)號(hào)。語(yǔ)句如下：USExscjGOALTERPROCEDUREstudent_info1@numberchar(6),@cnamechar(16) AS SELECT學(xué)號(hào),課程名,成績(jī) FROMcjb,kcb WHEREcjb.學(xué)號(hào)=@numberANDkcb.課程名=@cnameGO04存儲(chǔ)過程的刪除存儲(chǔ)過程的刪除當(dāng)不再使用一個(gè)存儲(chǔ)過程時(shí)，就要把它從數(shù)據(jù)庫(kù)中刪除。在此之前，必須確認(rèn)該存儲(chǔ)過程沒有任何依賴關(guān)系。語(yǔ)法格式如下。DROPPROCEDURE|PROC過程名【例7.8】刪除xscj數(shù)據(jù)庫(kù)中的encrypt_this存儲(chǔ)過程。語(yǔ)句如下：IFEXISTS(SELECTnameFROMsysobjectsWHEREname='encrypt_this') DROPPROCEDUREencrypt_this說明：在刪除存儲(chǔ)過程之前，可以先查找系統(tǒng)表sysobjects中是否存在這一存儲(chǔ)過程，然后再刪除。05界面方式操作存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程修改存儲(chǔ)過程執(zhí)行存儲(chǔ)過程刪除存儲(chǔ)過程界面方式操作存儲(chǔ)過程1．創(chuàng)建存儲(chǔ)過程在“對(duì)象資源管理器”中，選擇數(shù)據(jù)庫(kù)（如xscj）→可編程性→存儲(chǔ)過程，右擊，在彈出的快捷菜單中選擇新建→存儲(chǔ)過程命令，打開“存儲(chǔ)過程腳本編輯”窗口，在該窗口中輸入要?jiǎng)?chuàng)建的存儲(chǔ)過程的代碼，輸入完成后單擊“執(zhí)行”按鈕，若執(zhí)行成功則創(chuàng)建完成。2．修改存儲(chǔ)過程在“對(duì)象資源管理器”中，選擇要修改的存儲(chǔ)過程名（例如：kc_insert），右擊，在彈出的快捷菜單中選擇“修改”命令，打開“存儲(chǔ)過程腳本編輯”窗口，在該窗口中修改相關(guān)的T-SQL語(yǔ)句。如圖。界面方式操作存儲(chǔ)過程3．執(zhí)行存儲(chǔ)過程在“對(duì)象資源管理器”中，選擇要執(zhí)行的存儲(chǔ)過程名，如student_info1，右擊，在彈出的快捷菜單中選擇“執(zhí)行存儲(chǔ)過程”命令。在打開的“執(zhí)行過程”窗口中會(huì)列出存儲(chǔ)過程的參數(shù)形式，如果“輸出參數(shù)”選項(xiàng)為“否”，則表示該參數(shù)為輸入?yún)?shù)，用戶需要設(shè)置輸入?yún)?shù)的值，在@number“值”選項(xiàng)中輸入“221101”，在@cname選項(xiàng)中輸入“計(jì)算機(jī)導(dǎo)論”。單擊“確定”按鈕，系統(tǒng)顯示存儲(chǔ)過程執(zhí)行的結(jié)果。4．刪除存儲(chǔ)過程選擇要?jiǎng)h除的存儲(chǔ)過程名，右擊，在彈出的快捷菜單中選擇“刪除”命令，根據(jù)提示刪除該存儲(chǔ)過程。第7章

存儲(chǔ)過程、用戶定義函數(shù)和觸發(fā)器——用戶定義函數(shù)01用戶定義標(biāo)量函數(shù)標(biāo)量函數(shù)的定義標(biāo)量函數(shù)的調(diào)用用戶定義標(biāo)量函數(shù)1．標(biāo)量函數(shù)的定義語(yǔ)法格式如下。CREATEFUNCTION架構(gòu)名.函數(shù)名 (參數(shù)1[AS]類型1[=默認(rèn)值]),…RETURNS返回值類型[WITH選項(xiàng)][AS] BEGIN

函數(shù)體 RETURN標(biāo)量表達(dá)式 END創(chuàng)建用戶定義函數(shù)后，在“對(duì)象資源管理器”中，展開指定數(shù)據(jù)庫(kù)→可編程性→函數(shù)→標(biāo)量值函數(shù)，即可顯示已經(jīng)創(chuàng)建好的用戶定義函數(shù)名。用戶定義標(biāo)量函數(shù)2．標(biāo)量函數(shù)的調(diào)用（1）在SELECT語(yǔ)句中調(diào)用。語(yǔ)法格式如下。

架構(gòu)名.函數(shù)名(實(shí)參1,…,實(shí)參n)。實(shí)參可為已賦值的局部變量或表達(dá)式。（2）利用EXECUTE（EXEC）語(yǔ)句執(zhí)行。用T-SQLEXEC語(yǔ)句調(diào)用用戶定義函數(shù)時(shí)，參數(shù)的標(biāo)識(shí)次序與函數(shù)定義中的參數(shù)標(biāo)識(shí)次序可以不同。語(yǔ)法格式如下。

EXEC架構(gòu)名.函數(shù)名實(shí)參1,…,實(shí)參n或者：EXEC架構(gòu)名.函數(shù)名形參名1=實(shí)參1,…,形參名n=實(shí)參n在這里，前者實(shí)參順序應(yīng)與函數(shù)定義的形參順序一致，后者參數(shù)順序可以與函數(shù)定義的形參順序不一致。用戶定義標(biāo)量函數(shù)【例7.9】創(chuàng)建用戶定義函數(shù)，實(shí)現(xiàn)計(jì)算全體學(xué)生某門功課平均成績(jī)的功能。（1）創(chuàng)建用戶定義函數(shù)的語(yǔ)句如下。USExscjGOCREATEFUNCTIONaverage(@numchar(3))RETURNSint AS BEGIN DECLARE@averint SELECT@aver= ( SELECTavg(成績(jī)) FROMcjb WHERE課程號(hào)=@num GROUPBY課程號(hào) ) RETURN@aver END用戶定義標(biāo)量函數(shù)（2）調(diào)用用戶定義函數(shù)的代碼如下。DECLARE@course1char(3) /*定義局部變量*/DECLARE@aver1int SELECT@course1='101' /*給局部變量賦值*/SELECT@aver1=dbo.average(@course1) /*調(diào)用用戶函數(shù)，并將返回值賦給局部變量*/SELECT@aver1AS'101課程的平均成績(jī)' /*顯示局部變量的值*/執(zhí)行結(jié)果為101課程的平均成績(jī)。（3）在xscj中建立一個(gè)kcb-func表，并將一個(gè)列定義為計(jì)算列。語(yǔ)句如下。CREATETABLEkcb_func( cno int, /*課程號(hào)*/ cname nchar(20), /*課程名*/ creditint, /*學(xué)分*/ averAS /*將此列定義為計(jì)算列*/ ( dbo.average(cno) ))02內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)的定義內(nèi)嵌表值函數(shù)的調(diào)用多語(yǔ)句表值函數(shù)內(nèi)嵌表值函數(shù)1．內(nèi)嵌表值函數(shù)的定義語(yǔ)法格式如下。CREATEFUNCTION[架構(gòu)名.]函數(shù)名 /*定義函數(shù)名部分*/ (@參數(shù)名[AS][類型架構(gòu)名.]參數(shù)數(shù)據(jù)類型[=默認(rèn)],…) /*定義參數(shù)部分*/RETURNStable /*返回值為表類型*/[WITH函數(shù)選項(xiàng),…] /*定義函數(shù)的可選項(xiàng)*/ [AS]RETURN[(]SELECT語(yǔ)句[)] /*通過SELECT語(yǔ)句返回內(nèi)嵌表*/說明：RETURNS子句僅包含關(guān)鍵字table，表示此函數(shù)返回一個(gè)表。內(nèi)嵌表值函數(shù)的函數(shù)體僅有一個(gè)RETURN語(yǔ)句，并通過指定的SELECT語(yǔ)句返回內(nèi)嵌表值。其他參數(shù)項(xiàng)與標(biāo)量函數(shù)的定義類似。內(nèi)嵌表值函數(shù)2．內(nèi)嵌表值函數(shù)的調(diào)用【例7.10】創(chuàng)建xscj數(shù)據(jù)庫(kù)xsb、kcb、cjb表視圖，查詢指定學(xué)生各科成績(jī)及學(xué)分。（1）創(chuàng)建視圖語(yǔ)句如下。USExscjDROPVIEWxsvGOCREATEVIEWxsv AS SELECTdbo.xsb.學(xué)號(hào),dbo.xsb.姓名,dbo.kcb.課程名,dbo.cjb.成績(jī)

FROMdbo.kcb INNERJOINdbo.cjbONdbo.kcb.課程號(hào)=dbo.cjb.課程號(hào)

INNERJOINdbo.xsbONdbo.cjb.學(xué)號(hào)=dbo.xsb.學(xué)號(hào)內(nèi)嵌表值函數(shù)（2）定義內(nèi)嵌函數(shù)。語(yǔ)句如下。CREATEFUNCTIONstudent_score(@idchar(6))RETURNStable ASRETURN ( SELECT* FROMxscj.dbo.xsv WHEREdbo.xsv.學(xué)號(hào)=@id )（3）調(diào)用內(nèi)嵌函數(shù)，查詢學(xué)號(hào)為221101的學(xué)生的各科成績(jī)及學(xué)分。語(yǔ)句如下，執(zhí)行結(jié)果如圖。SELECT* FROMxscj.[dbo].student_score('221101')內(nèi)嵌表值函數(shù)3．多語(yǔ)句表值函數(shù)內(nèi)嵌表值函數(shù)和多語(yǔ)句表值函數(shù)都返回表，二者不同之處在于：內(nèi)嵌表值函數(shù)沒有函數(shù)主體，返回的表是單個(gè)SELECT語(yǔ)句的結(jié)果集；而多語(yǔ)句表值函數(shù)在BEGIN…END塊中定義的函數(shù)主體包含T-SQL語(yǔ)句，這些語(yǔ)句可生成行并將行插入至表中，最后返回表。語(yǔ)法格式如下。CREATEFUNCTION[架構(gòu)名.]函數(shù)名 /*定義函數(shù)名部分*/ (@參數(shù)名[AS][類型架構(gòu)名.]參數(shù)數(shù)據(jù)類型[=默認(rèn)],…) /*定義函數(shù)參數(shù)部分*/RETURNS@返回變量table表類型定義 /*定義作為返回值的表*/[WITH函數(shù)選項(xiàng),…] /*定義函數(shù)的可選項(xiàng)*/ [AS] BEGIN

函數(shù)體 /*定義函數(shù)體*/ RETURN END內(nèi)嵌表值函數(shù)說明：（1）@返回變量：表變量，用于存儲(chǔ)作為函數(shù)值返回的記錄集。（2）函數(shù)體：T-SQL語(yǔ)句序列，只用于標(biāo)量函數(shù)和多語(yǔ)句表值函數(shù)。在標(biāo)量函數(shù)中，函數(shù)體是一系列合起來求得標(biāo)量值的T-SQL語(yǔ)句；在多語(yǔ)句表值函數(shù)中，函數(shù)體是一系列在表變量“@返回變量”中插入記錄行的T-SQL語(yǔ)句。（3）表類型定義：指定定義表結(jié)構(gòu)的語(yǔ)句。語(yǔ)法格式中的其他項(xiàng)與標(biāo)量函數(shù)的定義相同。內(nèi)嵌表值函數(shù)【例7.11】在xscj數(shù)據(jù)庫(kù)中創(chuàng)建返回表的函數(shù)，通過以學(xué)號(hào)作為實(shí)參調(diào)用該函數(shù)，可顯示該學(xué)生各門功課的成績(jī)和學(xué)分。（1）對(duì)函數(shù)進(jìn)行定義。語(yǔ)句如下。CREATEFUNCTIONscore_table(@idchar(6))RETURNS@scoretable(

學(xué)號(hào) char(6),

姓名 char(8),

課程 char(16),

成績(jī) tinyint)AS BEGIN INSERT@score SELECTS.學(xué)號(hào),S.姓名,P.課程名,O.成績(jī) FROMxscj.[dbo].xsbASS INNERJOINxscj.[dbo].cjbASOON(S.學(xué)號(hào)=O.學(xué)號(hào)) INNERJOINxscj.[dbo].kcbASPON(O.課程號(hào)=P.課程號(hào)) WHERES.學(xué)號(hào)=@id RETURN END內(nèi)嵌表值函數(shù)（2）查詢學(xué)號(hào)為221101的學(xué)生的各科成績(jī)和學(xué)分。語(yǔ)句如下，執(zhí)行結(jié)果與上例相同。SELECT*FROMxscj.[dbo].score_table('221101')03用戶定義函數(shù)的刪除用戶定義函數(shù)的刪除語(yǔ)法格式如下。DROPFUNCTION[架構(gòu)名.]函數(shù)名,…說明：（1）“函數(shù)名”是要?jiǎng)h除的用戶定義函數(shù)的名稱?？梢赃x擇是否指定架構(gòu)名稱，但不能指定服務(wù)器名稱和數(shù)據(jù)庫(kù)名稱?？梢砸淮蝿h除一個(gè)或多個(gè)用戶定義函數(shù)。（2）要?jiǎng)h除用戶定義函數(shù)，先要?jiǎng)h除與之相關(guān)的對(duì)象。例如，如果在前面建立的kcb-func表中引用了average函數(shù)來創(chuàng)建計(jì)算列，則要先刪除與之相關(guān)的列后才能刪除average函數(shù)。第7章

存儲(chǔ)過程、用戶定義函數(shù)和觸發(fā)器——觸

發(fā)

器01觸發(fā)器的類型DML觸發(fā)器DDL觸發(fā)器觸發(fā)器的類型1．DML觸發(fā)器當(dāng)數(shù)據(jù)庫(kù)中發(fā)生DML事件時(shí)將調(diào)用DML觸發(fā)器。一般情況下，DML事件包括對(duì)表或視圖的INSERT語(yǔ)句、UPDATE語(yǔ)句和DELETE語(yǔ)句，因而DML觸發(fā)器也可分為3種類型，即INSERT、UPDATE和DELETE。利用DML觸發(fā)器可以方便地保持?jǐn)?shù)據(jù)庫(kù)中數(shù)據(jù)的完整性。例如，xscj數(shù)據(jù)庫(kù)，有xsb表、cjb表和kcb表，當(dāng)插入某一學(xué)號(hào)學(xué)生某一課程的成績(jī)時(shí)，該學(xué)號(hào)應(yīng)是xsb表中已存在的，課程號(hào)應(yīng)是kcb表中已存在的，此時(shí)，可通過定義INSERT觸發(fā)器實(shí)現(xiàn)上述功能。2．DDL觸發(fā)器DDL觸發(fā)器也是由相應(yīng)的事件觸發(fā)的，但它觸發(fā)的事件是數(shù)據(jù)定義語(yǔ)句（DDL），主要是以CREATE、ALTER、DROP等關(guān)鍵字開頭的語(yǔ)句。DDL觸發(fā)器的主要作用是執(zhí)行管理操作，如審核系統(tǒng)、控制數(shù)據(jù)庫(kù)的操作等。通常情況下，DDL觸發(fā)器主要用于以下操作需求：防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些修改；希望數(shù)據(jù)庫(kù)中發(fā)生某些變化以利于相應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改；記錄數(shù)據(jù)庫(kù)架構(gòu)中的更改或事件。DDL觸發(fā)器只在響應(yīng)由T-SQL語(yǔ)法所指定的DDL事件時(shí)才會(huì)觸發(fā)。02觸發(fā)器的創(chuàng)建創(chuàng)建DML觸發(fā)器觸發(fā)器說明創(chuàng)建INSERT觸發(fā)器創(chuàng)建UPDATE觸發(fā)器創(chuàng)建DELETE觸發(fā)器創(chuàng)建INSTEADOF觸發(fā)器創(chuàng)建DDL觸發(fā)器觸發(fā)器的創(chuàng)建1．創(chuàng)建DML觸發(fā)器語(yǔ)法格式如下。CREATETRIGGER觸發(fā)器名 ON表名|視圖名 /*指定操作對(duì)象*/ AFTER|INSTEADOF [INSERT][,][UPDATE][,][DELETE] AS SQL語(yǔ)句塊說明：（1）觸發(fā)器名：用于指定觸發(fā)器的名稱，觸發(fā)器名必須符合標(biāo)識(shí)符規(guī)則，并且在數(shù)據(jù)庫(kù)中必須唯一。（2）表名|視圖名：指在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖。（3）AFTER：用于說明觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，如AFTERINSERT表示向表中插入數(shù)據(jù)后激活觸發(fā)器。（4）INSTEADOF：指定用DML觸發(fā)器中的操作代替觸發(fā)語(yǔ)句的操作。（5）INSERT、UPDATE和DELETE：指定激活觸發(fā)器的語(yǔ)句的類型，必須至少指定一個(gè)選項(xiàng)。（6）SQL語(yǔ)句塊：指定DML觸發(fā)器觸發(fā)后將要執(zhí)行的動(dòng)作。觸發(fā)器的創(chuàng)建2．觸發(fā)器說明（1）觸發(fā)器中使用的特殊表。執(zhí)行觸發(fā)器時(shí)，系統(tǒng)創(chuàng)建了兩個(gè)特殊的臨時(shí)表inserted表和deleted表，下面介紹這兩個(gè)表的內(nèi)容。inserted表：當(dāng)向表中插入數(shù)據(jù)時(shí)，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。deleted表：用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個(gè)DELETE觸發(fā)器時(shí)，被刪除的記錄存放到deleted表中。修改一條記錄等于插入一條新記錄，同時(shí)刪除舊記錄。當(dāng)對(duì)定義了UPDATE觸發(fā)器的表記錄修改時(shí)，表中原記錄移到deleted表中，修改過的記錄插入到inserted表中。觸發(fā)器的創(chuàng)建（2）創(chuàng)建DML觸發(fā)器的說明①CREATETRIGGER語(yǔ)句必須是批處理中的第一條語(yǔ)句，并且只能應(yīng)用到一個(gè)表中。②DML觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫(kù)中創(chuàng)建，但可以引用當(dāng)前數(shù)據(jù)庫(kù)的外部對(duì)象。③創(chuàng)建DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者。④在同一CREATETRIGGER語(yǔ)句中，可以為多種操作（如INSERT和UPDATE）定義相同的觸發(fā)器。⑤不能對(duì)臨時(shí)表或系統(tǒng)表創(chuàng)建DML觸發(fā)器。⑥對(duì)于含有DELETE或UPDATE操作定義的外鍵表，不能使用INSTEADOFDELETE和INSTEADOFUPDATE觸發(fā)器。⑦TRUNCATETABLE語(yǔ)句雖然能夠刪除表中的記錄，但它不會(huì)觸發(fā)DELETE觸發(fā)器。⑧在觸發(fā)器內(nèi)可以指定任意的SET語(yǔ)句，所選擇的SET選項(xiàng)在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。⑨DML觸發(fā)器最大的用途是返回行級(jí)數(shù)據(jù)的完整性，而不是返回結(jié)果，所以應(yīng)當(dāng)盡量避免返回任何結(jié)果集。⑩CREATETRIGGER權(quán)限默認(rèn)授予定義觸發(fā)器的表所有者、sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫(kù)角色成員，并且不可轉(zhuǎn)讓。?DML觸發(fā)器中不包含以下語(yǔ)句：ALTERDATABASE、CREATEDATABASE、DROPDATABASE、LOADDATABASE、LOADLOG、RECONFIGURE、RESTOREDATABASE、RESTORELOG。觸發(fā)器的創(chuàng)建3．創(chuàng)建INSERT觸發(fā)器INSERT觸發(fā)器是當(dāng)對(duì)觸發(fā)器表執(zhí)行INSERT語(yǔ)句時(shí)激活的觸發(fā)器，可以用來修改，甚至拒絕接收正在插入的記錄?！纠?.12】創(chuàng)建cjb表INSERT觸發(fā)器，當(dāng)向cjb表中插入一個(gè)學(xué)生的成績(jī)時(shí)，將xsb表中該學(xué)生的總學(xué)分加上添加的課程的學(xué)分。1）準(zhǔn)備測(cè)試表為了不破壞原來表記錄，這里先復(fù)制臨時(shí)表，然后操作臨時(shí)表。USExscjSELECT* INTOxsb3 FROMxsbSELECT* INTOkcb3 FROMkcbSELECT* INTOcjb3 FROMcjb觸發(fā)器的創(chuàng)建2）創(chuàng)建臨時(shí)成績(jī)表INSERT觸發(fā)器語(yǔ)句如下：CREATETRIGGERcjb3_insert ONcjb3AFTERINSERT AS BEGIN DECLARE@numchar(6),@kc_numchar(3) DECLARE@xfint SELECT@num=學(xué)號(hào),@kc_num=課程號(hào)FROMinserted SELECT@xf=學(xué)分FROMkcb3WHERE課程號(hào)=@kc_num UPDATExsb3SET總學(xué)分=總學(xué)分+@xfWHERE學(xué)號(hào)=@num PRINT'修改成功' END觸發(fā)器的創(chuàng)建3）cjb3表INSERT觸發(fā)器功能驗(yàn)證語(yǔ)句如下：SELECT*FROMxsb3WHERE學(xué)號(hào)='211101'SELECT*FROMcjb3WHERE學(xué)號(hào)='211101'INSERTINTOcjb3VALUES('211101','301',80)SELECT*FROMxsb3WHERE學(xué)號(hào)='211101'執(zhí)行結(jié)果如圖。觸發(fā)器的創(chuàng)建4．創(chuàng)建UPDATE觸發(fā)器UPDATE觸發(fā)器在對(duì)觸發(fā)器表執(zhí)行UPDATE語(yǔ)句后觸發(fā)。在執(zhí)行UPDATE觸發(fā)器時(shí)，將觸發(fā)器表的原記錄保存到deleted臨時(shí)表中，將修改后的記錄保存到inserted臨時(shí)表中?！纠?.13】創(chuàng)建xsb3表UPDATE觸發(fā)器，當(dāng)修改xsb3表中的學(xué)號(hào)時(shí)，同時(shí)也要將cjb3表中的學(xué)號(hào)修改成相應(yīng)的學(xué)號(hào)。（1）創(chuàng)建xsb3表UPDATE觸發(fā)器，語(yǔ)句如下。CREATETRIGGERxsb3_update ONxsb3AFTERUPDATE AS BEGIN DECLARE@old_numchar(6),@new_numchar(6) SELECT@old_num=學(xué)號(hào)FROMdeleted SELECT@new_num=學(xué)號(hào)FROMinserted UPDATEcjb3SET學(xué)號(hào)=@new_numWHERE學(xué)號(hào)=@old_num END觸發(fā)器的創(chuàng)建（2）修改xsb3表中的一行數(shù)據(jù)，并查看觸發(fā)器的執(zhí)行結(jié)果，語(yǔ)句如下：SELECT*FROMcjb3WHERE學(xué)號(hào)='211101'UPDATExsb3SET學(xué)號(hào)='211341'WHERE學(xué)號(hào)='211101'GOSELECT*FROMcjb3WHERE學(xué)號(hào)='211101'SELECT*FROMcjb3WHERE學(xué)號(hào)='211341'執(zhí)行結(jié)果如圖。觸發(fā)器的創(chuàng)建5．創(chuàng)建DELETE觸發(fā)器【例7.14】在刪除xsb3表中的一條學(xué)生記錄時(shí)將cjb3表中該學(xué)生的相應(yīng)記錄也刪除。語(yǔ)句如下：CREATETRIGGERxsb3_delete ONxsb3AFTERDELETE AS BEGIN DELETEFROMcjb3 WHERE學(xué)號(hào)IN(SELECT學(xué)號(hào)FROMdeleted) END觸發(fā)器的創(chuàng)建創(chuàng)建DML觸發(fā)器時(shí)還可以同時(shí)創(chuàng)建多個(gè)類型的觸發(fā)器。【例7.15】在kcb3表中創(chuàng)建UPDATE和DELETE觸發(fā)器，當(dāng)修改或刪除kcb3表中的課程號(hào)字段時(shí)，同時(shí)修改或刪除cjb3表中的該課程號(hào)。語(yǔ)句如下：CREATETRIGGERkcb3_trig ONkcb3AFTERUPDATE,DELETE AS BEGIN IF(UPDATE(課程號(hào))) UPDATEcjb3SET課程號(hào)=(SELECT課程號(hào)FROMinserted) WHERE課程號(hào)=(SELECT課程號(hào)FROMdeleted) ELSE DELETEFROMcjb3 WHERE課程號(hào)IN(SELECT課程號(hào)FROMdeleted) END觸發(fā)器的創(chuàng)建6．創(chuàng)建INSTEADOF觸發(fā)器AFTER觸發(fā)器是在觸發(fā)語(yǔ)句執(zhí)行后觸發(fā)的，與之不同的是，INSTEADOF觸發(fā)器觸發(fā)時(shí)只執(zhí)行觸發(fā)器內(nèi)部的SQL語(yǔ)句，而不執(zhí)行激活該觸發(fā)器的SQL語(yǔ)句。一個(gè)表或視圖中只能有一個(gè)INSTEADOF觸發(fā)器?！纠?.16】創(chuàng)建表table1，值包含一列a，在表中創(chuàng)建INSTEADOFINSERT觸發(fā)器，當(dāng)向表中插入記錄時(shí)顯示相應(yīng)消息。語(yǔ)句如下：USExscjGOCREATETABLEtable1(aint)GOCREATETRIGGERtable1_insert ONtable1INSTEADOFINSERT AS PRINT'INSTEADOFTRIGGERISWORKING'向表中插入一行記錄，代碼如下。INSERTINTOtable1VALUES(10)SELECT*FROMtable1執(zhí)行結(jié)果：使用SELECT語(yǔ)句查詢表table1可以發(fā)現(xiàn)，table1中并沒有插入數(shù)據(jù)。觸發(fā)器的創(chuàng)建說明：（1）INSTEADOF觸發(fā)器的主要作用是使不可更新視圖支持更新。（2）若在一個(gè)多表視圖上定義了INSTEADOFINSERT觸發(fā)器，視圖各列的值可能允許為空，也可能不允許為空。（3）如果視圖的列為以下幾種情況之一，即基本表中的計(jì)算列、基本表中的標(biāo)識(shí)列、具有timestamp數(shù)據(jù)類型的基本表列，該視圖的INSERT語(yǔ)句必須為這些列指定值，INSTEADOF觸發(fā)器執(zhí)行將值插入基本表的INSERT語(yǔ)句時(shí)會(huì)忽略指定的值?！纠?.17】在xscj數(shù)據(jù)庫(kù)中創(chuàng)建視圖stu_view，包含學(xué)生學(xué)號(hào)、專業(yè)、課程號(hào)、成績(jī)。該視圖依賴于表xsb3和cjb3，是不可更新視圖?？梢栽谝晥D上創(chuàng)建INSTEADOF觸發(fā)器，當(dāng)向視圖中插入數(shù)據(jù)時(shí)分別向表xsb3和cjb3中插入數(shù)據(jù)，從而實(shí)現(xiàn)向視圖插入數(shù)據(jù)的功能。創(chuàng)建視圖的語(yǔ)句如下。CREATEVIEWstu_view AS SELECTxsb3.學(xué)號(hào),專業(yè),課程號(hào),成績(jī) FROMxsb3,cjb3 WHERExsb3.學(xué)號(hào)=cjb3.學(xué)號(hào)觸發(fā)器的創(chuàng)建創(chuàng)建INSTEADOF觸發(fā)器的語(yǔ)句如下。CREATETRIGGERInsteadTrig ONstu_viewINSTEADOFINSERT AS BEGIN DECLARE@xhchar(6),@xmchar(8),@zychar(12),@kchchar(3),@cjint SET@xm='佚名' SELECT@xh=學(xué)號(hào),@zy=專業(yè),@kch=課程號(hào),@cj=成績(jī) FROMinserted INSERTINTOxsb3(學(xué)號(hào),姓名,專業(yè)) VALUES(@xh,@xm,@zy) INSERTINTOcjb3VALUES(@xh,@kch,@cj) END觸發(fā)器的創(chuàng)建向視圖插入一行數(shù)據(jù)的語(yǔ)句如下。INSERTINTOstu_viewVALUES('231301','計(jì)算機(jī)','101',85)SELECT*FROMstu_viewWHERE學(xué)號(hào)='231301'SELECT*FROMxsb3WHERE學(xué)號(hào)='231301'查看stu_view視圖和與視圖關(guān)聯(lián)的xsb3基表數(shù)據(jù)是否插入，執(zhí)行結(jié)果如圖。觸發(fā)器的創(chuàng)建7．創(chuàng)建DDL觸發(fā)器其語(yǔ)法格式如下。CREATETRIGGER觸發(fā)器名 ONDATABASE|ALLSERVERAFTER事件類型|事件組 AS SQL語(yǔ)句說明：（1）DATABASE|ALLSERVER：DATABASE是指將當(dāng)前DDL觸發(fā)器的作用域應(yīng)用于當(dāng)前數(shù)據(jù)庫(kù)；ALLSERVER關(guān)鍵字是指將當(dāng)前DDL觸發(fā)器的作用域應(yīng)用于當(dāng)前服務(wù)器。（2）事件類型：執(zhí)行之后將導(dǎo)致觸發(fā)DDL觸發(fā)器的T-SQL語(yǔ)句事件的名稱。當(dāng)ON關(guān)鍵字后面指定DATABASE選項(xiàng)時(shí)使用該名稱。（3）事件組：預(yù)定義的T-SQL語(yǔ)句事件分組的名稱。ON關(guān)鍵字后面指定為ALLSERVER選項(xiàng)時(shí)使用該名稱，如CREATE_DATABAS