存储过程和触发器课件

存儲過程和觸發器

7.1存儲過程

7.1.1存儲過程的類型

(1)系統存儲過程系統存儲過程是由系統提供的存儲過程，作為命令執行各種操作。

(2)本地存儲過程本地存儲過程是指在用戶數據庫中創建的存儲過程，這種存儲過程完成特定資料庫操作任務，其名稱不能以sp_為首碼。(3)臨時存儲過程臨時存儲過程屬於本地存儲過程。如果本地存儲過程的名稱前面有一個“#”，該存儲過程就稱為局部臨時存儲過程，這種存儲過程只能在一個用戶會話中使用。(4)遠程存儲過程遠程存儲過程指從遠程伺服器上調用的存儲過程。(5)擴展存儲過程在SQLServer環境之外執行的動態鏈接庫稱為擴展存儲過程，其首碼是sp_。使用時需要先加載到SQLServer系統中，並且按照使用存儲過程的方法執行。7.1.2用戶存儲過程的創建與執行

在用戶存儲過程的定義中不能使用下列對象創建語句：CREATEVIEWCREATEDEFAULTCREATERULECREATEPROCEDURECREATETRIGGER1．通過SQL命令創建和執行存儲過程如果要通過SQL命令定義一個存儲過程查詢XSCJ資料庫中每個同學各門功課的成績，然後調用該存儲過程步驟如下：定義如下存儲過程USEXSCJGoCREATEPROCEDUREstudent_gradeASSELECTXS.學號,XS.姓名,KC.課程名,XS_KC.成績FROMXS,XS_KC,KCWHEREXS.學號=XS_KC.學號ANDXS_KC.課程號=KC.課程號Go7.1存儲過程使用存儲過程的優點：

(1)存儲過程在伺服器端運行，執行速度快。

(2)存儲過程執行一次後，其執行規劃就駐留在高速緩衝記憶體，在以後的操作中，只需從高速緩衝記憶體中調用已編譯好的二進位代碼執行，提高了系統性能。

(3)確保資料庫的安全。使用存儲過程可以完成所有資料庫操作，並可通過編程方式控制上述操作對數據庫資訊訪問的許可權。

(4)自動完成需要預先執行的任務。存儲過程可以在系統啟動時自動執行，而不必在系統啟動後再進行手工操作，大大方便了用戶的使用，可以自動完成一些需要預先執行的任務。7.1存儲過程

調用存儲過程EXECstudent_gradeGO通過上例瞭解了存儲過程的使用，下麵介紹創建和執行存儲過程的語法格式。1)創建存儲過程語法格式:CREATEPROC[EDURE]procedure_name[;number]/*定義過程名*/[{@parameterdata_type} /*定義參數的類型*/[VARYING][=default][OUTPUT]

] /*定義參數的屬性*/[,...n1][WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] /*定義存儲過程的處理方式*/[FORREPLICATION]ASsql_statement[...n2]/*執行的操作*/7.1存儲過程

對於存儲過程要注意下列幾點：(1)用戶定義的存儲過程只能在當前資料庫中創建（臨時過程除外，臨時過程總是在tempdb

中創建）。(2)成功執行CREATEPROCEDURE語句後，過程名稱存儲在sysobjects

系統表中，而CREATEPROCEDURE語句的文本存儲在syscomments

中。(3)自動執行存儲過程

SQLServer啟動時可以自動執行一個或多個存儲過程。這些存儲過程必須由系統管理員在master資料庫中創建，並在sysadmin

固定伺服器角色下作為後臺過程執行。(4)sql_statement的限制除了SETSHOWPLAN_TEXT和SETSHOWPLAN_ALL外，其他SET語句均可在存儲過程內使用。(5)許可權。CREATEPROCEDURE的許可權默認授予sysadmin固定伺服器角色成員、db_owner

和db_ddladmin

固定資料庫角色成員。7.1存儲過程2）存儲過程的執行通過EXEC命令可以執行一個已定義的存儲過程。語法格式：[EXEC[UTE]]

{

[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n][WITHRECOMPILE]｝存儲過程的執行要注意下列幾點：(1)如果存儲過程名的前三個字元為sp_，SQLServer會在Master資料庫中尋找該過程。如果沒能找到合法的過程名，SQLServer會尋找所有者名稱為dbo

的過程。(2)參數可以通過value或@parameter_name=value提供。(3)執行存儲過程時，若語句是批處理中的第一個語句，則不一定要指定EXECUTE關鍵字。7.1存儲過程3)舉例(1)設計簡單的存儲過程【例7.1】從XSCJ資料庫的三個表中查詢，返回學生學號、姓名、課程名、成績、學分。該存儲過程不使用任何參數。USEXSCJ/*檢查是否已存在同名的存儲過程，若有，刪除。*/IFEXISTS(SELECTnameFROMsysobjects

WHEREname='student_info'ANDtype='P')DROPPROCEDUREstudent_infoGO/*創建存儲過程*/CREATEPROCEDUREstudent_infoASSELECTa.學號,姓名,課程名,成績,學分

FROMXSaINNERJOINXS_KCbONa.學號=b.學號INNERJOINKCtONb.課程號=t.課程號GO7.1存儲過程(2)使用帶參數的存儲過程【例7.2】從XSCJ資料庫的三個表中查詢某人指定課程的成績和學分。該存儲過程接受與傳遞參數精確匹配的值。USEXSCJIFEXISTS(SELECTnameFROMsysobjects

WHEREname='student_info1'ANDtype='P') DROPPROCEDUREstudent_info1GOCREATEPROCEDUREstudent_info1 @namechar(8),@cnamechar(16)ASSELECTa.學號,姓名,課程名,成績,學分

FROMXSaINNERJOINXS_KCbONa.學號=b.學號INNERJOINKCtONb.課程號=t.課程號

WHEREa.姓名=@nameandt.課程名=@cnameGO7.1存儲過程(3)使用帶有通配符參數的存儲過程【例7.3】從三個表的聯接中返回指定學生的學號、姓名、所選課程名稱及該課程的成績。該存儲過程在參數中使用了模式匹配，如果沒有提供參數，則使用預設的默認值。USEXSCJIFEXISTS(SELECTnameFROMsysobjects

WHEREname='st_info'ANDtype='P') DROPPROCEDUREst_infoGOCREATEPROCEDUREst_info @namevarchar(30)='劉%'ASSELECTa.學號,a.姓名,c.課程名,b.成績

FROMXSaINNERJOINXS_KCb ONa.學號=b.學號INNERJOINKCc ONc.課程號=b.課程號

WHERE姓名LIKE@nameGO7.1存儲過程(4)使用帶OUTPUT參數的存儲過程【例7.4】用於計算指定學生的總學分，存儲過程中使用了一個輸入參數和一個輸出參數。USEXSCJGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='totalcredit'ANDtype='P')DROPPROCEDUREtotalcreditGOUSEXSCJGOCREATEPROCEDUREtotalcredit@namevarchar(40), @totalintOUTPUTASSELECT@total=SUM(學分) FROMXS,XS_KC,KC WHERE姓名=@nameANDXS.學號=XS_KC.學號

GROUPBYXS.學號GO7.1存儲過程(5)使用OUTPUT游標參數的存儲過程OUTPUT游標參數用於返回存儲過程的局部游標。【例7.5】在XSCJ資料庫的XS表上聲明並打開一個游標。USEXSCJIFEXISTS(SELECTnameFROMsysobjects

WHEREname='st_cursor'andtype='P') DROPPROCEDUREst_cursor GO CREATEPROCEDUREst_cursor@st_cursorCURSORVARYINGOUTPUT AS SET@st_cursor=CURSORFORWARD_ONLYSTATICFOR SELECT* FROMXS OPEN@st_cursor GO7.1存儲過程

(6)使用WITHENCRYPTION選項WITHENCRYPTION子句對用戶隱藏存儲過程的文本。【例7.6】創建加密過程，使用sp_helptext

系統存儲過程獲取關於加密過程的資訊，然後嘗試直接從

syscomments

表中獲取關於該過程的資訊。IFEXISTS(SELECTnameFROMsysobjectsWHEREname='encrypt_this'ANDtype='P')DROPPROCEDUREencrypt_thisGOUSEXSCJGOCREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMXSGO7.1存儲過程(7)創建用戶定義的系統存儲過程【例7.7】創建一個過程，顯示表名以xs

開頭的所有表及其對應的索引。如果沒有指定參數，該過程將返回表名以kc

開頭的所有表及對應的索引。IFEXISTS(SELECTnameFROMsysobjects WHEREname='sp_showtable'ANDtype='P')DROPPROCEDUREsp_showtableGOUSEmasterGOCREATEPROCEDUREsp_showtable@TABLEvarchar(30)='kc%'ASSELECTASTABLE_NAME,

ASINDEX_NAME,

indidASINDEX_ID FROMsysindexes

inxINNERJOINsysobjectstabONtab.id=inx.id

WHERELIKE@TABLEGOUSEXSCJEXECsp_showtable'xs%'GO7.1存儲過程2．利用企業管理器創建用戶存儲過程第1步在SQLServer企業管理器窗口中，選擇相應的伺服器、資料庫和存儲過程圖示（本例選擇XSCJ資料庫），單擊滑鼠右鍵，出現如圖7.1所示的快捷菜單。第2步選擇新建存儲過程，則出現編輯存儲過程的屬性窗口，在窗口中輸入定義的存儲過程，如圖7.2所示，然後選擇“確定”按鈕。7.1.3用戶存儲過程的編輯修改

語法格式：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][0=default][OUTPUT]][,...n1][WITH

{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}][FORREPLICATION]AS

sql_statement[

...n2]【例7.8】對存儲過程student_info1進行修改。USEXSCJGOALTERPROCEDUREstudent_info1 @namechar(8),@cnamechar(16)ASSELECTa.學號,姓名,課程名,成績,學分

FROMXSaINNERjoinXS_KCb ONa.學號=b.學號INNERJOINKCt ONb.課程號=t.課程號

WHEREa.姓名=@nameandt.課程名=@cnameGO7.1.3用戶存儲過程的編輯修改【例7.9】創建名為select_students

的存儲過程，默認情況下，該過程可查詢所有學生資訊，隨後授予許可權。USEXSCJGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='select_students'ANDtype='P')DROPPROCEDUREselect_students /*若該存儲過程已存在，則刪除*/GOUSEXSCJGOCREATEPROCEDUREselect_students /*創建存儲過程*/ASSELECT* FROMXS ORDERBY學號GO7.1.4用戶存儲過程的刪除

使用DROPPROCEDURE語句可永久地刪除存儲過程。在此之前，必須確認該存儲過程沒有任何依賴關係。語法格式：DROPPROCEDURE{procedure}[,...n]【例7.10】刪除XSCJ資料庫中的student_info1存儲過程。USEXSCJGODROPPROCEDUREstudent_info1procedure指要刪除的存儲過程或存儲過程組的名稱；n：表示可以指定多個存儲過程同時刪除。7.2.1利用SQL命令創建觸發器

語法格式CREATETRIGGERtrigger_name

ON{table|view} /*指定操作對象*/[WITHENCRYPTION] /*說明是否採用加密方式*/{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}

[WITHAPPEND]

[NOTFORREPLICATION] /*說明該觸發器不用於複製*/

AS

[{IFUPDATE(column)

[{AND|OR}UPDATE(column)]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask

)

{comparison_operator}column_bitmask[...n]

}] /*兩個IF子句用於說明觸發器執行的條件*/

sql_statement[

...n] /*一條或若干條SQL語句*/

}}

7.2.1利用SQL命令創建觸發器2．觸發器中使用的特殊表

inserted邏輯表：當向表中插入數據時，INSERT觸發器觸發執行，新的記錄插入到觸發器表和inserted表中。

deleted邏輯表：用於保存已從表中刪除的記錄，當觸發一個DELETE觸發器時，被刪除的記錄存放到deleted邏輯表中。3．使用觸發器的限制使用觸發器有下列限制：(1)CREATETRIGGER必須是批處理中的第一條語句，並且只能應用到一個表中。(2)觸發器只能在當前的資料庫中創建，但觸發器可以引用當前資料庫的外部對象。(3)如果指定觸發器所有者名限定觸發器，要以相同的方式限定表名。(4)在同一CREATETRIGGER語句中，可以為多種操作（如INSERT和UPDATE）定義相同的觸發器操作。(5)一個表的外鍵在DELETE、UPDATE操作上定義了級聯，不能在該表上定義INSTEADOFDELETE、INSTEADOFUPDATE觸發器。7.2.1利用SQL命令創建觸發器(7)在觸發器內可以指定任意的SET語句，所選擇的SET選項在觸發器執行期間有效，並在觸發器執行完後恢復到以前的設置。

(8)觸發器中不允許包含以下T-SQL語句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE(9)觸發器不能返回任何結果，為了阻止從觸發器返回結果，不要在觸發器定義中包含SELECT語句或變數賦值。4．許可權CREATETRIGGER許可權默認授予定義觸發器的表所有者、sysadmin

固定伺服器角色成員、db_owner

和db_ddladmin

固定資料庫角色成員，並且不可轉讓。7.2.1利用SQL命令創建觸發器5．舉例【例7.11】對於XSCJ資料庫，如果在XS表中添加或更改數據，則將向客戶端顯示一條資訊。/*使用帶有提示消息的觸發器*/USEXSCJIFEXISTS(SELECTnameFROMsysobjects WHEREname='reminder'ANDtype='TR')DROPTRIGGERreminderGOCREATETRIGGERreminderONXS FORINSERT,UPDATEASRAISERROR(4008,16,10)GO7.2.1利用SQL命令創建觸發器【例7.12】在資料庫XSCJ中創建一觸發器，當向XS_KC表插入一記錄時，檢查該記錄的學號在XS表是否存在，檢查課程號在KC表中是否存在，若有一項為否，則不允許插入。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_trig'ANDtype='TR')DROPTRIGGERcheck_trigGOCREATETRIGGERcheck_trig ONXS_KC FORINSERTASSELECT* FROMinserteda WHEREa.學號NOTIN(SELECTb.學號FROMXSb)ORa.課程號NOTIN(SELECTc.課程號FROMKCc)BEGIN RAISERROR('違背數據的一致性.',16,1) ROLLBACKTRANSACTIONEND7.2.1利用SQL命令創建觸發器【例7.13】在XSCJ資料庫的XS_KC表上創建一觸發器，若對學號列和課程號列修改，則給出提示資訊，並取消修改操作。USEXSCJGOCREATETRIGGERupdate_trig ONXS_KC FORupdateAS/*檢查學號列（C0）和課程號列（C1）是否被修改，如果有某些列被修改了，則取消修改操作。*/IF(COLUMNS_UPDATED()&3)>0 BEGIN RAISERROR('違背數據的一致性.',16,1) ROLLBACKTRANSACTION ENDGO7.2.1利用SQL命令創建觸發器6.INSTEADOF觸發器的設計如果視圖的數據來自於多個基表，則必須使用INSTEADOF觸發器支持引用表中數據的插入、更新和刪除操作。如果視圖的列為以下幾種情況之一：（1）基表中的計算列。（2）IDENTITYINSERT為OFF的基表中的標識列。（3）具有timestamp數據類型的基表列。該視圖的INSERT語句必須為這些列指定值，INSTEADOF觸發器在構成將值插入基表的INSERT語句時會忽略指定的值。【例7.14】在XSCJ資料庫中創建表、視圖和觸發器，以說明INSTEADOFINSERT觸發器的使用。USEXSCJCREATETABLEbooks(BookKey

intIDENTITY(1,1),

BookNamenvarchar(10)NOTNULL,Colornvarchar(10)NOTNULL,

ComputedColAS(BookName+Color),Pagesint)GO/*建立一個視圖，包含基表的所有列*/CREATEVIEWView2ASSELECTBookKey,BookName,Color,ComputedCol,Pages FROMbooksGO/*在View2視圖上創建一個INSTEADOFINSERT觸發器*/CREATETRIGGERInsteadTrigonView2 INSTEADOFINSERTASBEGIN/*實際插入時，INSERT語句中不包含BookKey字段和.ComputedCol.字段的值*/ INSERTINTObooks SELECTBookName,Color,PagesFROMinsertedENDGO7.2.2利用企業管理器創建觸發器

步驟如下：第1步展開伺服器組，然後展開伺服器。第2步展開“資料庫”檔夾，展開將定義觸發器的表所屬的資料庫，然後單擊“表”檔夾。第3步選擇將在其上創建觸發器的表右擊，出現快捷菜單，選擇“所有任務”菜單項下的“管理觸發器”子菜單項，如圖7.1所示。執行該命令後，進入如圖7.2所示的介面。第4步在“名稱”中，單擊“新建”，在“文本”框中輸入觸發器文本。若要檢查語法，單擊“檢查語法”命令。7.2.3觸發器的修改1．利用SQL命令修改觸發器語法格式：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{

{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}

[N