SQLServer2022數(shù)據(jù)庫技術(shù)項目教程 課件 胡伏湘 項目6-8 數(shù)據(jù)庫的編程操作-數(shù)據(jù)庫應用程序開發(fā)項目實戰(zhàn)

項目六數(shù)據(jù)庫的編程操作任務6.1建立視圖任務6.2游標的創(chuàng)建與應用任務6.3存儲過程的創(chuàng)建與管理任務6.4觸發(fā)器的創(chuàng)建與管理任務6.5索引與事務的應用12345任務6.1建立視圖6.1.1創(chuàng)建視圖6.1.2應用視圖6.1.3修改視圖建立視圖6.1.1創(chuàng)建視圖視圖View是數(shù)據(jù)庫的另一種對象，與表的級別相同，以表的方式顯示，有列名和若干行數(shù)據(jù)。視圖是一個虛擬表，它的數(shù)據(jù)來源于表（一個或多個表，稱為基表），甚至是視圖，其內(nèi)容由SELECT語句定義。視圖就如同一張表一樣，對表能夠進行的一般操作都可以應用于視圖，例如查詢、插入、修改、刪除操作等。任務6.1建立視圖6.1.1創(chuàng)建視圖

視點集中視圖相當于提供了一個特定的“窗口”，用戶所看到的數(shù)據(jù)只跟用戶的需求有關系。1234方便操作視圖可以將幾個表的數(shù)據(jù)集中到一起，對該視圖的操作相當于對表操作。數(shù)據(jù)安全通過視圖，用戶只能查詢和修改他們所能見到的數(shù)據(jù)，但不能看到其它沒有權(quán)限的數(shù)據(jù)或者敏感信息。定制數(shù)據(jù)視圖能夠?qū)崿F(xiàn)讓不同的用戶以不同的方式看到不同或相同的數(shù)據(jù)集。使用視圖四大優(yōu)點任務6.1建立視圖6.1.1創(chuàng)建視圖視圖分為兩類：用戶視圖和系統(tǒng)視圖，前者是由用戶建立的，后者是由系統(tǒng)自動建立的，伴隨數(shù)據(jù)庫存在。在系統(tǒng)視圖中，一種以INFORMATION_SCHEMA開頭，視圖名全部用大寫字母表示，表示與系統(tǒng)信息和模式相關，初始記錄為空；還有一種以sys（系統(tǒng)）開頭，視圖名全部用小寫字母表示，記錄了當前數(shù)據(jù)庫的數(shù)據(jù)信息。系統(tǒng)視圖不可以修改，用戶視圖不可以修改表結(jié)構(gòu)，但可以進行增刪改操作。系統(tǒng)視圖sys.all_objects的記錄內(nèi)容如下圖所示：任務6.1建立視圖6.1.1創(chuàng)建視圖視圖的創(chuàng)建方法有兩種：一種是使用SSMS管理器窗口，另一種是使用SQL命令。本任務的例題均以libsys數(shù)據(jù)庫為例。使用SSMS管理器窗口創(chuàng)建視圖主要包括篩選表及字段、輸入條件、設置視圖名等步驟。任務6.1建立視圖6.1.1創(chuàng)建視圖例6-1創(chuàng)建視圖View1_bookInfo，其功能是存儲bookInfo表中由電子工業(yè)出版社出版的圖書的編號、名稱、類型、作者姓名、出版單位、銷售價格、采購數(shù)量和庫存數(shù)量。第1步，添加表。在SSMS管理器窗口中依次展開“數(shù)據(jù)庫”→“l(fā)ibsys”節(jié)點，右擊“視圖”節(jié)點，在彈出的快捷菜單中選擇“新建視圖...”命令，在彈出的“添加表”對話框的“表”選項卡中選擇bookInfo表，如圖所示，單擊“添加”按鈕，任務6.1建立視圖6.1.1創(chuàng)建視圖第2步，選擇視圖包含的列。以粗體格式顯示的列名表示該列為主鍵，*代表全部列。選擇BookID、BookName、BookType、Writer、Publisher、Price、BuyCount、AbleCount共8個列。任務6.1建立視圖6.1.1創(chuàng)建視圖第3步，設置篩選表達式。在“Publisher”所在行的“篩選器”文本框中輸入篩選條件“='電子工業(yè)出版社'”。任務6.1建立視圖6.1.1創(chuàng)建視圖第4步，輸入視圖名，保存視圖。單擊工具欄中的“保存”按鈕，在彈出的“選擇名稱”對話框的“輸入視圖名稱”文本框中輸入視圖名“View1_bookInfo”，然后單擊“確定”按鈕。第5步，顯示視圖的內(nèi)容。在代碼窗口中右擊，在彈出的快捷菜單中選擇“執(zhí)行SQL”命令，或者在“對象資源管理器”窗口中右擊視圖名View1_bookInfo，在彈出的快捷菜單中選擇“選擇前1000行”命令，即可顯示視圖的內(nèi)容。任務6.1建立視圖6.1.1創(chuàng)建視圖用Transact-SQL命令創(chuàng)建視圖:CREATEVIEW[schema_name.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[WITHCHECKOPTION][;]<view_attribute>::={[ENCRYPTION][SCHEMABINDING][VIEW_METADATA]}任務6.1建立視圖6.1.1創(chuàng)建視圖格式說明:（1）schema_name：視圖所屬架構(gòu)的名稱。（2）view_name：視圖名稱。視圖名稱必須符合有關標識符的規(guī)則。（3）column：視圖中的列使用的名稱。（4）AS：指定視圖要執(zhí)行的操作。（5）select_statement：定義視圖的SELECT語句，該語句可以使用多個表和其他視圖。（6）WITHCHECKOPTION：對視圖進行UPDATE、INSERT和DELETE操作時，要保證更新、插入或刪除的記錄滿足視圖中SELECT語句的條件表達式。（7）可以使用WITHENCYPTION對存放的CREATEVIEW的文本加密。（8）SCHEMABINDING：將視圖綁定到基礎表的架構(gòu)。（9）VIEW_METADATA：指定為引用視圖的查詢請求瀏覽模式的元數(shù)據(jù)時，SQLServer實例將向DB-Library、ODBC和OLEDBAPI返回有關視圖的元數(shù)據(jù)信息。任務6.1建立視圖6.1.1創(chuàng)建視圖例6-2創(chuàng)建視圖View2_TeacherReader，其功能是獲得所有教師讀者的借書證號、姓名、所在部門、讀者類型、聯(lián)系電話和電子郵箱。CREATEVIEWView2_TeacherReaderASSELECTReaderID,ReaderName,Department,ReaderType,Mobile,EmailFROMreaderInfoWHEREReaderType='教師'任務6.1建立視圖6.1.1創(chuàng)建視圖例6-3創(chuàng)建視圖View3_NoReturnReader，其功能是獲取當前尚未歸還圖書的圖書編號、圖書名稱、讀者姓名、讀者類型、借書日期、應歸還日期和實際歸還日期。要求將CREATEVIEW語句的原始文本轉(zhuǎn)換為模糊格式（可以通過WITHENCRYPTION實現(xiàn)）。CREATEVIEWView3_NoReturnReaderWITHENCRYPTIONASSELECTbk.BookID,bk.BookName,rd.ReaderName,rd.ReaderType,br.BorrowDate,br.Deadline,br.ReturnDateFROMborrowInfobrINNERJOINbookInfobkONbr.BookID=bk.BookIDINNERJOINreaderInfordONbr.ReaderID=rd.ReaderIDWHEREbr.ReturnDateISNULL任務6.1建立視圖6.1.2應用視圖視圖是一個虛擬表，本身并不存儲數(shù)據(jù)，它的數(shù)據(jù)來源于表，因此對視圖的操作實際上就是對表的操作。與表相比，使用視圖對表進行添加記錄、更新記錄和刪除記錄等操作會更加簡潔方便，命令格式與表操作的命令格式相同，只需將表名修改成視圖名即可。只要滿足以下條件，就可以通過視圖修改基表中的數(shù)據(jù)：（1）任何修改（包括UPDATE、INSERT和DELETE語句）都只能引用一個基表中的列。（2）視圖中正在修改的列必須直接引用表列中的基礎數(shù)據(jù)。（3）被修改的列不受GROUPBY子句、HAVING子句或DISTINCT子句的影響。（4）在視圖的查詢語句中，TOP子句不能與WITHCHECKOPTION子句一起使用。任務6.1建立視圖6.1.2應用視圖1、通過視圖插入表數(shù)據(jù)例6-4利用例6-1所建立的視圖View1_bookinfo（功能是存儲表bookinfo中電子工業(yè)出版社出版的圖書的相關信息）向表bookinfo添加一條記錄。INSERTINTOView1_bookInfoVALUES('9787121446795','SpringBoot實用教程','計算機','鄭阿奇','電子工業(yè)出版社',66.5,20,18)任務6.1建立視圖6.1.2應用視圖2、通過視圖更新表記錄使用UPDATE命令通過視圖可以更新基表中的記錄，該命令的要求與INSERT命令的要求相同，命令格式與更新表中記錄的命令格式相同。例6-5利用例6-1所創(chuàng)建的View1_bookInfo視圖修改bookInfo表中的記錄，要求將圖書類型（BookType字段）由“經(jīng)濟管理”修改為“經(jīng)管類”。UPDATEView1_bookInfoSETBookType='經(jīng)管類'WHEREBookType='經(jīng)濟管理'任務6.1建立視圖6.1.2應用視圖3、通過視圖刪除表記錄使用DELETE命令通過視圖可以刪除基表中的記錄，該命令的要求與INSERT命令的要求相同，命令格式與刪除表中記錄的命令格式相同。例6-6利用例6-1所創(chuàng)建的View1_bookInfo視圖刪除bookInfo表中圖書編號是9787121446795的記錄。DELETEFROMView1_bookinfoWHEREBookID='9787121446795'任務6.1建立視圖6.1.3修改視圖1、修改視圖（1）使用SSMS管理器窗口修改視圖例6-7修改View1_bookInfo視圖，將其功能修改為存儲bookInfo表中由清華大學出版社出版的圖書的編號、名稱、類型、作者姓名、出版單位和銷售價格。第1步，在SSMS管理器窗口中依次展開“數(shù)據(jù)庫”→“l(fā)ibsys”→“視圖”節(jié)點。第2步，右擊要修改的視圖的名稱View1_bookInfo，在彈出的快捷菜單中選擇“設計”命令，打開查詢設計器的圖表窗格。第3步，在查詢設計器的圖表窗格中，通過以下一種或多種方式修改視圖：任務6.1建立視圖6.1.3修改視圖勾選要添加的元素的復選框，或者取消勾選要刪除的元素的復選框。在圖表窗格中右擊，在彈出的快捷菜單中選擇“添加表”命令，然后在彈出的“添加表”對話框的“表”選項卡中選擇要添加的表，單擊“添加”按鈕，然后關閉該對話框。系統(tǒng)會自動將該表中的所有列顯示出來，此時可以選擇要添加到視圖的列。右擊要刪除的表的標題欄，在彈出的快捷菜單中選擇“刪除”命令。這里取消BuyCount列和AbleCount列的選中狀態(tài)，在“Publisher”所在行的“篩選器”文本框中輸入篩選條件“='清華大學出版社'”。第4步，單擊工具欄中的“保存”按鈕保存視圖。任務6.1建立視圖6.1.3修改視圖（2）使用Transact-SQL命令修改視圖ALTERVIEW[schema_name.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[WITHCHECKOPTION][;]<view_attribute>::={[ENCRYPTION][SCHEMABINDING][VIEW_METADATA]}任務6.1建立視圖6.1.3修改視圖例6-8修改View2_TeacherReader視圖，將其功能修改為獲得所有教師讀者的借書證號、姓名、性別、聯(lián)系電話和電子郵箱。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下SQL命令，然后單擊“執(zhí)行”按鈕。ALTERVIEWView2_TeacherReaderASSELECTReaderID,ReaderName,ReaderSex,Mobile,EmailFROMdbo.readerinfoWHERE(ReaderType='教師')任務6.1建立視圖6.1.3修改視圖3、刪除視圖（1）使用SSMS管理器窗口刪除視圖例6-9刪除視圖View1_bookinfo。第1步，在SSMS管理器窗口中依次展開“數(shù)據(jù)庫”→“l(fā)ibsys”→“視圖”節(jié)點。第2步，右擊要刪除的視圖的名稱View1_bookInfo，在彈出的快捷菜單中選擇“刪除”命令。第3步，在彈出的“刪除對象”對話框中單擊“確定”按鈕。任務6.1建立視圖6.1.3修改視圖3、刪除視圖（2）使用Transact-SQL命令修改視圖刪除視圖的命令格式如下：DROPVIEW[IFEXISTS][schema_name.]view_name[...,n][;]說明：IFEXISTS：如果視圖存在，則刪除。schema_name：視圖所屬架構(gòu)的名稱。view_name：要刪除的視圖的名稱。任務6.1建立視圖6.1.3修改視圖例6-10刪除視圖View2_TeacherReader。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下SQL命令，然后單擊“執(zhí)行”按鈕。DROPVIEWView2_TeacherReader任務6.1任務6.2游標的創(chuàng)建與應用

6.2.1游標的創(chuàng)建6.2.2游標的應用6.2.3關閉與釋放游標游標的創(chuàng)建與應用6.2.1游標的創(chuàng)建關系型數(shù)據(jù)庫中的操作會對整個行集起作用。這種由語句返回的完整行集稱為結(jié)果集，應用程序并不總能將整個結(jié)果集作為一個單元進行有效的處理。這些應用程序需要一種機制以便每次處理一行或一部分行，游標可以滿足上述要求。游標的基本操作包括創(chuàng)建游標、打開游標、循環(huán)讀取游標、關閉游標和刪除游標。任務6.2游標的創(chuàng)建與應用6.2.1游標的創(chuàng)建創(chuàng)建游標的命令格式是：DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]][;]任務6.2游標的創(chuàng)建與應用6.2.1游標的創(chuàng)建格式說明：（1）cursor_name：游標名稱，游標名稱必須符合有關標識符的規(guī)則。（2）LOCAL：局部游標。該游標僅在創(chuàng)建它的批處理、存儲過程或觸發(fā)器作用域內(nèi)有效。（3）GLOBAL：全局游標。該游標在當前連接范圍內(nèi)均有效。如果GLOBAL和LOCAL參數(shù)都未指定，由數(shù)據(jù)庫選項中的“默認游標”值（GLOBAL或LOCAL）決定。（4）FORWARD_ONLY：指定游標只能向前移動，并從第一行滾動到最后一行。FETCHNEXT是唯一支持的提取選項。（5）STATIC：指定游標始終以第一次打開時的樣式顯示結(jié)果集，并制作數(shù)據(jù)的臨時副本，供游標使用。（6）KEYSET：指定當游標打開時，游標中行的成員身份和順序已經(jīng)固定。任務6.2游標的創(chuàng)建與應用6.2.1游標的創(chuàng)建（7）DYNAMIC：定義一個游標，無論更改是發(fā)生于游標內(nèi)部還是由游標外的其他用戶執(zhí)行，在你四處滾動游標并提取新紀錄時，該游標均能反映對其結(jié)果集中的行所做的所有數(shù)據(jù)更改。（8）FAST_FORWARD：指定已啟用了性能優(yōu)化的FORWARD_ONLY和READ_ONLY游標。如果還指定了SCROLL或FOR_UPDATE，則無法指定FAST_FORWARD。此類型的游標不允許從游標內(nèi)修改數(shù)據(jù)。（9）READ_ONLY：禁止通過該游標進行更新。無法在UPDATE或DELETE語句的WHERECURRENTOF子句中引用游標。（10）SCROLL_LOCKS：指定通過游標進行的定位更新或刪除一定會成功。任務6.2游標的創(chuàng)建與應用6.2.1游標的創(chuàng)建（11）OPTIMISTIC：指定如果行自讀入游標以來已得到更新，則通過游標進行的定位更新或定位刪除不成功。（12）TYPE_WARNING：指定如果游標從所請求的類型隱式轉(zhuǎn)換為另一種類型，則向客戶端發(fā)送警告消息。（13）select_statement：定義游標結(jié)果集的標SELEC語句。在游標聲明的select_statement中不允許使用關鍵字COMPUTE、COMPUTEBY、FORBROWSE和INTO。（14）FORUPDATE[OFcolumn_name[,...n]]：定義游標中可更新的列。如果提供了OF<column_name>[,<...n>]，則只允許修改所列出的列。如果指定了UPDATE，但未指定列的列表，則除非指定了READ_ONLY并發(fā)選項，否則可以更新所有的列。任務6.2游標的創(chuàng)建與應用6.2.1游標的創(chuàng)建例6-11創(chuàng)建游標book_cursor。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下SQL命令，然后單擊“執(zhí)行”按鈕。USElibsysGODECLAREbook_cursorCURSORFORSELECT*FROMbookinfo;任務6.2游標的創(chuàng)建與應用6.2.2游標的應用創(chuàng)建好游標以后，可以使用opencursor_name;打開游標，然后再使用FETCHcursor_name;檢索游標數(shù)據(jù)。1、使用游標提取數(shù)據(jù)FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]任務6.2游標的創(chuàng)建與應用6.2.2游標的應用（1）NEXT：返回緊跟在當前行之后的結(jié)果行，并將當前行增加到返回的行。如果FETCHNEXT為對游標的第一次提取操作，則返回結(jié)果集中的第一行。NEXT為默認的游標提取選項。（2）PRIOR：返回緊鄰當前行前面的結(jié)果行，并且當前行遞減為返回行。（3）FIRST：返回游標中的第一行并將其作為當前行。（4）LAST：返回游標中的最后一行并將其作為當前行。（5）ABSOLUTE{n|@nvar}：如果n或@nvar為正，則返回從游標起始處開始向后的第n行，并將返回行變成新的當前行。如果n或@nvar為負，則返回從游標末尾處開始向前的第n行，并將返回行變成新的當前行。如果n或@nvar為0，則不返回行。n必須是整數(shù)常量，并且@nvar必須是smallint、tinyint或int。任務6.2游標的創(chuàng)建與應用6.2.2游標的應用（6）RELATIVE{n|@nvar}：如果n或@nvar為正，則返回從當前行開始向后的第n行，并將返回行變成新的當前行。如果n或@nvar為負，則返回從當前行開始向前的第n行，并將返回行變成新的當前行。如果n或@nvar為0，則返回當前行。在對游標進行第一次提取時，如果在將n或@nvar設置為負數(shù)或0的情況下指定FETCHRELATIVE，則不返回行。n必須是整數(shù)常量，并且@nvar必須是smallint、tinyint或int。（7）GLOBAL：指定cursor_name引用全局游標。（8）cursor_name：要從中進行提取的開放游標的名稱。當同時存在以cursor_name作為名稱的全局游標和局部游標時，如果指定GLOBAL，則cursor_name指全局游標，如果未指定GLOBAL，則指局部游標。任務6.2游標的創(chuàng)建與應用6.2.2游標的應用（9）@cursor_variable_name：游標變量名，引用要從中進行提取操作的已經(jīng)打開的游標。（10）INTO@variable_name[,...n]：允許將提取操作的列數(shù)據(jù)放到局部變量中。列表中的各個變量從左到右與游標結(jié)果集中的相應列相關聯(lián)。各變量的數(shù)據(jù)類型必須與相應的結(jié)果集列的數(shù)據(jù)類型匹配，或是結(jié)果集列數(shù)據(jù)類型所支持的隱式轉(zhuǎn)換。變量的數(shù)目必須與游標選擇列表中的列數(shù)一致。任務6.2游標的創(chuàng)建與應用6.2.2游標的應用例6-12利用游標book_cursor每次從bookInfo表中獲取一條記錄。在例6-11所打開的“查詢編輯器”窗口中輸入以下命令：OPENbook_cursor--打開游標FETCHNEXTFROMbook_cursor;--獲取下一條記錄此時顯示bookInfo表中的第一條記錄，當需要顯示下一條記錄時，只需選中上述的“FETCHNEXTFROMbook_cursor;”語句，然后單擊“執(zhí)行”按鈕即可，重復執(zhí)行該步驟，可以依次顯示bookInfo表中的記錄。任務6.2游標的創(chuàng)建與應用6.2.2游標的應用例6-13利用游標逐條讀取讀者信息表中的記錄，每條記錄包括讀者編號、讀者姓名、讀者部門、讀者電話。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。USElibsysGO--聲明游標DECLAREreader_cursorCURSORFORSELECTReaderID,ReaderName,Department,MobileFROMreaderInfo;--打開游標任務6.2游標的創(chuàng)建與應用6.2.2游標的應用OPENreader_cursor--聲明局部變量，用來存儲FETCH語句返回的值DECLARE@idCHAR(10),@nameCHAR(10),@deptVARCHAR(30),@mobileVARCHAR(12);--讀取第一條記錄，把讀取到的數(shù)據(jù)保存到變量中FETCHNEXTFROMreader_cursorINTO@id,@name,@dept,@mobile;--循環(huán)讀取游標結(jié)果集中的記錄print'讀取游標數(shù)據(jù)如下：';任務6.2游標的創(chuàng)建與應用6.2.2游標的應用--判斷FETCH語句是否成功，如果成功，則@@fetch_status返回0，否則返回負數(shù)while(@@fetch_status=0)beginprint'借書證號：'+@id+',讀者姓名：'+@name+',所在部門：'+@dept+',聯(lián)系電話：'+@mobile;--繼續(xù)讀取下一條記錄

FETCHNEXTFROMreader_cursorINTO@id,@name,@dept,@mobile;END--關閉游標CLOSEreader_cursor;任務6.2游標的創(chuàng)建與應用6.2.2游標的應用執(zhí)行結(jié)果如下：任務6.2游標的創(chuàng)建與應用6.2.2游標的應用2、使用游標更新刪除數(shù)據(jù)（1）使用游標更新數(shù)據(jù)例6-14使用游標逐條將bookInfo表中BookType列內(nèi)的值由“經(jīng)濟管理”修改成“經(jīng)管類”第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。--聲明變量，用來存儲FETCH語句返回的值DECLARE@IdCHAR(20);--聲明游標DECLAREIdCursorCURSORFOR(SELECTBookIDFROMbookInfoWHEREbookType='經(jīng)濟管理')FORUPDATEOFBookType;--打開游標任務6.2游標的創(chuàng)建與應用6.2.2游標的應用OPENIdCursor;--獲取游標中的第一條記錄FETCHNEXTFROMIdCursorINTO@Id;--當成功獲取游標中的記錄時，循環(huán)執(zhí)行WHILE@@FETCH_STATUS=0BEGIN--逐條將BookType列中的值由“經(jīng)濟管理”修改成“經(jīng)管類”

UPDATEbookInfoSETBookType='經(jīng)管類'WHEREBookID=@Id;--移動游標到下一條記錄

FETCHNEXTFROMIdCursorINTO@Id;END;CLOSEIdCursor;任務6.2游標的創(chuàng)建與應用6.2.2游標的應用（2）使用游標刪除數(shù)據(jù)例6-15使用游標逐條刪除borrowInfo表中ReturnDate列內(nèi)的值為空的記錄。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。--聲明變量，用來存儲游標獲得的數(shù)據(jù)DECLARE@ReaderIDCHAR(10);DECLARE@BookIDCHAR(20);DECLARE@BorrowDateDATE;--聲明游標，獲取ReturnDate列中的值為空的記錄DECLAREborrowCursorCURSORFORSELECTReaderID,BookID,BorrowDateFROMborrowInfoWHEREReturnDateISNULL;任務6.2游標的創(chuàng)建與應用6.2.2游標的應用--打開游標OPENborrowCursor;--獲取游標中的第一條記錄FETCHNEXTFROMborrowCursorINTO@ReaderID,@BookID,@BorrowDate;--當成功獲取游標中的記錄時，循環(huán)執(zhí)行WHILE@@FETCH_STATUS=0BEGIN--逐條刪除歸還日期為空的圖書借閱記錄

DELETEFROMborrowInfoWHEREReaderID=@ReaderIDANDBookID=@bookIdANDBorrowDate=@BorrowDate;--移動游標到下一條記錄FETCHNEXTFROMborrowCursorINTO@ReaderID,@BookID,@BorrowDate;任務6.2游標的創(chuàng)建與應用6.2.2游標的應用END;CLOSEborrowCursor;結(jié)果顯示5行受影響。再次查詢borrowInfo表中的所有記錄，發(fā)現(xiàn)查詢結(jié)果中沒有歸還日期為空的記錄。任務6.2游標的創(chuàng)建與應用6.2.3關閉與釋放游標關閉游標使用完游標后，需要關閉游標。關閉游標的命令格式是：closecursor_name;格式說明：cursor_name表示游標名稱。釋放游標如果游標不再使用，可以釋放游標。釋放游標的命令格式是：deallocatecursor_name;格式說明：cursor_name表示游標名稱。任務6.2任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程6.3.2調(diào)用存儲過程6.3.3管理存儲過程存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程存儲過程（StoredProcedure）簡稱過程，就是為了完成一定的功能而編寫的程序段，由一系列SQL語句構(gòu)成，相當于C語言中的函數(shù)或Java語言中的方法，通過調(diào)用存儲過程名來執(zhí)行存儲過程。存儲過程存放在數(shù)據(jù)庫對象中，屬于數(shù)據(jù)庫，與表和視圖的級別相同。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程1、用SSMS管理器窗口創(chuàng)建存儲過程例6-16創(chuàng)建存儲過程P0_GetBookInfoTest，其功能是獲取bookInfo表中指定作者和出版單位的圖書的信息（包括圖書編號、圖書名稱、作者姓名、出版單位和銷售價格）。第1步，在SSMS管理器窗口中依次展開“數(shù)據(jù)庫”→“l(fā)ibsys”→“可編程性”節(jié)點，右擊“存儲過程”節(jié)點，在彈出的快捷菜單中選擇“新建”→“存儲過程”命令，打開包含創(chuàng)建存儲過程的語句的窗口。第2步，在“查詢”菜單中選擇“指定模板參數(shù)的值”命令。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程第3步，在彈出的“指定模板參數(shù)的值”對話框中輸入如上一頁圖中所示的參數(shù)值。第4步，單擊“確定”按鈕。第5步，在“查詢編輯器”窗口中，使用以下語句替換SELECT語句：SELECTBookID,BookName,Writer,Publisher,PriceFROMbookInfoWHEREWriter=@WriterANDPublisher=@Publisher;第6步，選擇“查詢”菜單中的“執(zhí)行”命令，創(chuàng)建存儲過程。該存儲過程作為數(shù)據(jù)庫中的對象創(chuàng)建。第7步，在彈出的“執(zhí)行過程”窗口中，輸入“胡振華”作為參數(shù)@Writer的值，并輸入“清華大學出版社”作為參數(shù)@Publisher的值任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程執(zhí)行結(jié)果如下：任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程2、用Transact-SQL命令創(chuàng)建存儲過程（1）創(chuàng)建存儲過程的命令格式是：CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[;number][{@parameter_name[type_schema_name.]data_type}[VARYING][NULL][=default][OUT|OUTPUT|[READONLY]][,...n][WITH<procedure_option>[,...n]][FORREPLICATION]AS{[BEGIN]sql_statement[;][...n][END]}[;]任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程<procedure_option>::=[ENCRYPTION][RECOMPILE][EXECUTEASClause]格式說明：①schema_name：存儲過程所屬架構(gòu)的名稱。②procedure_name：存儲過程的名稱。存儲過程名稱必須遵循有關標識符的規(guī)則，并且在架構(gòu)中必須唯一。③;number：用于對同名的存儲過程分組的可選整數(shù)。使用一個DROPPROCEDURE語句可將這些分組存儲過程一起刪除。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程④@parameter_name：存儲過程中聲明的參數(shù)。通過將@符號用作第一個字符來指定參數(shù)名稱，參數(shù)名稱必須符合標識符的有關規(guī)則。⑤[type_schema_name.]data_type：參數(shù)的數(shù)據(jù)類型及該數(shù)據(jù)類型所屬的架構(gòu)。⑥[VARYING][NULL][=default][OUT|OUTPUT|[READONLY]：VARYING：指定輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲過程動態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變，僅適用于游標參數(shù)。default：參數(shù)的默認值。如果為參數(shù)定義了默認值，則無須指定該參數(shù)的值即可執(zhí)行存儲過程，默認值必須是常量或NULL，當默認值是常量時，可以使用通配符的形式，這使其可以在將該參數(shù)傳遞到存儲過程時使用關鍵字LIKE。OUT|OUTPUT：指明參數(shù)是輸出參數(shù)。使用OUTPUT參數(shù)將值返回給存儲過程的調(diào)用方，不能將表值數(shù)據(jù)類型指定為存儲過程的OUTPUT參數(shù)。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程READONLY：不能在存儲過程的主體中更新或修改參數(shù)。如果參數(shù)類型為表值數(shù)據(jù)類型，則必須指定READONLY。⑦<procedure_option>表示存儲過程選項，各個選項介紹如下。RECOMPILE：數(shù)據(jù)庫引擎不緩存該存儲過程的查詢計劃，強制在每次執(zhí)行該存儲過程時都對該存儲過程進行編譯。ENCRYPTION：指示SQLServer數(shù)據(jù)庫將CREATEPROCEDURE語句的原始文本轉(zhuǎn)換為模糊格式。EXECUTEASClause：指定在其中執(zhí)行存儲過程的安全上下文。⑧FORREPLICATION：指定為了復制操作而創(chuàng)建該存儲過程。⑨{[BEGIN]sql_statement[;][...n][END]}：構(gòu)成存儲過程主體的一個或多個Transact-SQL語句。可以使用可選的關鍵字BEGIN和END將這些語句括起來。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程（2）創(chuàng)建無參數(shù)存儲過程例6-17創(chuàng)建存儲過程P1_AllBook，其功能是顯示bookInfo表中由電子工業(yè)出版社出版的全部圖書的記錄。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程USElibsys --打開libsys數(shù)據(jù)庫GO--判斷是否存在存儲過程P1_AllBook，如果存在，則刪除該存儲過程DROPPROCEDUREIFEXISTSP1_AllBookGOCREATEPROCEDUREP1_AllBook --創(chuàng)建存儲過程ASBEGINSELECT*FROMbookInfoWHEREPublisher='電子工業(yè)出版社'END任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程例6-18創(chuàng)建存儲過程P2_BookBorrow，其功能是顯示由清華大學出版社出版的圖書的編號、名稱、銷售價格、出版日期、外借情況（包括圖書編號、圖書名稱、銷售價格、出版日期、借閱人、借閱人所在部門、借閱人聯(lián)系電話、借書日期、應歸還日期）。要求將CREATEPROCEDURE語句的原始文本轉(zhuǎn)換為模糊格式（可以通過WITHENCRYPTION實現(xiàn)）第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。USElibsysGOCREATEPROCP2_BookBorrowWITHENCRYPTION任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程ASBEGINSELECTbookInfo.BookID,BookName,Price,PublishDate,ReaderName,Department,Mobile,BorrowDate,ReturnDateFROMbookInfo,readerInfo,borrowInfoWHEREPublisher='清華大學出版社'ANDbookInfo.BookID=borrowInfo.BookIDANDborrowInfo.ReaderID=readerInfo.ReaderIDEND任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程（3）創(chuàng)建帶輸入?yún)?shù)的存儲過程存儲過程是一個子程序，可以在創(chuàng)建存儲過程的代碼中設計若干個參數(shù)，這種參數(shù)稱為形式參數(shù)，而在調(diào)用存儲過程時，傳遞給存儲過程的參數(shù)稱為實際參數(shù)。在定義參數(shù)時，必須明確該參數(shù)是輸入?yún)?shù)還是輸出參數(shù)。如果參數(shù)的后面添加了關鍵字OUTPUT，則該參數(shù)是輸出參數(shù)；如果參數(shù)的后面沒有添加關鍵字OUTPUT，則該參數(shù)是輸入?yún)?shù)，帶輸入?yún)?shù)的存儲過程使用更廣泛。例6-19創(chuàng)建存儲過程P3_BookWriter，其功能是顯示bookInfo表中由指定作者編寫的全部圖書的記錄。分析：指定作者并不知道是誰，必須在執(zhí)行存儲過程時才能知道，所以需要將作者（對應的列名為Writer）設置為輸入?yún)?shù)。如果將參數(shù)名設為editor，則其數(shù)據(jù)類型及長度必須與bookInfo表中Writer列內(nèi)數(shù)據(jù)的數(shù)據(jù)類型及長度匹配。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程USElibsysGODROPPROCEDUREIFEXISTSP3_BookWriterGOCREATEPROCP3_BookWriter@editorvarchar(8)ASBEGINSELECT*FROMbookInfoWHEREWriter=@editorEND任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程如果要查詢由作者“胡振華”編寫的圖書的記錄，則執(zhí)行該存儲過程的命令如下：EXECP3_BookWriter'胡振華’查詢結(jié)果會顯示由作者“胡振華”編寫的所有圖書的記錄。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程例6-20創(chuàng)建存儲過程P4_WriterPublisher，其功能是顯示bookInfo表中由指定作者編寫并由指定出版單位出版的圖書的記錄。分析：指定作者和指定出版單位都不明確，但在調(diào)用存儲過程時會指定，所以需要設置兩個輸入?yún)?shù)，一個參數(shù)表示作者（對應的列名Writer），另一個參數(shù)表示出版單位（對應的列名是Publisher）。這兩個參數(shù)的數(shù)據(jù)類型及長度必須與bookInfo表中Writer列和Publisher列內(nèi)數(shù)據(jù)的數(shù)據(jù)類型及長度匹配。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程CREATEPROCP4_WriterPublisher@editorvarchar(8),@pressvarchar(30)ASBEGINSELECT*FROMbookInfoWHEREWriter=@editorANDPublisher=@pressEND任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程假如要查詢由作者“劉小華”編寫并由“電子工業(yè)出版社”出版的圖書的記錄，則執(zhí)行該存儲過程的命令如下：EXECP4_WriterPublisher'劉小華','電子工業(yè)出版社'在使用存儲過程時，要注意以下兩點：①在定義參數(shù)時，形式參數(shù)名（如@editor、@press）最好不要與對應的列名完全相同，以免引起混淆，但相同并不會出錯。②在執(zhí)行存儲過程時，實際參數(shù)要與形式參數(shù)的個數(shù)相等、類型一致、順序相同。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程（4）創(chuàng)建帶輸入?yún)?shù)和輸出參數(shù)的存儲過程存儲過程可以帶輸出參數(shù)，用于將運行結(jié)果返回給該存儲過程的調(diào)用方。在定義輸出參數(shù)時，需要在參數(shù)的后面添加關鍵字OUTPUT。在編寫存儲過程時，經(jīng)常需要定義變量，以保存中間結(jié)果。在SQLServer2022中，定義變量的命令是DECLARE，格式如下：DECLARE變量名類型(長度)在調(diào)用帶輸出參數(shù)的存儲過程時，也需要設計一段代碼才能將結(jié)果保存到指定的變量中，供其他程序使用。例6-21創(chuàng)建一個存儲過程P5_ReaderType，其功能是根據(jù)輸入的讀者姓名判斷該讀者的類型。如果其是教師，則返回“教師讀者”；如果其是學生，則返回“學生讀者”；否則返回“其他讀者”。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程USElibsys GOCREATEPROCEDUREP5_ReaderType @namechar(10),@informationchar(20)outputASBEGINDECLARE@typechar(10)SET@type=(SELECTReaderTypeFROMreaderInfoWHEREReaderName=@name) 任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程IFexists(SELECTReaderTypeFROMreaderInfoWHEREReaderName=@name)IF@type='教師' SET@information='教師讀者’ELSEIF@type='學生' SET@information='學生讀者’ELSE SET@information='其他讀者'SELECT@nameAS'讀者姓名',@informationAS'讀者類型'END任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程如果要查詢讀者“張紅軍”的類型，則調(diào)用存儲過程P5_ReaderType，并為存儲過程指定兩個參數(shù)值，第一個參數(shù)值為“張紅軍”，第二個參數(shù)值任意（符合命名規(guī)范即可）。命令如下：EXECP5_ReaderType'張紅軍',xxx查詢結(jié)果如圖所示。任務6.3存儲過程的創(chuàng)建與管理6.3.1創(chuàng)建存儲過程如果要將輸出結(jié)果保存在變量中，便于后續(xù)訪問，則需要編寫一段代碼。示例如下：DECLARE@resultCHAR(20)EXECP5_ReaderType'張紅軍',@resultOUTPUTSELECT@resultas'讀者類型'上述代碼表示將存儲過程的查詢結(jié)果保存在變量@result中。任務6.3存儲過程的創(chuàng)建與管理6.3.2調(diào)用存儲過程1、執(zhí)行系統(tǒng)存儲過程系統(tǒng)存儲過程以前綴sp_開頭。因為從邏輯意義上講，這些系統(tǒng)存儲過程出現(xiàn)在所有用戶定義的數(shù)據(jù)庫和系統(tǒng)定義的數(shù)據(jù)庫中，所以可以在任意數(shù)據(jù)庫中執(zhí)行這些系統(tǒng)存儲過程，而不必完全限定系統(tǒng)存儲過程的名稱。但是，建議使用sys架構(gòu)名稱對所有系統(tǒng)存儲過程的名稱進行架構(gòu)限定，以防止名稱沖突，如：EXECsys.sp_who;任務6.3存儲過程的創(chuàng)建與管理6.3.2調(diào)用存儲過程2、執(zhí)行用戶定義存儲過程當執(zhí)行用戶定義的存儲過程時，建議使用架構(gòu)名稱來限定存儲過程的名稱。這種做法可以使性能得到小幅度提升，因為數(shù)據(jù)庫引擎不必搜索多個架構(gòu)。如果某個數(shù)據(jù)庫在多個架構(gòu)中存在同名的存儲過程，則還可以防止執(zhí)行錯誤的存儲過程。例如，執(zhí)行例6-21中的存儲過程P5_ReaderType，命令如下：USElibsysGOEXECdbo.P5_ReaderType'張紅軍',xxxGOdbo為系統(tǒng)預先定義的架構(gòu)，新創(chuàng)建的存儲過程的默認架構(gòu)為dbo。任務6.3存儲過程的創(chuàng)建與管理6.3.2調(diào)用存儲過程3、使用SSMS管理器窗口執(zhí)行存儲過程（1）在SSMS管理器窗口中依次展開“服務器”→“數(shù)據(jù)庫”→“l(fā)ibsys”→“可編程性”→“存儲過程”節(jié)點。（2）右擊需要執(zhí)行的存儲過程的名稱，在彈出的快捷菜單中選擇“執(zhí)行存儲過程”命令。（3）在彈出的“執(zhí)行過程”窗口中輸入?yún)?shù)的值（見6.3.1節(jié)中的“1．使用SSMS管理器窗口創(chuàng)建存儲過程”部分）。（4）單擊“確定”按鈕，執(zhí)行存儲過程。任務6.3存儲過程的創(chuàng)建與管理6.3.2調(diào)用存儲過程4、使用Transact-SQL命令執(zhí)行存儲過程[{EXEC|EXECUTE}]procedure_name[{value|@variable[OUT|OUTPUT]}][,...n][WITH<execute_option>[,...n]]}[;]<execute_option>::={RECOMPILE|{RESULTSETSUNDEFINED}|{RESULTSETSNONE}|{RESULTSETS(<result_sets_definition>[,...n])}}任務6.3存儲過程的創(chuàng)建與管理6.3.2調(diào)用存儲過程（1）value：要傳遞給模塊或傳遞給命令的參數(shù)值。（2）@variable：用來存儲參數(shù)或返回參數(shù)的變量。（3）OUTPUT：指定模塊或命令字符串返回一個參數(shù)。（4）RECOMPILE：在執(zhí)行模塊后，強制編譯、使用和放棄新計劃。（5）RESULTSETSUNDEFINED：該選項不保證將返回任何結(jié)果（如果有），并且不提供任何定義。如果返回任何結(jié)果，則說明語句正常執(zhí)行而沒有發(fā)生錯誤，否則不會返回任何結(jié)果。（6）RESULTSETSNONE：保證執(zhí)行語句不返回任何結(jié)果。如果返回任何結(jié)果，則會中止批處理。（7）RESULTSETS(<result_sets_definition>[,...n])：保證返回result_sets_definition中指定的結(jié)果。對于返回多個結(jié)果集的語句，需要提供多個result_sets_definition部分，將每個result_sets_definition用括號括上，并以逗號隔開。任務6.3存儲過程的創(chuàng)建與管理6.3.2調(diào)用存儲過程例6-22執(zhí)行存儲過程P0_GetBookInfoTest，其功能是獲取作者為“張安平”、出版單位為“清華大學出版社”的圖書的信息。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。EXECP0_GetBookInfoTest'張安平','清華大學出版社'任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程1、修改存儲過程（1）使用SSMS管理器窗口修改存儲過程例6-23修改存儲過程P0_GetBookInfoTest，其功能是獲取bookInfo表中由指定作者編寫和采購數(shù)量大于指定數(shù)量的圖書的信息（包括圖書編號、圖書名稱、作者姓名、出版單位、出版時間、銷售價格和采購數(shù)量）。第1步，在SSMS管理器窗口中依次展開“服務器”→“數(shù)據(jù)庫”→“l(fā)ibsys”→“可編程性”→“存儲過程”節(jié)點。第2步，右擊存儲過程名P0_GetBookInfoTest，在彈出的快捷菜單中選擇“修改”命令。第3步，在彈出的窗口中，修改存儲過程的文本如下：任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOALTERPROCEDUREP0_GetBookInfoTest--Addtheparametersforthestoredprocedurehere@Writervarchar(8)=null,@BuyCountintAS任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程BEGIN--SETNOCOUNTONaddedtopreventextraresultsetsfrom--interferingwithSELECTstatements.SETNOCOUNTON;--InsertstatementsforprocedurehereSELECTBookID,BookName,Writer,Publisher,Price,BuyCountFROMbookinfoWHEREWriter=@WriterANDBuyCount>@BuyCount;ENDGO任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程第4步，如果要測試語法，則在“查詢”菜單中選擇“分析”命令；如果要保存對存儲過程定義的修改，則在“查詢”菜單中選擇“執(zhí)行”命令；如果要將修改后的存儲過程另存為T-SQL腳本，則在“文件”菜單中選擇“另存為”命令，在彈出的“另存為”對話框中，系統(tǒng)會默認指定一個文件名，可以接受該文件名，也可以將其修改為其他名稱，然后單擊“保存”按鈕。在“查詢”菜單中選擇“執(zhí)行”命令。第5步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口，在該窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。USElibsysGOEXECP0_GetBookInfoTest'胡振華',20GO任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程查詢結(jié)果如下：任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程（2）使用SQL命令修改存儲過程ALTER{PROC|PROCEDURE}[schema_name.]procedure_name[;number][{@parameter_name[type_schema_name.]data_type}[VARYING][=default][OUT|OUTPUT][READONLY]][,...n][WITH<procedure_option>[,...n]][FORREPLICATION]AS{[BEGIN]sql_statement[;][...n][END]}[;]<procedure_option>::=[ENCRYPTION][RECOMPILE][EXECUTEASClause]任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程例6-24修改存儲過程P1_AllBook，其功能是顯示bookInfo表中由電子工業(yè)出版社出版的計算機類的圖書的信息。第1步，在SSMS管理器窗口連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。USElibsys --打開libsys數(shù)據(jù)庫GOALTERPROCEDUREP1_AllBook --修改存儲過程ASBEGINSELECT*FROMbookInfoWHEREPublisher='電子工業(yè)出版社'ANDBookType='計算機'END任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程2、刪除存儲過程刪除存儲過程的方法有兩種：一種是使用SSMS管理器窗口，另一種是使用T-SQL命令。（1）使用SSMS管理器窗口刪除存儲過程例6-25刪除存儲過程P0_GetBookInfoTest。第1步，在SSMS管理器窗口中依次展開“數(shù)據(jù)庫”→“l(fā)ibsys”→“可編程性”→“存儲過程”節(jié)點。第2步，右擊存儲過程名P0_GetBookInfoTest，在彈出的快捷菜單中選擇“刪除”命令。。第3步，在彈出的“刪除對象”對話框中單擊“確定”按鈕。任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程（2）使用T-SQL命令刪除存儲過程。刪除存儲過程的命令格式如下：DROP{PROC|PROCEDURE}[IFEXISTS]{[schema_name.]procedure}[,...n]格式說明：①IFEXISTS：有條件地刪除存儲過程（僅當其已存在時）。②schema_name：存儲過程所屬架構(gòu)的名稱。③procedure：要刪除的存儲過程的名稱。任務6.3存儲過程的創(chuàng)建與管理6.3.3管理存儲過程例6-26刪除存儲過程P1_AllBook。第1步，在SSMS管理器窗口中連接到數(shù)據(jù)庫引擎的實例“l(fā)ibsys”。第2步，單擊工具欄中的“新建查詢”按鈕，打開一個新的“查詢編輯器”窗口。第3步，在“查詢編輯器”窗口中輸入以下命令，然后單擊“執(zhí)行”按鈕。/*判斷是否存在存儲過程P1_AllBook，如果存在，則刪除該存儲過程*/DROPPROCEDUREIFEXISTSP1_AllBookGO任務6.3任務6.4觸發(fā)器的創(chuàng)建與管理6.4.1觸發(fā)器的分類6.4.2創(chuàng)建觸發(fā)器6.4.3管理觸發(fā)器觸發(fā)器的創(chuàng)建與管理6.4.1觸發(fā)器的分類觸發(fā)器為特殊類型的存儲過程，可以在INSERT、UPDATE或DELETE語句執(zhí)行時自動生效，以便影響觸發(fā)器中定義的表或視圖。觸發(fā)器可以用于強制執(zhí)行業(yè)務規(guī)則和保證數(shù)據(jù)的完整性。數(shù)據(jù)庫系統(tǒng)會將觸發(fā)器和觸發(fā)它的語句作為可以在觸發(fā)器內(nèi)回滾的單個事務對待。如果檢測到錯誤（如磁盤空間不足），則整個事務自動回滾。任務6.4觸發(fā)器的創(chuàng)建與管理6.4.1觸發(fā)器的分類觸發(fā)器是表的對象，由系統(tǒng)自動觸發(fā)執(zhí)行，不要也不能使用命令來執(zhí)行，它是對表約束（在建立表時）的補充。觸發(fā)器在INSERT、UPDATE和DELETE語句上操作，并且有助于在表或視圖中修改數(shù)據(jù)時強制執(zhí)行業(yè)務規(guī)則，保證數(shù)據(jù)的完整性?？梢詫⒂|發(fā)器分為兩類：AFTER觸發(fā)器和INSTEADOF觸發(fā)器。1、AFTER觸發(fā)器在執(zhí)行INSERT、UPDATE或DELETE語句的操作之后執(zhí)行AFTER觸發(fā)器。2、INSTEADOF觸發(fā)器INSTEADOF觸發(fā)器用來代替通常的觸發(fā)動作，即當對表進行INSERT、UPDATE或DELETE操作時，系統(tǒng)不是直接對表執(zhí)行這些操作，而是把操作內(nèi)容交給觸發(fā)器，讓觸發(fā)器檢查所進行的操作是否正確，操作正確才進行相應的操作。因此，INSTEADOF觸發(fā)器的動作要早于表的約束處理。任務6.4觸發(fā)器的創(chuàng)建與管理6.4.1觸發(fā)器的分類在觸發(fā)器操作中，系統(tǒng)會創(chuàng)建INSERTED表和DELETED表這兩個臨時表。：（1）INSERTED表存放由于INSERT或UPDATE語句的執(zhí)行，而導致需要添加到該觸發(fā)器作用的表中的所有新記錄，即保存要插入表中的新記錄和要在表中更新的新記錄，在表中插入新記錄或更新表中的記錄時，也將記錄副本存入INSERTED表。（2）DELETED表存放由于DELETE或UPDATE語句的執(zhí)行，而導致要從被該觸發(fā)器作用的表中刪除的記錄。任務6.4觸發(fā)操作INSERTED表DELETED表INSERT存放插入的數(shù)據(jù)無UPDATE存放更新后的數(shù)據(jù)存放更新前的數(shù)據(jù)DELETE無存放被刪除的數(shù)據(jù)觸發(fā)器的創(chuàng)建與管理6.4.2創(chuàng)建觸發(fā)器1、觸發(fā)器的創(chuàng)建方式（1）SSMS管理器窗口方式創(chuàng)建觸發(fā)器例6-27創(chuàng)建觸發(fā)器TR0_Insert_BookInfoTest，其功能是當在bookInfo表中插入一條記錄時，輸出該記錄中的圖書編號、圖書名稱、作者姓名、出版單位和銷售價格等信息。第1步，在SSMS管理器窗口中依次展開“數(shù)據(jù)庫”→“l(fā)ibsys”→“表”→“dbo.bookInfo”節(jié)點，右擊“觸發(fā)器”節(jié)點，在彈出的快捷菜單中選擇“新建觸發(fā)器”命令。第2步，在“查詢”菜單中選擇“指定模板參數(shù)的值”命令。第3步，在彈出的“指定模板參數(shù)的值”對話框中輸入如圖所示的參數(shù)值。任務6.4觸發(fā)器的創(chuàng)建與管理6.4.2創(chuàng)建觸發(fā)器第4步，單擊“確定”按鈕。第5步，在“查詢編輯器”窗口中，使用以下語句替換注釋“--Insertstatementsfortriggerhere”：DECLARE@publishdateDateSELECT@publishdate=PublishDateFROMINSERTEDIF@publishdate>GETDATE()

PRINT'插入書籍失敗，出版日期必須在當前日期之前'ROLLBACKTRANSACTION第6步，在“查詢”菜單中選擇“執(zhí)行”命令，創(chuàng)建觸發(fā)器。如果要查看在“對象資源管理器”窗口中列出的觸發(fā)器，則可以右擊“觸發(fā)器”節(jié)點，在彈出的快捷菜單中選擇“刷新”命令。任務6.4觸發(fā)器的創(chuàng)建與管理6.4.2創(chuàng)建觸發(fā)器第7步，在“查詢編輯器”窗口輸入以下命令，插入一本圖書的記錄：USElibsysGOINSERTINTObookinfoVALUES('9787111669807','Java語言程序設計基礎篇','計算機','梁勇','機械工業(yè)出版社','2024-05-01',139,'2022-01-10',20,19,'出版社優(yōu)秀教材');第8步，單擊“執(zhí)行”按鈕，消息窗口中顯示的提示信息如圖所示。任務6.4觸發(fā)器的創(chuàng)建與管理6.4.2創(chuàng)建觸發(fā)器（2）使用SQL命令創(chuàng)建觸發(fā)器。CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITH<dml_trigger_option>[,...n]]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[WITHAPPEND][NOTFORREPLICATION]AS{sql_statement[;][,...n]}<dml_trigger_option>::=[ENCRYPTION][EXECUTEASClause]任務6.4觸發(fā)器的創(chuàng)建與管理6.4.2創(chuàng)建觸發(fā)器參數(shù)說明：CREATETRIGGER必須作為批處理的第1條語句才可以執(zhí)行。schema_name：觸發(fā)器所屬架構(gòu)的名稱。trigger_name：觸發(fā)器的名稱。觸發(fā)器的名稱必須遵循標識符的相關規(guī)則，但不得以#或##開頭。table|view：運行觸發(fā)器的表或視圖。WITHENCRYPTION：讓CREATETRIGGER語句的文本復雜難懂。使用WITHENCRYPTION可以防止將觸發(fā)器作為SQLServer數(shù)據(jù)庫復制的一部分進行發(fā)布。EXECUTEAS：指定用于執(zhí)行該觸發(fā)器的安全上下文。FOR|AFTER：指定僅當觸發(fā)SQL語句中指定的所有操作都已成功啟動時，觸發(fā)器才觸發(fā)。無法對視圖定義AFTER觸發(fā)器。任務6.4觸發(fā)器的創(chuàng)建與管理6.4.2創(chuàng)建觸發(fā)器INSTEADOF：指定啟動觸發(fā)器（而不是觸發(fā)SQL語句），從而覆蓋觸發(fā)語句的操作。在表或視圖上，每個INSERT、UPDATE或DELETE語句最多可以定義一個INSTEADOF觸發(fā)器。{[DELETE][,][INSERT][,