數(shù)據(jù)庫原理與應(yīng)用：第7章 SQL Server 2000高級(jí)應(yīng)用

第7章SQLServer2000高級(jí)應(yīng)用27.1Transact-SQL程序設(shè)計(jì)7.2存儲(chǔ)過程7.3觸發(fā)器7.4備份和還原37.1Transact-SQL程序設(shè)計(jì)Transact-SQL語言就是在標(biāo)準(zhǔn)SQL的基礎(chǔ)上進(jìn)行擴(kuò)充而推出的SQLServer專用的結(jié)構(gòu)化SQL。Transact-SQL語言最主要的用途是設(shè)計(jì)服務(wù)器端的能夠在后臺(tái)執(zhí)行的程序塊。7.1.1Transact-SQL程序的結(jié)構(gòu)與批處理Transact-SQL程序的結(jié)構(gòu)以下程序是打開教學(xué)管理數(shù)據(jù)庫TEACH，并從數(shù)據(jù)表SC中讀取學(xué)號(hào)為“S1”同學(xué)所學(xué)課程的平均分，如果該平均分大于或等于60分，則程序輸出“課程平均成績超過60”，否則輸出“課程平均成績不超過60”。

存儲(chǔ)過程、觸發(fā)器等4/*Transact-SQL程序的實(shí)例*/USETeach/*將教學(xué)管理數(shù)據(jù)庫Teach置為當(dāng)前數(shù)據(jù)庫*/GODECLARE@AvgScoreDECIMAL/*定義變量AvgScore*/SET@AvgScore=60/*給變量AvgScore賦值初值*/IF(SELECTAVG(Score)FROMSCWHERESNo='S1')>=@AvgScore--輸出結(jié)果

PRINT'課程平均成績超過'+CONVERT(VARCHAR(10),@AvgScore)ELSE--輸出結(jié)果

PRINT'課程平均成績不超過'+CONVERT(VARCHAR(10),@AvgScore)--執(zhí)行批處理GO5Transact-SQL程序的批處理在Transact-SQL程序內(nèi)兩個(gè)“GO”標(biāo)記符之間的代碼稱為一個(gè)“批”。SQLServer對Transact-SQL程序的編譯和執(zhí)行是按照“批”為單位來進(jìn)行的，稱為批處理。一個(gè)Transact-SQL程序內(nèi)可以包含多個(gè)“批”。

通過該程序可以看出，一個(gè)Transact-SQL程序與一般的高級(jí)語言的語法要素是基本一致的，主要含有注釋、變量與常量、各種運(yùn)算符、函數(shù)與表達(dá)式、流程控制語句、批處理等。67.1.2變量局部變量DECLARE@變量名變量類型

[，@變量名變量類型……]SELECT@局部變量=變量值或SET@局部變量=變量值[例7-1]聲明一個(gè)長度為8個(gè)字符的變量id，并賦值。

DECLARE@idchar(8) SELECT@id='100100017全局變量全局變量由系統(tǒng)定義和維護(hù)的，只能使用預(yù)先說明及定義的全局變量。全局變量對用戶而言是只讀的，用戶無法對它們進(jìn)行修改或管理。注釋符在Transact-SQL中可以使用兩類注釋符：（1）ANSI標(biāo)準(zhǔn)的注釋符“--”用于單行注釋；（2）與C語言相同的程序注釋符，即“/*……*/”，“/*”用于注釋文字的開頭，“*/”用于注釋文字的結(jié)尾，可在程序中標(biāo)識(shí)多行文字為注釋。87.1.3流程控制命令BEGIN<命令行或程序塊>ENDIF<條件表達(dá)式><命令行或程序塊>[ELSE<命令行或程序塊>]IF[NOT]EXISTS(SELECT子查詢)<命令行或程序塊>[ELSE<命令行或程序塊>]CASE<表達(dá)式>WHEN<表達(dá)式>THEN<表達(dá)式>…WHEN<表達(dá)式>THEN<表達(dá)式>[ELSE<表達(dá)式>]ENDCASE9WHILE<條件表達(dá)式>BEGIN<命令行或程序塊>[BREAK][CONTINUE][命令行或程序塊]ENDWAITFOR{DELAY<'時(shí)間'>|TIME<'時(shí)間'>|ERROREXIT|PROCESSEXIT|MIRROREXIT}GOTO標(biāo)識(shí)符RETURN（[整數(shù)值]）107.1.4常用命令BACKUP用于將數(shù)據(jù)庫內(nèi)容或其事務(wù)處理日志備份到存儲(chǔ)介質(zhì)上（軟盤、硬盤、磁帶等）。CHECKPOINT用于將當(dāng)前工作的數(shù)據(jù)庫中被更改過的數(shù)據(jù)頁或日志頁從數(shù)據(jù)緩沖器中強(qiáng)制寫入硬盤。DBCC用于驗(yàn)證數(shù)據(jù)庫完整性、查找錯(cuò)誤、分析系統(tǒng)使用情況等。/imagesnew/software/tsql/index.html11DECLARE DECLARE的語法格式如下：

DECLARE{{@local_variabledata_type} |{@cursor_variable_nameCURSOR} |{table_type_definition} }[,...n]DECLARE命令用于聲明一個(gè)或多個(gè)局部變量、游標(biāo)變量或表變量。例：DECLARE@xCHAR,@yCHAR(10)SELECT@x='123',@y='data_type'PRINT@xPRINT@y12EXECUTEEXECUTE命令用來執(zhí)行存儲(chǔ)過程。KILLKILL命令用于終止某一過程的執(zhí)行。PRINTPRINT的語法格式如下：PRINT'anyASCIItext'|@local_variable|@@FUNCTION|string_expressionPRINT命令向客戶端返回一個(gè)用戶自定義的信息，即顯示一個(gè)字符串、局部變量或全局變量。13RAISERROR用于在SQLServer系統(tǒng)返回錯(cuò)誤信息時(shí)，同時(shí)返回用戶指定的信息。READTEXTREADTEXT命令的語法格式如下：READTEXT{table.columntext_pointeroffsetsize}[HOLDLOCK]READTEXT命令用于從數(shù)據(jù)類型為TEXT、NTEXT或IMAGE的列中讀取數(shù)據(jù)。RESTORERESTORE命令用來將數(shù)據(jù)庫或其事務(wù)處理日志備份文件由存儲(chǔ)介質(zhì)回存到SQLServer系統(tǒng)中。14SELECTSELECT命令可用于給變量賦值，其語法格式如下：SELECT{@local_variable=expression}[，...n]SELECT命令可以一次給多個(gè)變量賦值。SET命令有兩種用法。（1）用于給局部變量賦值。（2）用于用戶執(zhí)行SQL命令時(shí)，SQLServer處理選項(xiàng)的設(shè)定。SET：選項(xiàng)ON；SET：選項(xiàng)OFF；SET：選項(xiàng)值。15SHUTDOWNSHUTDOWN[WITHNOWAIT]SHUTDOWN命令用于停止SQLServer的執(zhí)行。WRITETEXTWRITETEXT{table.columntext_pointer}[WITHLOG]{data}WRITETEXT命令用于向數(shù)據(jù)類型為TEXT、NTEXT或IMAGE的列中讀取數(shù)據(jù)。USEUSE{database}USE命令用于改變當(dāng)前使用的數(shù)據(jù)庫為指定的數(shù)據(jù)庫。167.1.5常用函數(shù)統(tǒng)計(jì)函數(shù)STDEV函數(shù)STDEV函數(shù)返回表達(dá)式中所有數(shù)據(jù)的標(biāo)準(zhǔn)差。STDEVP函數(shù)STDEVP函數(shù)返回表達(dá)式中所有數(shù)據(jù)的總體標(biāo)準(zhǔn)差。VAR函數(shù)VAR函數(shù)返回表達(dá)式中所有數(shù)據(jù)的統(tǒng)計(jì)變異數(shù)。VARP函數(shù)VARP函數(shù)返回表達(dá)式中所有數(shù)據(jù)的總體變異數(shù)。17算術(shù)函數(shù)函數(shù)功能三角函數(shù)SINCOSTANCOT返回以弧度表示的角的正弦返回以弧度表示的角的余弦返回以弧度表示的角的正切返回以弧度表示的角的余切反三角函數(shù)ASINACOSATAN返回正弦是FLOAT值的以弧度表示的角返回余弦是FLOAT值的以弧度表示的角返回正切是FLOAT值的以弧度表示的角角度弧度轉(zhuǎn)換DEGREESRADIANS把弧度轉(zhuǎn)換為角度把角度轉(zhuǎn)換為弧度18冪函數(shù)EXPLOGLOG10SQRT

返回表達(dá)式的指數(shù)值返回表達(dá)式的自然對數(shù)值返回表達(dá)式的以10為底的對數(shù)值返回表達(dá)式的平方根取近似值函數(shù)

CEILINGFLOORROUND

返回大于等于表達(dá)式的最小整數(shù)返回小于等于表達(dá)式的最大整數(shù)取整數(shù)，小數(shù)的第一位四舍五入符號(hào)函數(shù)ABSSIGN

返回表達(dá)式的絕對值測試參數(shù)的正負(fù)號(hào)，返回0、1或－1其他函數(shù)PIRAND

返回值為π，即3.1415926535897936

返回0到1之間的隨機(jī)浮點(diǎn)數(shù)19字符串函數(shù)

字符轉(zhuǎn)換函數(shù)

ASCII（character_expression）返回字符表達(dá)式最左端字符的ASCII碼值CHAR（integer_expression）CHAR函數(shù)用于將ASCII碼轉(zhuǎn)換為字符LOWER（character_expression）LOWER函數(shù)用于把字符串全部轉(zhuǎn)換為小寫UPPER（character_expression）UPPER函數(shù)用于把字符串全部轉(zhuǎn)換為大寫STR（float_expression[，length[，<decimal>]]）STR函數(shù)用于把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)20去空格函數(shù)LTRIM（character_expression）LTRIM函數(shù)用于把字符串頭部的空格去掉。RTRIM（character_expression）RTRIM函數(shù)用于把字符串尾部的空格去掉。取子串函數(shù)LEFT（character_expression,integer_expression）LEFT函數(shù)返回的子串是從字符串最左邊起到第integer_expression個(gè)字符的部分。RIGHT（character_expression,integer_expression）RIGHT函數(shù)返回的子串是從字符串右邊第integer_expression個(gè)字符起到最后一個(gè)字符的部分。21取子串函數(shù)SUBSTRING(expression,starting_position,length)SUBSTRING函數(shù)返回的子串是從字符串左邊第starting_position個(gè)字符起length個(gè)字符的部分。字符串比較函數(shù)CHARINDEX（substring_expression，expression）CHARINDEX函數(shù)返回字符串中某個(gè)指定的子串出現(xiàn)的開始位置。PATINDEX（‵%substring_expression%′，expression）與CHARINDEX函數(shù)不同的是，PATINDEX函數(shù)的子串中可以使用通配符，且此函數(shù)可用于CHAR、VARCHAR和TEXT數(shù)據(jù)類型。22字符串比較函數(shù)SOUNDEX（character_expression）SOUNDEX函數(shù)返回一個(gè)四位字符碼。DIFFERENCE（character_expression1，character_expression2）DIFFERENCE函數(shù)返回由SOUNDEX函數(shù)返回的兩個(gè)字符表達(dá)式的值的差異。值的差異是用0、1、2、3、4來表示的，含義如下：0——兩個(gè)SOUNDEX函數(shù)返回值的第一個(gè)字符不同；1——兩個(gè)SOUNDEX函數(shù)返回值的第一個(gè)字符相同；2——兩個(gè)SOUNDEX函數(shù)返回值的第一、二個(gè)字符相同；3——兩個(gè)SOUNDEX函數(shù)返回值的第一、二、三個(gè)字符相同；4——兩個(gè)SOUNDEX函數(shù)返回值完全相同。23字符串操作函數(shù)QUOTENAME（character_expression[，quote_character]）QUOTENAME函數(shù)返回被特定字符括起來的字符串。REPLICATE（character_expression，integer_expression）REPLICATE函數(shù)返回一個(gè)重復(fù)指定次數(shù)的字符串。REVERSE（character_expression）REVERSE函數(shù)將指定的字符串的字符排列順序顛倒。24REPLACE（string_expression1，string_expression2，string_expression3）REPLACE函數(shù)返回被替換了指定子串的字符串。SPACE（integer_expression）SPACE函數(shù)返回一個(gè)有指定長度的空白字符串。STUFF（character_expression1，start_position，length，character_expression2）STUFF函數(shù)用另一子串替換字符串中指定位置長度的子串。數(shù)據(jù)類型轉(zhuǎn)換函數(shù)CAST（<expression>AS<data_type>[length]）CONVERT（<data_type>[，length]，<expression>[，style]）25日期函數(shù)DAY（<date_expression>）DAY函數(shù)返回date_expression中的日期值。MONTH（<date_expression>）MONTH函數(shù)返回date_expression中的月份值。YEAR（<date_expression>）YEAR函數(shù)返回date_expression中的年份值。DATEADD（<datepart><number><date>）DATEADD函數(shù)返回指定日期date加上指定的額外日期間隔number產(chǎn)生的新日期。26DATEDIFF（<datepart>，<date1>，<date2>）DATEDIFF函數(shù)返回兩個(gè)指定日期在datepart方面的不同之處，即date2超過date1的差距值，其結(jié)果值是一個(gè)帶有正負(fù)號(hào)的整數(shù)值。DATENAME（<datepart>，<date>）DATENAME函數(shù)以字符串的形式返回日期的指定部分，此部分由datepart來指定。DATEPART（<datepart>，<date>）DATEPART函數(shù)以整數(shù)值的形式返回日期的指定部分，此部分由datepart來指定。GETDATE（）GETDATE函數(shù)以DATETIME的缺省格式返回系統(tǒng)當(dāng)前的日期和時(shí)間，它常作為其他函數(shù)或命令的參數(shù)使用。27TEXT函數(shù)和IMAGE函數(shù)TEXTPTR（<column>）TEXTPTR函數(shù)返回一個(gè)指向存儲(chǔ)文本的第一個(gè)數(shù)據(jù)庫頁的指針。TEXTVALID（<'table.column'>，<text_pointer>）TEXTVALID函數(shù)用于檢查指定的文本指針是否有效。用戶自定義函數(shù)從SQLServer2000開始，用戶可以自定義函數(shù)，在SQLServer2000中用戶自定義函數(shù)作為一個(gè)數(shù)據(jù)庫對象來管理，可以使用企業(yè)管理器或Transact-SQL命令來進(jìn)行用戶自定義函數(shù)的創(chuàng)建、修改和刪除。287.2存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程用戶自定義存儲(chǔ)過程7.2.1存儲(chǔ)過程的概念、優(yōu)點(diǎn)及分類存儲(chǔ)過程是一組為了完成特定功能的SQL語句集。存儲(chǔ)過程的優(yōu)點(diǎn)：存儲(chǔ)過程的分類：

模塊化的程序設(shè)計(jì)高效率的執(zhí)行減少網(wǎng)絡(luò)流量可以作為安全機(jī)制使用297.2.2創(chuàng)建存儲(chǔ)過程當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三個(gè)組成部分：（1）所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。（2）被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲(chǔ)過程的語句。（3）返回給調(diào)用者的狀態(tài)值以指明調(diào)用是成功還是失敗。用CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過程30CREATEPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n][例7-11]在Teach數(shù)據(jù)庫中，創(chuàng)建一個(gè)名稱為MyProc的不帶參數(shù)的存儲(chǔ)過程，該存儲(chǔ)過程的功能是從數(shù)據(jù)表S中查詢所有男同學(xué)的信息。CREATEPROCEDUREMyProcASSELECT*FROMSWHEREsex='男'31[例7-12]定義具有參數(shù)的存儲(chǔ)過程。在Teach數(shù)據(jù)庫中，創(chuàng)建一個(gè)名稱為InsertRecord的存儲(chǔ)過程，該存儲(chǔ)過程的功能是向S數(shù)據(jù)表中插入一條記錄，新記錄的值由參數(shù)提供。CREATEPROCEDUREInsertRecord( @snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10))ASINSERTINTOSVALUES(@sno,@sn,@age,@sex,@dept)32[例7-13]定義具有參數(shù)默認(rèn)值的存儲(chǔ)過程。在Teach數(shù)據(jù)庫中，創(chuàng)建一個(gè)名稱為InsertRecordDefa的存儲(chǔ)過程，該存儲(chǔ)過程的功能是向S數(shù)據(jù)表中插入一條記錄，新記錄的值由參數(shù)提供，如果未提供系別Dept的值時(shí)，由參數(shù)的默認(rèn)值代替。CREATEPROCEDUREInsertRecordDefa( @snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)='無')ASINSERTINTOSVALUES(@sno,@sn,@age,@sex,@dept)33[例7-14]定義能夠返回值的存儲(chǔ)過程。在Teach數(shù)據(jù)庫中，創(chuàng)建一個(gè)名稱為QueryTeach的存儲(chǔ)過程。該存儲(chǔ)過程的功能是從數(shù)據(jù)表S中根據(jù)學(xué)號(hào)查詢某一同學(xué)的姓名和系別，查詢的結(jié)果由參數(shù)@sn和@dept返回。CREATEPROCEDUREQueryTeach( @snochar(6),@snchar(20)OUTPUT,@deptchar(10)OUTPUT)ASSELECT@sn=SN,@dept=DeptFROMSWHERESNo=@sno34查看存儲(chǔ)過程重新命名存儲(chǔ)過程

刪除存儲(chǔ)過程執(zhí)行存儲(chǔ)過程修改存儲(chǔ)過程EXECsp_helptext存儲(chǔ)過程名稱sp_rename原存儲(chǔ)過程名,新存儲(chǔ)過程名DROPPROCEDURE{procedure}}[,…n]ALTERPROCEDUREprocedure_nameEXECMyProc357.3觸發(fā)器7.3.1觸發(fā)器的概念、分類與作用觸發(fā)器是一種特殊類型的存儲(chǔ)過程。觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過程可以通過存儲(chǔ)過程名字而被直接調(diào)用。觸發(fā)器有4個(gè)要素：名稱：觸發(fā)器有一個(gè)符合標(biāo)志符命名規(guī)則的名稱。定義的目標(biāo)：觸發(fā)器必須定義在表或者視圖上。觸發(fā)條件：是UPDATE、INSERT還是DELETE語句。觸發(fā)邏輯：觸發(fā)之后如何處理。36強(qiáng)化約束跟蹤變化存儲(chǔ)過程的調(diào)用級(jí)聯(lián)運(yùn)行觸發(fā)器的種類AFTER觸發(fā)器AFTER觸發(fā)器是告訴SQL語句執(zhí)行了INSERT、UPDATE或者DELETE操作后干什么。INSTEADOF觸發(fā)器告訴當(dāng)要執(zhí)行INSERT、UPDATE或DELETE操作時(shí)用什么別的操作來代替。觸發(fā)器的作用377.3.2觸發(fā)器的工作原理SQLServer在工作時(shí)為每個(gè)觸發(fā)器在服務(wù)器的內(nèi)存上建立兩個(gè)特殊的表：插入表和刪除表。（1）插入表的功能一旦對該表執(zhí)行了插入（INSERT）操作，那么對該表插入的所有行來說，都有一個(gè)相應(yīng)的副本存放到Inserted表中，即Inserted表用來存儲(chǔ)原表插入的內(nèi)容。（2）刪除表的功能一旦對該表執(zhí)行了刪除（DELETE）操作，則將所有的刪除行存放至Deleted表中。這樣做的目的是，一旦觸發(fā)器遇到了強(qiáng)迫它中止的語句被執(zhí)行時(shí)，刪除的那些行可以從Deleted表中得以還原。387.3.3創(chuàng)建觸發(fā)器在創(chuàng)建觸發(fā)器以前必須考慮到以下幾個(gè)方面：CREATETRIGGER語句必須是批處理的第一個(gè)語句。表的所有者具有創(chuàng)建觸發(fā)器的缺省權(quán)限，表的所有者不能把該權(quán)限傳給其他用戶。觸發(fā)器是數(shù)據(jù)庫對象，所以其命名必須符合命名規(guī)則。盡管在觸發(fā)器的SQL語句中可以參照其他數(shù)據(jù)庫中的對象，但是觸發(fā)器只能創(chuàng)建在當(dāng)前數(shù)據(jù)庫中。雖然觸發(fā)器可以參照視圖或臨時(shí)表，但不能在視圖或臨時(shí)表上創(chuàng)建觸發(fā)器，只能在基表或在創(chuàng)建視圖的表上創(chuàng)建觸發(fā)器。一個(gè)觸發(fā)器只能對應(yīng)一個(gè)表，這是由觸發(fā)器的機(jī)制決定的。盡管TRUNCATETABLE語句如同沒有WHERE從句的DELETE語句，但是由于TRUNCATETABLE語句沒有被記入日志，所以該語句不能觸發(fā)DELETE型觸發(fā)器。WRITETEXT語句不能觸發(fā)INSERT或UPDATE型的觸發(fā)器。39[例7-22]創(chuàng)建一個(gè)觸發(fā)器，當(dāng)向S表中插入一條記錄時(shí)，自動(dòng)顯示S表中的記錄。CREATETRIGGERChangeDisplayONSFORINSERTASSELECT*FROMS使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器EXECsp_help'觸發(fā)器名'了解觸發(fā)器的一般信息，如觸發(fā)器的名字、屬性、類型、創(chuàng)建時(shí)間40EXECsp_helptext'觸發(fā)器名'查看觸發(fā)器的正文信息EXECsp_depends'觸發(fā)器名'EXECsp_depends'表名'查看指定觸發(fā)器所引用的表或指定的表所涉及到的所有觸發(fā)器修改觸發(fā)器使用sp_rename修改觸發(fā)器的名字sp_renameoldname,newname41…通過ALTERTRIGGER命令修改觸發(fā)器正文ALTERTRIGGERtrigger_nameON（table|view）[WITHENCRYPTION]{{FOR|AFTER|INSTEADOF{[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]AS刪除觸發(fā)器用系統(tǒng)命令DROPTRIGGER刪