第5章數(shù)據(jù)庫的高級應(yīng)用

任課人：cws第5章

數(shù)據(jù)庫的高級應(yīng)用章節(jié)內(nèi)容5.1架構(gòu)5.2索引5.3視圖5.4Transact-SQL編程基礎(chǔ)5.5存儲過程5.6觸發(fā)器5.7事務(wù)5.8鎖數(shù)據(jù)庫的高級應(yīng)用學(xué)習(xí)目標(biāo)：了解索引、視圖、游標(biāo)、存儲過程、觸發(fā)器及事務(wù)的作用掌握索引、視圖、游標(biāo)、存儲過程、觸發(fā)器及事務(wù)的創(chuàng)建方法掌握索引、視圖、游標(biāo)、存儲過程、觸發(fā)器及事務(wù)的修改及刪除方法5.3視圖【課堂任務(wù)】本節(jié)要理解視圖的作用及使用。視圖的概念及作用視圖的創(chuàng)建、修改和刪除5.3.1視圖概述視圖（虛擬表）是從一個或者幾個基本表或者視圖中導(dǎo)出的虛擬表，是從現(xiàn)有基表中抽取若干子集組成用戶的“專用表”，這種構(gòu)造方式必須使用SQL中的SELECT語句來實現(xiàn)。在定義一個視圖時，只是把其定義存放在數(shù)據(jù)庫中，并不直接存儲視圖對應(yīng)的數(shù)據(jù)，直到用戶使用視圖時才去查找對應(yīng)的數(shù)據(jù)。5.3.1視圖概述使用視圖具有如下優(yōu)點：(1)簡化對數(shù)據(jù)的操作(2)自定義數(shù)據(jù)(3)數(shù)據(jù)集中顯示(4)導(dǎo)入和導(dǎo)出數(shù)據(jù)(5)合并分割數(shù)據(jù)(6)安全機制5.3.2視圖的創(chuàng)建視圖創(chuàng)建方法1.使用SSMS創(chuàng)建視圖（演示）2.使用Transact-SQL語句創(chuàng)建視圖CREATEVIEWview_name[(Column[,…n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]5.3.2視圖的創(chuàng)建命令中的參數(shù)含義如下:Column：聲明視圖中使用的列名。如果省略，就使用基表的列名作為視圖的列名，也可在Select_statement語句中指定列名。WITHENCRYPTION：給系統(tǒng)表syscomments中視圖定義的SELECT命令加密。這個選項可用于提高SQLServer系統(tǒng)的安全性。AS：說明視圖要完成的操作。5.3.2視圖的創(chuàng)建命令中的參數(shù)含義如下:Select_statement：定義視圖的SELECT命令。注意：視圖中的SELECT命令不能包括INTO、ORDERBY等子句。臨時表也不能在查詢中引用。WITHCHECKOPTION：強制所有通過視圖修改的數(shù)據(jù)滿足Select_statement語句中指定的選擇條件。視圖創(chuàng)建成功后，可以在SSMS視圖窗口中看到新定義的視圖名稱。視圖可以由一個或多個表或視圖來定義。5.3.2視圖的創(chuàng)建【例5-2】有條件的視圖定義。定義視圖v_student，查詢所有選修數(shù)據(jù)庫課程的學(xué)生的學(xué)號(sno)、姓名(sname)、課程名稱(cname)和成績(degree)。該視圖的定義涉及了student表、course表和sc表。視圖定義后，可以像基本表一樣進(jìn)行查詢。例：若要查詢以上定義的視圖v_student，可以使用命令：SELECT*FROMv_studentCREATEVIEWv_studentASSELECTA.sno,sname,cname,degreeFROMstudentA,courseB,scCWHEREA.sno=C.snoANDB.cno=C.cnoANDcname='數(shù)據(jù)庫'5.3.3視圖的使用視圖的使用主要包括視圖的檢索、通過視圖對基表進(jìn)行插入、修改、刪除操作。視圖的檢索幾乎沒有什么限制：同基本表對通過視圖實現(xiàn)表的插入、修改、刪除操作則有一定的限制條件。5.3.3視圖的使用【例5-3】利用視圖向表student中插入一條數(shù)據(jù)創(chuàng)建視圖CREATEVIEWV1_studentASSELECTsno,sname,specialityFROMstudent向表中插入一條數(shù)據(jù)，腳本如下：INSERTINTOV1_studentVALUES('20050203','王小龍','計算機應(yīng)用')通過基本表，查詢是否插入了該條記錄：SELECT*FROMstudentWHEREsname='王小龍'5.3.3視圖的使用如果視圖以下屬性，則插入、更新或刪除基表將失?。?1)視圖定義中的FROM子句包含兩個或多個表，且SELECT選擇列表達(dá)式中的列包含來自多個表的列。(2)視圖的列是從集合函數(shù)派生的。

(3)視圖中的SELECT語句包含GROUPBY子句或DISTINCT選項。

(4)視圖的列是從常量或表達(dá)式派生的。5.3.4視圖的修改1、使用SSMS修改視圖（鼠標(biāo)右鍵選擇修改）2、使用T-SQL語句修改視圖ALTERVIEWview_name[(Column[,…n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]提示：如果在創(chuàng)建視圖時使用了WITHENCRYPTION選項和WITHCHECKOPTION選項，則在使用ALTERVIEW命令時，也必須包括這些選項。5.3.4視圖的修改【例5-5】修改例5-3中的視圖V1_student。ALTERVIEWV1_studentASSELECTsno,snameFROMstudent5.3.5視圖的刪除1.使用SSMS刪除視圖2.使用Transact-SQL語句刪除視圖DROPVIEW{view}[,…n]提示：DROPVIEW命令可以刪除多個視圖，各視圖名之間用逗號分隔。5.3.5視圖的刪除【例5-6】刪除視圖V1_student。DROPVIEWV1_student提示：①刪除視圖時，將從系統(tǒng)目錄中刪除視圖的定義和有關(guān)視圖的其他信息，還將刪除視圖的所有權(quán)限。②使用DROPTABLE刪除的表上的任何視圖都必須用DROPVIEW命令刪除。5.4Transact-SQL編程基礎(chǔ)【課堂任務(wù)】本節(jié)要熟悉Transact-SQL語言。Transact-SQL的語法規(guī)范Transact-SQL的語言基礎(chǔ)常用函數(shù)游標(biāo)的基本操作5.4Transact-SQL編程基礎(chǔ)1、常量與變量常量，也稱為文字值或標(biāo)量值，是指程序運行中值始終不改的量。在Transact-SQL程序設(shè)計過程中，定義常量的格式取決于它所表示的值的數(shù)據(jù)類型。5.4Transact-SQL編程基礎(chǔ)1、常量與變量變量，就是在程序執(zhí)行過程中，其值是可以改變的量。可以利用變量存儲程序執(zhí)行過程中涉及的數(shù)據(jù)。變量由變量名和變量值構(gòu)成，其類型與常量一樣。變量名不能與命令和函數(shù)名相同在SQLServer2008系統(tǒng)中，存在兩種類型的變量：一種是系統(tǒng)定義和維護(hù)的全局變量；另一種是用戶定義用來保存中間結(jié)果的局部變量。1、常量與變量1)系統(tǒng)全局變量通常將系統(tǒng)全局變量的值賦給局部變量以便保存和處理。全局變量以兩個@符號開頭。例如：@@error,@@rowcount等等。用戶可以通過全局變量來檢測系統(tǒng)的參數(shù)值。例：SELECT@@version：記錄當(dāng)前版本信息。1、常量與變量2)局部變量局部變量是作用域局限在一定范圍內(nèi)的Transact-SQL對象。局部變量被引用時要在其名稱前加上標(biāo)志@，而且必須先用DECLARE命令定義后才可以使用。1、常量與變量2)局部變量聲明局部變量，其語法如下:DECLARE@變量名變量類型[,@變量名變量類型]給變量賦值，其語法如下:Set@變量名稱=表達(dá)式Select@變量名稱=表達(dá)式[,…n]其中，@變量名稱是除cursor、text、ntext、image外的任何類型變量名。例：DECLARE@hellochar(20)Set@hello=‘hello,China!’1、常量與變量2)局部變量提示：①SELECT語句給局部變量賦值，可以一次給多個變量賦值；而SET一次只能給一個變量賦值。②局部變量的輸出可以使用PRINT。③聲明一個變量后，該變量將被初始化為NULL。1、常量與變量【例】USEgrademanagerGODECLARE@sexchar(2)SET@sex='女’SELECTsno,sname,sdeptFROMstudentWHEREssex=@sex1、常量與變量Go命令GO不是sql語句，是sqlcmd和osql實用工具及SSMS編譯器可識別的命令；作用：是將當(dāng)前批的sql語句信號；當(dāng)前批處理由上一個GO命令后所有語句組成；批處理：指多條語句放在一起依次執(zhí)行，批處理語句之間用GO隔開；GO命令不能和SQL語句同一行中，但可包含注釋；5.4Transact-SQL編程基礎(chǔ)2.表達(dá)式在Transact-SQL語言中，表達(dá)式由變量、常量、運算符、函數(shù)等元素組成。表達(dá)式可以在查詢語句中的任何位置使用。例如，檢索數(shù)據(jù)的條件，指定數(shù)據(jù)的值等。5.4Transact-SQL編程基礎(chǔ)【例】5.4Transact-SQL編程基礎(chǔ)3.Transact-SQL流程控制結(jié)構(gòu)化程序設(shè)計語言的基本結(jié)構(gòu)是順序結(jié)構(gòu)、條件分支結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。在Transact-SQL語言中，流程控制語句就是用來控制程序執(zhí)行流程的語句，也稱流控制語句或控制流語句。3.Transact-SQL流程控制1)BEGIN…END語句塊BEGIN…END可以定義Transact-SQL語句塊，這些語句塊作為一組語句執(zhí)行，允許語句嵌套。關(guān)鍵字BEGIN定義Transact-SQL語句的起始位置，END定義同一塊Transact-SQL語句的結(jié)尾。它的語法格式如下。BEGIN{sql_statement|statement_block}END3.Transact-SQL流程控制2)IF…ELSE條件語句用于指定Transact-SQL語句的執(zhí)行條件。如果條件為真，則執(zhí)行條件表達(dá)式后面的Transact-SQL語句。當(dāng)條件為假時，可以用ELSE關(guān)鍵字指定要執(zhí)行的Transact-SQL語句。它的語法格式如下。IFBoolean_expression{sql_statement|statement_block}ELSE{sql_statement|statement_block}3.Transact-SQL流程控制例如，使用IF…ELSE條件語句查詢李勇同學(xué)的家庭住址，如果空，則顯示“家庭地址不詳”，否則顯示其家庭地址。USEgrademanagerGOIF(SELECTsaddressFROMstudentWHEREsname='李勇')ISNULLBEGINPRINT'家庭地址不詳'ENDELSESELECTsaddressFROMstudentWHEREsname='李勇'3.Transact-SQL流程控制3)CASE分支語句CASE關(guān)鍵字可根據(jù)表達(dá)式的真假來確定是否返回某個值，可以允許使用表達(dá)式的任何位置使用這一關(guān)鍵字。使用CASE語句可以進(jìn)行多個分支的選擇。CASE語句具有如下兩種格式。①簡單格式：將某個表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定結(jié)果。②搜索格式：計算一組布爾表達(dá)式以確定結(jié)果。3.Transact-SQL流程控制3)CASE分支語句

(1)簡單CASE格式的語法如下。CASE<表達(dá)式>WHEN<條件表達(dá)式1>THEN<結(jié)果表達(dá)式1>…WHEN<條件表達(dá)式1>THEN<結(jié)果表達(dá)式1>[ELSE<結(jié)果表達(dá)式1>]END簡單CASE格式將某個表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定結(jié)果3.Transact-SQL流程控制3)CASE分支語句

(2)搜索CASE格式的語法如下。CASEWHEN<條件表達(dá)式1>THEN<結(jié)果表達(dá)式1>…WHEN<條件表達(dá)式1>THEN<結(jié)果[ELSE<結(jié)果表達(dá)式1>]ENDCASE搜索格式計算一組布爾表達(dá)式以確定結(jié)果。3.Transact-SQL流程控制簡單CASE格式舉例USEpubsGOSELECTCategory=CASEtypeWHEN'popular_comp'THEN'PopularComputing‘WHEN'mod_cook'THEN'ModernCooking'WHEN'business'THEN'Business'WHEN'psychology'THEN'Psychology'WHEN'trad_cook'THEN'TraditionalCooking'ELSE'Notyetcategorized'END,CAST(titleASvarchar(25))AS'ShortenedTitle',priceASPriceFROMtitlesWHEREpriceISNOTNULLORDERBYtype,priceCOMPUTEAVG(price)BYtypeGO3.Transact-SQL流程控制搜索CASE格式USEgrademanagerGOselectsnameas姓名,degree=case

whendegree>=90then'A'

whendegree<90anddegree>=70then'B'

whendegree<70anddegree>=60then'C'

whendegree<60then'D'endfromstudenta,scbwherea.sno=b.sno3.Transact-SQL流程控制4)WHILE循環(huán)語句WHILE語句是設(shè)置重復(fù)執(zhí)行Transact-SQL語句或語句塊的條件。當(dāng)指定的條件為真時，重復(fù)執(zhí)行循環(huán)語句?？梢栽谘h(huán)體內(nèi)設(shè)置BREAK和CONTINUE關(guān)鍵字，以便控制循環(huán)語句的執(zhí)行過程。其語法格式如下：WHILE<條件表達(dá)式>BEGIN<命令行或程序塊>IF<條件表達(dá)式>[BREAK]<命令行或程序塊>END3.Transact-SQL流程控制例如，使用WHILE語句求1～100之和。DECLARE@iint,@sumintSELECT@i=1,@sum=0/*或分兩行用set賦值*/WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDSELECT@sum3.Transact-SQL流程控制4．注釋注釋是程序代碼中不被執(zhí)行的文本字符串，用于對代碼進(jìn)行說明或進(jìn)行診斷的部分語句。在SQLServer2008系統(tǒng)中，支持兩種注釋方式:雙連字符(--)：從雙連字符到行尾都是注釋內(nèi)容。正斜杠星號字符(/*…*/)：開始注釋對(/*)和結(jié)束注釋對(*/)之間的所有內(nèi)容均視為注釋。5.4.2常用函數(shù)5.5存儲過程【課堂任務(wù)】本節(jié)要求掌握存儲過程的概念及應(yīng)用。存儲過程的概念存儲過程的創(chuàng)建及管理存儲過程中參數(shù)的使用5.5.1存儲過程概述1.什么是存儲過程存儲過程(StoredProcedure)是一組完成特定功能的SQL語句集（SQL語句+流程控制語句），經(jīng)編譯后存儲在數(shù)據(jù)庫中。存儲過程可包含程序流、編輯及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或者多個結(jié)果集及返回值。5.5.1存儲過程概述2.存儲過程的分類1)用戶定義的存儲過程該類存儲過程是指封裝了可重用代碼的模塊或者例程。存儲過程可以接受輸入函數(shù)、向客戶端返回表格或者標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言(DDL)和數(shù)據(jù)操作語言(DML)語句，然后返回輸入?yún)?shù)。在SQLServer2005中，用戶定義的存儲過程有兩種：Transact-SQL和CLR。5.5.1存儲過程概述(1)Transact-SQL存儲過程是指保存的Transact-SQL語句集合，可以接受和返回用戶提供的參數(shù)。(2)CLR存儲過程是指對Microsoft.NETFramework公共語言運行時(CLR)方法的引用，可以接受和返回用戶提供的參數(shù)。它們在.NETFramework程序集中作為類的公共靜態(tài)方法實現(xiàn)。5.5.1存儲過程概述2)擴展存儲過程擴展存儲過程允許使用編程語言(例如C語言)建立自己的外部例程。擴展存儲過程是指MicrosoftSQLServer2005的實例可以動態(tài)加載和運行DLL。擴展存儲過程直接在SQLServer2005的實例的地址空間中運行，可以使用SQLServer擴展存儲過程API完成編程。5.5.1存儲過程概述3)系統(tǒng)存儲過程SQLServer2005中的許多管理活動都通過執(zhí)行一種特殊的存儲過程實現(xiàn)，這種存儲過程被稱為系統(tǒng)存儲過程。例如，sys.sp_changedbowner就是一個系統(tǒng)存儲過程。雖然SQLServer2005中的系統(tǒng)存儲過程被放在master數(shù)據(jù)庫中，但是仍可以在其他數(shù)據(jù)庫中對其進(jìn)行調(diào)用，而且在調(diào)用時不必再在存儲過程名前加上數(shù)據(jù)庫名。例如查看該存儲過程：EXECsp_helptextsp_changedbowner。5.5.1存儲過程概述3.存儲過程的特點(1)接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過程或者批處理返回多個值。(2)包含用于在數(shù)據(jù)庫中執(zhí)行操作(包括調(diào)用其他過程)的編程語句。(3)向調(diào)用過程或者批處理返回狀態(tài)值，以指明成功或者失敗(以及失敗的原因)。注意：存儲過程與函數(shù)不同，因為存儲過程不返回取代其名稱的值，也不能直接在表達(dá)式中使用。5.5.1存儲過程概述Transact-SQL程序有以下幾點優(yōu)點:(1)存儲過程與其他應(yīng)用程序共享應(yīng)用程序邏輯，因而確保了數(shù)據(jù)訪問和修改的一致性。(2)存儲過程提供了安全機制。即使是沒有訪問存儲過程引用的表或者視圖權(quán)限的用戶，也可以被授權(quán)執(zhí)行該存儲過程。(3)存儲過程允許模塊化程序設(shè)計。存儲過程一旦創(chuàng)建，以后即可在程序中調(diào)用任意次。這可以改進(jìn)應(yīng)用程序的可維護(hù)性，并允許應(yīng)用程序統(tǒng)一訪問數(shù)據(jù)庫。(4)存儲過程可以減少網(wǎng)絡(luò)通信流量。用戶可以通過發(fā)送一個單獨的語句實現(xiàn)一個復(fù)雜的操作，而不需要在網(wǎng)絡(luò)上發(fā)送幾百個Transact-SQL代碼，這樣減少了在服務(wù)器和客戶機之間傳遞請求的數(shù)量。5.5.2使用存儲過程1、創(chuàng)建存儲過程使用CREATEPROCEDURE語句創(chuàng)建存儲過程的語法如下

CREATEPROCEDUREprocedure_name[{@parameterdata_type}[=default][OUTPUT][,…n]ASsql_statement[…n]1、創(chuàng)建存儲過程其中各參數(shù)含義如下：(1)procedure_name：存儲過程的名稱。(2)@parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或者多個參數(shù)。(3)data_type：參數(shù)的數(shù)據(jù)類型。(4)default：參數(shù)的默認(rèn)值。如果定義default值，則無需指定此參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或者NULL。如果過程使用帶LIKE關(guān)鍵字的參數(shù)，則可包含下列通配符：%、_。(5)OUTPUT：指示參數(shù)的輸出參數(shù)。此選項的值可以返回給調(diào)用的EXECUTE的語句。(6)<sql_statement>：要包含在過程中的一個或者多個Transact-SQL語句。1、創(chuàng)建存儲過程例題：下面創(chuàng)建一個基本存儲過程，從數(shù)據(jù)庫grademanager的student表中檢索出姓“李”的學(xué)生的學(xué)號、姓名、班級號及家庭地址等信息。具體語句如下：USEgrademanagerGOCREATEPROCEDUREpro_學(xué)生信息ASSELECTsno,sname,classno,addressFROMstudentWHEREsnameLIKE‘李%'ORDERBYsnoGO1、創(chuàng)建存儲過程執(zhí)行存儲過程“pro_學(xué)生信息”，返回所有“姓李”的學(xué)生信息USEgrademanagerGOEXECpro_學(xué)生信息或pro_學(xué)生信息1、創(chuàng)建存儲過程建議使用下面的方式建立存儲過程。ifexists(selectnamefromsysobjectswherename='pro_學(xué)生信息'andtype='p')dropprocedurepro_學(xué)生信息GoCREATEPROCEDUREpro_學(xué)生信息ASSELECTsno,sname,classno,saddressFROMstudentWHEREsnameLIKE‘李%'ORDERBYsnoGO5.5.2使用存儲過程2.使用存儲過程參數(shù)1)參數(shù)的定義SQLServer2005的存儲過程可以使用兩種類型的參數(shù)：輸入?yún)?shù)和輸出參數(shù)。參數(shù)用于在存儲過程以及應(yīng)用程序之間交換數(shù)據(jù)。(1)輸入?yún)?shù)允許用戶將數(shù)據(jù)值傳遞到存儲過程或者函數(shù)。(2)輸出參數(shù)允許存儲過程將數(shù)據(jù)值或者游標(biāo)變量傳遞給用戶。(3)每個存儲過程向用戶返回一個整數(shù)代碼，如果存儲過程沒有明顯設(shè)置返回代碼的值，返回代碼為零。2.使用存儲過程參數(shù)2)輸入?yún)?shù)：在執(zhí)行存儲過程時需指定值例：USEgrademanagerGOCREATEPROCEDUREpro_學(xué)生_性別_信息@性別NVARCHAR(10)ASSELECTa.sno,sname,ssex,cname,degreeFROMstudenta,scb,coursecWHEREa.sno=o=oandcname='數(shù)據(jù)庫'andssex=@性別GO2.使用存儲過程參數(shù)執(zhí)行帶有輸入?yún)?shù)的存儲過程時，SQLServer2005提供了兩種傳遞參數(shù)的方式。(1)按位置傳遞。在執(zhí)行過程的語句中，直接給出參數(shù)的值。當(dāng)有多個參數(shù)時，參數(shù)傳遞的順序就是參數(shù)定義的順序。

EXECpro_學(xué)生_性別_信息'女'(2)通過參數(shù)名傳遞。在執(zhí)行存儲過程的語句中，使用“參數(shù)名=參數(shù)值”。好處，參數(shù)可以以任意順序給出。

EXECpro_學(xué)生_性別_信息@性別='男'2.使用存儲過程參數(shù)3)使用默認(rèn)參數(shù)值如果希望不給出參數(shù)時也能夠正確運行，則可以通過給參數(shù)設(shè)置默認(rèn)值來實現(xiàn)。USEgrademanagerGOCREATEPROCEDUREpro_學(xué)生_性別_信息@性別VARCHAR(2)='男'ASSELECTa.sno,sname,ssex,sname,cname,degreeFROMstudenta,scb,coursecWHEREa.sno=o=oandcname='數(shù)據(jù)庫'andssex=@性別GO2.使用存儲過程參數(shù)4)輸出參數(shù)通過定義輸出參數(shù)，可以從存儲過程中返回一個或者多個值。為了使用輸出參數(shù)，必須在CREATEPROCEDURE語句和EXECUTE語句中指定關(guān)鍵字OUTPUT。在執(zhí)行存儲過程時，如果忽略O(shè)UTPUT關(guān)鍵字，存儲過程仍然會執(zhí)行但不返回值。2.使用存儲過程參數(shù)4)輸出參數(shù)usegrademanagergocreateprocedureavgscore@c_nochar(3),@avgsnumericoutputasselect@avgs=avg(degree)fromscwherecno=@c_nogroupbycnogodeclare@avgsnumericexecavgscore'c01',@avgsoutput