第七章數(shù)據(jù)庫(kù)行為特征設(shè)計(jì)：SQL程序設(shè)計(jì)匯報(bào)人：AiPPTDESIGNCatalogue目錄1.變量的定義和使用2.函數(shù)和表達(dá)式3.存儲(chǔ)過程4.觸發(fā)器5.臨時(shí)表6.游標(biāo)7.批處理和事務(wù)01變量的定義和使用局部變量局部變量在BEGIN..END塊內(nèi)有效，聲明后自動(dòng)消失。示例：DECLARENumINTEGERDEFAULT0，聲明整型變量Num，默認(rèn)值為0。用戶變量用戶變量從賦值后到斷開連接有效，其他連接無(wú)法訪問。示例：SET@MaxGrade=(SELECTMAX(Grade)FROMStudent_ElectiveWHEREStdId='19001')*0.9，計(jì)算最高成績(jī)的90%。系統(tǒng)變量系統(tǒng)變量分為全局變量和會(huì)話變量，用戶可讀取或修改其值。示例：SET@@GLOBAL.auto_increment_offset=100，設(shè)置全局自增長(zhǎng)列的起始值。變量的聲明和使用變量的定義和使用局部變量生存期為定義它的批處理或存儲(chǔ)過程局部變量的定義：

DECLARE@<變量名><數(shù)據(jù)類型>[，...n]

例：DECLARE@numINTEGER局部變量賦值：SET@變量名=<表達(dá)式>或SELECT@<變量名>=〈表達(dá)式〉[FROM<表名>[，...n]WHERE<子句>]顯示局部變量：PRINT@<局部變量>例：在查詢分析器中輸入下列內(nèi)容，運(yùn)行后看結(jié)果。1.DECLARE@vnamevarchar(10)SET@vname=‘WangWeiHua’PRINT@vname或SELECT@vname2.SET語(yǔ)句可以為：

SELECT@vname=nameFROMstudentsWHEREid=‘000001’若SELECT返回多行，則最后一行的name賦給變量vname系統(tǒng)變量MySQL系統(tǒng)變量分為兩種，一種是全局（global）變量，另一種是會(huì)話（session）變量，全局變量全局有效，而會(huì)話變量則只在當(dāng)前會(huì)話有效。全局變量的引用方法為：@@變量名。02函數(shù)和表達(dá)式數(shù)據(jù)運(yùn)算MySQL支持不同類型數(shù)據(jù)的混合運(yùn)算，自動(dòng)進(jìn)行類型轉(zhuǎn)換。示例：SELECT100+'123'，結(jié)果為223?？罩堤幚肀磉_(dá)式中包含空值時(shí)，結(jié)果為NULL。使用ISNULL或ISNOTNULL判斷空值。CASE表達(dá)式CASE表達(dá)式用于條件判斷，返回不同結(jié)果。示例：CASEWHENconditionTHENresultELSEdefaultEND，根據(jù)條件返回不同值。表達(dá)式CURDATE()：返回當(dāng)前日期。DATE_ADD()：日期加指定時(shí)間間隔。TIMESTAMPDIFF()：計(jì)算兩個(gè)日期之間的差異。日期函數(shù)CAST()：將表達(dá)式轉(zhuǎn)換為指定類型。CONVERT()：與CAST類似，用于類型轉(zhuǎn)換。類型轉(zhuǎn)換函數(shù)CONCAT()：字符串拼接。SUBSTRING()：獲取子字符串。REPLACE()：替換字符串中的子串。字符函數(shù)系統(tǒng)函數(shù)010203分支語(yǔ)句IF語(yǔ)句用于條件判斷，支持嵌套。示例：IFconditionTHEN...ELSE...ENDIF，根據(jù)條件執(zhí)行不同代碼塊。循環(huán)語(yǔ)句WHILE循環(huán)：條件為TRUE時(shí)重復(fù)執(zhí)行語(yǔ)句塊。REPEAT循環(huán)：至少執(zhí)行一次，直到條件為TRUE。LOOP循環(huán)：簡(jiǎn)單循環(huán)，需配合LEAVE語(yǔ)句退出。其他控制語(yǔ)句ITERATE：類似CONTINUE，用于跳過當(dāng)前循環(huán)。LEAVE：類似BREAK，用于退出循環(huán)或語(yǔ)句塊。RETURN：函數(shù)中返回值并結(jié)束函數(shù)。流程控制語(yǔ)句流程控制語(yǔ)句

(1)IF語(yǔ)句

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

BEGIN<語(yǔ)句>[...n]END[ELSE]BEGIN<語(yǔ)句>[...n]END//如果IF和ELSE后只有一個(gè)語(yǔ)句,可以省略BEGIN和ENDDECLARE@aINTEGERDECLARE@bINTEGERSELECT@a=AVG(grade)FROMstudentsa,gradebWHEREa.id=b.idanda.sex=0SELECT@b=AVG(grade)FROMstudentsa,gradebWHEREa.id=b.idanda.sex=1if@a>@bPRINT‘女同學(xué)平均成績(jī)高于男同學(xué)’elsePRINT‘男同學(xué)平均成績(jī)高于男同學(xué)’例：比較男女同學(xué)的平均成績(jī)。（或把較高成績(jī)記入某數(shù)據(jù)表中某字段）(2)循環(huán)語(yǔ)句

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

BEGIN

語(yǔ)句[...n][BREAK][CONTINUE]

ENDBREAK語(yǔ)句使循環(huán)結(jié)束

CONTINUE語(yǔ)句進(jìn)入下一循環(huán) WHILE(SELECTAVG(grade)FROMgrade)<85 BEGIN UPDATEgradeSETgrade=99 WHEREgrade*1.1>99 UPDATEgradeSETgrade=grade*1.1 WHEREgrade*1.1<=99 END注意兩個(gè)Update語(yǔ)句順序不能顛倒，顛倒后85分的學(xué)生加一次分變成了99分，而應(yīng)該是85+8.5例：每個(gè)學(xué)生成績(jī)加10%，但不能超過99(若超過99則加到99)，然后判別平均分?jǐn)?shù)是否達(dá)到85，若沒有達(dá)到，則繼續(xù)加分。WHILE(SELECTAVG(grade)FROMgrade)<85BEGIN UPDATEgradeSETgrade=99WHEREgrade*1.1>99UPDATEgradeSETgrade=grade*1.1WHEREgrade*1.1<=99 IF(SELECTCOUNT(*)FROMGRADEWHEREGRADE>=99)>0BREAKEND例：修改以上程序，如果有人加分后達(dá)到或超過99，則不再繼續(xù)加分。(3)CASE表達(dá)式

CASEWHEN<條件表達(dá)式>THEN<結(jié)果表達(dá)式>[...n]ELSE<結(jié)果表達(dá)式>

END注意CASE是一個(gè)表達(dá)式而不是流程控制。當(dāng)條件表達(dá)式成立,則返回相應(yīng)的結(jié)果表達(dá)式,若條件表達(dá)式均不成立,則返回ELSE后的表達(dá)式SELECTsubid,grade=CASEWHENgrade<100ANDgrade>=90THEN'優(yōu)'WHENgrade<90ANDgrade>=80THEN'良'WHENgrade<80ANDgrade>=70THEN'中'WHENgrade<70ANDgrade>=60THEN'及格'ELSE'不及格'ENDFROMgrade成績(jī)列列名顯示為grade，“grade=”省略列名顯示為“無(wú)列名”。例：把百分制成績(jī)以“優(yōu)、良、中、差”顯示。(4)RETURN和注釋RETURN<整數(shù)表達(dá)式>：結(jié)束程序并返回表達(dá)式的值行注釋使用“--”塊注釋使用/*....*/函數(shù)

(1)數(shù)學(xué)函數(shù)CEILING(數(shù)值表達(dá)式)：進(jìn)位取整FLOOR(數(shù)值表達(dá)式)：去尾取整SIGN(數(shù)值表達(dá)式)：取符號(hào)ROUND(數(shù)值表達(dá)式)：四舍五入ABS/SQRT/SQUARE：絕對(duì)值/開根/平方ACOS/ASIN/ATAN/LOG/LOG10/PI(2)字符串函數(shù)LEN(str_expre):取長(zhǎng)度LEFT/RIGHT(char_expre，int_expre):取子串SUBSTRING(char_expre，start,length):取子串UPPER/LOWER(char_expre):轉(zhuǎn)成大/下寫SPACE(int_expre):空格組成字符串LTRIM/RTRIM(character_expresion):去空格REVERSE(char_expr):顛倒順序STR(float_expr[,length,[,decimal]]):數(shù)轉(zhuǎn)字符串關(guān)于字符串長(zhǎng)度字符串函數(shù)中用到的長(zhǎng)度，指的是字符個(gè)數(shù)，中文字為一個(gè)字符，如“st長(zhǎng)虹電”，長(zhǎng)度為5。如要取中英文字母混合的前4個(gè)字符，上例返回“st長(zhǎng)虹”，與需求不符。函數(shù)datalength，返回字符串的字節(jié)數(shù)，上述字符串返回8由datalength和len返回的差值可以推算出中文和英文字母?jìng)€(gè)數(shù)，從而調(diào)整substring的長(zhǎng)度(3)日期函數(shù)GETDATE():系統(tǒng)日期DAY(date):日期的日MONTH(date):日期的月YEAR(date):日期的年(4)系統(tǒng)函數(shù)CURRENT_USER:當(dāng)前用戶名SYSTEM_USER：當(dāng)前用戶登入帳戶HOST_ID:運(yùn)行SQL_SERVER的計(jì)算機(jī)標(biāo)識(shí)HOST_NAME：運(yùn)行SQL_SERVER的計(jì)算機(jī)名DB_NAME/DB_ID：數(shù)據(jù)庫(kù)名/標(biāo)識(shí)(5)其他常用函數(shù)ISNULL(check_expre,replacement_value):check_expre為空，返回replacement_value，否則返回check_expre。CAST(expreasdata_type):把expre轉(zhuǎn)化成data_type類型。例：查詢學(xué)生編號(hào)，姓名和年齡selectid,name,year(getdate())-year(birthday)asagefromstudents例：查詢學(xué)生編號(hào)，姓名和電話，若電話為空，不顯示。Selectid,name,isnull(telephone,’’)astelephonefromstudents例：把姓名分成姓和名兩列顯示selectleft(name,1),substring(name,2,len(name)-1)fromstudents(6)自定義函數(shù)-標(biāo)量函數(shù)例:查詢學(xué)生編號(hào),姓名和性別,要求性別顯示“男”和“女”。建立一個(gè)函數(shù)f_conv，把0和1轉(zhuǎn)換成“女”和“男”。然后用Select語(yǔ)句查詢:SELECTid,name,dbo.f_conv(sex)FROMstudents建立函數(shù)的命令為：CREATEFUNCTIONf_conv(@sexint)RETURNSCHAR(2)ASBEGINDECLARE@vsCHAR(2)IF@sex=0SET@vs=‘女’

ELSESET@vs=‘男’

RETURN@vsEND例：抽取字符串中中文alterfunctionGetChinese(@svarchar(50))returnsvarchar(50)asbegindeclare@iintdeclare@retsvarchar(50)set@i=1set@rets=''while@i<=LEN(@s)beginif(unicode(substring(@s,@i,1))>255)set@rets=@rets+substring(@s,@i,1)set@i=@i+1endreturn@retsEnd驗(yàn)證：selectdbo.GetChinese('12中b國(guó)dssad')(7)自定義函數(shù)-內(nèi)嵌表值函數(shù)內(nèi)嵌用戶定義函數(shù)是返回table

的用戶定義函數(shù)的子集。內(nèi)嵌函數(shù)可用于實(shí)現(xiàn)參數(shù)化視圖的功能。例：完成一個(gè)對(duì)某個(gè)學(xué)生所有學(xué)科的成績(jī)查詢，用視圖實(shí)現(xiàn)的方法為： CREATEVIEWv_AllGradeASSELECTa.id,,b.subname,c.gradeFROMstudentsa,subjectsb,gradecwherea.id=c.idandb.subid=c.subidanda.id=‘學(xué)生id’由于視圖中不能包含參數(shù)，上述視圖只能查詢某個(gè)學(xué)生的所有學(xué)科成績(jī)，顯然在實(shí)際應(yīng)用中不可行。單語(yǔ)句表值函數(shù)內(nèi)嵌用戶定義表值函數(shù)可用于支持在WHERE子句中指定的搜索條件的參數(shù)。下面是示例：CREATEFUNCTIONfn_AllGrade(@idchar(6))RETURNStableAS RETURN(SELECTa.id,,b.subname,c.gradeFROMstudentsa,subjectsb,gradecwherea.id=c.idandb.subid=c.subidanda.id=@id)內(nèi)嵌表值函數(shù)的調(diào)用：SELECT*FROMfn_AllGrade(‘學(xué)生編號(hào)')。單語(yǔ)句表值函數(shù)通過單個(gè)SELECT語(yǔ)句定義TABLE返回值。進(jìn)一步的技能說明：事實(shí)上上述問題單用視圖也可以解決，使用單語(yǔ)句表值函數(shù)提供了另一種解決方法。CREATEVIEWv_AllGradeASSELECTa.id,,b.subname,c.gradeFROMstudentsa,subjectsb,gradecwherea.id=c.idandb.subid=c.subidSELECT*FROMv_AllGradeWHEREandid=‘學(xué)生id’多語(yǔ)句表值函數(shù)CREATEFUNCTION函數(shù)名(@參數(shù)名參數(shù)類型，……)RETURNS@返回表值的表名retTableNameTABLE(返回表結(jié)構(gòu))

AS BEGIN

…… END通過retTableName返回表值,例如:SELECT*FROM函數(shù)名(參數(shù))例：統(tǒng)計(jì)某班各門課成績(jī)及格和不及格的人數(shù) ALTERFUNCTIONfn_Gather(@classidCHAR(6))RETURNS@tb_GatherTABLE(subid1CHAR(6)PRIMARYKEY,num1INT,num2INT)AS BEGIN --插入班級(jí)各課程記錄 INSERTinto@tb_Gather SELECTsubid,0,0FROMgradeWHEREidin(SELECTidFROMstudents WHEREclassid=@classid)groupbysubid --計(jì)算及格和不及格人數(shù) UPDATE@tb_GatherSETnum1=(SELECTcount(*)FROMgrade WHEREsubid=subid1ANDgrade<60ANDidIN(SELECTidFROMstudentsWHEREclassid=@classid)) UPDATE@tb_GatherSETnum1=(SELECTcount(*)FROMgrade WHEREsubid=subid1ANDgrade>=60ANDidIN(SELECTidFROMstudentsWHEREclassid=@classid)) RETURN ENDSQLServer不支持Update…WHEREsubid=@tb_gather.subid，所以tb_Gather第一個(gè)字段取名subid1,以使系統(tǒng)能區(qū)分。返回表定義primarykey很重要，否則返回表的行序并不一定和函數(shù)中插入行的順序一致多語(yǔ)句函數(shù)的主體中允許使用的語(yǔ)句未在下面的列表中列出的語(yǔ)句不能用在函數(shù)主體中。賦值語(yǔ)句?？刂屏髡Z(yǔ)句。DECLARE語(yǔ)句，該語(yǔ)句定義函數(shù)局部的數(shù)據(jù)變量和游標(biāo)。SELECT語(yǔ)句，該語(yǔ)句包含帶有表達(dá)式的選擇列表，其中的表達(dá)式將值賦予函數(shù)的局部變量。游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標(biāo)。只允許使用以INTO子句向局部變量賦值的FETCH語(yǔ)句；不允許使用將數(shù)據(jù)返回到客戶端的FETCH語(yǔ)句。INSERT、UPDATE和DELETE語(yǔ)句，這些語(yǔ)句修改函數(shù)的局部table

變量。EXECUTE語(yǔ)句調(diào)用擴(kuò)展存儲(chǔ)過程。03存儲(chǔ)過程使用CREATE

PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過程。示例：CREATEPROCEDUREGetPassNum(EleIdCHAR(6),ClassIdCHAR(6),OUTPassNumINTEGER,OUTNotPassNumINTEGER)...，統(tǒng)計(jì)及格和不及格人數(shù)。調(diào)用存儲(chǔ)過程刪除存儲(chǔ)過程使用DROP

PROCEDURE語(yǔ)句刪除存儲(chǔ)過程。示例：DROPPROCEDUREGetPassNum，刪除存儲(chǔ)過程。創(chuàng)建存儲(chǔ)過程使用CALL語(yǔ)句調(diào)用存儲(chǔ)過程。示例：CALL

GetPassNum('ele001','1901',@PassNum,@NotPassNum)，獲取結(jié)果。創(chuàng)建和調(diào)用存儲(chǔ)過程存儲(chǔ)過程存儲(chǔ)過程是保存起來(lái)的可以接受和返回用戶提供參數(shù)的SQL語(yǔ)句的集合存儲(chǔ)過程在創(chuàng)建時(shí)已編譯和優(yōu)化,在執(zhí)行時(shí)不再需要此過程,并且第一次被調(diào)用后，駐留在內(nèi)存中，所以執(zhí)行效率高。存儲(chǔ)過程創(chuàng)建后，可被應(yīng)用程序任意調(diào)用，其功能的實(shí)現(xiàn)（修改）獨(dú)立于應(yīng)用程序。存儲(chǔ)過程運(yùn)行于服務(wù)器端，大大減少了客戶機(jī)和服務(wù)器之間的通信量。1)創(chuàng)建存儲(chǔ)過程命令格式CREATEPROCEDUREprocedure_name[{@parameterdata_type}[=default][OUTPUT]][,...n][WITH{RECOMPLE|ENCRIPTION| //在運(yùn)行時(shí)重編譯和

RECOMPILE,ENCRIPTION} //加密程序]AS sql_statement[,...n]使用OUTPUT的參數(shù)：存儲(chǔ)過程在退出可將該參數(shù)的當(dāng)前值返回至調(diào)用程序，以便在調(diào)用程序中獲得并使用該參數(shù)值。對(duì)OUTPUT參數(shù)，調(diào)用時(shí)也要使用關(guān)鍵字OUTPUT存儲(chǔ)過程可以使用return給調(diào)用過程或應(yīng)用程序返回整型值（等同于函數(shù)）例1：編寫一個(gè)存儲(chǔ)過程nopasssnum，其參數(shù)為課程編號(hào)，輸出參數(shù)為該課程不及格的人數(shù)。使用output參數(shù)： CREATEPROCEDUREnopassnum @subidCHAR(6),@nopassnINTEGEROUTPUT AS SELECT@nopassn=count(*) FROMGRADEWHEREsubid=@subidANDgrade<60使用return： CREATEPROCEDUREnopassnum@subidCHAR(6) AS DECLARE@nopassninteger SELECT@nopassn=count(*) FROMGRADEWHEREsubid=@subidANDgrade<60 RETURN@nopassn例2：編寫一個(gè)存儲(chǔ)過程getnum，其參數(shù)為課程編號(hào)和一個(gè)分?jǐn)?shù)段，輸出參數(shù)為該課程在該分分?jǐn)?shù)段的人數(shù)。CREATEPROCEDUREgetnum@subidCHAR(6),@grade1INTEGER,@grade2INTEGER,@numINTEGEROUTPUTASSELECT@num=count(*)FROMGRADEWHEREsubid=@subidANDgrade<=@grade2andgrade>=@grade12)執(zhí)行存儲(chǔ)過程命令格式EXECUTE[@returnval=]procedure_name{value|variable[OUTPUT][,...n]}WITHRECOMPILE]執(zhí)行上列例1中過程：DECLARE@npnintegerEXECUTEnopassnum'sub001',@npnOUTPUT使用return：EXECUTE@npn=nopassnum'sub001'PRINT'不及格人數(shù)'+CAST(@npnaschar(3))執(zhí)行例2的過程，輸出‘sub001’課程的下列分?jǐn)?shù)段的人數(shù)：0-59，60-69，70-79，80-89，90-100。1.程序一:DECLARE@npnintegerEXECUTEgetnum‘sub001’,0,59,@npnOUTPUTPRINTnpnEXECUTEgetnum‘sub001’60,69,@npnOUTPUTPRINTnpn......2.程序二(使用循環(huán))DECLARE@npnINTEGERDECLARE@aINTEGERDECLARE@bINTEGERSET@a=0SET@b=59WHILE@b<=100BEGINEXECUTEgetnum‘sub001’,@a,@b,@npnOUTPUTPRINTSTR(@a)+STR(@b)+STR(@npn)SET@a=@b+1SET@b=@b+10IF@b=99SET@b=100END3)刪除和修改存儲(chǔ)過程刪除存儲(chǔ)過程。命令格式：DROPPROCEDURE{proc_name}[,...n]修改存儲(chǔ)過程。命令格式:ALTERPROCEDURE...(余下部分同CREATEPROCEDURE)使用ALTERPROCEDURE可避免使用DROPPROCEDURE12使用存儲(chǔ)過程生成各月月初庫(kù)存。示例：CREATEPROCEDUREFillMonStock(YearINT,MonINT)...，生成指定月份的月初庫(kù)存。存儲(chǔ)過程支持遞歸調(diào)用，用于處理復(fù)雜邏輯。示例：遞歸調(diào)用存儲(chǔ)過程生成上月月初庫(kù)存，確保數(shù)據(jù)連續(xù)性。月初庫(kù)存生成使用遞歸存儲(chǔ)過程實(shí)例04觸發(fā)器觸發(fā)器觸發(fā)器是一種特殊的存儲(chǔ)過程,當(dāng)對(duì)指定表執(zhí)行指定的數(shù)據(jù)修改語(yǔ)句時(shí)自動(dòng)執(zhí)行?；靖袷剑篊REATETRIGGERtrigger_nameON{table|view}{FOR|AFTER{[INSERT][,UPDATE][,DELETE]}ASsql_statement[1..n]使用CREATE

TRIGGER語(yǔ)句創(chuàng)建觸發(fā)器。示例：CREATETRIGGERTri_SaleDetailAFTERINSERTONSaleDetailFOREACHROW...，插入銷售記錄時(shí)更新商品表的銷售額。創(chuàng)建觸發(fā)器使用觸發(fā)器實(shí)現(xiàn)外碼置空操作。示例：UPDATEStudentSETClassId=NULLWHEREClassId=OLD.ClassId，刪除班級(jí)時(shí)置空學(xué)生班級(jí)信息。引用表外碼置空操作觸發(fā)器用于插入關(guān)聯(lián)數(shù)據(jù)。示例：INSERTINTOStudent_ElectiveSELECTNEW.StdId,EleId,NULLFROMElective，為新學(xué)生插入選課信息。條件關(guān)聯(lián)插入使用觸發(fā)器實(shí)時(shí)更新商品表中的銷售額。示例：UPDATEGoodsSETSaleAmt=IFNULL(SaleAmt,0)+NEW.Quantity*NEW.SalePrice，累加銷售額。合計(jì)數(shù)同步觸發(fā)器應(yīng)用實(shí)例CREATETRIGGERtri_appgradeONstudentsFORINSERTASINSERTINTOgradeSELECTa.id,b.subid,0FROMinserteda,subjectb其中inserted表示students中新插入的記錄，另有deleted表示用delete命令刪除的記錄。刪除觸發(fā)器的命令為DROPTRIGGER觸發(fā)器名例：在Students中建立一個(gè)觸發(fā)器，當(dāng)插入一條記錄后，在成績(jī)表（grade）中插入該學(xué)生的所有課程（subject表）的成績(jī)記錄，成績(jī)?yōu)?。用觸發(fā)器實(shí)現(xiàn)參照完整性當(dāng)被引用表（專業(yè)表）進(jìn)行刪除操作時(shí)，引用表（學(xué)生表）中的對(duì)應(yīng)專業(yè)號(hào)的處理：全部設(shè)置為NULL全部設(shè)置成某個(gè)確定值當(dāng)被引用表（專業(yè)表）進(jìn)行專業(yè)號(hào)修改操作時(shí)，引用表（學(xué)生表）中的對(duì)應(yīng)專業(yè)號(hào)的處理：如要非級(jí)聯(lián)修改，需要用觸發(fā)器實(shí)現(xiàn)使用觸發(fā)器和直接執(zhí)行update語(yǔ)句的區(qū)別05臨時(shí)表臨時(shí)表臨時(shí)表就是那些名稱以井號(hào)(#)開頭的表。如果當(dāng)用戶斷開連接時(shí)沒有除去臨時(shí)表，MySQL將自動(dòng)除去臨時(shí)表。臨時(shí)表不存儲(chǔ)在當(dāng)前數(shù)據(jù)庫(kù)內(nèi)，而是存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)tempdb內(nèi)。臨時(shí)表有兩種類型。本地臨時(shí)表以一個(gè)井號(hào)(#)開頭的那些表名。只有在創(chuàng)建本地臨時(shí)表的連接上才能看到這些表。不同任務(wù)執(zhí)行同一個(gè)存儲(chǔ)過程，在存儲(chǔ)過程中創(chuàng)建的臨時(shí)表內(nèi)容可以各不相同。本地臨時(shí)表常用于處理業(yè)務(wù)邏輯中存放中間結(jié)果，然后由中間結(jié)果產(chǎn)生最終結(jié)果。最終結(jié)果產(chǎn)生后不再需要中間結(jié)果，若使用普通表，則必須手工刪除。不同用戶執(zhí)行同一個(gè)存儲(chǔ)過程，結(jié)果可能各不相同，若使用普通表存放中間結(jié)果，做到這一點(diǎn)比較困難。全局臨時(shí)表以兩個(gè)井號(hào)(##)開頭的那些表名。在所有連接上都能看到全局臨時(shí)表。所有任務(wù)停止引用全局臨時(shí)表，這些表即被除去。通常情況下，只要?jiǎng)?chuàng)建全局臨時(shí)表的連接斷開，全局臨時(shí)表即被除去。使用CREATETEMPORARYTABLE語(yǔ)句創(chuàng)建臨時(shí)表。示例：CREATETEMPORARYTABLEMyTemp(IdCHAR(6)PRIMARYKEY,NameVARCHAR(20))，創(chuàng)建臨時(shí)表存儲(chǔ)中間結(jié)果。0102創(chuàng)建臨時(shí)表例：不同任務(wù)下臨時(shí)表各不相同啟動(dòng)兩個(gè)查詢分析器在同一數(shù)據(jù)庫(kù)中分別建立兩個(gè)臨時(shí)表。分別插入不同的數(shù)據(jù)行，可以發(fā)現(xiàn)兩個(gè)表五不干擾。可以看出，即使同一用戶啟動(dòng)兩個(gè)查詢分析器，由于是不同的任務(wù)，建立的同名臨時(shí)表無(wú)不相關(guān)。使用臨時(shí)表生成月銷售匯總表。示例：CREATETEMPORARYTABLEMonthSaleSummary(SaleYearINT,SaleMonINT,GoodsNoCHAR(6),MonSaleQtyDECIMAL(10,2),YearSaleQtyDECIMAL(10,2))，存儲(chǔ)月銷售數(shù)據(jù)。月銷售匯總表臨時(shí)表應(yīng)用實(shí)例06游標(biāo)游標(biāo)嵌入式SQL：在高級(jí)語(yǔ)言程序中嵌入SQL語(yǔ)句，C語(yǔ)言作為主語(yǔ)言的嵌入式SQL語(yǔ)句的一般形式EXECSQL<SQL語(yǔ)句>高級(jí)語(yǔ)言對(duì)嵌入式SQL的處理方法為：由預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描，識(shí)別出SQL語(yǔ)句。把它們轉(zhuǎn)換成主語(yǔ)言調(diào)用語(yǔ)句，以使主語(yǔ)言編譯程序能識(shí)別它。最后由主語(yǔ)言的編譯程序?qū)⒄麄€(gè)源程序編譯成目標(biāo)碼。為什么要使用游標(biāo)：SQL語(yǔ)言與主語(yǔ)言具有不同數(shù)據(jù)處理方式SQL語(yǔ)言是面向集合的，一條SQL語(yǔ)句原則上可以產(chǎn)生或處理多條記錄。主語(yǔ)言是面向記錄的，一般一組變量一次只能存放一條記錄。嵌入式SQL引入了游標(biāo)的概念，用來(lái)協(xié)調(diào)這兩種不同的處理方式。什么是游標(biāo)：游標(biāo)是系統(tǒng)為用戶開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果。每個(gè)游標(biāo)區(qū)都有一個(gè)名字。用戶可以用SQL語(yǔ)句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語(yǔ)言進(jìn)一步處理。使用游標(biāo)的步驟1.聲明游標(biāo)2.打開游標(biāo)3.移動(dòng)游標(biāo)指針并取的當(dāng)前記錄數(shù)據(jù)4.關(guān)閉和釋放游標(biāo)使用DECLARECURSOR語(yǔ)句聲明游標(biāo)。語(yǔ)句格式DECLARE<游標(biāo)名>CURSORFOR<SELECT語(yǔ)句>示例：DECLAREcursor_nameCURSORFORSELECT...，聲明游標(biāo)對(duì)應(yīng)查詢語(yǔ)句。Part01Part02聲明游標(biāo)使用OPEN語(yǔ)句打開游標(biāo)。示例：OPENcursor_name，執(zhí)行查詢并獲取結(jié)果集。打開游標(biāo)功能打開游標(biāo)實(shí)際上是執(zhí)行相應(yīng)的SELECT語(yǔ)句，把所有滿足查詢條件的記錄從指定表取到緩沖區(qū)中這時(shí)游標(biāo)處于活動(dòng)狀態(tài)，指針指向查詢結(jié)果集中第一條記錄之前使用FETCH語(yǔ)句從游標(biāo)中獲取數(shù)據(jù)。語(yǔ)句格式 FETCH[[NEXT|PRIOR| FIRST|LAST]FROM]<游標(biāo)名> INTO<變量>[,<變量>]...示例：FETCHNEXTFROMcursor_nameINTOvar_name,...，將當(dāng)前行數(shù)據(jù)賦值給變量。移動(dòng)游標(biāo)指針并取當(dāng)前行數(shù)據(jù)功能指定方向移動(dòng)游標(biāo)指針，然后將緩沖區(qū)中的當(dāng)前記錄取出來(lái)賦給變量。NEXT|PRIOR|FIRST|LAST：指定推動(dòng)游標(biāo)指針的方式。

NEXT：向前推進(jìn)一條記錄

PRIOR：向回退一條記錄

FIRST：推向第一條記錄

LAST：推向最后一條記錄缺省值為NEXT說明(1)變量必須與SELECT語(yǔ)句中的目標(biāo)列表達(dá)式具有一一對(duì)應(yīng)關(guān)系(2)FETCH語(yǔ)句通常用在一個(gè)循環(huán)結(jié)構(gòu)中，通過循環(huán)執(zhí)行FETCH語(yǔ)句逐條取出結(jié)果集中的行進(jìn)行處理(3)通過檢測(cè)全局變量@@FETCH_STATUS的值控制循環(huán)結(jié)束：若前一個(gè)FETCH成功取到行數(shù)據(jù)則變量值為0，當(dāng)取到最后一行數(shù)據(jù)后再執(zhí)行FETCH，則該變量值為-1。使用CLOSE語(yǔ)句關(guān)閉游標(biāo)。釋放當(dāng)前結(jié)果集并且解除定位游標(biāo)的行上的游標(biāo)鎖定，被關(guān)閉的游標(biāo)可再次被打開。示例：CLOSEcursor_name，釋放游標(biāo)資源。使用deallocate語(yǔ)句刪除游標(biāo)引用，組成該游標(biāo)的數(shù)據(jù)結(jié)構(gòu)由系統(tǒng)釋放。不使用deallocate，再次declare這個(gè)游標(biāo)時(shí)，將出現(xiàn)“游標(biāo)已存在”的錯(cuò)誤。關(guān)閉游標(biāo) 例：參觀券只有學(xué)生數(shù)的一半左右，給學(xué)生分配參觀券，方法為獨(dú)坐同學(xué)得一張票，兩個(gè)鄰坐同學(xué)只分配一張票，分配原則為：若鄰坐同學(xué)為異性，則分配給女同學(xué)，否則分配給平均成績(jī)高的一個(gè)，若平均成績(jī)相同，則分配兩張票。（在students中增加ticket列，類型為bit,0為初始狀態(tài)，1表示得到票子）舉例使用存儲(chǔ)過程及游標(biāo)createprocedureticketasbegindeclare@idchar(6)declare@id_sidechar(6)declare@sexbitdeclare@sex_sidebitdeclare@avggradeintegerdeclare@avggrade_sideintegerdeclarecur_studentscursorforselectid,id_side,sex,avggradefromstudentsopencur_studentsfetchnextfromcur_studentsinto@id,@id_side,@sex,@avggradeupdatestudentssetticket=0--恢復(fù)ticket為初始狀態(tài)while@@FETCH_STATUS=0--若前一fetch取到行數(shù)據(jù)值為0，取到最后一行數(shù)據(jù)后再運(yùn)行fetch，則該值為-1begin select@sex_side=sex,@avggrade_side=avggradefromstudentswhereid=@id_side--獲得鄰座同學(xué)信息 if@id_sideisnullor(@sex<>@sex_sideand@sex=1)or(@sex=@sex_sideand@avggrade>=@avggrade_side) updatestudentssetticket=1whereid=@id fetchnextfromcur_studentsinto@id,@id_side,@sex,@avggrade--取下一行數(shù)據(jù)endclosecur_studentsdeallocatecur_studentsend //運(yùn)行executeticket判斷語(yǔ)句解釋： if@id_sideisnullor(@sex<>@sex_sideand@sex=1)or(@sex=@sex_sideand@avggrade>=@avggrade_side)(鄰座為空)or(和鄰座不同性且為女同學(xué))or(和鄰座同性且成績(jī)大于等于鄰座同學(xué))，在此條件下，當(dāng)前記錄對(duì)應(yīng)的同學(xué)得到票子。 while@@FETCH_STATUS=0全局變量@@Fetch_status：0：FETCH語(yǔ)句成功；-1：FETCH語(yǔ)句失敗或此行不在結(jié)果集中；-2：被提取的行不存在。07批處理和事務(wù)批處理和事務(wù)批處理：批處理是把一組SQL語(yǔ)句,作為一個(gè)整體提交該數(shù)據(jù)庫(kù)服務(wù)器執(zhí)行執(zhí)行批處理：在查詢分析器中輸入組成批處理的SQL語(yǔ)句，然后執(zhí)行。語(yǔ)法錯(cuò)誤：批處理語(yǔ)句中只要其中有一句有語(yǔ)法錯(cuò)誤，則所有語(yǔ)句都不執(zhí)行。邏輯錯(cuò)誤：若一個(gè)批處理中的某個(gè)語(yǔ)句有邏輯錯(cuò)誤（如數(shù)據(jù)溢出完整性錯(cuò)誤），該語(yǔ)句將不能正常運(yùn)行，但不會(huì)影響其他語(yǔ)句執(zhí)行。這樣可能會(huì)造成數(shù)據(jù)完整性上的邏輯錯(cuò)誤。數(shù)據(jù)不一致性不可重復(fù)讀：事務(wù)中多次讀取數(shù)據(jù)不一致。臟讀：讀取未提交的數(shù)據(jù)。丟失修改：事務(wù)修改被其他事務(wù)覆蓋?；米x：插入或刪除操作導(dǎo)致事務(wù)失敗。隔離級(jí)別未提交讀：最低隔離級(jí)別，允許臟讀。提交讀：避免臟讀，但可能發(fā)生不可重復(fù)讀?？芍貜?fù)讀：避免不可重復(fù)讀，但可能發(fā)生幻讀?？纱凶x：最高隔離級(jí)別，避免所有不一致性。并發(fā)引起的數(shù)據(jù)不一致性與隔離級(jí)別使用事務(wù)可以保證一組SQL語(yǔ)句的執(zhí)行，要么全部成功，若有一句語(yǔ)句不成功，則全部語(yǔ)句均不執(zhí)行。事務(wù)的開始使用BEGINTRANSACTION事務(wù)的結(jié)束可以是：COMMIT：提交，即所有語(yǔ)句均執(zhí)行ROLLBAK：回滾，即取消所有語(yǔ)句例：用一個(gè)事務(wù)完成下列工作students中address全部為ShangHai刪除‘000005’號(hào)學(xué)生（先添加）分別用COMMIT和ROLLBACK結(jié)束該事務(wù)。事務(wù)定義事務(wù)使用STARTTRANSACTION和COMMIT/ROLLBACK定義事務(wù)。示例：STARTTRANSACTION;...COMMIT;，確保操作的原子性。75%事務(wù)特性原子性：操作要么全部執(zhí)行，要么全部不執(zhí)行。一致性：事務(wù)執(zhí)行前后數(shù)據(jù)保持一致。隔離性：并發(fā)事務(wù)互不干擾。持久性：事務(wù)提交后結(jié)果永久有效。52%0102事務(wù)定義方法及基本特性操作步驟是：在查詢分析器中運(yùn)行：1.STARTTRANSACTIONUPDATEstudentsSETaddress=‘ShangHai’DELETEFROMstudentsWHEREid=‘000005’2.SELECT*FROMstudents--觀察執(zhí)行結(jié)果3.ROLLBACK --回滾上列修改4.SELECT*FROMstudents--觀察執(zhí)行結(jié)果重復(fù)以上步驟，ROLLBACK換成COMMIT。事務(wù)操作步驟12在存儲(chǔ)過程中加入事務(wù)控制，確保數(shù)據(jù)一致性。示例：STARTTRANSACTION;...IFHaveErrTHENROLLBACK;ELSECOMMIT;ENDIF;，處理錯(cuò)誤并回滾事務(wù)。事務(wù)應(yīng)用實(shí)例共享鎖（S鎖）：允許多個(gè)事務(wù)讀取，但不允許修改。排它鎖（X鎖）：允許一個(gè)事務(wù)讀取和修改，其他事務(wù)無(wú)法加鎖。全局鎖：鎖定整個(gè)數(shù)據(jù)庫(kù)。表級(jí)鎖：鎖定整個(gè)表。行級(jí)鎖：鎖定表中的行。間隙鎖：鎖定索引值范圍，防止幻讀。鎖的封鎖粒度鎖的類型加鎖練習(xí)和思考：對(duì)鄰座同學(xué)為同性且平均成績(jī)相同的情況為何在程序中未反映？程序是否有漏洞？學(xué)生A和Ｂ為鄰座，在決定Ｂ是否可得票的同時(shí)，Ａ是否得票也已確定，是否可改進(jìn)程序使循環(huán)次數(shù)減少到原來(lái)的一半？平均成績(jī)事實(shí)上可根據(jù)表grade計(jì)算得到，若不使用students中avggrade列，如何修改程序？增加事務(wù)控制在存儲(chǔ)過程執(zhí)行中，若其中某SQL語(yǔ)句執(zhí)行失敗，將不影響該語(yǔ)句前后的其他SQL語(yǔ)句的執(zhí)行，這將威脅數(shù)據(jù)邏輯上的一致性，所以必須增加事務(wù)控制。驗(yàn)證：可以在該