第7章

數據庫高級對象的使用本章要點◆視圖的概念、創建及應用。◆

索引的概念、創建及應用。◆

事務和鎖的概念。◆

數據庫編程基礎知識。◆

存儲過程的概念、創建及應用。◆

函數的概念、創建及應用。◆

掌握觸發器的概念、創建及應用。◆

游標的概念及應用。一、視圖視圖是一個虛擬的表，該表提供了對一個或多個表中一系列列的訪問，作為對象存儲在數據庫中。因此，視圖是從一個或多個表中派生出數據的對象，這些表稱為基表或基本表。對視圖的操作與對表的操作一樣，可以對其進行查詢、修改(有一定的限制)、刪除。對通過視圖看到的數據進行修改時，相應的基本表的數據也要發生變化；同時，若基表的數據發生變化，則這種變化也可以自動地反映到視圖中。可以通過CREATEVIEW語句來創建視圖，其基本語法如下：CREATEVIEW視圖名[(視圖字段列表)]AS查詢語句[WITHCHECKOPTION];其中，WITHCHECKOPTION強制針對視圖執行的所有數據修改語句都必須符合在“查詢語句”中設置的條件。通過視圖修改行時，WITHCHECKOPTION可確保提交修改后，仍可通過視圖看到數據。視圖字段列表如果省略，將使用查詢語句返回的字段名。查詢語句返回的字段必須有字段名且唯一。一、視圖【例7-1】

已知學生表student的數據如圖所示。創建一個視圖，視圖中只包含deptno為“1”的數據，SQL語句如下：CREATEVIEWvw_dept1ASSELECT*FROMstudentWHEREdeptno='1'通過SELECT語句查詢視圖中的數據，語句如下：SELECT*FROMvw_dept1一、視圖可以通過視圖修改數據，如：UPDATEvw_dept1SETbirthday='2007-8-12'WHEREsno='2023019001'由于視圖只是一個查詢，并不保存數據，所以對視圖數據的修改其實是對基表數據的修改。視圖使用的數據表一、視圖在創建視圖時可以加一個“WITHCHECKOPTION”選項，這樣對視圖修改時，必須滿足視圖創建時SELECT語句中的WHERE條件。CREATEVIEWvw_dept1ASSELECT*FROMstudentWHEREdeptno='1'WITHCHECKOPTION這樣，當在“vw_dept1”視圖中修改或插入數據時，deptno必須等于“1”才能允許修改或插入。一、視圖【例7-2】

顯示購物者的名字、所訂購的玩具的名字和訂購數量CREATEVIEWvwOrdersASSELECTShopper.vShopperName,vToyName,siQtyFROMShopperJOINOrdersONShopper.cShopperId=Orders.cShopperIdJOINOrderDetailONOrders.cOrderNo=OrderDetail.cOrderNoJOINToysONOrderDetail.cToyId=Toys.cToyId一、視圖視圖沒有指定列名，視圖的列名就是select語句的列名。【例7-3】顯示購物者的名字、所訂購的玩具的名字和訂購數量，以中文顯示字段名，語句如下：CREATEVIEWv_wOrders(購物者名字,玩具名字,訂購數量)WITHENCRYPTIONASSELECTShopper.vFirstName,vToyName,siQtyFROMShopperJOINOrdersONShopper.cShopperId=Orders.cShopperIdJOINOrderDetail ONOrders.cOrderNo=OrderDetail.cOrderNoJOINToysONOrderDetail.cToyId=Toys.cToyId一、視圖視圖指定了中文列名

通過視圖管理數據，應該注意以下情況：如果修改將影響多個基本表，則不能在視圖中一次性修改多個基表的數據，否則可以。

不能修改那些內容為計算結果的列，例如，一個經過計算的列或一個集合函數。

視圖引用多個表時，無法用DELETE命令刪除數據。

確認那些不包括在視圖列中但屬于表的列，是否允許NULL值或有缺省值的情況。一、視圖【例7-5】vwOrderWrapper視圖定義如下：CREATEVIEWvwOrderWrapperASSELECTcOrderNo,cToyId,siQty,vDescription,mWrapperRate FROMOrderDetailJOINWrapper ONOrderDetail.cWrapperId=Wrapper.cWrapperId要通過視圖更新siQty和mWrapperRate時，使用UPDATEvwOrderWrapperSETsiQty=2,mWrapperRate=mWrapperRate+1WHEREcOrderNo='000001'命令系統會出錯，因為siQty和mWrapperRate分別屬于OrderDetail和Wrapper表中，不能同時修改多個基本表。必須修改為：UpdatevwOrderWrapperSETsiQty=2WHEREcOrderNo='000001';UpdatevwOrderWrapperSETmWrapperRate=mWrapperRate+1WHEREcOrderNo='000001';一、視圖視圖的優點：1．視點集中 用戶只關心它感興趣的某些特定數據和他們所負責的特定任務。2．簡化操作 視圖大大簡化了用戶對數據的操作。因為在定義視圖時，若視圖本身就是一個復雜查詢的結果集，這樣在每一次執行相同的查詢時，不必重新寫這些復雜的查詢語句，只要一條簡單的查詢視圖語句即可。可見視圖向用戶隱藏了表與表之間的復雜的連接操作。3．定制數據 視圖能夠實現讓不同的用戶以不同的方式看到不同或相同的數據集。因此，當有許多不同水平的用戶共用同一數據庫時，這顯得極為重要。4．合并分割數據 在有些情況下，由于表中數據量太大，故在表的設計時常將表進行水平分割或垂直分割，但表的結構的變化卻對應用程序產生不良的影響。5．提高了數據的安全性 視圖可以作為一種安全機制。通過視圖用戶只能查看和修改他們所能看到的數據。其它數據庫或表既不可見也不可以訪問。視圖所引用表的訪問權限與視圖權限的設置互不影響。一、視圖二、索引用戶對數據庫最頻繁的操作是進行數據查詢。一般情況下，數據庫在進行查詢操作時需要對整個表進行數據搜索。當表中的數據很多時，搜索數據就需要很長的時間，這就造成了服務器的資源浪費。為了提高檢索數據的能力，數據庫引入了索引機制。SQLServer中的索引是以B+樹結構來維護的，MySQL中的索引分為B+樹和HASH兩種，與表的存儲引擎相關，MyISAM和InnoDB存儲引擎只支持B+樹，MEMORY存儲引擎可以支持B+樹和HASH索引。二、索引B+樹是一個多層次、自維護的結構。一個B+樹包括一個頂層，稱為根節點(RootNode)；0到多個中間層(Intermediate)；一個底層(Level0)，底層中包括若干葉子節點(LeafNode)。1、索引的結構2、聚集索引聚集索引對表的物理數據頁中的數據按列進行排序，然后再重新存儲到磁盤上，即聚集索引與數據是混為一體，的它的葉節點中存儲的是實際的數據。由于聚集索引對表中的數據一一進行了排序，因此用聚集索引查找數據很快。但由于聚集索引將表的所有數據完全重新排列了，它所需要的空間也就特別大，大概相當于表中數據所占空間的120%。一個表只能有一個聚集索引。聚集索引改變表中數據的物理順序。二、索引2.聚集索引二、索引在聚集索引中，數據存儲在B+樹的葉節點層。當數據庫使用聚集索引搜索某個值時，遵循下列步驟：(1)?讀取根頁的地址。(2)將搜索值和根頁中的關鍵字值進行比較。(3)找到包含小于或等于搜索值的最大關鍵字值的那一頁。(4)頁指針下到索引的下一層。(5)重復步驟(3)、(4)，直到到達數據頁。(6)在數據頁上搜索數據行，直到找到搜索值為止。如果沒有在數據頁上找到搜索值，則查詢不返回任何行。3.非聚集索引非聚集索引具有與表的數據完全分離的結構，使用非聚集索引不用將物理數據頁中的數據按列排序。理論上講，一個表最多可以建249個非聚集索引。非聚集索引不改變表中數據的物理順序。二、索引3.非聚集索引二、索引當使用非聚集索引搜索值時，遵循以下步驟：(1)獲取根頁的地址。(2)將搜索值和根頁中的關鍵字值進行比較。(3)找到小于或等于搜索值的最大關鍵字值所在的頁。(4)頁指針下到索引的下一層。(5)重復步驟(3)、(4)，直到到達數據頁。(6)搜索葉節點頁上的行以尋找指定值。若未找到匹配的值，則說明表中不包含搜索的值；若找到了匹配的值，則根據數據頁指針及行號(Eid)讀取數據塊并定位到行，從而檢索到所需的行。4．索引與系統的性能優點：提高查詢執行的速度。強制實施數據的唯一性。提高表之間聯接的速度。缺點存儲索引要占用磁盤空間。數據修改需要更長的時間，因為索引也要更新。創建索引要花時間。二、索引5.索引的特性索引加快了表聯接查詢的速度，以及完成排序、分組的速度。索引可以用于實施行的唯一性。索引適用于大部分數據都是唯一的那些列。在包含大量重復值的列上建立索引是無用的。當你修改一個索引列的數據時，相關索引將自動更新。維護索引需要時間和資源。你不應該創建一個利用率很底的索引。聚集索引應當創建于非聚集索引創建之前。聚集索引改變行的次序，如果非聚集索引創建于聚集索引之前，則它將被重新構建。通常，非聚集索引創建在外關鍵字之上。二、索引6.索引的分類1.根據索引特征進行分類可以分為普通索引、唯一索引、主鍵索引、全文索引、空間索引。（1）普通索引：最基本的索引，允許在定義索引的列中插入重復值和空值。（2）唯一索引：要求索引列的值必須唯一，允許有空值。（3）主鍵索引：要求索引列的值必須唯一，不能有空值。在建立主鍵時會自動建立此索引。（4）全文索引：在較長字符串類型字段中查找數據時，使用此索引可提高查找效率。（5）空間索引：對空間類型的字段建立的索引。二、索引6.索引的分類2.根據索引涉及列數進行分類，可分為單列索引和復合索引。（1）單列索引：索引字段只有一個列的索引。（2）復合索引：索引字段包括多個列的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用。3.根據索引存儲方式進行分類（1）BTreee索引：使用了B+樹結構的索引。（2）HASH索引：使用了HASH結構的索引。二、索引6.索引的分類4.根據索引與數據物理存儲關系進行分類（1）聚集索引：數據行的存儲順序按索引字段數據項排序。通常定義主鍵會自動建立一個聚集索引。一個表只能建立一個聚集索引。（2）非聚集索引：不會影響數據行的存儲順序的索引。二、索引7、管理索引CREATEINDEX可以創建唯一索引和普通索引的基本語法如下：CREATE[UNIQUE]INDEX索引名稱ON表名稱(字段名稱[ASC|DESC][,...]);其中：

UNIQUE：為可選參數，創建一個唯一索引，未指明則創建普通索引。

ON關鍵字指明索引針對的表。

ASC和DESC表明索引的排序方式。當字段名稱只有一項時，將建立單列索引。當字段名稱有多項時，將建立復合索引。二、索引7、管理索引【例7-6】在學生表student的sname字段上創建一個普通索引。

CREATEINDEXidx_student ONstudent(sname)【例7-7】

在購物者表shopper的cPhone字段上創建一個唯一索引。

CREATEUNIQUEINDEXidx_shopper_phone ONshopper(cPhone)【例7-8】

在訂單細節表OrderDetail的cOrderNo和cToyID字段上創建一個復合索引。 CREATEINDEXidx_OrderDetail ONOrderDetail(cOrderNo,cToyID)二、索引7、管理索引DROPINDEX命令可以刪除當前數據庫中的索引。其語法如下： DROPINDEXindexnameONtablename【例7-9】刪除表OrderDetail中的索引idx_OrderDetail。DROPINDEXidx_OrderDetailONOrderDetail二、索引三、事務事務(Transaction)是完成一個應用處理的最小單元，由一個或多個對數據庫操作的語句組成。事務是一個完整的執行單元，如果成功執行，則事務中的數據更新會全部提交；如果事務中有一個語句執行失敗，則取消全部操作，并將數據庫恢復到事務執行之前的狀態。1、事務的概念使用INSERT、DELETE或UPDATE命令對數據庫進行操作時一次只能操作一個表，這會帶來數據庫的數據不一致的問題。例如：從A賬號中向B賬號轉賬100元的命令是：UPDATE賬戶set余額=余額-100where賬號='A';UPDATE賬戶set余額=余額+100where賬號='B';在執行第一條UPDATE語句后，如果計算機突然出現故障，無法再繼續執行第二條UPDATE語句，則會出現嚴重問題。三、事務因此，必須保證這兩條UPDATE語句同時執行。為解決類似的數據一致性的問題，數據庫系統通常都引入了事務（Transaction）的概念。原子性（Atomicity）：事務必須是原子工作單元，要么完成所有數據的修改，要么對這些數據不作任何修改。

一致性（Consistency）：在成功地完成一個事務之后，所有的數據都處于一致狀態。必須將關系數據庫的所有規則應用于事務中的修改，以維護完全的數據完整性。獨立性（Isolation）：事務中所進行的任何數據修改都必須獨立于同時發生的其他事務對數據的修改。換句話說，該事務訪問的數據所處的狀態要么是在同時發生的事務修改之前的，要么是在第二個事務完成之后的。沒有間隙讓該過程看到一個中間狀態。持久性（Durability）：完整的事務對數據的任何修改能夠在系統中永久保持其效果。因此，完整的事務對數據的任何修改即便是遇到系統失敗也能保持下來。這一屬性通過事務日志的備份和恢復來確保。ACID1、事務的概念三、事務事務包括顯示事務和隱式事務兩種。（1）顯式事務

一個顯式事務是指事務的開始和結束都明確定義的事務。在SQLServer中，顯式事務用BEGINTRANSACTION和COMMITTRANSACTION語句來指定。在MySQL中，顯式事務用STARTTRANSACTION和COMMIT語句來指定。（2）隱式事務

當連接以隱式事務模式進行操作時，數據庫將在提交或回滾當前事務后自動啟動新事務。因此，隱式事務不需要標示事務的開始，只需要用戶使用COMMIT或ROLLBACK語句提交或回滾事務。在SQLServer中，可使用SETIMPLICIT_TRANSACTIONS語句把隱式事務模式打開。在MySQL中，可以使用SET語句將AUTOCOMMIT設置為1，把隱式事務模式打開。例如：

SETIMPLICIT_TRANSACTIONSON--SQLServer中開啟隱式事務SETAUTOCOMMIT=1--MySQL中開啟隱式事務1、事務的概念三、事務2、顯式事務定義SQLServer語法如下：BEGINTRANSACTIONCOMMITTRAN[SACTION其中BEGINTRANSACTION可以縮寫為BEGINTRAN，COMMITTRANSACTION可以縮寫為COMMITTRAN或COMMIT。MySQL語法如下：STARTTRANSACTIONCOMMIT三、事務命令之間的所有語句被視為一體，只有執行到COMMIT命令時，事務中對數據庫的更新操作才算確認2、顯式事務定義【例7-10】

使用事務從A賬號中轉100元到B賬號中。SQLServer中使用如下代碼：BEGINTRANSACTIONUPDATE賬戶SET余額=余額?-?100WHERE賬號='A'UPDATE賬戶SET余額=余額?+?100WHERE賬號='B'COMMITMySQL中使用如下代碼：STARTTRANSACTION;UPDATE賬戶SET余額=余額-100WHERE賬號='A';UPDATE賬戶SET余額=余額+100WHERE賬號='B';COMMIT;三、事務事務回滾(TransactionRollback)是指當顯式或隱式事務中的某一語句執行失敗時，將對數據庫的操作恢復到事務執行前或某個指定位置。事務回滾使用ROLLBACK

命令。2、事務回滾三、事務2、事務回滾三、事務【例7-11】在提交訂單時需要同時提交訂單細節，不允許出現有訂單而沒有訂單細節的情況，因此，使用事務在訂單表和訂單細節表中插入數據。SQLServer程序如下：

BEGINTRANBEGINTRYINSERTINTOOrders(cOrderno,dOrderDate,cShopperId)VALUES('300002',GETDATE(),'000001')INSERTINTOOrderDetail(cOrderNo,cToyId,siQty)VALUES('300002','000001',6)COMMIT

ENDTRYBEGINCATCH

ROLLBACKENDCATCH四、鎖鎖(Lock)是在多用戶環境下對資源訪問的一種限制機制。數據庫管理系統使用鎖來確保事務的完整性和數據庫的一致性。鎖的功能是防止用戶訪問正在被其他用戶修改的信息。在多用戶環境下，鎖防止用戶在同一時刻修改同樣的數據。鎖是自動實現的，通過理解鎖、在應用程序中定制鎖，可以設計出更高效的應用程序。1、沒有鎖將導致的問題離開了鎖，當同一時刻在數據庫中使用同樣的數據時，可能發生4類問題：丟失更新讀臟數據不可重復讀幻象讀四、鎖例如，有兩個轉賬事務,事務T1將賬戶A中的余額減少100元,將賬戶B中的余額增加100元.事務T2將賬戶B的余額減少50元，將賬戶A的金額增加50元。A=260B=1000T1T2R(A=260)W(A=160)R(B=1000)R(B=1000)W(B=950)R(A=160)W(A=210)W(B=1100)A=210B=1100正確的結果應該是A為210，B為1050此更新丟失了！！！1、沒有鎖將導致的問題四、鎖（1）丟失更新A=260B=1000T1T2R(B=1000)W(B=950)R(A=260)W(A=160)R(B=950)ROLLBACKW(B=1050)A=160B=1050讀臟數據正確的結果應該是A為160，B為1100臟數據回滾了事務!!!1、沒有鎖將導致的問題四、鎖（2）讀臟數據讀臟數據也稱為臟讀，是指一個事務讀取了另一個事務未提交的數據。事務T2先將B賬戶的余額寫為950，事務T1讀B賬戶的余額為950，事務T2執行ROLLBACK命令取消了W(B?=?950)操作，但事務T1仍然執行W(B?=?1050)，在無效數據B?=?950上進行了操作，B?=?950是一個臟數據。A=260B=1000T1T2R(A=260)W(A=160)R(B=1000)R(B=1000)W(B=950)R(B=950)ROLLBACKR(A=260)W(A=310)A=310B=950A=160B=1050兩次讀的數據不一樣1、沒有鎖將導致的問題四、鎖（3）不可重復讀不可重復讀指一個事務對同一數據的讀取結果前后不致，這是由于在兩次查詢期間該數據被另一個事務修改并提交了。事務T1連續兩次執行讀B操作，第1次的結果是1000，第2次的結果是950，因為兩次讀的中間事務T2修改了B賬戶的余額，這樣對于同一個事務每次讀的數據可能不一樣，會引起混亂。1、沒有鎖將導致的問題四、鎖（4）幻象讀幻象讀指讀到的數據是不真實的，有一種“幻象”的感覺。原因在于在兩次查詢間隔中，有其他事務對相同的表作了插入或刪除操作。時間事務T1事務T2t0SELECT

t1

INSERTt2

COMMITt3INSERT（失敗）

t4SELECT事務T1在t0時刻執行了查詢語句，事務T2在t1時刻插入了一條數據并提交了，事務T1在t3時刻也插入一條相同的數據，但插入失敗了，t4時刻再次執行查詢，發現多了一行數據。這種數據忽多忽少的現象，稱為幻象讀。2、鎖的粒度四、鎖數據庫管理系統通過使用鎖方式來解決并行事務之間的沖突。封鎖的數據庫對象的大小稱為封鎖粒度。鎖定的數據量越少，發生鎖爭用的可能就越小，系統的并發程度就越高。SQLServer、MySQL等數據庫一般都提供了表級鎖和行級鎖。（1）表級鎖：鎖住整張表，其他事務不能操作這張表的數據。這種方式加鎖快，但是并發程度低。（2）行級鎖：鎖住正在使用的行（記錄），其他事務不能對這一行進行操作。這種方式加鎖慢，但是并發程度高。2、鎖的類型四、鎖（1）共享鎖共享鎖簡稱S鎖，又稱為讀鎖。共享鎖允許并行事務讀取同一項資源。如果對某對象加了共享鎖，則其他事務可以讀取該對象，但不能對該對象進行修改。共享鎖與排它鎖相容矩陣列

共享鎖S排它鎖X-共享鎖S√×√排它鎖X××√-√√√（2）排它鎖排它鎖簡稱X鎖，又稱為寫鎖。專門用于限制并行事務對資源的訪問。沒有其他事務可以讀或修改帶有排它鎖的數據。如果一個事務對某對象加了排它鎖，則其他事務在該對象上不能加任何鎖，直到鎖釋放。2、鎖的類型四、鎖在MySQL中，封鎖和解鎖數據表的語法格式如下：LOCKTABLEStbl_name{READ|WRITE},[tbl_name{READ|WRITE},…]UNLOCKTABLES【例7-12】事務T1獲得對數據表Orders的排它鎖權限，事務T2嘗試讀取數據表中的數據。T1:SELECT*FROMorders;--為數據表account加上排它鎖，轉向事務T2LOCKTABLESordersWRITE;T2:--事務T2讀取數據受阻SELECT*FROMorders;在SQLServer中，可以在SQL命令后加封鎖選項，如下：SELECT*FROMordersWITH(TABLOCKX)用commit或rollback解鎖2、鎖的類型四、鎖數據庫管理系統可以自動根據SQL指令加上行級鎖，表7-2是MySQL的行級鎖加鎖類型。表7-2MySQL的行級鎖加鎖類型SQL行級鎖類型INSERT排它鎖XUPDATE排它鎖XDELETE排它鎖XSELECT不加鎖SELECT...LOCKINSHAREMODE共享SSELECT...FORUPDATE排它鎖X在MySQL中，普通SELECT語句不加任何鎖，SELECT語句后用LOCKINSHAREMODE才加共享鎖。在SQLServer中，普通SELECT會加上共享鎖。2、鎖的類型四、鎖【例7-13】事務T1獲得對訂單編號為“202305200001”的訂單加排它鎖權限，事務T2嘗試對同樣的數據加共享鎖。T1:STARTTRANSACTION;SELECT*FROMordersWHEREcOrderNo='202305200001'FORUPDATE;T2:STARTTRANSACTION;SELECT*FROMordersWHEREcOrderNo='202305200001'LOCKINSHAREMODE;事務T2讀取數據會受阻。在SQLServer中，如果不希望SELECT加鎖，可在語句后面加WITHNOLOCK，例如：SELECT*FROMordersWITH(NOLOCK)。3、隔離級別隔離級別(IsolationLevel)是指一個事務和其他事務的隔離程度，即指定了數據庫如何保護(鎖定)那些當前正在被其他用戶或服務器請求使用的數據。SQL標準定義了以下四種隔離級別：READCOMMITTED：在此隔離級別下，SELECT命令不會返回尚未提交（Committed）的數據，也不能返回臟數據。它是SQLServer默認的隔離級別。READUNCOMMITTED：與READCOMMITTED隔離級別相反，它允許讀取已經被其它用戶修改但尚未提交確定的數據。REPEATABLEREAD：在此隔離級別下，用SELECT命令讀取的數據在整個命令執行過程中不會被更改。此選項會影響系統的效能，非必要情況最好不用此隔離級別。SERIALIZABLE：與DELETE語句中SERIALIZABLE選項含義相同。四、鎖隔離級別需要使用SET命令來設定其語法如下：SETTRANSACTIONISOLATIONLEVEL{READCOMMITTED|READUNCOMMITTED|REPEATABLEREAD|SERIALIZABLE} 3、隔離級別四、鎖【例7-14】設置事務T1的隔離級別為讀取未提交的數據（READUNCOMMITTED）。事務T2將A賬號的余額增加100元，不提交事務；事務T1讀取A賬號的余額。T1：SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;--設置隔離級別STARTTRANSACTION;--開啟事務,SQLServer中用BEGINTRANSACTIONT2：STARTTRANSACTION;--開啟事務,SQLServer中用BEGINTRANSACTIONUPDATE賬戶SET余額=余額?-?100WHERE賬號='A';T1：SELECT*FROM賬戶WHERE賬號='A';--讀取了T2未提交的數據T2：ROLLBACK;--回滾數據，使T1讀的數據成為了臟數據。T1：SELECT*FROM賬戶WHERE賬號='A';3、隔離級別四、鎖上述兩個事務的執行需要打開兩個不同的窗口，一個窗口啟動一個事務，MySQL如圖：3、隔離級別四、鎖圖7-10隔離級別為讀取未提交的數據圖中左邊窗口為事務T1，右邊窗口為事務T2，從圖中可以看出，事務T1查詢到賬號A的余額為900，是T2事務未提交的數據，當T2回滾后，T1再次讀取賬號A的余額為1000，之前讀的900是一個臟數據。1234【例7-15】設置事務T1的隔離級別為讀取提交的數據（READCOMMITTED）。事務T2將A賬號的余額增加100元，不提交事務；事務T1讀取A賬號的余額。T1：SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;--設置隔離級別STARTTRANSACTION;--開啟事務,SQLServer中用BEGINTRANSACTIONT2：STARTTRANSACTION;--開啟事務,SQLServer中用BEGINTRANSACTIONSELECT*FROM賬戶WHERE賬號='A';UPDATE賬戶SET余額=余額?-?100WHERE賬號='A'SELECT*FROM賬戶WHERE賬號='A';T1：SELECT*FROM賬戶WHERE賬號='A';--未讀取未提交的數據，MySQL返回已經提交了的數據，SQLServer將等待其他事務提交數據3、隔離級別四、鎖上述兩個事務的執行需要打開兩個不同的窗口，一個窗口啟動一個事務，MySQL如圖：3、隔離級別四、鎖圖7-11隔離級別為READCOMMITTED左邊窗口為事務T1，右邊窗口為事務T2，從圖中可以看出，雖然事務T2將A賬戶的余額修改為900，但事務T1讀到的余額仍然是1000，沒有讀到未提交的數據。12344、死鎖及其防止表7-3死鎖的例子步驟用戶1用戶21BEGINTRANSACTIONBEGINTRANSACTION2UPDATEToysSETcCategoryId='001'WHEREcToyID='000002'UPDATECategorySETvDescription=’布衣類’WHEREcCategoryId='001'3SELECT*FROMCategorySELECT*FROMToys四、鎖死鎖是這樣一種狀態：兩個用戶(或事務)各自的對象上有鎖，同時每個對象又在等待另一個對象上的鎖(釋放)4、死鎖及其防止四、鎖事務A鎖住了表Toys并想鎖住表Category，事務B鎖住了表Category并想鎖住表Toys。這就導致了一個死鎖，因為兩個事務都在等待另一個事務釋放各自的表。而兩個事務都不可能釋放各自鎖住的表，因此都必須等待。防止死鎖的途徑就是不能讓滿足死鎖條件的情況發生，為此，用戶需要遵循以下原則：盡量避免并發地執行涉及到修改數據的語句；要求每個事務一次就將所有要使用的數據全部加鎖，否則就不予執行；預先規定一個封鎖順序所有的事務，都必須按這個順序對數據執行封鎖，例如，不同的過程在事務內部對對象的更新執行順序應盡量保持一致；每個事務的執行時間不可太長，對程序段長的事務可考慮將其分割為幾個事務。4、死鎖及其防止四、鎖五、數據庫編程大多數據庫系統提供了編程語言，例如SQLServer的Transact-SQL、Oracle的PL/SQL等，這些編程語言對標準SQL語句進行了擴展，在普通SQL語句的使用上增加了編程語言的特點，通過邏輯判斷、循環等操作實現復雜的功能或者計算。MySQL同樣提供了編程功能。（1）聲明變量DECLARE@變量名

變量類型[@變量名

變量類型…]（2）變量賦值SELECT@局部變量=變量值SET@局部變量=變量值（3）if語句IF<條件表達式><命令行或程序塊>[ELSE<條件表達式>]<命令行或程序塊>]（4）程序塊BEGIN<命令行或程序塊>END1、SQLServer數據庫編程基礎【例7-18】DECLARE@xINT,@yINT,@zINTSELECT@x=1,@y=2,@z=3IF@x>@y

PRINT'x>y'--打印字符串?'x>y'ELSEIF@y>@zPRINT'y>z'ELSEPRINT'z>y'五、數據庫編程（5）while循環WHILE<條件表達式>BEGIN<命令行或程序塊>[BREAK][CONTINUE][命令行或程序塊]END1、SQLServer數據庫編程基礎【例7-19】WHILE語句用法示例。DECLARE@xINT,@yINT,@cINTSELECT@x=1,@y=1WHILE@x<3BEGINPRINT@x --打印變量x的值

WHILE@y<3

BEGINSELECT@c=100*@x+@yPRINT@c --打印變量c的值SELECT@y=@y+1

ENDSELECT@x=@x+1SELECT@y=1END五、數據庫編程（1）聲明變量DECLAREvar_name[,var_name]...type[DEFAULTvalue]（2）變量賦值SETvariable=expr[,variable=expr]...SELECTexpr1[,expr2,…]INTOvariable1[,variable1][FROMtable[WHEREcondition]]（3）IF語句IFsearch_condition1THENstatement_list1[ELSEIFsearch_condition2THENstatement_list2]...[ELSEstatement_listn]ENDIF2、MySQL數據庫編程基礎【例7-26】IF語句使用示例。CREATEPROCEDUREmyproc()BEGINDECLAREn,mINT;DECLAREretVARCHAR(50);SETn=200;

IFn>=1000THEN

SETret='n>=1000';SETm=1;ELSEIFn<=100THENSETret='n<=100';SETm=2;ELSESETret='n<1000&&n>100';SETm=3;

ENDIF;SELECTret,m;END五、數據庫編程五、數據庫編程（4）WHILE循環語句[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]2、MySQL數據庫編程基礎【例7-27】數據表test有三個字段：oidchar(36)、idnumberint、createtimedatetime。向數據表中插入10000行數據，oid字段的值使用UUID，idnumber字段的值從1到10000，createtime字段的值使用當前時間。CREATEPROCEDUREmyproc()BEGINDECLAREnINT;SETn=1;WHILEn<=10000DOINSERTINTOtestVALUES(UUID(),n,NOW());SETn=n+1;ENDWHILE;END創建此存儲過程后調用存儲過程：CALLmyproc();存儲過程（StoredProcedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數據庫中。用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。六、存儲過程六、存儲過程1、存儲過程的優點(1)存儲過程允許標準組件式編程。存儲過程在被創建以后可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL語句；而且數據庫專業人員可隨時對存儲過程進行修改，對應用程序源代碼毫無影響(因為應用程序源代碼只包含存儲過程的調用語句)，從而極大地提高了程序的可移植性。(2)存儲過程能夠實現較快的執行速度。如果某一操作包含大量的SQL代碼或分別被多次執行，那么存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。(3)存儲過程能夠減少網絡流量。對于一個針對數據庫對象的操作(如查詢、修改)，如果這一操作所涉及的SQL語句被組織成存儲過程，那么當在客戶計算機上調用該存儲過程時，網絡中傳送的只是該調用語句，否則將是多條SQL語句和語句返回的結果，從而大大增加了網絡流量。因此，使用存儲過程降低了網絡負載。(4)存儲過程可被作為一種安全機制來充分利用。系統管理員通過對執行某一存儲過程的權限進行限制，從而能夠實現對相應的數據訪問權限的限制，避免非授權用戶對數據的訪問，保證數據的安全。創建存儲過程時，需要確定存儲過程的三個組成部分；所有的輸入參數以及傳給調用者的輸出參數。被執行的針對數據庫的操作語句，包括調用其它存儲過程的語句；返回給調用者的狀態值，以指明調用是成功還是失敗。六、存儲過程2、創建存儲過程在SQLServer中創建存儲過程的語法如下：CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[OUTPUT]][,...n]ASsql_statement[...n]各參數的含義如下：procedure_name：是要創建的存儲過程的名字。@parameter：是存儲過程的參數。

Data_type：指參數的數據類型。

Default：指參數的缺省值。如果定義了缺省值，那么即使不給出參數值，該存儲過程仍能被調用。缺省值必須是常數，或者是空值。如果過程使用帶LIKE關鍵字的參數，則可包含下列通配符：%、_、[]?和?[^]。OUTPUT：表明該參數是一個返回參數。用OUTPUT參數可以向調用者返回信息。Text類型參數不能用作OUTPUT參數。

AS：指明該存儲過程將要執行的動作。Sql_statement：指任何數量和類型的包含在存儲過程中的SQL語句。六、存儲過程2、創建存儲過程（1）在SQLServer中創建存儲過程【例7-28】

帶輸入參數的存儲過程，根據輸入的購物者的ID號，返回購物者的名字、所訂購的玩具的名字和訂購數量。CREATEPROCEDUREprcShopper@ShopperIdCHAR(6)ASBEGIN SELECTvShopperName,vToyName,siQtyFROMShopper JOINOrdersONShopper.cShopperId=Orders.cShopperId JOINOrderDetailONOrders.cOrderNo=OrderDetail.cOrderNo JOINToysONOrderDetail.cToyId=Toys.cToyId WHEREShopper.cShopperId=@ShopperIdEND運行程序過程：EXECprcShopper'000002'六、存儲過程2、創建存儲過程（1）在SQLServer中創建存儲過程【例7-30】

在購物者表中有用戶的賬號(vUserName)和密碼(vPassword)，請編寫一個存儲過程，用于驗證用戶輸入的用戶名和密碼是否正確，如果正確返回0，不正確返回?-1。CREATEPROCEDUREcheckpassword@usernameVARCHAR(100),@passwordVARCHAR(100)ASBEGINIFEXISTS(SELECT*FROMShopperWHEREvUserName=@usernameandvPassword=@password)BEGINRETURN0ENDELSEBEGINRETURN-1ENDEND運行存儲過程：DECLARE@resultINTEXEC@result=checkpassword'liming','123'PRINT@result如果用戶名和密碼輸入正確，將返回0，否則返回?-1。六、存儲過程2、創建存儲過程（1）在SQLServer中創建存儲過程【例7-31】

創建一個存儲過程，用于計算某個玩具某年某月的總銷售量(存儲過程應有玩具ID、年、月三個參數)，將計算結果存入PickOfMonth表中。如果表中已經存在這個統計結果，需要先將其刪除再插入。CREATEPROCEDUREstat@toyidCHAR(6),@yearINT,@monthINTASBEGINDELETEFROMPickOfMonthWHEREcToyId=@toyidANDsiMonth=@monthANDiYear=@yearINSERTINTOPickOfMonth(cToyId,iYear,siMonth,iTotalSold)SELECT@toyid,@year,@month,SUM(siqty)FROMOrderDetailWHERE(cOrderNoIN(SELECTcOrderNoFROMOrdersWHEREYEAR(dOrderDate)=@yearANDMONTH(dorderdate)=@month))ANDcToyId=@toyidEND六、存儲過程2、創建存儲過程（1）在SQLServer中創建存儲過程【例7-32】

在開發應用程序時，可能需要分頁顯示數據。要求每次從數據庫中只讀取一頁數據，編寫一個具有分頁讀取數據功能的存儲過程來實現。CREATEPROCEDUREReadDataByPager(@Tablevarchar(100), /*表名*/@pkvarchar(100), /*主鍵*/@SortFieldvarchar(200), /*排序的字段*/@CurrentPageint=1, /*頁碼*/@PageSizeint=20, /*每頁大小*/@Fieldsvarchar(1000)='*', /*查詢的字段*/@Filtervarchar(1000)=NULL /*查詢條件*/)ASBEGINDECLARE@rowcountvarchar(50) /*用于存儲要排除的行數*/DECLARE@strFiltervarchar(1000) /*用于存儲條件表示式，不含Where*/DECLARE@SQLvarchar(4000) /*用于存儲要執行的Select語句*/六、存儲過程2、創建存儲過程（1）在SQLServer中創建存儲過程DECLARE@topnumvarchar(50) /*用于存儲要返回的行數*/SET@rowcount=CAST(((@Current1)*@PageSize)ASvarchar(50))/*計算要排除的行數*/SET@topnum=CAST(@PageSizeASvarchar(50))SET@strFilter='1=1' /*設置初始查詢條件*/IF@FilterISNOTNULLAND@Filter!=''BEGINSET@strFilter=@strFilter+'and('+@Filter+')'/*加入查詢條件*/ENDSET@SQL='SELECTTOP?'+@topnum+'*FROM?'+@Table+'?WHERE?'+@pk+'?NOTIN(SELECTTOP?'+@rowcount+'?cOrderNoFROM?'+@Table+'?WHERE?'+@strFilter+'?ORDERBY?'+@pk+')AND?'+@strFilter+'?ORDERBY?'+@pkEXEC(@SQL)/*執行命令*/END此存儲過程工作原理是：根據主鍵字段對數據排序，找到要排除的前面若干條((頁碼?-?1)?×

頁的大小)不屬于此頁的數據，再取若干條(頁的大小)此頁的數據。TOP關鍵字是每次限定取多少條數據，NOTIN關鍵字用于排除某部分數據。六、存儲過程2、創建存儲過程（1）在SQLServer中創建存儲過程在MySQL中創建存儲過程的語法基本格式如下：CREATEPROCEDURE[DEFINER={user|current_user}]procedure_name([procedure_parameter[,…]])BEGINroutine_bodyEND參數說明：（1）procedure_name：表示要創建的存儲過程名稱。（2）procedure_parameter：表示存儲過程的參數（即形式參數），是可選參數。每個參數由3部分組成，即參數傳遞類型、名稱和數據類型，其格式如下：[IN|OUT|INOUT]parameter_nametype①

參數傳遞類型有IN、OUT、INOUT三種類型。IN表示輸入類型；OUT表示輸出類型；INOUT表示既可以是輸入類型，也可是輸出類型。如果省略，默認為IN。②parameter_name表示參數的名稱，必須由用戶給出。③type表示參數的數據類型，可以是MySQL所支持的所有數據類型。（3）DEFINER：指明存儲過程的定義者，如果省略，則為當前用戶。（4）routine_body：表示存儲過程體。六、存儲過程2、創建存儲過程（2）在MySQL中創建存儲過程【例7-33】

在購物者表中有用戶的賬號(vUserName)和密碼(vPassword)，請編寫一個存儲過程，用于驗證用戶輸入的用戶名和密碼是否正確，如果正確返回0，不正確返回?-1。CREATEPROCEDUREcheckpassword(INusernameVARCHAR(100),INpasswordVARCHAR(100))BEGINDECLAREresultINTDEFAULT0;#聲明并初始化SELECTCOUNT(*)INTOresultFROMShopperWHEREvUserName=usernameANDvPassword=password;--用戶名和密碼正確，會有一條數據，將數量放入變量result中IFresult>0THENSETresult=0;ELSESETresult=-1;ENDIF;SELECTresult;END六、存儲過程2、創建存儲過程（2）在MySQL中創建存儲過程或者：CREATEPROCEDUREcheckpassword(INusernameVARCHAR(100),INpasswordVARCHAR(100))BEGINIFEXISTS(SELECT*FROMShopperWHEREvUserName=usernameandvPassword=password)THEN--用戶名和密碼正確會存在一條數據SELECT0;ELSESELECT-1;ENDIF;END調用存儲過程：callcheckpassword('wangming','youbet');如果用戶名和密碼輸入正確，將返回0，否則返回?-1。六、存儲過程2、創建存儲過程（2）在MySQL中創建存儲過程【例7-34】

在MySQL中創建一個存儲過程，用于計算某個玩具某年某月的總銷售量(存儲過程應有玩具ID、年、月三個參數)，將計算結果存入PickOfMonth表中。如果表中已經存在這個統計結果，需要先將其刪除再插入。CREATEPROCEDUREstat(p_toyidCHAR(6),p_yearINT,p_monthINT)BEGINDELETEFROMPickOfMonthWHEREcToyId=p_toyidANDsiMonth=p_monthANDiYear=p_year;INSERTINTOPickOfMonth(cToyId,iYear,siMonth,iTotalSold)SELECTp_toyid,p_year,p_month,SUM(siqty)FROMOrderDetailWHERE(cOrderNoIN(SELECTcOrderNoFROMOrdersWHEREYEAR(dOrderDate)=p_yearANDMONTH(dorderdate)=p_month))ANDcToyId=p_toyid;END六、存儲過程2、創建存儲過程（2）在MySQL中創建存儲過程【例7-35】

在MySQL中創建一個用于分頁讀取數據的存儲過程。CREATEPROCEDUREReadDataByPager(sqlstrvarchar(100), /*查詢語句*/currentpageint, /*頁碼*/pagesizeint /*每頁大小*/)BEGINDECLAREstartlineINT;setstartline=(current1)*pagesize;--計算起始行號，行號從0開始/*拼接要執行的SQL語句，使用limit子句限定數據行數*/SET@sqltext=CONCAT(sqlstr,'limit',CAST(startlineASCHAR),',',CAST(pagesizeASCHAR));PREPAREstatfrom@sqltext;--定義預處理語句

EXECUTEstat;--執行語句DEALLOCATEPREPAREstat;--釋放預處理語句END調用存儲過程：CALLReadDataByPager('select*fromtoys',3,3);六、存儲過程2、創建存儲過程（2）在MySQL中創建存儲過程（1）修改存儲過程可用ALTERPROCEDURE命令修改存儲過程，但存儲過程中有代碼，需要查看原來的代碼，對原來的代碼進行修改，一般用可視化工具修改存儲過程。（2）刪除存儲過程drop命令可將一個或多個存儲過程或者存儲過程組從當前數據庫中刪除，其語法規則如下：DROPPROCEDURE{procedure}}[,…n]【例7-36】

如將存儲過程prcGetShopperName從數據庫中刪除，則執行：DROPPROCEDUREcheckpassword六、存儲過程3、修改和刪除存儲過程七、用戶自定義函數用戶自定義函數(UserDefinedFunctions)是數據庫對象。用戶自定義函數不能用于執行一系列改變數據庫狀態的操作，但它可以像系統函數一樣在查詢或存儲過程等程序段中使用。用戶自定義函數可以返回一定的值。用戶自定義函數不能用于執行一系列改變數據庫狀態的操作，但它可以像系統函數一樣在查詢或存儲過程等的程序段中使用。七、用戶自定義函數SQLServer中創建標量型函數，其語法格式如下：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]parameter_data_type[=default]}[,...n]])RETURNSscalar_return_data_type[WITH<function_option>[[,]...n]][AS]BEGINfunction_bodyRETURNscalar_expressionEND各參數說明如下：?owner_name：指定用戶自定義函數的所有者。?function_name：指定用戶自定義函數的名稱，應是唯一的。

?@parameter_name：定義一個或多個參數的名稱。?parameter_data_type：指定標量型參數的數據類型。?scalar_return_data_type：指定標量型返回值的數據類型。?scalar_expression：指定標量型用戶自定義函數返回的標量值表達式。

?function_body：指定一系列的Transact-SQL語句，它們決定了函數的返回值。1、創建用戶自定義函數（1）SQLServer中創建自定義函數七、用戶自定義函數【例7-37】創建一個函數，判斷日期是否為周末。CREATEFUNCTIONisweekend(@dtdatetime)RETURNSintASBEGINDECLARE@aINT,@retINTSET@a=DATEPART(WEEKDAY,@dt)IF@a=7or@a=1SET@ret=1--周末ELSESET@ret=0--平時RETURN@retEND運行需要在函數名前加函數所有者，如下：SELECTdbo.isweekend('2024-9-20')1、創建用戶自定義函數（1）SQLServer中創建自定義函數七、用戶自定義函數【例7-38】一個值班安排表duty中的數據如圖所示，表中personid為人員編號，startdate為值班起始時間，enddate為值班結束時間。由于值班必須打卡，所以創建一個函數，判斷某人在打卡時間段內有沒有值班安排，輸入參數為：人員編號、打卡起始時間、打卡結束時間，有值班安排返回1，無值班安排返回0。1、創建用戶自定義函數（1）SQLServer中創建自定義函數節假日值班表duty七、用戶自定義函數CREATEFUNCTIONifonduty(@personidCHAR(36),--人員編號@sdDATETIME,--打卡起始時間@edDATETIME--打卡結束時間)RETURNSINTASBEGINDECLARE@retINTIFEXISTS(SELECT*FROMdutyWHEREpersonid=@personidANDstartdate<=@edANDenddate>=@sd)--參數給定的時間段與值班時間段有交集SET@ret=1--有值班ELSESET@ret=0--無值班RETURN@ret--將結果返回END1、創建用戶自定義函數（1）SQLServer中創建自定義函數七、用戶自定義函數一般在存儲過程或其他函數中調用函數，下面是調用此函數的一段代碼：ifdbo.ifonduty(@personid,@starttime,@endtime)=1--函數調用，判斷是不是該時段值班人員beginifdbo.ifcheck(@personid,@starttime,@endtime)=1--函數調用，判斷有無打卡記錄set@morning='值班'elseset@morning='休息'endelse--不是值班人員set@morning='休息1、創建用戶自定義函數（1）SQLServer中創建自定義函數七、用戶自定義函數在MySQL中創建用自定義函數的語法如下：CREATE[DEFINER={user|current_user}]FUNCTIONfunc_name([func_parameter[,…]])RETURNStype[characteristic...]BEGINfunc_bodyEND各參數說明如下：func_name：要創建的函數的名字，默認在當前數據庫中創建函數。若需要在特定數據庫中創建存儲過程，則要在名稱前面加上數據庫的名稱，即：數據庫名.func_name。RETURNStype：指明函數返回值的數據類型，type表示數據類型。func_body：函數體，由于函數運算結束后，必須有返回值，所以函數體中必須至少要有一個RETURN語句，格式是：RETURNvalue;。func_parameter是函數的參數（形式參數），函數的形式參數只能是IN類型，不能為OUT和INOUT類型。characteristic用于定義函數的狀態特征，它們的含義與格式同CREATEPROCEDURE語句中的規定。1、創建用戶自定義函數（2）MySQL中創建自定義函數七、用戶自定義函數【例7-39】在MySQL中創建一個函數，判斷日期是否為周末。CREATEFUNCTIONisweekend(dtdatetime)RETURNSint--判別日期是否為周末DETERMINISTIC--存儲函數的屬性，這里表示如果有相同輸入就有相同輸出BEGINDECLAREa,retINT;SETa=DAYOFWEEK(dt);--調用內置函數獲得星期的序號IFa=7ORa=1THENSETret=1;--周末ELSESETret=0;--平時ENDIF;RETURNret;END調用方法如下：SELECTISWEEKEND('2024-9-26');。1、創建用戶自定義函數（2）MySQL中創建自定義函數七、用戶自定義函數【例7-39】在MySQL中創建一個函數，判斷日期是否為周末。CREATEFUNCTIONisweekend(dtdatetime)RETURNSint--判別日期是否為周末DETERMINISTIC--存儲函數的屬性，這里表示如果有相同輸入就有相同輸出BEGINDECLAREa,retINT;SETa=DAYOFWEEK(dt);--調用內置函數獲得星期的序號IFa=7ORa=1THENSETret=1;--周末ELSESETret=0;--平時ENDIF;RETURNret;END調用方法如下：SELECTISWEEKEND('2024-9-26');。1、創建用戶自定義函數（2）MySQL中創建自定義函數七、用戶自定義函數ALTERFUNCTION命令也可以修改用戶自定義函數。此命令的語法與CREATEFUNCTION相同。

可以用DROPFUNCTION命令刪除用戶自定義函數，其語法如下：DROPFUNCTION{[owner_name.]function_name}[,...n]【例7-40】

刪除用戶自定義函數SalesByOrder，命令如下：DROPFUNCTIONSalesByOrder2、管理用戶自定義函數八、觸發器觸發器是一種特殊類型的存儲過程，它不同于前面介紹過的存儲過程。觸發器主要是通過事件進行觸發而被執行的，而存儲過程可以通過存儲過程名字而被直接調用。觸發器通常定義在表上，當對數據表進行插入（INSERT）、修改（UPDATE）和刪除（DELETE）操作時，觸發器就會被執行。八、觸發器CREATETRIGGER命令創建DML觸發器，其語法如下：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{

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

[NOTFORREPLICATION]

AS