




版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認(rèn)領(lǐng)
文檔簡介
第7章SQLServer2000高級應(yīng)用27.1Transact-SQL程序設(shè)計7.2存儲過程7.3觸發(fā)器7.4備份和還原37.1Transact-SQL程序設(shè)計Transact-SQL語言就是在標(biāo)準(zhǔn)SQL的基礎(chǔ)上進行擴充而推出的SQLServer專用的結(jié)構(gòu)化SQL。Transact-SQL語言最主要的用途是設(shè)計服務(wù)器端的能夠在后臺執(zhí)行的程序塊。7.1.1Transact-SQL程序的結(jié)構(gòu)與批處理Transact-SQL程序的結(jié)構(gòu)以下程序是打開教學(xué)管理數(shù)據(jù)庫TEACH,并從數(shù)據(jù)表SC中讀取學(xué)號為“S1”同學(xué)所學(xué)課程的平均分,如果該平均分大于或等于60分,則程序輸出“課程平均成績超過60”,否則輸出“課程平均成績不超過60”。
存儲過程、觸發(fā)器等4/*Transact-SQL程序的實例*/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)兩個“GO”標(biāo)記符之間的代碼稱為一個“批”。SQLServer對Transact-SQL程序的編譯和執(zhí)行是按照“批”為單位來進行的,稱為批處理。一個Transact-SQL程序內(nèi)可以包含多個“批”。
通過該程序可以看出,一個Transact-SQL程序與一般的高級語言的語法要素是基本一致的,主要含有注釋、變量與常量、各種運算符、函數(shù)與表達式、流程控制語句、批處理等。67.1.2變量局部變量DECLARE@變量名變量類型
[,@變量名變量類型……]SELECT@局部變量=變量值或SET@局部變量=變量值[例7-1]聲明一個長度為8個字符的變量id,并賦值。
DECLARE@idchar(8) SELECT@id='100100017全局變量全局變量由系統(tǒng)定義和維護的,只能使用預(yù)先說明及定義的全局變量。全局變量對用戶而言是只讀的,用戶無法對它們進行修改或管理。注釋符在Transact-SQL中可以使用兩類注釋符:(1)ANSI標(biāo)準(zhǔn)的注釋符“--”用于單行注釋;(2)與C語言相同的程序注釋符,即“/*……*/”,“/*”用于注釋文字的開頭,“*/”用于注釋文字的結(jié)尾,可在程序中標(biāo)識多行文字為注釋。87.1.3流程控制命令BEGIN<命令行或程序塊>ENDIF<條件表達式><命令行或程序塊>[ELSE<命令行或程序塊>]IF[NOT]EXISTS(SELECT子查詢)<命令行或程序塊>[ELSE<命令行或程序塊>]CASE<表達式>WHEN<表達式>THEN<表達式>…WHEN<表達式>THEN<表達式>[ELSE<表達式>]ENDCASE9WHILE<條件表達式>BEGIN<命令行或程序塊>[BREAK][CONTINUE][命令行或程序塊]ENDWAITFOR{DELAY<'時間'>|TIME<'時間'>|ERROREXIT|PROCESSEXIT|MIRROREXIT}GOTO標(biāo)識符RETURN([整數(shù)值])107.1.4常用命令BACKUP用于將數(shù)據(jù)庫內(nèi)容或其事務(wù)處理日志備份到存儲介質(zhì)上(軟盤、硬盤、磁帶等)。CHECKPOINT用于將當(dāng)前工作的數(shù)據(jù)庫中被更改過的數(shù)據(jù)頁或日志頁從數(shù)據(jù)緩沖器中強制寫入硬盤。DBCC用于驗證數(shù)據(jù)庫完整性、查找錯誤、分析系統(tǒng)使用情況等。/imagesnew/software/tsql/index.html11DECLARE DECLARE的語法格式如下:
DECLARE{{@local_variabledata_type} |{@cursor_variable_nameCURSOR} |{table_type_definition} }[,...n]DECLARE命令用于聲明一個或多個局部變量、游標(biāo)變量或表變量。例:DECLARE@xCHAR,@yCHAR(10)SELECT@x='123',@y='data_type'PRINT@xPRINT@y12EXECUTEEXECUTE命令用來執(zhí)行存儲過程。KILLKILL命令用于終止某一過程的執(zhí)行。PRINTPRINT的語法格式如下:PRINT'anyASCIItext'|@local_variable|@@FUNCTION|string_expressionPRINT命令向客戶端返回一個用戶自定義的信息,即顯示一個字符串、局部變量或全局變量。13RAISERROR用于在SQLServer系統(tǒng)返回錯誤信息時,同時返回用戶指定的信息。READTEXTREADTEXT命令的語法格式如下:READTEXT{table.columntext_pointeroffsetsize}[HOLDLOCK]READTEXT命令用于從數(shù)據(jù)類型為TEXT、NTEXT或IMAGE的列中讀取數(shù)據(jù)。RESTORERESTORE命令用來將數(shù)據(jù)庫或其事務(wù)處理日志備份文件由存儲介質(zhì)回存到SQLServer系統(tǒng)中。14SELECTSELECT命令可用于給變量賦值,其語法格式如下:SELECT{@local_variable=expression}[,...n]SELECT命令可以一次給多個變量賦值。SET命令有兩種用法。(1)用于給局部變量賦值。(2)用于用戶執(zhí)行SQL命令時,SQLServer處理選項的設(shè)定。SET:選項ON;SET:選項OFF;SET:選項值。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)計函數(shù)STDEV函數(shù)STDEV函數(shù)返回表達式中所有數(shù)據(jù)的標(biāo)準(zhǔn)差。STDEVP函數(shù)STDEVP函數(shù)返回表達式中所有數(shù)據(jù)的總體標(biāo)準(zhǔn)差。VAR函數(shù)VAR函數(shù)返回表達式中所有數(shù)據(jù)的統(tǒng)計變異數(shù)。VARP函數(shù)VARP函數(shù)返回表達式中所有數(shù)據(jù)的總體變異數(shù)。17算術(shù)函數(shù)函數(shù)功能三角函數(shù)SINCOSTANCOT返回以弧度表示的角的正弦返回以弧度表示的角的余弦返回以弧度表示的角的正切返回以弧度表示的角的余切反三角函數(shù)ASINACOSATAN返回正弦是FLOAT值的以弧度表示的角返回余弦是FLOAT值的以弧度表示的角返回正切是FLOAT值的以弧度表示的角角度弧度轉(zhuǎn)換DEGREESRADIANS把弧度轉(zhuǎn)換為角度把角度轉(zhuǎn)換為弧度18冪函數(shù)EXPLOGLOG10SQRT
返回表達式的指數(shù)值返回表達式的自然對數(shù)值返回表達式的以10為底的對數(shù)值返回表達式的平方根取近似值函數(shù)
CEILINGFLOORROUND
返回大于等于表達式的最小整數(shù)返回小于等于表達式的最大整數(shù)取整數(shù),小數(shù)的第一位四舍五入符號函數(shù)ABSSIGN
返回表達式的絕對值測試參數(shù)的正負(fù)號,返回0、1或-1其他函數(shù)PIRAND
返回值為π,即3.1415926535897936
返回0到1之間的隨機浮點數(shù)19字符串函數(shù)
字符轉(zhuǎn)換函數(shù)
ASCII(character_expression)返回字符表達式最左端字符的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個字符的部分。RIGHT(character_expression,integer_expression)RIGHT函數(shù)返回的子串是從字符串右邊第integer_expression個字符起到最后一個字符的部分。21取子串函數(shù)SUBSTRING(expression,starting_position,length)SUBSTRING函數(shù)返回的子串是從字符串左邊第starting_position個字符起length個字符的部分。字符串比較函數(shù)CHARINDEX(substring_expression,expression)CHARINDEX函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置。PATINDEX(‵%substring_expression%′,expression)與CHARINDEX函數(shù)不同的是,PATINDEX函數(shù)的子串中可以使用通配符,且此函數(shù)可用于CHAR、VARCHAR和TEXT數(shù)據(jù)類型。22字符串比較函數(shù)SOUNDEX(character_expression)SOUNDEX函數(shù)返回一個四位字符碼。DIFFERENCE(character_expression1,character_expression2)DIFFERENCE函數(shù)返回由SOUNDEX函數(shù)返回的兩個字符表達式的值的差異。值的差異是用0、1、2、3、4來表示的,含義如下:0——兩個SOUNDEX函數(shù)返回值的第一個字符不同;1——兩個SOUNDEX函數(shù)返回值的第一個字符相同;2——兩個SOUNDEX函數(shù)返回值的第一、二個字符相同;3——兩個SOUNDEX函數(shù)返回值的第一、二、三個字符相同;4——兩個SOUNDEX函數(shù)返回值完全相同。23字符串操作函數(shù)QUOTENAME(character_expression[,quote_character])QUOTENAME函數(shù)返回被特定字符括起來的字符串。REPLICATE(character_expression,integer_expression)REPLICATE函數(shù)返回一個重復(fù)指定次數(shù)的字符串。REVERSE(character_expression)REVERSE函數(shù)將指定的字符串的字符排列順序顛倒。24REPLACE(string_expression1,string_expression2,string_expression3)REPLACE函數(shù)返回被替換了指定子串的字符串。SPACE(integer_expression)SPACE函數(shù)返回一個有指定長度的空白字符串。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ù)返回兩個指定日期在datepart方面的不同之處,即date2超過date1的差距值,其結(jié)果值是一個帶有正負(fù)號的整數(shù)值。DATENAME(<datepart>,<date>)DATENAME函數(shù)以字符串的形式返回日期的指定部分,此部分由datepart來指定。DATEPART(<datepart>,<date>)DATEPART函數(shù)以整數(shù)值的形式返回日期的指定部分,此部分由datepart來指定。GETDATE()GETDATE函數(shù)以DATETIME的缺省格式返回系統(tǒng)當(dāng)前的日期和時間,它常作為其他函數(shù)或命令的參數(shù)使用。27TEXT函數(shù)和IMAGE函數(shù)TEXTPTR(<column>)TEXTPTR函數(shù)返回一個指向存儲文本的第一個數(shù)據(jù)庫頁的指針。TEXTVALID(<'table.column'>,<text_pointer>)TEXTVALID函數(shù)用于檢查指定的文本指針是否有效。用戶自定義函數(shù)從SQLServer2000開始,用戶可以自定義函數(shù),在SQLServer2000中用戶自定義函數(shù)作為一個數(shù)據(jù)庫對象來管理,可以使用企業(yè)管理器或Transact-SQL命令來進行用戶自定義函數(shù)的創(chuàng)建、修改和刪除。287.2存儲過程系統(tǒng)存儲過程擴展存儲過程用戶自定義存儲過程7.2.1存儲過程的概念、優(yōu)點及分類存儲過程是一組為了完成特定功能的SQL語句集。存儲過程的優(yōu)點:存儲過程的分類:
模塊化的程序設(shè)計高效率的執(zhí)行減少網(wǎng)絡(luò)流量可以作為安全機制使用297.2.2創(chuàng)建存儲過程當(dāng)創(chuàng)建存儲過程時,需要確定存儲過程的三個組成部分:(1)所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。(2)被執(zhí)行的針對數(shù)據(jù)庫的操作語句,包括調(diào)用其他存儲過程的語句。(3)返回給調(diào)用者的狀態(tài)值以指明調(diào)用是成功還是失敗。用CREATEPROCEDURE命令創(chuàng)建存儲過程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)建一個名稱為MyProc的不帶參數(shù)的存儲過程,該存儲過程的功能是從數(shù)據(jù)表S中查詢所有男同學(xué)的信息。CREATEPROCEDUREMyProcASSELECT*FROMSWHEREsex='男'31[例7-12]定義具有參數(shù)的存儲過程。在Teach數(shù)據(jù)庫中,創(chuàng)建一個名稱為InsertRecord的存儲過程,該存儲過程的功能是向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)值的存儲過程。在Teach數(shù)據(jù)庫中,創(chuàng)建一個名稱為InsertRecordDefa的存儲過程,該存儲過程的功能是向S數(shù)據(jù)表中插入一條記錄,新記錄的值由參數(shù)提供,如果未提供系別Dept的值時,由參數(shù)的默認(rèn)值代替。CREATEPROCEDUREInsertRecordDefa( @snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)='無')ASINSERTINTOSVALUES(@sno,@sn,@age,@sex,@dept)33[例7-14]定義能夠返回值的存儲過程。在Teach數(shù)據(jù)庫中,創(chuàng)建一個名稱為QueryTeach的存儲過程。該存儲過程的功能是從數(shù)據(jù)表S中根據(jù)學(xué)號查詢某一同學(xué)的姓名和系別,查詢的結(jié)果由參數(shù)@sn和@dept返回。CREATEPROCEDUREQueryTeach( @snochar(6),@snchar(20)OUTPUT,@deptchar(10)OUTPUT)ASSELECT@sn=SN,@dept=DeptFROMSWHERESNo=@sno34查看存儲過程重新命名存儲過程
刪除存儲過程執(zhí)行存儲過程修改存儲過程EXECsp_helptext存儲過程名稱sp_rename原存儲過程名,新存儲過程名DROPPROCEDURE{procedure}}[,…n]ALTERPROCEDUREprocedure_nameEXECMyProc357.3觸發(fā)器7.3.1觸發(fā)器的概念、分類與作用觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的,而存儲過程可以通過存儲過程名字而被直接調(diào)用。觸發(fā)器有4個要素:名稱:觸發(fā)器有一個符合標(biāo)志符命名規(guī)則的名稱。定義的目標(biāo):觸發(fā)器必須定義在表或者視圖上。觸發(fā)條件:是UPDATE、INSERT還是DELETE語句。觸發(fā)邏輯:觸發(fā)之后如何處理。36強化約束跟蹤變化存儲過程的調(diào)用級聯(lián)運行觸發(fā)器的種類AFTER觸發(fā)器AFTER觸發(fā)器是告訴SQL語句執(zhí)行了INSERT、UPDATE或者DELETE操作后干什么。INSTEADOF觸發(fā)器告訴當(dāng)要執(zhí)行INSERT、UPDATE或DELETE操作時用什么別的操作來代替。觸發(fā)器的作用377.3.2觸發(fā)器的工作原理SQLServer在工作時為每個觸發(fā)器在服務(wù)器的內(nèi)存上建立兩個特殊的表:插入表和刪除表。(1)插入表的功能一旦對該表執(zhí)行了插入(INSERT)操作,那么對該表插入的所有行來說,都有一個相應(yīng)的副本存放到Inserted表中,即Inserted表用來存儲原表插入的內(nèi)容。(2)刪除表的功能一旦對該表執(zhí)行了刪除(DELETE)操作,則將所有的刪除行存放至Deleted表中。這樣做的目的是,一旦觸發(fā)器遇到了強迫它中止的語句被執(zhí)行時,刪除的那些行可以從Deleted表中得以還原。387.3.3創(chuàng)建觸發(fā)器在創(chuàng)建觸發(fā)器以前必須考慮到以下幾個方面:CREATETRIGGER語句必須是批處理的第一個語句。表的所有者具有創(chuàng)建觸發(fā)器的缺省權(quán)限,表的所有者不能把該權(quán)限傳給其他用戶。觸發(fā)器是數(shù)據(jù)庫對象,所以其命名必須符合命名規(guī)則。盡管在觸發(fā)器的SQL語句中可以參照其他數(shù)據(jù)庫中的對象,但是觸發(fā)器只能創(chuàng)建在當(dāng)前數(shù)據(jù)庫中。雖然觸發(fā)器可以參照視圖或臨時表,但不能在視圖或臨時表上創(chuàng)建觸發(fā)器,只能在基表或在創(chuàng)建視圖的表上創(chuàng)建觸發(fā)器。一個觸發(fā)器只能對應(yīng)一個表,這是由觸發(fā)器的機制決定的。盡管TRUNCATETABLE語句如同沒有WHERE從句的DELETE語句,但是由于TRUNCATETABLE語句沒有被記入日志,所以該語句不能觸發(fā)DELETE型觸發(fā)器。WRITETEXT語句不能觸發(fā)INSERT或UPDATE型的觸發(fā)器。39[例7-22]創(chuàng)建一個觸發(fā)器,當(dāng)向S表中插入一條記錄時,自動顯示S表中的記錄。CREATETRIGGERChangeDisplayONSFORINSERTASSELECT*FROMS使用系統(tǒng)存儲過程查看觸發(fā)器EXECsp_help'觸發(fā)器名'了解觸發(fā)器的一般信息,如觸發(fā)器的名字、屬性、類型、創(chuàng)建時間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刪
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 無機顏料制造考核試卷
- 樂器聲音的數(shù)字化處理與優(yōu)化考核試卷
- 木樓梯的聲學(xué)性能改善措施考核試卷
- 勞動法律法規(guī)解讀考核試卷
- 固體廢物處理與環(huán)保科技創(chuàng)新考核試卷
- 體育會展新媒體運營與粉絲經(jīng)濟考核試卷
- 體育經(jīng)紀(jì)公司體育場館運營與管理策略考核試卷
- 房屋改建施工合同范本
- 簡易土建勞務(wù)合同范本
- 俱樂部合同范本模板
- A類《職業(yè)能力傾向測驗》貴州省畢節(jié)地區(qū)2024年事業(yè)單位考試考前沖刺試卷含解析
- 沙子檢測報告
- 博物館消防培訓(xùn)課件
- 腦血管造影術(shù)護理查房課件
- 殘疾人就業(yè)困境及其破解對策
- 《油液分析技術(shù)》課件
- 運動療法技術(shù)學(xué)
- 《蜀道難》理解性默寫(帶答案)
- 塔吊租賃(大型機械)-招標(biāo)文件模板(完整版)2021.5.13
- 護理學(xué)基礎(chǔ)期末試卷及答案
- IMS攪拌樁施工方案
評論
0/150
提交評論