第六章-SQL-編程技術(shù)

第六章SQL編程技術(shù)16.1SQL編程基礎(chǔ)6.1.1批處理批處理是一個以“ＧＯ”為結(jié)束的語句集．這些語句是作為一個組來執(zhí)行并一起提交的．

腳本是一系列順序執(zhí)行的批處理．腳本文件的文件類型為.sql注意：ＧＯ不是ＳＱＬ語句．2例：給出含有三個批處理的腳本CREATETABLE學(xué)院( 學(xué)院名稱char(12)PRIMARYKEY, 院長char(8), 學(xué)院地址char(16), 辦公電話char(8), 編制人數(shù)smallint)GOINSERTINTO學(xué)院VALUES('計算機學(xué)院','劉洋','一號樓4','88889999',50)INSERTINTO學(xué)院VALUES('商學(xué)院','梁山','五號樓302','99998888',60)GOSELECT*FROM學(xué)院GO36.1.2變量1.變量的類型局部變量--以@引導(dǎo),由用戶定義.其作用域定義的轄域中.全局變量--以@@引導(dǎo),由系統(tǒng)提供并賦值.其作用域為全局.46.1.2變量2.變量的聲明語句格式:DECLARE@變量名數(shù)據(jù)類型[,@變量名數(shù)據(jù)類型……]例:聲明變量DECLARE@NameCHAR(8),@SexCHAR(2),@AgeSMALLINT56.1.2變量3.變量的賦值語句格式一:SET@變量名稱=表達(dá)式語句格式二:SELECT@變量名稱=表達(dá)式

語句格式三:

SELECT

@變量名稱=字段(或函數(shù))FROM表(或視圖)名

WHERE<條件表達(dá)式>

6舉例例1:DECLARE@NameCHAR(8),@SexCHAR(2),@AgeSMALLINTSET@Name=‘李華’SET@Sex=‘女’SET@Age=20例2:DECLARE@NameCHAR(8)SELECT@Name=‘李華’7舉例例3:DECLARE@NameCHAR(8),@SexCHAR(2),@AgeSMALLINTSELECT@Name=姓名,@Sex=性別,@Age=YEAR(GETDATE())-YEAR(出生日期)FROM學(xué)生WHERE學(xué)號='00150236'SELECT@Name,@Sex,@AgeGO顯示結(jié)果86.1.3控制流語句包含條件控制語句、無條件轉(zhuǎn)移語句、循環(huán)語句等?？刂普Z句說明IF...ELSE條件語句GOTO無條件轉(zhuǎn)移語句WHILE循環(huán)語句CONTINUE用于重新開始下一次循環(huán)BREAK用于退出最內(nèi)層的循環(huán)RETURN無條件返回96.1.3控制流語句１.BEGIN…END功能：將一組SQL語句作為一個語句塊。語句格式：BEGIN<SQL語句序列>END與IF語句和WHILE語句配合使用106.1.3控制流語句2.IF…ELSE語句語句格式：IF<條件表達(dá)式>

<SQL語句序列1>ELSE

<SQL語句序列2>1112舉例USE教學(xué)數(shù)據(jù)庫GODECLARE@gavgsmallintSELECT@gavg=AVG(成績)FROM選課WHERE課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語')IF(@gavg>80)PRINT'大學(xué)英語課程的平均成績高于80分'ELSE

BEGIN

PRINT'大學(xué)英語課程的平均成績低于80分'select'平均成績:',@gavg

END136.1.3控制流語句3.循環(huán)語句語句格式：WHILE<條件表達(dá)式><SQL語句序列1>[BREAK][<SQL語句序列1>][CONTINUE]

146.1.3控制流語句15求出大學(xué)英語課程的平均成績，當(dāng)該分?jǐn)?shù)小于８0分時，循環(huán)做,給所有成績高于平均分的成績加１分，當(dāng)最高分大于１００時停止加分．并顯示加的分?jǐn)?shù)．該分?jǐn)?shù)大于８0分時，循環(huán)停止并顯示加的分?jǐn)?shù)16舉例DECLARE@gavgsmallint,@COUNsmallintSET@COUN=0SELECT@gavg=AVG(成績)FROM選課WHERE課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語')WHILE(@gavg<80)

BEGINSET@COUN=@COUN+1UPDATE選課set成績=成績+1where課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語')AND成績>@gavgIF(SELECTmax(成績)FROM選課WHERE課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語'))>100BREAK

ENDPRINT@COUN17舉例DECLARE@gavgsmallint,@COUNsmallintSET@COUN=0SELECT@gavg=AVG(成績)FROM選課WHERE課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語')PRINT@gavgWHILE(@gavg<80)BEGINSET@COUN=@COUN+1UPDATE選課set成績=成績+1where課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語')AND成績>@gavgIF(SELECTmax(成績)FROM選課WHERE課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語'))>=100BREAKELSESELECT@gavg=AVG(成績)FROM選課WHERE課程號=(SELECT課程號FROM課程WHERE課程名稱='大學(xué)英語')

ENDPRINT@COUN184.RETURN語句語句格式：RETURN[整數(shù)]功能：無條件所在批處理、存儲過程或觸發(fā)器?？梢苑祷匾粋€整數(shù)值6.1.3控制流語句19舉例題目：根據(jù)給定的學(xué)號（@param）檢查學(xué)生的平均成績，若>75，將返回狀態(tài)代碼1，將返回狀態(tài)代碼2。USE教學(xué)數(shù)據(jù)庫CREATEPROCEDUREcheckavg@paramvarchar(10)ASIF(SELECTAVG(成績)FROM選課WHERE學(xué)號=@param)>75RETURN１ELSERETURN０20執(zhí)行存儲過程：declare@aasmallintexec@aa=checkavg'00150236'if@aa=０print'小于75'elseprint'大于75'216.1.4EXECUTE語句功能：執(zhí)行函數(shù)、存儲過程語法格式：EXEC[UTE]<過程名><參數(shù)>［output］226.1.5注釋１.單行注釋－－（兩個減號）２.多行注釋/**/236.1.6程序設(shè)計舉例題目：轉(zhuǎn)帳，若Ａ帳戶的余額大于等于100元，從Ａ帳戶上支取100元，存入Ｂ帳戶.否則，不執(zhí)行轉(zhuǎn)帳操作。begintransactionupdate帳戶set余額=余額-100where帳戶='A'IF(SELECT余額from帳戶where帳戶='A')<=0beginprint'金額不夠。轉(zhuǎn)帳失敗！'rollbacktransactionendelsebeginupdate帳戶set余額=余額+100where帳戶='B'print'轉(zhuǎn)帳成功！'committransactionend24事務(wù)概述事務(wù)是作為單個邏輯工作單元執(zhí)行的一系列操作。事務(wù)的屬性（ACID)原子性：原子工作單元一致性：保證數(shù)據(jù)的一致性隔離性：并發(fā)事務(wù)之間所做的修改要隔離并發(fā)操作：幾個用戶程序同時讀寫一個數(shù)據(jù)的情況持久性：對系統(tǒng)的影響要持久25事務(wù)操作語法格式：BEGINTRANSACTION事務(wù)開始COMMITTRANSACTION提交ROLLBACKTRANSACTION回滾說明：如果沒有明確給出BEGINTRANSACTION語句，則SQLServer是將每個SQL語句都當(dāng)成一個事務(wù)進行執(zhí)行266.3存儲過程6.3.1存儲過程的概念

是一組被編譯在一起的T-SQL語句的集合，它們被集合在一起以完成一個特定的任務(wù)。存儲過程的分類系統(tǒng)存儲過程擴展存儲過程（提供從SQLServer到外部程序的接口，以便進行各種維護活動）用戶自定義的存儲過程27使用存儲過程的優(yōu)勢模塊化編程：創(chuàng)建一個存儲過程存放在數(shù)據(jù)庫中后，就可以被其他程序反復(fù)使用。快速執(zhí)行：存儲過程第一次被執(zhí)行后，就駐留在內(nèi)存中。以后執(zhí)行就省去了重新分析、優(yōu)化、編譯的過程。減少網(wǎng)絡(luò)通信量有了存儲過程后，在網(wǎng)絡(luò)上只要一條語句就能執(zhí)行一個存儲過程。安全機制通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性，通過授權(quán)可以讓用戶只能執(zhí)行存儲過程而不能直接訪問數(shù)據(jù)庫對象。286.3.2創(chuàng)建存儲過程創(chuàng)建過程語法格式：CREATEPROCEDURE<過程名>［<參數(shù)>數(shù)據(jù)類型，…］[OUTPUT]AS<SQL語句序列>執(zhí)行過程語法格式：EXECUTE或EXEC<過程名>［變量=］值刪除存儲過程語法格式：

DROPPROCEDURE<過程名>29例：檢索某個學(xué)生(學(xué)號為‘01111111’）的成績單，包括姓名、課程名、分?jǐn)?shù)。存儲過程名為sc_gradeIFEXISTS(SELECTnameFROMsysobjectsWHEREname='sc_grade'ANDtype='P')DROPPROCEDUREsc_gradeGOCREATEPROCEDUREsc_grade@SNOCHAR(8)ASSELECT姓名,課程名稱,成績FROM學(xué)生S,課程C,選課SCWHERES.學(xué)號=SC.學(xué)號ANDC.課程號=SC.課程號ANDS.學(xué)號=@SNOGO30執(zhí)行過程EXECsc_grade'01111111'或EXECsc_grade@sno='01111111'31例在“教學(xué)”數(shù)據(jù)庫建立一個名為ScroeProc的存儲過程，它帶有兩個輸入?yún)?shù)并返回兩個輸出參數(shù)和一個返回值。程序清單如下：USE教學(xué)GO--存儲過程ScroeProc若存在，則刪除之IFEXISTS(SELECTnameFROMsysobjectsWHEREname=’Scroe’ANDtype=’P’)DROPPROCEDUREScroeProcGO32--創(chuàng)建存儲過程ScroeProc--輸入?yún)?shù)：@Dno輸入系科號；@Cname輸入課名--輸出參數(shù)：@Avg接受平均分；@Max接受最高分CREATEPROCEDUREScroeProc@Dnochar(4),@Cnamevarchar(30),@AvgdecimalOUTPUT,@MaxdecimalOUTPUTAS/*聲明和初始化一個局部變量，用于保存系統(tǒng)函數(shù)@@ERROR的返回值*/DECLARE@ErrorSaveintSET@ErrorSave=033--執(zhí)行一個選擇查詢，統(tǒng)計指定班級某門課的平均成績--通過兩個輸入?yún)?shù)接受系號和課程名稱--通過一個輸出參數(shù)@Avg接受平均分SELECT@Avg=AVG(成績)FROM選課scINNERJOIN學(xué)生stONsc.學(xué)號=st.學(xué)號INNERJOIN課程COONsc.課程號=co.課程號GROUPBY系科號,課程名稱HAVING系科號=@DnoAND課程名稱=@CnameIF(@@ERROR<>0)SET@ErrorSave=@@ERROR34/*執(zhí)行一個選擇查詢，統(tǒng)計某門課的最高分*//*通過兩個輸入?yún)?shù)接受系科號和課程名稱*//*通過一個輸出參數(shù)@Max接受最高分*/SELECT@Max=Max(成績)FROM選課scINNERJOIN學(xué)生stONsc.學(xué)號=st.學(xué)號INNERJOIN課程COONsc.課程號=co.課程號GROUPBY系科號,課程名稱HAVING系科號=@DnoAND課程名稱=@CnameIF(@@ERROR<>0)SET@ErrorSave=@@ERRORRETURN@ErrorSave35/*聲明變量，用于保存返回值和輸出結(jié)果*/DECLARE@RetCodeint@AvgGradedecimal,@MaxGradedecimal/*執(zhí)行存儲過程，并指定輸入?yún)?shù)和輸出參數(shù)*/EXECUTE@RetCode=ScroeProc’15’,’數(shù)據(jù)結(jié)構(gòu)’,@AvgGradeOUTPUT,@MaxGradeOUTPUTselect@AvgGrade,@MaxGrade366.4數(shù)據(jù)庫觸發(fā)器6.4.1觸發(fā)器的概念是一種實現(xiàn)復(fù)雜完整性約束的特殊存儲過程，是能夠在符合條件是自動觸發(fā)的SQL程序。1.觸發(fā)器的特點

2.觸發(fā)器的優(yōu)點376.4.1觸發(fā)器的概念若觸發(fā)器所依賴的表中有約束，則在執(zhí)行時，約束優(yōu)于觸發(fā)器，而且如果在操作中觸發(fā)器和約束發(fā)生沖突，觸發(fā)器將不執(zhí)行。觸發(fā)器操作是一個事務(wù)操作。386.4.1觸發(fā)器的概念在下列情況下可以考慮使用觸發(fā)器：強制比CHECK約束復(fù)雜的數(shù)據(jù)完整性使用自定義的錯誤信息和執(zhí)行復(fù)雜的錯誤處理實現(xiàn)多張表的級聯(lián)修改比較數(shù)據(jù)庫修改前后數(shù)據(jù)的狀態(tài)維護非規(guī)范數(shù)據(jù)396.4.2創(chuàng)建觸發(fā)器語法格式：CREATETRIGGER<觸發(fā)器名>

On<表名>

FOR

|AFTER|INSTEADOFINSERT|UPDATE|DELETEAS<SQL語句序列>刪除觸發(fā)器語法格式：DROPTRIGGER<觸發(fā)器名>

40語句解釋：AFTER:指定觸發(fā)器只有在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定FOR關(guān)鍵字，則AFTER是默認(rèn)設(shè)置。不能在視圖上定義AFTER觸發(fā)器。INSTEADOF:指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而替代觸發(fā)語句的操作。INSERT,UPDATE,DELETE:是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個選項。在觸發(fā)器定義中允許使用以任意順序組合的這些關(guān)鍵字。如果指定的選項多于一個，需用逗號分隔這些選項。41使用觸發(fā)器的限制(1)CREATETRIGGER必須是批處理中的第一條語句，并且只能應(yīng)用到一個表中。(2)觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)建，但觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫的外部對象。(3)如果指定觸發(fā)器所有者名限定觸發(fā)器，要以相同的方式限定表名。(4)在同一CREATETRIGGER語句中，可以為多種操作（如INSERT和UPDATE）定義相同的觸發(fā)器操作。

42使用觸發(fā)器的限制(5)一個表的外鍵在DELETE、UPDATE操作上定義了級聯(lián)，不能在該表上定義INSTEADOFDELETE、INSTEADOFUPDATE觸發(fā)器。(6)觸發(fā)器中不允許包含以下T-SQL語句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE

436.4.3觸發(fā)器的工作原理當(dāng)觸發(fā)器被觸發(fā)時，系統(tǒng)會創(chuàng)建兩個專用臨時表：inserted表和deleted表。這兩個表由系統(tǒng)來維護，不允許用戶直接對這兩個表進行修改（可以讀）。它們存放于內(nèi)存中，不存放在數(shù)據(jù)庫中。這兩個表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同。觸發(fā)器工作完成后，與該觸發(fā)器相關(guān)的這兩個表也會被刪除。446.4.3觸發(fā)器的工作原理insterted表：存放由于INSERT或UPDATE語句的執(zhí)行而導(dǎo)致要加到該觸發(fā)表中去的所有新行。即用于插入或更新表的新行值，在插入或更新表的同時，也將其副本存入insterted表中。因此，在insterted表中的行總是與觸發(fā)表中的新行相同。456.4.3觸發(fā)器的工作原理deleted表：存放由于DELETE或UPDATE語句的執(zhí)行而導(dǎo)致要從該觸發(fā)表中刪除的所有行。也就是說，把觸發(fā)表中要刪除或要更新的舊行移到deleted表中。因此，deleted表和觸發(fā)表不會有相同的行。466.4.3觸發(fā)器的工作原理對INSERT操作，只在insterted表中保存所插入的新行，而deleted表中無一行數(shù)據(jù)?！顚τ贒ELETE操作，只在deleted表中保存被刪除的舊行，而insterted表中無一行數(shù)據(jù)。對于UPDATE操作，可以將它考慮為DELETE操作和INSERT操作的結(jié)果，所以在inserted表中存放著更新后的新行值，deleted表中存放著更新前的舊行值。

47例:在學(xué)生表上創(chuàng)建觸發(fā)器reminder，若在學(xué)生表中添加、更改和刪除數(shù)據(jù)，則將向客戶端顯示信息。USE教學(xué)

IFEXISTS(SELECTnameFROMsysobjects WHEREname='reminder'ANDtype='TR')DROPTRIGGERreminderGOCREATETRIGGERreminderON學(xué)生 FORINSERT,UPDATE,DELETEASprint'注意：學(xué)生表數(shù)據(jù)被修改。'SELECT*FROMINSERTEDSELECT*FROMDELETEDGO48INSERTED表DELETED表insertinto學(xué)生(學(xué)號,姓名)values('9999','dd')UPDATE學(xué)生SET姓名='WWWW'WHERE學(xué)號='9999'DELETED表INSERTED表deletefrom學(xué)生where學(xué)號='9999'49舉例：創(chuàng)建一觸發(fā)器，當(dāng)向成績表插入一記錄時，檢查該記錄的學(xué)號在學(xué)生表是否存在，檢查課程號在課程表中是否存在，若有一項為否，則不允許插入。

Use教學(xué)GOCREATETRIGGERcheck_trigON成績FORINSERTASIFEXISTS(SELECT* FROMinserteda WHEREa.學(xué)號NOTIN(SELECT學(xué)號FROM學(xué)生)ORa.課程編號NOTIN(SELECT課程編號FROM課程))BEGIN RAISERROR(‘違背數(shù)據(jù)的一致性?。。?！',10,1) ROLLBACKTRANSACTIONENDGO50insertinto成績values('3012','C6',90)SELECT*FROM成績WHERE課程編號LIKE'%6'執(zhí)行插入操作：insertinto成績values('5012','C6',90)服務(wù)器:消息547，級別16，狀態(tài)1，行1INSERT語句與COLUMNFOREIGNKEY約束'FK__成績__學(xué)號__6B24EA82'沖突。該沖突發(fā)生于數(shù)據(jù)庫'教學(xué)'，表'學(xué)生',column'學(xué)號'。語句已終止。51分別刪除了成績表、課程表、學(xué)生表的聯(lián)系insertinto成績values('5012','C6',90)526.2游標(biāo)及其應(yīng)用6.2.1游標(biāo)的概念

如果要求每次只顯示表格中的一行，該如何處理？——這在將T-SQL嵌入到其他高級語言（如VC、VB、Delphi等）的編程中經(jīng)常用到。536.2.1游標(biāo)的概念游標(biāo)(cursor)是一個存儲區(qū)域，用來存放結(jié)果集。游標(biāo)的指針，可以指向與它相關(guān)聯(lián)的結(jié)果集中的任意一行，以便對當(dāng)前位置的行進行處理。游標(biāo)提供了對一個結(jié)果集進行逐行處理的能力：在結(jié)果集中定位特定行從結(jié)果集的當(dāng)前位置檢索行支持對結(jié)果集中當(dāng)前位置的行進行數(shù)據(jù)處理（修改/刪除）546.2.2游標(biāo)的用法聲明游標(biāo)打開游標(biāo)處理數(shù)據(jù)（讀取/修改/刪除）——可以和其他T-SQL語句配