《程序設(shè)計(jì)》PPT課件.ppt

第十章 T-SQL程序設(shè)計(jì),1,本章主題,將兩個(gè)或更多的表聯(lián)接起來以看到更多的結(jié)果信息 獲取一種通過變量以臨時(shí)保存信息的方法 如何將行信息保存到非永久的表中 如何聚合值 組織輸出數(shù)據(jù)到相關(guān)的信息分組中 返回唯一和獨(dú)特的值 錯(cuò)誤處理：如何創(chuàng)建你自己的錯(cuò)誤，捕捉錯(cuò)誤，并讓代碼更安全,T-SQL程序設(shè)計(jì),3,T-SQL的目的在于為處理大量數(shù)據(jù)提供必要的結(jié)構(gòu)化處理能力。前面的章節(jié)中已經(jīng)介紹了一些處理簡單查詢的T-SQL語句，但是當(dāng)面對更為復(fù)雜的應(yīng)用時(shí)，前面所學(xué)的知識就遠(yuǎn)遠(yuǎn)不夠了。因此，在本章中將主要討論如何使用T-SQL提供的豐富編程結(jié)構(gòu)，編寫出復(fù)雜的例行程序。,第一節(jié) T-SQL簡介,4,T-SQL是Microsoft SQL Server提供的查詢語言，它是Microsoft公司對于ANSI SQL的一個(gè)擴(kuò)展，它不僅提供了對SQL標(biāo)準(zhǔn)的支持，另外還提供了類似于C等編程語言的基本功能。T-SQL的目的在于為事務(wù)型數(shù)據(jù)庫開發(fā)提供一套過程化的開發(fā)工具。 T-SQL對于使用SQL Server非常重要，它是SQL Server功能的核心，使用T-SQL編寫程序可以完成所有的數(shù)據(jù)庫管理工作，與SQL Server通信的所有程序都通過向數(shù)據(jù)庫服務(wù)器發(fā)送T-SQL語句來進(jìn)行通信，而與應(yīng)用程序的用戶界面是什么形式無關(guān)。,第二節(jié) T-SQL編程基礎(chǔ),5,(一) 注釋,6,T-SQL中的注釋語句，也稱為注解，注釋內(nèi)容通常是一些說明性文字，對程序的結(jié)構(gòu)及功能給出簡要的解釋。注釋語句不是可執(zhí)行語句，不被系統(tǒng)編譯，也不被程序執(zhí)行。使用注釋語句的目的是為了使程序代碼易讀易分析，也便于日后的管理和維護(hù)。 SQL Server支持兩種形式的程序注釋語句： 1單行注釋語句：使用ANSI標(biāo)準(zhǔn)的注釋符 “-”，注釋語句寫在“-”的后面，只能書寫單行。 2多行注釋語句：使用與C語言相同的程序注釋符“/* */”，注釋語句寫在“/*”和“*/”之間，可以連續(xù)書寫多行。 例10-1：合法的注釋語句。 -打開Student數(shù)據(jù)庫 USE Student GO /*查詢Classes表中所有記錄的Class_id, Class_name以及 Class_department三個(gè)字段的內(nèi)容*/,7,注釋,SELECT Class_id, Class_name, Class_department FROM Classes GO 當(dāng)然，連續(xù)多行注釋也可以使用注釋符“-”，只要每行注釋開頭都加上注釋符“-”。單行注釋語句也可以使用注釋符“/* */”，只要將注釋語句寫在“/*”和“*/”之間。 例如上面的例子中注釋語句也可以寫成： /*打開Student數(shù)據(jù)庫*/ USE Student GO -本條SQL語句用來查詢Classes表中所有記錄的Class_id, Class_name, - Class_department三個(gè)字段的內(nèi)容 SELECT Class_id, Class_name, Class_department FROM Classes GO,(二) 批處理,8,所謂批是指從客戶機(jī)傳送到服務(wù)器上的一組完整數(shù)據(jù)和SQL指令，批中的所有SQL語句做為一個(gè)整體編譯成一個(gè)執(zhí)行單元后從應(yīng)用程序一次性地發(fā)送到SQL Server服務(wù)器進(jìn)行執(zhí)行，稱之為批處理。 所有的批處理命令都使用GO作為結(jié)束標(biāo)志，當(dāng)T-SQL的編譯器掃描到某行的前兩個(gè)字符是GO的時(shí)候，它會把GO前面的所有語句作為一個(gè)批處理送往服務(wù)器。 由于批處理中的所有語句被當(dāng)作是一個(gè)整體，因此若其中一個(gè)語句出現(xiàn)了編譯錯(cuò)誤，則該批處理內(nèi)所有語句的執(zhí)行都將被取消。 例10-2：正確的批處理。 -第一個(gè)批處理打開Student數(shù)據(jù)庫 USE Student GO -第二個(gè)批處理在Teachers表中查詢姓王的教師的記錄 SELECT * FROM Teachers WHERE SUBSTRING(Teacher_name,1,1)= 王 GO,(三) 局部變量,9,變量是執(zhí)行程序中必不可少的部分，它主要是用來在程序運(yùn)行過程中存儲和傳遞數(shù)據(jù)。變量其實(shí)就是內(nèi)存中的一個(gè)存儲區(qū)域，存儲在這個(gè)區(qū)域中的數(shù)據(jù)就是變量的值。在T-SQL語句中變量有兩種，局部變量與全局變量。這兩種變量在使用方法和具體意義上均不相同，本部分主要介紹局部變量。 局部變量是作用域局限在一定范圍內(nèi)的變量，是用戶自定義的變量。一般來說，局部變量的使用范圍局限于定義它的批處理內(nèi)。定義它的批處理中的SQL語句可以引用這個(gè)局部變量，直到批處理結(jié)束，這個(gè)局部變量的生命周期也就結(jié)束了。 1局部變量的聲明 在使用一個(gè)局部變量之前，必須先聲明該變量。聲明一個(gè)局部變量的語法格式如下： DECLARE 變量名 變量類型 ，變量名 變量類型 聲明語句中的各部分說明如下： 局部變量名的命名必須遵循SQL Server的標(biāo)識符命名規(guī)則，并且必須以字符“”開頭。 局部變量的類型可以是系統(tǒng)數(shù)據(jù)類型，也可以是用戶自定義的數(shù)據(jù)類型。 DECLARE語句可以聲明一個(gè)或多個(gè)局部變量，變量被聲明以后初值都是NULL。 如語句DECLARE MyScore INT聲明了一個(gè)整型局部變量MyScore 。也可以在同一語句中聲明多個(gè)局部變量，例如： DECLARE Student_Name VARCHAR(20), Student_Address VARCHAR (60),10,2局部變量的賦值 局部變量被創(chuàng)建之后，系統(tǒng)將其初始值設(shè)為NULL。若要改變局部變量的值，可以使用SET語句或SELECT語句給局部變量重新賦值。 SELECT語句的語法格式為： SELECT 變量名 = 表達(dá)式 ，變量名 = 表達(dá)式 SET語句的語法格式為： SET 變量名 = 表達(dá)式 賦值語句中的各部分說明如下： 變量名是準(zhǔn)備為其賦值的局部變量。 表達(dá)式是有效的SQL Server表達(dá)式，且其類型應(yīng)與局部變量的數(shù)據(jù)類型相匹配。 從語法格式中可看出，SELECT語句和SET語句的區(qū)別在于使用SET語句一次只能給一個(gè)變量賦值，而在SELECT語句中可以一次給多個(gè)變量賦值。,局部變量,11,局部變量,例10-3：局部變量的賦值。 USE Student GO -聲明局部變量 DECLARE sname VARCHAR(30), sclassid VARCHAR(30) -使用SET語句給局部變量賦值 SET sname = 李佳佳 SET sclassid = 2005011 -根據(jù)局部變量的值查詢符合條件的記錄的姓名和性別 SELECT Student_name,Student_sex, Student_classid FROM Students WHERE Student_name =sname OR Student_classid =sclassid -將Student_id為11001的記錄的姓名和班級賦值給局部變量 SELECT sname =Student_name, sclassid =Student_classid FROM Students WHERE Student_id=11001 -輸出變量sname和sclassid的值 SELECT sname AS sname, sclassid AS sclassid GO,12,局部變量,本例主要完成了兩個(gè)操作，一是根據(jù)局部變量的初值在Students表中查詢符合條件的記錄，二是將指定記錄的姓名及班級號的值賦給局部變量，改變局部變量原來的值并輸出其新值。程序的運(yùn)行結(jié)果如圖10.1所示。,圖10.1 例10-3執(zhí)行結(jié)果,13,局部變量,3局部變量的輸出 例10-3中使用了SELECT語句來輸出局部變量的值，除此之外局部變量的輸出還可以使用PRINT語句。PRINT語句的語法格式如下： PRINT 局部變量名 如例10-3中最后的輸出語句SELECT sname AS sname, sclassid AS sclassid也可以改寫成如下的兩條PRINT語句： PRINT sname PRINT sclassid,(四) 全局變量,14,全局變量是以“”開頭，由系統(tǒng)預(yù)先定義并負(fù)責(zé)維護(hù)的變量，也可以把全局變量看成是一種特殊形式的函數(shù)。全局變量不可以由用戶隨意建立和修改，作用范圍也并不局限于某個(gè)程序，而是任何程序均可調(diào)用。常用的全局變量有三十多個(gè)，通常用來存儲一些SQL Server的配置值和效能統(tǒng)計(jì)數(shù)字，用戶可以通過查詢?nèi)肿兞縼頇z測系統(tǒng)的參數(shù)值或者執(zhí)行查詢命令后的狀態(tài)值。 在全局變量的使用過程中要注意，全局變量是由SQL Server服務(wù)器定義的，不是用戶自定義的，用戶只能使用預(yù)先定義的全局變量，且在引用全局變量時(shí)，必須以“”開頭。另外，局部變量的名稱不能與全局變量的名稱相同，否則就會在應(yīng)用程序中出錯(cuò)。 表10-1中列出了SQL Server的幾個(gè)常用全局變量及其含義，對于其他全局變量，讀者可通過自行查閱SQL Server 2005聯(lián)機(jī)叢書進(jìn)行學(xué)習(xí)。,表10-1 SQL Server的常用全局變量,15,全局變量,例10-4：全局變量ERROR的使用。 USE Student GO -在Teachers表中插入一條新記錄 INSERT INTO Teachers (Teacher_id, Teacher_name ,Teacher_department) VALUES (JS006, 張海濤, 002) -使用全局變量ERROR輸出錯(cuò)誤代碼的值 SELECT ERROR AS 錯(cuò)誤 GO,圖10.2例10-4執(zhí)行結(jié)果,(五) 運(yùn)算符,16,使用T-SQL來編寫例行程序時(shí)，不可避免的要使用到運(yùn)算符。運(yùn)算符用來完成一個(gè)或多個(gè)表達(dá)式中指定的操作，SQL Server中的運(yùn)算符包括有：算術(shù)運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、位運(yùn)算符、賦值運(yùn)算符、連接運(yùn)算符。 1算術(shù)運(yùn)算符 算術(shù)運(yùn)算符用來執(zhí)行算術(shù)運(yùn)算，T-SQL中的算術(shù)運(yùn)算符包括： +（加法運(yùn)算符） -（減法運(yùn)算符） *（乘法運(yùn)算符） /（除法運(yùn)算符） %（模運(yùn)算符或取余運(yùn)算符，返回一個(gè)除法的整數(shù)余數(shù)，要求數(shù)據(jù)類型為INT、 SMALLINT和TINYINT）,17,運(yùn)算符,例10-5：計(jì)算算術(shù)表達(dá)式的值。 DECLARE Value NUMERIC SET Value =132%7 PRINT Value 程序的運(yùn)行結(jié)果如圖10.3所示。 2賦值運(yùn)算符 T-SQL中的賦值運(yùn)算符只有一個(gè)，就是“”（等號）。和其它語言中的賦值運(yùn)算符一樣，T-SQL中的賦值運(yùn)算符的作用就是將數(shù)據(jù)值指派給特定的對象。此外，也可以使用賦值運(yùn)算符在列標(biāo)題和列定義值的表達(dá)式之間建立關(guān)系。,圖10.3 例10-5執(zhí)行結(jié)果,18,運(yùn)算符,例10-6：查詢經(jīng)濟(jì)信息系所有教師的信息，要求輸出時(shí)的列標(biāo)題為：教師號、教師姓名、教師所在系，且要求教師所在系名顯示為“信息系”。 USE Student GO SELECT 教師號= Teachers.Teacher_id, 教師姓名= Teachers.Teacher_name, 教師所在系=SUBSTRING(Departments.Department_name,3,3) FROM Teachers INNER JOIN Departments ON Teachers.Teacher_department= Departments.Department_id WHERE Departments. Department_name =經(jīng)濟(jì)信息系 GO,圖10.4例10-6執(zhí)行結(jié)果,19,運(yùn)算符,3比較運(yùn)算符 比較運(yùn)算符用于比較兩個(gè)表達(dá)式的大小，T-SQL中的比較運(yùn)算符包括：（大于）、=（大于等于）、（小于）、=（小于等于）、=（等于）、！=，（不等于）、！（不大于）、?。ú恍∮冢１容^運(yùn)算符的運(yùn)算結(jié)果是布爾數(shù)據(jù)類型，它有三種可能的結(jié)果：TRUE、FALSE以及UNKNOWN。 例10-7：查詢Student_course表中成績的70%仍然大于60分的學(xué)生學(xué)號和原來的成績。 USE Student GO SELECT Student_id,Student_grade FROM Student_course WHERE Student_grade*0.760 ORDER BY Student_id ASC GO,圖10.5 例10-7執(zhí)行結(jié)果,20,運(yùn)算符,4邏輯運(yùn)算符 邏輯運(yùn)算符用來測試某些條件是否成立，T-SQL中的邏輯運(yùn)算符包括： NOT（非運(yùn)算符） AND（與運(yùn)算符） OR（或運(yùn)算符） 非運(yùn)算符，用于表示對條件的否定。與運(yùn)算符用于連接查詢條件，只有AND兩邊的條件的值都為真時(shí)，其結(jié)果值才為真?；蜻\(yùn)算符用于連接查詢條件，只要OR兩邊的條件中有一個(gè)為真，其結(jié)果值就為真。 邏輯運(yùn)算符和比較運(yùn)算符一樣，運(yùn)算結(jié)果是布爾數(shù)據(jù)類型。那些返回布爾數(shù)據(jù)類型的表達(dá)式稱為布爾表達(dá)式，T-SQL中的布爾表達(dá)式有三種可能的值，分別是TRUE、FALSE以及UNKNOWN，其中UNKNOWN是由值為NULL的數(shù)據(jù)參加運(yùn)算得到的結(jié)果。表10-2、表10-3、表10-4列出了進(jìn)行各種邏輯運(yùn)算時(shí)不同情況得到的結(jié)果。,21,運(yùn)算符,表10-2 NOT運(yùn)算的各種結(jié)果,表10-3 AND運(yùn)算的各種結(jié)果,表10-4 OR運(yùn)算的各種結(jié)果,22,運(yùn)算符,例10-8：請查詢出所有家庭所在地為山西的男同學(xué)。 USE Student GO SELECT Student_id, Student_name, Student_sex, Student_home FROM Students WHERE Student_home= 山西 AND Student_sex=男 GO,圖10.6 例10-8執(zhí)行結(jié)果,23,運(yùn)算符,5位運(yùn)算符 位運(yùn)算符用來對兩個(gè)表達(dá)式進(jìn)行位操作，位運(yùn)算符的操作數(shù)允許是整型數(shù)據(jù)或者二進(jìn)制數(shù)據(jù)（但image數(shù)據(jù)類型除外）。此外，位運(yùn)算還要求兩個(gè)操作數(shù)不能同時(shí)是二進(jìn)制數(shù)據(jù)。T-SQL中的位運(yùn)算符包括: &（按位與） |（按位或） （按位異或） （按位取反） 例10-9：計(jì)算位運(yùn)算表達(dá)式的值。 SELECT 15&16 AS 按位與, 15|16 AS 按位或, 1516 AS 按位異或, 15 AS 按位取反,圖10.7 例10-9執(zhí)行結(jié)果,24,運(yùn)算符,6連接運(yùn)算符 T-SQL中的連接運(yùn)算符“+”用于連接字符串或二進(jìn)制數(shù)據(jù)串、列名或列的混合體，其實(shí)質(zhì)就是將一個(gè)串加入到另一個(gè)串的尾部。 例10-10：在輸出Classes表中班級名稱時(shí)前面加上班級所在系別，如“經(jīng)濟(jì)信息系電子商務(wù)1班”。 USE Student GO SELECT Classes.Class_id,Departments.Department_name+Classes.Class_name FROM Classes INNER JOIN Departments ON Classes. Class_department = Departments.Department_id GO,圖10.8 例10-10執(zhí)行結(jié)果,25,運(yùn)算符,7運(yùn)算符的優(yōu)先級 T-SQL中的運(yùn)算符的處理順序如表10-5所示。,表10-5 T-SQL運(yùn)算符優(yōu)先級,第三節(jié) 函數(shù),26,為了讓用戶更方便的對數(shù)據(jù)庫進(jìn)行操作，SQL Server 2005在T-SQL中提供了許多內(nèi)置函數(shù)。函數(shù)其實(shí)就是一段程序代碼，用戶可以通過調(diào)用內(nèi)置函數(shù)并為其提供所需的參數(shù)來執(zhí)行一些特殊的運(yùn)算或完成復(fù)雜的操作。T-SQL提供的函數(shù)有系統(tǒng)函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)、數(shù)學(xué)函數(shù)、轉(zhuǎn)換函數(shù)等。,(一) 系統(tǒng)函數(shù),27,系統(tǒng)函數(shù)可以返回有關(guān)當(dāng)前環(huán)境的信息，例如有關(guān)服務(wù)器、用戶、數(shù)據(jù)庫對象的系統(tǒng)信息。常用的系統(tǒng)函數(shù)及其功能如表10-6所示。,表10-6 常用系統(tǒng)函數(shù),28,系統(tǒng)函數(shù),例10-11：系統(tǒng)函數(shù)的使用。 SELECT 用戶名=USER_NAME(),登陸名= SUSER_SID(),工作站名=HOST_NAME() 該語句的運(yùn)行結(jié)果如圖10.9所示。 例10-12：使用系統(tǒng)函數(shù)查詢指定表的第一列的長度。 USE Student GO DECLARE col_name VARCHAR(30) SELECT col_name=COL_NAME(OBJECT_ID(Students),1) SELECT COL_LENGTH(Students, col_name) AS 第一列長度 GO,圖10.9例10-11執(zhí)行結(jié)果,圖10.10例10-12執(zhí)行結(jié)果,(二) 字符串函數(shù),29,字符串函數(shù)主要是為了方便用戶對二進(jìn)制數(shù)據(jù)、字符串和表達(dá)式進(jìn)行操作。常用的字符串函數(shù)及其功能如表10-7所示。,30,字符串函數(shù)例題,例10-13：在Students表中查詢所有2005級學(xué)生的學(xué)號、姓名、性別、出生日期及所在班級號。 USE Student GO SELECT Student_id,Student_name,Student_sex,Student_birthday,Student_classid FROM Students WHERE SUBSTRING(Student_classid,1,4) =2005 GO,圖10.11例10-13執(zhí)行結(jié)果,31,字符串函數(shù)例題,例10-14：在Users表中使用CHARINDEX()函數(shù)查詢所有User_name中包含了字符串“ia”的記錄的User_id。 USE Student GO SELECT User_id,User_name FROM Users WHERE CHARINDEX(ia, User_name)0 GO 程序的運(yùn)行結(jié)果如圖10.12所示。 例10-15：在Users表中使用PATINDEX()函數(shù)查詢所有User_name中包含了字符串“ia”的記錄的User_id。 USE Student GO SELECT User_id,User_name FROM Users WHERE PATINDEX (%_ia_%, User_name)0 GO 程序的運(yùn)行結(jié)果如圖10.13所示。 例10-14和例10-15可以反映PATINDEX()和CHARINDEX()的不同之處，雖然都是查找User_name中包含了字符串“ia”的記錄的User_id，但是PATINDEX()函數(shù)中包含了指定字符串的格式，所以查找的結(jié)果就不相同。,圖10.12例10-14執(zhí)行結(jié)果,圖10.13例10-15執(zhí)行結(jié)果,(三) 日期和時(shí)間函數(shù),32,日期和時(shí)間函數(shù)用于對日期和時(shí)間數(shù)據(jù)進(jìn)行運(yùn)算和操作，其返回值為字符串、數(shù)字值或日期和時(shí)間值。常用的日期和時(shí)間函數(shù)及其功能如表10-8所示。 表10-8中出現(xiàn)的參數(shù)datepart ，是指定要返回的日期部分的參數(shù)。下表列出了SQL Server 2005 可識別的日期部分及其縮寫。 表10-9中的weekday (dw) 日期部分返回星期幾（如星期日、星期一等）。,表10-8 常用日期時(shí)間函數(shù),表10-9 日期部分及縮寫,33,日期和時(shí)間函數(shù)例題,例10-16：返回系統(tǒng)時(shí)間。 SELECT GETDATE() AS 系統(tǒng)時(shí)間 該語句的運(yùn)行結(jié)果如圖10.14所示。 例10-17：使用DATEDIFF()函數(shù)查詢Students表中所有學(xué)生的年齡。 USE Student GO SELECT Student_name,Student_sex, DATEDIFF(year, Student_birthday, GETDATE() AS Student_age FROM Students GO 程序的運(yùn)行結(jié)果如圖10.15所示。,圖10.14例10-16執(zhí)行結(jié)果,圖10.15 例10-17執(zhí)行結(jié)果,(四) 數(shù)學(xué)函數(shù),34,數(shù)學(xué)函數(shù)能夠?qū)?shù)字型表達(dá)式進(jìn)行數(shù)學(xué)運(yùn)算，然后將結(jié)果或結(jié)果集返回給用戶。能夠在數(shù)學(xué)函數(shù)中使用的數(shù)據(jù)類型有decimal、integer、float、real、money、smallnoney、smallint和tinyint。常用的數(shù)學(xué)函數(shù)及其功能如表10-10所示。 例10-18：使用SIN()函數(shù)和PI()函數(shù)。 SELECT SIN(28.62) AS SIN(28.62)的值, PI() AS PI()的值 該語句的運(yùn)行結(jié)果如圖10.16所示。,表10-10 常用數(shù)學(xué)函數(shù),圖10.16 例10-18執(zhí)行結(jié)果,(五) 轉(zhuǎn)換函數(shù),35,一般情況下，將數(shù)據(jù)從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型的工作是由SQL Server自動完成的，這種轉(zhuǎn)換稱為隱性轉(zhuǎn)換或自動轉(zhuǎn)換。但也有SQL Server無法自動完成的轉(zhuǎn)換，這時(shí)可以使用轉(zhuǎn)換函數(shù)進(jìn)行顯式的轉(zhuǎn)換。顯式轉(zhuǎn)換函數(shù)及其功能如表10-11所示。 CAST()函數(shù)和 CONVERT()函數(shù)都可以將一種數(shù)據(jù)類型的數(shù)據(jù)強(qiáng)制的轉(zhuǎn)換為另一種數(shù)據(jù)類型，但是兩者也有差別。CAST()函數(shù)不是使用逗號，而是使用AS來分隔兩個(gè)參數(shù)。而CONVERT()函數(shù)的功能比CAST()函數(shù)更為細(xì)化，它可以按照參數(shù)style指定的格式將日期型數(shù)據(jù)轉(zhuǎn)換成不同的樣式，參數(shù)style可以使用的日期樣式讀者可自行查閱SQL Server 2005聯(lián)機(jī)叢書。 例10-19：查詢Students表中6月出生的學(xué)生信息。 USE Student GO SELECT Student_id,Student_name,Student_birthday FROM Students WHERE CONVERT(char(30), Student_birthday,101) LIKE 06% GO 程序的運(yùn)行結(jié)果如圖10.17所示。,表10-11 數(shù)據(jù)類型轉(zhuǎn)換函數(shù),圖10.17 例10-19執(zhí)行結(jié)果,(六) 用戶自定義函數(shù),36,在T-SQL中，除了可以直接使用前面介紹的系統(tǒng)函數(shù)之外，還允許用戶編寫自己的函數(shù)，以擴(kuò)展T-SQL的編程能力。用戶自行編寫的函數(shù)稱之為用戶自定義函數(shù)，和系統(tǒng)內(nèi)置函數(shù)一樣，用戶自定義函數(shù)中可以包含零個(gè)或多個(gè)參數(shù)，并返回相應(yīng)的數(shù)據(jù)。 1用戶自定義函數(shù)的創(chuàng)建 用戶可以使用CREATE FUNCTION語句來創(chuàng)建自定義函數(shù)，其語法格式如下： CREATE FUNCTION 函數(shù)名 （形式參數(shù)名稱 AS 數(shù)據(jù)類型，形式參數(shù)名稱 AS 數(shù)據(jù)類型，） RETURNS 返回?cái)?shù)據(jù)類型 AS BEGIN 函數(shù)內(nèi)容 RETURN 表達(dá)式 END 格式中各部分說明如下： 函數(shù)名的定義必須遵循SQL Server的標(biāo)識符命名規(guī)則。 RETURNS子句指定自定義函數(shù)的返回?cái)?shù)據(jù)類型。根據(jù)返回?cái)?shù)據(jù)類型的不同用戶自定義函數(shù)可以分為三類：標(biāo)量值用戶自定義函數(shù)、內(nèi)聯(lián)表值用戶自定義函數(shù)和多語句表值用戶自定義函數(shù)。如果 RETURNS 子句指定的是一種標(biāo)量數(shù)據(jù)類型，則函數(shù)為標(biāo)量值函數(shù)，即標(biāo)量值用戶自定義函數(shù)的返回值為單個(gè)數(shù)據(jù)值。如果 RETURNS 子句指定 TABLE，則函數(shù)為內(nèi)聯(lián)表值用戶自定義函數(shù)或多語句表值用戶自定義函數(shù)。 BEGINEND部分為函數(shù)體，標(biāo)量值用戶自定義函數(shù)和多語句表值用戶自定義函數(shù)的函數(shù)體都被封裝在以BEGIN開始，END結(jié)束的范圍內(nèi)，而直接表值用戶自定義函數(shù)沒有明確的函數(shù)體，只是一個(gè)單個(gè)的SELECT語句。,37,用戶自定義函數(shù),例10-20：編寫一個(gè)標(biāo)量值用戶自定義函數(shù)，根據(jù)輸入的半徑計(jì)算圓的面積。 CREATE FUNCTION Circle_Area (Radius AS DECIMAL(5,1) RETURNS DECIMAL(13,4) AS BEGIN RETURN(PI()*Radius*Radius) END GO 例10-21：編寫一個(gè)內(nèi)聯(lián)表值用戶自定義函數(shù)，根據(jù)輸入的課程號，查詢該課程的基本信息。 USE Student GO CREATE FUNCTION Course_Information (CourseNumber AS char(4) RETURNS TABLE AS RETURN (SELECT * FROM Courses WHERE Course_id=CourseNumber),38,用戶自定義函數(shù),2用戶自定義函數(shù)的調(diào)用 可以使用SELECT語句或PRINT語句調(diào)用用戶自定義函數(shù)。其基本語法格式為： SELECT 用戶名函數(shù)名稱（實(shí)際參數(shù)表） 或 PRINT 用戶名函數(shù)名稱（實(shí)際參數(shù)表） 如果調(diào)用的是標(biāo)量值用戶自定義函數(shù)，一定要在函數(shù)名稱的前面加上用戶名，如果是表值型用戶自定義函數(shù)，則沒有這個(gè)要求。 例10-22：調(diào)用自定義函數(shù)Circle_Area，求給定半徑的圓面積。 SELECT dbo.Circle_Area(1) AS 圓面積 程序的運(yùn)行結(jié)果如圖10.18所示。 例10-23：調(diào)用自定義函數(shù)Course_Information，輸出相應(yīng)班級的信息。 USE Student SELECT * FROM Course_Information(4001) 程序的運(yùn)行結(jié)果如圖10.19所示。,圖10.18 例10-22執(zhí)行結(jié)果,圖10.19 例10-23執(zhí)行結(jié)果,39,用戶自定義函數(shù),3用戶自定義函數(shù)的修改和刪除 用戶自定義函數(shù)的修改使用的是ALTER FUNCTION語句，ALTER FUNCTION語句的其余部分語法格式與CREATE FUNCTION語句相同，在此不做贅述。 用戶自定義函數(shù)的刪除使用DROP FUNCTION語句，其語法格式如下： DROP FUNCTION 用戶名函數(shù)名 4使用Microsoft SQL Server Management Studio創(chuàng)建用戶自定義函數(shù) 除了按照上述方法在查詢文檔中使用代碼來創(chuàng)建、修改和刪除用戶自定義函數(shù)，還可以使用Microsoft SQL Server Management Studio直接創(chuàng)建、修改和刪除用戶自定義函數(shù)，步驟如下： （1）打開Microsoft SQL Server Management Studio。 （2）打開相應(yīng)的數(shù)據(jù)庫前的“+”號，再打開“可編程性”前的“+”號。 （3）右擊【函數(shù)】，選擇【新建】命令，如圖10.20所示，即可創(chuàng)建不同類型的用戶 自定義函數(shù)。 （4）要修改或刪除已有自定義函數(shù)，只需在相應(yīng)類型的函數(shù)下找到要修改或刪除的具體函數(shù)，如圖10.21所示，使用右鍵快捷菜單選擇相應(yīng)命令即可。,40,用戶自定義函數(shù),圖10.20 創(chuàng)建用戶自定義函數(shù),圖10.21 修改、刪除用戶自定義函數(shù),第四節(jié) 流程控制語句,41,SQL Server支持結(jié)構(gòu)化的編程方法，結(jié)構(gòu)化編程中程序流程控制的三大結(jié)構(gòu)是順序結(jié)構(gòu)、選擇結(jié)構(gòu)、循環(huán)結(jié)構(gòu)。T-SQL語言提供了可以實(shí)現(xiàn)這三種結(jié)構(gòu)的流程控制語句，使用這些流程控制語句可以控制命令的執(zhí)行順序，以便更好的組織程序。SQL Server中的流程控制語句有BEGINEND、IFELSE、WHILECONTINUEBREAK、GOTO、WAITFOR、RETURN等。,(一) BEGINEND語句,42,BEGINEND語句相當(dāng)于其他語言中的復(fù)合語句，如C語言中的。它用于將多條T-SQL語句封裝為一個(gè)整體的語句塊，即將BEGINEND內(nèi)的所有T-SQL語句視為一個(gè)單元執(zhí)行。在實(shí)際應(yīng)用中，BEGINEND語句一般與IFELSE、WHILE等語句聯(lián)用，當(dāng)判斷條件符合需要執(zhí)行兩個(gè)或者多個(gè)語句時(shí)，就需要使用BEGINEND語句將這些語句封裝為一個(gè)語句塊。BEGIN.END 語句塊允許嵌套。 BEGINEND語句的基本語法格式為： BEGIN T-SQL命令行 END,(二) IFELSE語句,43,IFELSE語句是條件判斷語句，用以實(shí)現(xiàn)選擇結(jié)構(gòu)。當(dāng)IF后的條件成立時(shí)就執(zhí)行其后的T-SQL語句，條件不成立時(shí)執(zhí)行ELSE后的T-SQL語句。其中，ELSE子句是可選項(xiàng)，如果沒有ELSE子句，當(dāng)條件不成立則執(zhí)行IF語句后的其他語句。 IFELSE語句的語法格式為： IF 條件表達(dá)式 程序塊 ELSE 程序塊 格式中各部分說明如下： 條件表達(dá)式是作為執(zhí)行和判斷條件的布爾表達(dá)式，返回 TRUE 或 FALSE，如果布爾表達(dá)式中含有 SELECT 語句，必須用圓括號將 SELECT 語句括起來。 程序塊是一條T-SQL語句或者是一個(gè)BEGINEND語句塊。 IFELSE語句允許嵌套使用，可以在IF之后或在 ELSE 下面，嵌套另一個(gè) IF語句，嵌套級數(shù)的限制取決于可用內(nèi)存。,44,IFELSE語句例題,例10-24：在Student_course 表中查詢課程“電子商務(wù)基礎(chǔ)”的平均成績，并輸出相應(yīng)的提示信息。 USE Student GO /*若學(xué)生平均成績大于70，輸出“平均成績達(dá)到一般水平”，否則輸出“平均成績有待提高”*/ IF (SELECT AVG (Student_grade) FROM Student_course WHERE Course_id=1001)70 BEGIN PRINT 電子商務(wù)基礎(chǔ)的平均成績達(dá)到一般水平 SELECT AVG (Student_grade) AS 平均成績 FROM Student_course WHERE Course_id=1001 END ELSE BEGIN PRINT 電子商務(wù)基礎(chǔ)的平均成績還有待提高 SELECT AVG (Student_grade) AS 平均成績 FROM Student_course WHERE Course_id=1001 END GO,圖10.22 例10-24執(zhí)行結(jié)果,(三) CASE語句,45,CASE語句和IFELSE語句一樣，也用來實(shí)現(xiàn)選擇結(jié)構(gòu)。但是它與IFELSE語句相比，可以更方便的實(shí)現(xiàn)多重選擇的情況，從而可以避免多重的IFELSE語句的嵌套，使得程序的結(jié)構(gòu)更加簡練、清晰。T-SQL中的CASE語句可分為簡單CASE語句和搜索CASE語句兩種。 1簡單CASE語句 簡單CASE語句的語法格式為： CASE 表達(dá)式 WHEN 表達(dá)式 THEN 結(jié)果表達(dá)式 ELSE 結(jié)果表達(dá)式 END 格式中各部分說明如下： CASE后的表達(dá)式用于和WHEN后的表達(dá)式逐個(gè)進(jìn)行比較，兩者數(shù)據(jù)類型必須相同，或必須是可以進(jìn)行隱式轉(zhuǎn)換的數(shù)據(jù)類型。 表示可以有多個(gè)“WHEN 表達(dá)式THEN 結(jié)果表達(dá)式”結(jié)構(gòu)。 THEN后面給出當(dāng)CASE后的表達(dá)式值與WHEN后的表達(dá)式相等時(shí)，要返回的結(jié)果表達(dá)式。 簡單CASE語句的執(zhí)行過程為：首先計(jì)算CASE后面表達(dá)式的值，然后按指定順序?qū)γ總€(gè)WHEN子句后的表達(dá)式進(jìn)行比較，當(dāng)遇到與CASE后表達(dá)式值相等的，則執(zhí)行對應(yīng)的THEN后的結(jié)果表達(dá)式，并退出CASE結(jié)構(gòu)。若CASE后的表達(dá)式值與所有WHEN后的表達(dá)式均不相等，則返回ELSE后的結(jié)果表達(dá)式。若CASE后的表達(dá)式值與所有WHEN后的表達(dá)式均不相等，且“ELSE 結(jié)果表達(dá)式”部分被省略，則返回NULL值。,46,例10-25：查詢Students表中所有男生的基本情況，輸出學(xué)號、姓名、性別及班級名稱。 USE Student GO -使用CASE語句將學(xué)生的班級號替換為班級名稱 SELECT 學(xué)號=Student_id, 姓名=Student_name , 性別=Student_sex,班級名= CASE Student_classid WHEN 2005011 THEN 電子商務(wù)1班 WHEN 2005012 THEN 電子商務(wù)2班 WHEN 2005013 THEN 電子商務(wù)3班 WHEN 2004014 THEN 會計(jì)1班 WHEN 2004015 THEN 會計(jì)2班 END FROM Students WHERE Student_sex =男 ORDER BY Student_id GO,CASE語句例題,圖10.23 例10-25執(zhí)行結(jié)果,47,CASE語句,2搜索CASE語句 搜索CASE語句的語法格式為： CASE WHEN 條件表達(dá)式 THEN 結(jié)果表達(dá)式 ELSE 結(jié)果表達(dá)式 END 格式中各部分說明如下： CASE后無表達(dá)式。 WHEN后的條件表達(dá)式是作為執(zhí)行和判斷條件的布爾表達(dá)式。 表示可以有多個(gè)“WHEN 條件表達(dá)式 THEN 結(jié)果表達(dá)式”結(jié)構(gòu)。 搜索CASE語句的執(zhí)行過程為：首先測試WHEN后的條件表達(dá)式，若為真，則執(zhí)行THEN后的結(jié)果表達(dá)式，否則進(jìn)行下一個(gè)條件表達(dá)式的測試。若所有WHEN后的條件表達(dá)式都為假，則執(zhí)行ELSE后的結(jié)果表達(dá)式。若所有WHEN后的條件表達(dá)式都為假，且“ELSE 結(jié)果表達(dá)式”部分被省略，則返回NULL值。,48,CASE語句例題,例10-26：根據(jù)Student_course表中的學(xué)生成績，判斷學(xué)生成績是否達(dá)到優(yōu)秀（85分及以上為優(yōu)秀）。 USE Student SELECT 學(xué)號= Student_id,成績= Student_grade,優(yōu)秀否= CASE WHEN Student_grade =85 THEN 優(yōu)秀 WHEN Student_grade 85 THEN END FROM Student_course GO 程序的運(yùn)行結(jié)果如圖10.24所示。 從本例中可以看出搜索CASE語句和簡單CASE語句的主要區(qū)別在于，搜索CASE語句中WHEN后的表達(dá)式允許是各種的比較操作或多種條件的測試，而簡單CASE語句中WHEN后的表達(dá)式只能用來和CASE后的表達(dá)式值進(jìn)行相等的比較。,圖10.24 例10-26執(zhí)行結(jié)果,(四) WHILECONTINUEBREAK語句,49,WHILECONTINUEBREAK語句用以實(shí)現(xiàn)循環(huán)結(jié)構(gòu)，其功能是在滿足條件的情況下會重復(fù)執(zhí)行T-SQL語句或語句塊。當(dāng)WHILE后面的條件為真時(shí)，就重復(fù)執(zhí)行BEGINEND之間的語句塊。WHILE語句中的CONTINUE和BREAK可以是可選項(xiàng)。若有CONTINUE語句，其功能是使程序跳出本次循環(huán)，開始執(zhí)行下一次循環(huán)。而執(zhí)行到BREAK語句時(shí)，會立即終止循環(huán)，結(jié)束整個(gè)WHILE語句的執(zhí)行，并繼續(xù)執(zhí)行WHILE語句后的其他語句。 WHILECONTINUEBREAK語句的語法格式為： WHILE 條件表達(dá)式 BEGIN 程序塊 BREAK 程序塊 CONTINUE 程序塊 END 格式中各部分說明如下： 條件表達(dá)式是作為執(zhí)行和判斷條件的布爾表達(dá)式，返回 TRUE 或 FALSE，如果布爾表達(dá)式中含有 SELECT 語句，必須用圓括號將 SELECT 語句括起來。 程序塊是一條T-SQL語句或者是一個(gè)BEGINEND語句塊。,50,例10-27：在查詢到Student_course表中沒有學(xué)生成績超過95分的情況下，將所有成績提高3%，反復(fù)執(zhí)行，直到存在成績超過95分的情況下，退出循環(huán)。 USE Student GO WHILE NOT EXISTS (SELECT Student_grade FROM Student_course WHERE Student_grade 95) BEGIN UPDATE Student_course SET Student_grade= Student_grade*1.03 IF (SELECT MAX(Student_grade) FROM Student_course)95 BREAK ELSE CONTINUE END SELECT Student_id, Student_grade FROM Student_course GO 程序的運(yùn)行結(jié)果如圖10.25所示。,WHILECONTINUEBREAK語句例題,圖10.25 例10-27執(zhí)行結(jié)果,(五) GOTO語句,51,GOTO語句用來改變程序的執(zhí)行流程，使程序無條件跳轉(zhuǎn)到指定的標(biāo)簽處繼續(xù)執(zhí)行。GOTO語句可以出現(xiàn)在條件控制流語句、語句塊或過程中，但它不能跳轉(zhuǎn)到該批以外的標(biāo)簽。 GOTO語句的語法格式為： GOTO 標(biāo)簽 GOTO語句中的跳轉(zhuǎn)目標(biāo)的標(biāo)簽定義格式為： 標(biāo)簽： 定義標(biāo)簽時(shí)，需要在標(biāo)簽的名字后面加上一個(gè)冒號。 例10-28：求出1100之中所有的偶數(shù)之和。 DECLARE Sum int,i int SET i = 0 SET Sum = 0 Label_1: SET i=i+2 SET Sum =Sum +i IF i 100 GOTO Label_1 PRINT 1100之中所有的偶數(shù)之和是： +cast(Sum as varchar(50) 程序的運(yùn)行結(jié)果如圖10.26所示。,圖10.26 例10-28執(zhí)行結(jié)果,(六) WAITFOR語句,52,WAITFOR語句用于在達(dá)到指定時(shí)間或時(shí)間間隔之前，阻止執(zhí)行批處理、存儲過程或事務(wù)，直到所設(shè)定的時(shí)間已到或等待了指定的時(shí)間間隔之后才繼續(xù)往下運(yùn)行。 WAITFOR語句的語法格式為： WAITFOR DELAY 等待時(shí)間 | TIME 完成時(shí)間 格式中各部分說明如下： DELAY 等待時(shí)間是指定可以繼續(xù)執(zhí)行批處理、存儲過程或事務(wù)之前必須經(jīng)過的指定時(shí)段，最長可為24 小時(shí)。可使用 datetime 數(shù)據(jù)可接受的格式之一指定等待時(shí)間，也可以將其指定為局部變量，但不能指定日期，因此不允許指定 datetime 值的日期部分。 TIME 完成時(shí)間是指定運(yùn)行批處理、存儲過程或事務(wù)的具體時(shí)刻?？梢允褂?datetime 數(shù)據(jù)可接受的格式之一指定完成時(shí)間，也可以將其指定為局部變量，但不能指定日期，因此不允許指定 datetime 值的日期部分。,53,WAITFOR語句例題,例10-29：在晚上 10:20 (22:20) 執(zhí)行存儲過程 sp_update_job。 USE msdb EXECUTE sp_add_job job_name = TestJob BEGIN WAITFOR TIME 22:20 EXECUTE sp_update_job job_name = TestJob,new_name = UpdatedJob END GO 例10-30：先查詢Students表中男生姓名，等待5秒后再查詢男生姓名和出生日期。 USE Student GO SELECT Student_name FROM Students WHERE Student_sex =男 GO -設(shè)置等待時(shí)間為5秒鐘 WAITFOR DELAY 00:00:05 SELECT Student_name, Student_birthday FROM Students WHERE Student_sex =男 GO 程序的運(yùn)行結(jié)果如圖10.27所示。 運(yùn)行程序時(shí)先出現(xiàn)男同學(xué)姓名的查詢結(jié)果，5秒鐘后才出現(xiàn)下方的姓名和出生日期。,圖10.27 例10-30執(zhí)行結(jié)果,(七) RETURN語句,54,RETURN語句用來從批處理、查詢或存儲過程中無條件退出，RETURN的執(zhí)行是即時(shí)且完全的，可在任何時(shí)候用于從過程、批處理或語句塊中退出。位于RETURN語句之后的語句將不會被執(zhí)行。 RETURN語句的語法格式為： RETURN 整型表達(dá)式 使用RETURN語句可以返回一個(gè)整數(shù)值，一般情況下，只有存儲過程（存儲過程將在下一章中進(jìn)行介紹）才會向執(zhí)行調(diào)用的過程或應(yīng)用程序返回一個(gè)整數(shù)值，可將RETURN的返回值作為程序是否成功執(zhí)行的判斷標(biāo)志。在SQL Server中除非另有說明，所有系統(tǒng)存儲過程返回0值，即表示調(diào)用成功，而非零值則表示調(diào)用失敗。,第五節(jié) 游標(biāo),55,一.游標(biāo)簡介 數(shù)據(jù)庫中執(zhí)行的大多數(shù)SQL命令，都是用來處理行的集合的，即是針對集合內(nèi)所有數(shù)據(jù)的處理。但是在某些情況下，用戶也需要使用代碼來逐行的處理數(shù)據(jù)，為此SQL提供了可以逐行遍歷記錄行集合的游標(biāo)。 游標(biāo)的本質(zhì)是一種能從包括多數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。因此，游標(biāo)總是與一條SELECT語句相關(guān)聯(lián)，它可以定位于SELECT結(jié)果集中的某一指定行，從而實(shí)現(xiàn)對該行的操作。從這個(gè)角度上講，數(shù)據(jù)庫的游標(biāo)是一種類似于C語言中指針的語言結(jié)構(gòu)。 游標(biāo)在使用時(shí)首先根據(jù)SELECT語句創(chuàng)建結(jié)果集，然后一次從中獲取一行數(shù)據(jù)。游標(biāo)的生命周期包含以下5個(gè)階段： 聲明游標(biāo)。 打開游標(biāo)。 使用游標(biāo)，從游標(biāo)中提取信息。 關(guān)閉游標(biāo)。 釋放游標(biāo)。,使用游標(biāo),56,前面已經(jīng)提出了游標(biāo)有5種基本操作，即聲明游標(biāo)、打開游標(biāo)、使用游標(biāo)、關(guān)閉游標(biāo)和釋放游標(biāo)，下面就來分別討論一下這5種基本操作的具體實(shí)現(xiàn)。 1聲明游標(biāo) 使用游標(biāo)前首先要聲明游標(biāo)，聲明游標(biāo)時(shí)會為游標(biāo)指定獲取數(shù)據(jù)時(shí)所使用的SELECT語句。聲明游標(biāo)的語法格式如下： DECLARE 游標(biāo)名 INSENSITIVE SCROLL CURSOR FOR SELECT 語句 FOR READ ONLY | UPDATE OF 列名1，列名2，列名3 格式中各部分說明如下： 游標(biāo)名的命名必須遵守T-SQL的標(biāo)識符命名規(guī)則。 使用INSENSITIVE選項(xiàng)定義的游標(biāo)，會創(chuàng)建將由該游標(biāo)使用的數(shù)據(jù)的臨時(shí)副本。也就是說，該游標(biāo)使用的結(jié)果集將被復(fù)制到臨時(shí)數(shù)據(jù)庫tempdb中形成一個(gè)臨時(shí)表，對游標(biāo)的所有請求都從 tempdb 中的這一臨時(shí)表中得到應(yīng)答；因此，在對該游標(biāo)進(jìn)行提取操作時(shí)返回的數(shù)據(jù)中不反映對基礎(chǔ)表所做的修改，并且該游標(biāo)不允許修改。如果省略 INSENSITIVE，則已提交的對基礎(chǔ)表的刪除和更新都反映在后面的提取中。 SCROLL選項(xiàng)指定所有的提取選項(xiàng)（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未指定 SCROLL，則 NEXT 是唯一支持的提取選項(xiàng)。,57,使用游標(biāo),各提取選項(xiàng)含義（具體含義見FETCH語句說明部分）： FIRST：提取第一行數(shù)據(jù)； LAST：提取最后一行數(shù)據(jù)； PRIOR：提取前一行數(shù)據(jù)； NEXT：提取后一行數(shù)據(jù)； RELATIVE：按相對位置提取數(shù)據(jù)； ABSOLUTE：按絕對位置提取數(shù)據(jù)。 SELECT語句是指定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn)SELECT 查詢語句。在聲明游標(biāo)的SELECT語句內(nèi)不允許使用關(guān)鍵字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO，該SELECT 查詢語句所查詢的表稱為游標(biāo)的基礎(chǔ)表。 READ ONLY選項(xiàng)表示禁止通過該游標(biāo)進(jìn)行更新，即所聲明的游標(biāo)為只讀。在 UPDATE 或 DELETE 語句的 WHERE CURRENT OF 子句中不能引用游標(biāo)。 UPDATE OF 列名1，列名2，列名3用來定義在所聲明的游標(biāo)中可以更新的列。如果指定了OF 列名1，列名2，列名3，則只允許修改列出的列。如果指定了 UPDATE，但未指定列的列表，則可以更新所有列。,58,使用游標(biāo),2打開游標(biāo) 在使用游標(biāo)讀取數(shù)據(jù)之前，必須要打