java web項目開發(fā)案例精粹-補技術(shù)sql server2000數(shù)據(jù)庫chap

8提高數(shù)據(jù)庫性句和流程控制語句的集合。就本質(zhì)而言，觸發(fā)器也是一種過程。過程在運算時生成執(zhí)行方式，所以，之后對其再運算時其執(zhí)行速度變快。SQLServer2000中提供了許多可作為工具使用的系統(tǒng)過程。本章將學習SQLServer2000中過觸發(fā)器的使用。重點講解過觸發(fā)器的創(chuàng)建、過程的參數(shù)使用。通過本章學習，讀者可掌握過觸發(fā)器的創(chuàng)建，掌握過觸發(fā)器的的管理，并能熟練的使用它們。為Student_info表創(chuàng)建過程Student_info_pro，該過程中包Student_id、Name、Address l段為Student_info表創(chuàng)建 過程Student_info_pro1，該過程根據(jù)傳為Student_info表創(chuàng)建一帶默認值的過程Student_info_pro2，用來查詢是20歲的同學信息。為Student_info表創(chuàng)建過程Student_info_pro3，用來返回學生的為Student_info表創(chuàng)建過程Student_info_pro4，該過程根據(jù)查1個應用系統(tǒng)似乎并不難，但是要想使系統(tǒng)達到最優(yōu)化的性能并不是一件容易的程度上發(fā)揮出SQL的潛能。它支持用戶提供的變量，可以返回、修改值，也可以將用戶8.1過程概Sybase公司80年代末期在它的SQLServer產(chǎn)品中首創(chuàng)了過程(StoredProcedures)使用過程還可以提高性能。過程開始執(zhí)行后，其中包含的SQL語句在數(shù)據(jù)庫服。而且過程實際上只是在第一次使用時被編譯，編譯后的映像和過程在，安全性高。通過對執(zhí)行某一過程的權(quán)限進行限制，從而實現(xiàn)對相應的數(shù)據(jù)訪問權(quán)限的限制，避免非用戶對數(shù)據(jù)的，保證數(shù)據(jù)的安全。 。當使用的過程的數(shù)量非常多時，每個 在 8.1.2過程的類系統(tǒng)過程就是系統(tǒng)創(chuàng)建的過程，目的在于能夠方便地從系統(tǒng)表中查詢信息或綴，在master數(shù)據(jù)庫中，為檢索系統(tǒng)表的信息提供快捷方式。盡管這些過程前加上數(shù)據(jù)庫名。而且在創(chuàng)建一個新的數(shù)據(jù)庫時，一些系統(tǒng)過程會在新數(shù)據(jù)庫中自動通過程，沒有前綴“sp_”。種過程稱為局部臨時過程，只能在一個用戶會話中使用；如果在本地過程前過程指服務器上的過程，也就是非本地服務器上的過程。8.2過程的創(chuàng)在一個過程中，可以使用任何SQL語句，但是不包括下面的語句：CREATE使用Transact-SQLCREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE|ENCRYPTION| PILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE|ENCRYPTION| PILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...nprocedure_name表示新過程的名稱。過程名必須符合標識符規(guī)則，且對于數(shù)(##)。完整的名稱（包括#或##）過128個字符。指定過程所有者的名稱orders的應用程序使用的過程可@parameter是過程中的參數(shù)?？梢砸粋€或多個參數(shù)。用戶必須在執(zhí)行過程時data_type是參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型均可以用作過程的參數(shù)。不過，cursorOUTPUTcursor，也必須同時指定VARYING和OUTPUT關鍵字。VARYING認值必須是常量或NULL。OUTPUT表明參數(shù)是返回參數(shù)。該選項的值可以返回給EXEC[UTE]。使用sql_statementSQL語句。n表示此過程可以包含任意數(shù)目和類型的Transact-SQL語句。 l這段【任務分析】使用CREATEPROC語句創(chuàng)建此過程，其兩個參數(shù)的值分別為 lUSEStudentCREATEPROCEDUREStudent_info_proSELECTstudent_id,name,Address, FROMStudent_info圖8-1使用Transact-SQL語句創(chuàng)建過程后在企業(yè)管理器中的顯在企業(yè)管理器其中用鼠標右鍵單擊要創(chuàng)建過程的數(shù)據(jù)庫下的“過程”選項，彈出快捷菜單，如圖8-2所示。8-2彈出快在彈出的快捷菜單中單擊“新建過程”命令，打開“過程屬性”如果要檢查用于創(chuàng)建過程的語法，可以單擊“檢查語法”按鈕。若腳本中沒有語法錯誤，將會彈出提示“語法檢查成功”的框，單擊“確定”按鈕，保存\administrator，PROCEDURENAME=Student_info_pro。過程的執(zhí)行語句為select lfromStudent_info。在企業(yè)管理器其中用鼠標右鍵單擊Student數(shù)據(jù)庫下的“過程”選項，在彈出快捷菜單，單擊“新建過程”命令，打開“過程屬性”框。在“文本”列的語句，如圖8-3。圖8-3“過程屬性”查成功”的框，如圖8-4所示。單擊“確定”按鈕，保存過程定義。圖8-4“語法檢查成功”的框，在該框中選中“數(shù)據(jù)庫”下的“創(chuàng)建過程向?qū)А边x項。單擊“確定”按單擊“下一步”按鈕，打開“正在完成創(chuàng)建過程向?qū)А笨?，該框中用于確認過程信息。在該框中選中其中一個過程，單擊“編輯”按鈕，打開“編輯過程屬性”框，在該框中的“名稱”文本框中修改該過程的名單擊“編輯SQL”按鈕，彈出“編輯過程SQL”框，用戶可以在已Student_info，操作可何一項或全部選擇，在“編輯過程SQL”框中將PROCEDURENAME改為Student_info_pro，將操作改為查詢 l和Address段的值?？颍谠摽蛑羞x中“數(shù)據(jù)庫”下的“創(chuàng)建過程向?qū)А边x項，如圖8-5所示。圖8-5“選擇向?qū)А?圖8-6“歡迎使用創(chuàng)建過程向?qū)А狈Q”下拉列表中選擇Student數(shù)據(jù)庫，如圖8-7所示?！币约霸摫砗蟮牟迦?、刪除和更新復選框，如圖8-8所示?！眻D8-7“選擇數(shù)據(jù)庫” 圖8-8“創(chuàng)建過程向?qū)А笨蛑羞x中insert_Student_info_1過程，如圖8-9所示。單擊“編輯”按鈕，打開“編輯過程屬性”框，在該框中的“名 圖8-9“正在完成創(chuàng)建過程向?qū)А?圖8-10“編輯過程屬性”如圖8-11：8-完成對過程的創(chuàng)建。如圖8-11。8.3執(zhí)行過過程可以由應用程序通過一個調(diào)用來執(zhí)行，而且允許用戶變量和有條件執(zhí)可用EETE句來執(zhí)這個過程。EXECUTE語句的語法格式如下：[[[EXEC[UTE]{[[@return_status={procedure_name[;number]|}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][ PILEEXECUTE語句中各參數(shù)的含義如下：@return_status是一個可選的整型變量，保存 于EXECUTE語句前，必須在批處理、 加上符號(@)。在以“@parameter_namevalue”格式使用時，參數(shù)名稱和常量不一定按照CREATEPROCEDURE語句中定義的順序出現(xiàn)。value是過程中參數(shù)的值。如果參數(shù)名稱沒有指定，參數(shù)值必須以CREATEPROCEDURE語句中定義的順序給出。OUTPUT指定過程必須返回一個參數(shù)。該過程的匹配參數(shù)也必須由關鍵字OUTPUT創(chuàng)建。 PILE強制編譯新的計劃。該選項不能用于擴展過程。建議盡【任務8-4】通過執(zhí)行名為Student_info_pro的過程，查看Student_info表中【任務分析】使用EXECUTE語句來執(zhí)行這個過程，其參數(shù)的值USEUSEStudentEXECUTE圖8-12執(zhí)行過8.4過程的參從過程中返回。輸入?yún)?shù)必須以@提示符開始，而且這些參數(shù)必須是Transcat-SQL的合法數(shù)據(jù)類型。輸出參數(shù)也必須以@提示符開始。另外，OUTPUT關鍵字必須緊跟著輸出【任務8-5】前面創(chuàng)建的過程是顯示表中的所有記錄，下面創(chuàng)建另一個過【任務分析】使用CREATEPROC語句創(chuàng)建此過程，其各參數(shù)的值分別為：SELECTStudent_id,Name,Address,lFROMStudent_infoWHERESex=@Sex_pro。USEUSEStudentCREATEPROCEDUREStudent_info_pro1@Sex_prochar(2)SELECTStudent_id,Name,Address, FROMStudent_infoWHERESexEXECUTE'男8-13使用參數(shù)傳有默認參數(shù)的過程，并使用一個默認值來執(zhí)行過程?！救蝿?-6】創(chuàng)建一個帶有默認值參數(shù)的過程Student_info_pro2，用來查詢Student_info表中字段為20的記錄?！救蝿辗治觥渴褂肅REATEPROC語句創(chuàng)建此過程，其各參數(shù)的值分別為=CREATECREATEPROCEDUREStudent_info_pro2(@ageInt=20)WHEREAge=@ageexec8-量。下面將創(chuàng)建帶OUTPUT的返回參數(shù)的過程?！救蝿辗治觥咳蝿?-7涉及到CREATEPRODE語句的應用，過程（procedure_name）為Student_info_pro2；過程參數(shù)@id用來傳入學生編號；過程參數(shù)@addressOUTPUT類型，用以返回學生地址信息；sql語句為根據(jù)輸入的參數(shù)查詢學生USEUSEStudentCREATEPROCEDUREStudent_info_pro3@idInt,@addressVARCHAR(50)OUTPUTSELECT@address=addressFROMStudent_infoWHEREDECLAREDECLARE@address1EXECUTE1002,@address=@address1OUTPUTPRINT@address18-15使用OUTPUT的返回參RETURN0~-14OUTPUT參數(shù)面將創(chuàng)建帶RETURN的過程?！救蝿?-8】創(chuàng)建過程Student_info_pro4，該過程為帶有RETURN語句和參且這一字段值所對應記錄的Address字段值為“唐山”，該過程的返回值為error。Student_info_pro4，過程參數(shù)（@parameter）為@Student_id，用來傳入學生編號信息；RETURN語句根據(jù)查詢情況返回執(zhí)行狀態(tài)。USEUSEStudentCREATEPROCEDUREStudent_info_pro4@Student_idint(4)(SELECTAddress(SELECTAddressWHEREStudent_id@Student_id)=’唐山RETURN@@ERROR8-16RETURN語8.5過程的查看、修改和刪在數(shù)據(jù)庫建立一個過程后，可以查看該過程的定義和相關性等。如果對已存在的過程不滿意，可以對該過程進行修改。而對于不再需要的過程，可以將8.5.1過程的查創(chuàng)建過程后，可以用企業(yè)管理器或有關的系統(tǒng)過程來查看該過程的信在企業(yè)管理器中，選擇相應數(shù)據(jù)庫下的“過程”選項，在詳細信息列表框中右鍵單擊要查看的過程，在彈出的快捷菜單中單擊“屬性”命令，打開“過程屬性”框，在該框中列出了過程的定義信息。這里可以通過單擊“權(quán)限”按在企業(yè)管理器中，選擇相應數(shù)據(jù)庫下的“過程”選項，在詳細信息列表框Student_infoStudent_info_pro過程，在彈出的快捷菜單中單擊“屬性”命令，打開“過程屬性”框，在該框中列出了過程的定義信息，如圖8-17所示。在企業(yè)管理器中，用鼠標右鍵單擊Student數(shù)據(jù)庫下Student_info過程，在彈出的快捷菜單中依次選擇“所有任務”|“顯示相關性”選項，打開“相關性”框，在該框可以查看依附于該過程的對象和該過程依附的對象，如圖8-18所示。圖8-17“過程屬性” 圖8-18“相關性”[EXECUTE]sp_help[EXECUTE]sp_help【任務分析】要查看的過程維Student_info_pro，使用sp_helptext查看其定義，使用sp_help查看其參數(shù)，使用sp_depends查看其相關性，這三個系統(tǒng)過程的參數(shù)都是USEUSEsp_helptextStudent_info_prosp_helpStudent_info_pro圖8-19查看過程的定義、參數(shù)和相關8.5.2過程的修在創(chuàng)建過程后，用戶可能需要對過程進行修改。修改過程通常指編輯它的參數(shù)和SQL語句。在企業(yè)管理器中，選擇相應數(shù)據(jù)庫下的“過程”選項，在詳細信息列表框中右鍵單擊要修改的過程，然后選擇“屬性”選項，打開“過程屬性”框。在“文本”編輯框中編輯過程的參數(shù)和SQL語句但是不能修改CREATEPROCEDURE如果要設置過程的權(quán)限，可通過單擊“權(quán)限”按鈕，打開“對象屬性”對話框，如圖8-20所示。在此可指定過程的權(quán)限。圖8-20“對象屬性”l l字段去掉。在企業(yè)管理器中，選擇Student數(shù)據(jù)庫下的“過程”選項，在詳細信息列表框中右鍵單擊Student_info_pro過程，然后選擇“屬性”選項，打開“過程屬性”框。在“文本”編輯框中的SELECT后的 l字段去掉，如圖8-21所示。（與圖8-17進行對比）。單擊“確定”按鈕，Student_info_pro過程修改成功。圖8-21修改過除了使用企業(yè)管理器外，還可以使用ALTERPROCEDURE語句修改過程，但該語句不會更改權(quán)限，也不影響相關的過程。其語法格式與創(chuàng)建過程的語法格式相ALTERALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE| PILE,}][FORREPLICATION]sql_statement[...nVARYING為(procedure_name)Student_info_pro，過程要執(zhí)行的sql語句為查詢Student_info表中的Student_id，Name和Address三個字段。USEUSEStudentALTERPROCEDUREStudent_info_proFROMStudent_info8.5.3過程的刪 DROPPROCEDURE{procedure}[,...nDROPPROCEDURE{procedure}[,...nDROPPROCEDURE語句中參數(shù)的procedure是要刪除的過程或過程組的名稱。過程名稱必須符合標識符規(guī)觸發(fā)器的CHECK約束更為復雜的觸發(fā)器可以通過數(shù)據(jù)庫中的相關表進行級聯(lián)修改。例如，可以在pubs表的id列上作為唯一鍵，在title、sales及emp表中對各匹配行進行定位。CHECKDROPLOADDISKDISKLOAD觸發(fā)器的CREATETRIGGER語句來完成。觸發(fā)器定義語句的INSERT、DELETE、UPDATE語Student，數(shù)據(jù)表為Student_infoStudent_info_tri，執(zhí)行語句為輸出字符串“操作已執(zhí)行”。Student_info表，在彈出的快捷菜單中選擇“所有任務”|“管理觸發(fā)器”命令，TRIGGERNAMEStudent_info_triAS后輸入“PRINT'數(shù)據(jù)插入'”，如圖8-23所示。圖8-23“觸發(fā)器屬性”CREATECREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION][{IFUPDATE(column[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask{comparison_operator}column_bitmask[...n}sql_statement[...n}}AFTERSQL語句中指定的所有操作都已成功執(zhí)行后才激UPDATE指明是UPDATE觸發(fā)器，每UPDATE語句修改OF子句指定的列值DBMS都將激發(fā)觸發(fā)器。WITHAPPEND指定應該添加現(xiàn)有類型的其它觸發(fā)器。WITHAPPEND不能與NOTFORREPLICATION表示當進程更改觸發(fā)器所涉及的表時，不應執(zhí)行該sql_statement是觸發(fā)器的條件和操作。觸發(fā)器條件指定其它準則，以確定DELETE、INSERT或UPDATE語句是否導致執(zhí)行觸發(fā)器操作。于DELETE操作?？梢灾付ǘ嗔小！救蝿辗治觥咳蝿?-14涉及到CREATETRIGGER的應用，其參數(shù)reae=efriae=osql_statement=PRINT‘操作已執(zhí)行’。。USEUSEStudentCREATETRIGGERStudent_info_triONStudent_infoAFTERINSERT,UPDATE,DELETE只能在當前的數(shù)據(jù)庫中創(chuàng)建，不過觸發(fā)器可以當前數(shù)據(jù)庫的外部對象。而CREATETRIGGER語句中，可以為多種用戶操作(INSERT和觸發(fā)器的inserteddeleted每個觸發(fā)器有兩個特殊的插入表（inserted表）和刪除表（deleted表，這兩個表是邏輯表，由系統(tǒng)管理，在內(nèi)存中，因此不允許用戶直接對其進行修改，當觸發(fā)器工作完inserted表和deleted表只能被創(chuàng)建它們的觸發(fā)器即它們的范圍僅限于該觸發(fā)器，6-1說明了在使用何種觸發(fā)器時，觸發(fā)6-1觸發(fā)器創(chuàng)建的INSERT是否DELETE觸發(fā)否是是是inserted表，該表映射了與該觸發(fā)器相連接的表的列的結(jié)構(gòu)。例如當用戶在A表中插入一行時，A表的觸發(fā)器使用A表的insertedAinserted表中也包含該行。刪除的每一行都包含在刪除觸發(fā)器內(nèi)的deleted表中。inserted表和deleted表，這兩個表和它們相連接的表有相同的列結(jié)構(gòu)。inserteddeleted表分別包含相連接表中數(shù)據(jù)的前后映射。例如，如果Ainserteddeleted表中所改變的每一行數(shù)據(jù)都在inserted表中行的數(shù)據(jù)值是執(zhí)行更新語句之后的A表中行的數(shù)據(jù)值。Inserted表中的新行，確定是否應該執(zhí)行以及如何執(zhí)行該插入【任務8-15】創(chuàng)建觸發(fā)器Student_info_ins，該觸發(fā)器能夠保證在學生信息表中添加新記USEUSEStudentCREATETRIGGERStudent_info_insONstudent_infoAFTERINSERTIFEXISTS(SELECT*FROMINSERTEDWHEREClass_idIN(SELECTClass_idROLLBACKTRANSACTIONINSERT ,20,'女運行后的結(jié)果如圖8-24所示。該圖中的字符串“數(shù)據(jù)插入”是進行INSERT操作時觸發(fā)了Student_info_tri觸發(fā)器產(chǎn)生的字符串數(shù)據(jù)已入”是進行INSERT操作時觸發(fā)了student_info_ins觸發(fā)器產(chǎn)生的字符串。8-24使用INSERT語句添加記據(jù)并且刪除一條舊的數(shù)據(jù)。UPDATEDeleted表中，將一個新行插入InsertedDeletedInserted表和被修改的表來【任務8-16】創(chuàng)建觸Student_info_upd，只有當對Student_info表中的數(shù)據(jù)執(zhí)行更【任務分析】使用CREATETRIGGER語句創(chuàng)建此過程，其參數(shù)信息如下：USEUSEStudentCREATETRIGGERStudent_info_updONStudent_infoAFTERUPDATEUpdateStudent_infoset ’where8-25創(chuàng)建和使用IPDATE觸發(fā)到Deleted表中。并檢查Deleted表中的行，以確定是否需要執(zhí)行觸發(fā)器操作或應如何執(zhí)行觸據(jù)變被在Deleted表中?！救蝿?-17】創(chuàng)建觸發(fā)器Student_info_del，當學生信息表中刪除某一條記錄時，留言【任務分析】任務817涉及到CREATETRIGGER語句的應用，=Student_info_del，table=Student_info，觸發(fā)事件為DELETE，執(zhí)