ransactSQL程序設(shè)計.ppt

1、1,第四章 Transact SQL 程序設(shè)計,2,本章內(nèi)容： 1、如何在T-SQL里面使用變量 2、如何使用語句控制程序的執(zhí)行順序 3、如何在T-SQL里面使用函數(shù) 4、如何使用批處理語句 5、如何使用游標(biāo)處理數(shù)據(jù),3,在進行數(shù)據(jù)庫應(yīng)用軟件開發(fā)的過程中,sql server 和前端開發(fā)工具的通訊都是通過T-SQL代碼實現(xiàn)的，因此從事數(shù)據(jù)庫管理系統(tǒng)的開發(fā)都要了解T-SQL 程序設(shè)計,4,作用:在程序設(shè)計中變量是用來傳遞數(shù)據(jù) 的方式之一,它由系統(tǒng)或用戶自定義并賦值 變量分為: 全局變量 局部變量,4.1 變 量,5,A: 全局變量,作用： SQL使用全局變量來記錄SQL SERVER 服務(wù)器的活

2、動狀態(tài)。是由系統(tǒng) 預(yù)先定義好的變量，以開頭，用戶 無法對它進行修改或管理，對于用戶來 講是只讀的。 全局變量的用途,6,舉例說明,1、ERROR 作用:該全局變量將返回最后執(zhí)行的T-SQL 語句的錯誤代碼,返回值的數(shù)據(jù)類型 整數(shù).輸入SQL語句執(zhí)行成功則返回0, 如果不成功,則返回相關(guān)錯誤信息對 應(yīng)的錯誤號,而錯誤信息對應(yīng)的代碼存放在 master.dbo.SYSMESSAGE系統(tǒng)表中,7,eg:使用ERROR變量檢驗SQL語句是否執(zhí)行成功,select sno from students where sname=周杰倫 if error = 0 print 你的SQL 語句執(zhí)行成功,8,Eg

3、:使用ERROR全局變量檢測在SQL語句中出現(xiàn)的限制沖突,假設(shè)在數(shù)據(jù)庫的STUDENTS表中為BDAY字段建立了 CHECK約束(bday1980-1-1and bday2000-1-1) update students set bday=1976-8-7 where sname=周迅 if error=547 print 你更改的出生日期是不允許的,9,2、rowcount使用,作用：該變量將返回受上面SQL語句影響的記錄條數(shù)，返回的數(shù)據(jù)類型是整數(shù)型數(shù)據(jù)，如果不返回記錄的SQL語句將返回0,10,eg: 在update 語句中使rowcout檢測是否存在發(fā)生更改的記錄數(shù),更新teachers

4、表，給pay大于2000的教師工 資增加500 Update teachers set pay =pay+500 where pay2000 Print 共有 + RTRIM(rowcount)+人增長工資,11,B：局部變量,在Transact-SQL批處理中用來保存數(shù)據(jù)值的對象。 作用范圍： 從申明該局部變量的地方開始，到申明局部變量的批處理或存儲過程的地方結(jié)尾。,12,B：局部變量, 如何聲明局部變量 使用DECLARE語句來聲明T-SQL變量 格式：DECLEAR 變量名稱 數(shù)據(jù)類型,可以是系統(tǒng)提供的數(shù)據(jù)類型，也可以是用戶自己定義的數(shù)據(jù)類型,Eg:DECLARE V1 char(20)

5、,v2 int,13, 如何為變量賦值,可以使用SET和SELECT給變量賦值,eg: set v1 =常量/變量表達(dá)式,eg: select v2 =MAX(mgrade) from students print v2,14,補充： 運算符,SQL SERVER支持的運算符有算術(shù)運算 符、比較運算符、字符串連接運算符、邏 輯運算符和位運算符,15,字符串連接運算符,字符串連接用+號來連接 EG:ABC+123=ABC123,16,運算符優(yōu)先權(quán),括號（） 求反（） 乘除（ 、 ） 加減（ +、 ） 按位異或（） 按位與（&） 按位或（|） 邏輯非（NOT） 邏輯與（AND） 邏輯或（OR）,1

6、7,4.3 程序流程控制,作用：程序流程控制語句主要用于控制 T-SQL語句、語句塊和存儲過程的 執(zhí)行過程。,18,4.3.1 BEGINEND語句塊,作用：將多條T-SQL語句組合在一起， 組成一個邏輯塊，當(dāng)控制流語句必須 執(zhí)行一個包含一條或兩條以上的T-SQL 語句的語句塊時使用。 注意：相當(dāng)與很多語言中的 ,19,4.3.2 IF ELSE語句,格式 IF (布爾表達(dá)式) SQL語句塊 ELSE SQL 語句塊,20,eg:如果學(xué)生表中有入學(xué)總分大于400分的學(xué)生，請輸出信息，否則提示用戶沒有,IF exists(select sno from students where mgrade

7、400) BEGIN PRINT下列學(xué)生成績400 select * from students where mgrade400 END ELSE BEGIN PRINT沒有總分大于400的學(xué)生 END,21,4.3.3.casewhen,CASE表達(dá)式是一個特殊的T-SQL表達(dá)式，它使用戶能夠方便的實現(xiàn)多重選擇的情況。使用CASE表達(dá)式，實際上相當(dāng)于使用多重IF.ELSE語句的嵌套,22,CASE表達(dá)式語法,CASE 字段名或變量名 when 邏輯表示式1 then 結(jié)果表達(dá)式1 when 邏輯表示式2 then 結(jié)果表達(dá)式2 when 邏輯表示式3 then 結(jié)果表達(dá)式3 . ELSE 結(jié)

8、果表示式 END,23,Eg: 分別輸出teachers表中教師姓名和所在的部門，并在部門后面添加部門說明,SELECT tname, dept, 部門說明= CASE dept when基礎(chǔ)部 then 教基礎(chǔ)課程的部門 when經(jīng)管系 then培養(yǎng)經(jīng)濟管理和行政管理的人才 when計算機系then培養(yǎng)計算機高級技術(shù)人才 when國際交流系then培養(yǎng)英語實用人才 else 其他 end from teachers,24,4.3.4、無條件轉(zhuǎn)移（GOTO）,格式： GOTO 標(biāo)號處 作用：本語句將T-SQL語句的執(zhí)行順序無 條件的轉(zhuǎn)移到用戶指定的標(biāo)號處,如何定義標(biāo)號出： 標(biāo)號名稱：,25,E

9、g: 使用GOTO語句求10的階乘,DECLARE I int,time int select I=1,time=2 label: select I=I*time select time=time+1 if time=10 GOTO Label else select i,time,26,GOTO語句的使用注意情況,在書寫程序的過程中，應(yīng)盡量避免使用GOTO語句，因為過多的GOTO語句可能造成T-SQL處理的邏輯混亂，而難于理解,27,4.3.5 WHILE語句,格式：,WHILE 布爾表達(dá)式 BEGIN 命令程序塊 END,作用：當(dāng)WHILE后面的布爾表達(dá)式為真， 會重復(fù)執(zhí)行命令程序塊，直到

10、條件不成立 時為止,28,通常與WHILE語句同時使用的T-SQL語句有兩條：BREAK和CONTINUE。,BREAK：執(zhí)行該語句時，程序無條件的退 出整個WHILE循環(huán)結(jié)構(gòu),CONTINUE：執(zhí)行該語句時，程序無條件 的跳過CONTINUE后面的語句，而立即執(zhí) 行下一層循環(huán),29,EG：,判斷teachers表中是否有老師的獎金少于300，如果有，則將所有老師的工資增加500，直到所有老師的獎金都多于300（教師工資的30%是獎金）,30,WHILE EXISTS(SELECT * FROM teachers where pay*0.3300) BEGIN UPDATE teachers

11、set pay=pay+500 END,31,4.3.6 RETURN語句,作用：無條件終止查詢、存儲過程或批處 理。RETURN后面的語句將不再執(zhí) 行。 主要用于存儲過程中,32,4.3.7 Wait for 語句,作用：掛起執(zhí)行連接，直到超過指定的時間間隔或者達(dá)到一天中指定的時間。 語法格式： 指定時間間隔：Wait for delay 時間間隔 固定時間點： Wait for time 時間值,33,EG,在執(zhí)行SELECT語句之前等待兩秒 Waitfor delay 00：00：02 Select sno,sname From students Where class=03物流2 下例

12、在晚上 10:20 執(zhí)行指定存儲過程 WAITFOR TIME 22:20 EXECUTE update_all_stats,34,4.2 SQL Server函數(shù),1、系統(tǒng)函數(shù) 可以使用戶在不訪問系統(tǒng)表的情況下，獲 取SQL Server系統(tǒng)表中的信息。 Col_length 返回列長度,35,EG:返回students表中sname字段的長度,Select col_length(students,sname) as 字段長度 From students,36,2、聚合函數(shù) 作用：用于計算SELECT語句查詢行的統(tǒng)計 值，通常與GROUP BY語句一起使用，對 表分組統(tǒng)計。,37,聚合函數(shù),

13、38,39,字符串函數(shù) 日期與時間函數(shù) 數(shù)學(xué)函數(shù),40,字符串函數(shù)舉例,1、SUBSTRING（expression,start,length） 從expression的第start個字符處返回 length個字符。 Print SUBSTRING(DIELDOSPOE,5,3) 結(jié)果：DOS,41,2、RTRIM RTRIM（expression） 截斷字符串中所有尾隨空格。 返回數(shù)據(jù)類型為varchar,42,3、STR(float_expr,length,decimal) 作用：將一個數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符串， length為字符串的長度，decimal小數(shù)點后 的位數(shù) (char) Eg

14、:STR(15.2222,10,6) 則結(jié)果是：15.222200,43,日期和時間函數(shù)舉例,1、GETDATE（） 作用：取的當(dāng)前的日期 2、DATEPART(返回的日期部分，日期) 作用：返回日期的指定日期部分的整數(shù)。 返回類型：int,44,3、DATEDIFF(datepart1,date1,date2) 作用：返回兩個日期之間的時間間隔 DATEDIFF(year,1998-4-18 ,2004-4-9) 最終結(jié)果： 6,45,PRINT DATEADD(MONTH,-2,2006-6-6),4、DATEADD(datepart,number,date) 作用：給指定的日期的某一部分

15、增加值 datepart:指定部分 number:增加的整數(shù)值 date: 指定的日期,46,數(shù)學(xué)函數(shù)舉例,ABS(數(shù)字表達(dá)式) 返回給定數(shù)字表達(dá)式的絕對值 返回數(shù)據(jù)類型和給定數(shù)字表達(dá)式的數(shù)據(jù)類 型相同。 Eg: ABS(-235) 結(jié)果：235,47,用戶自己定義的函數(shù),用戶可以在SQL SERVER中創(chuàng)建自己的函數(shù)，來補充和擴展系統(tǒng)支持的內(nèi)置函數(shù)。用戶自定義函數(shù)的輸入?yún)?shù)可以有多個，也可以沒有。,48,1、用戶函數(shù)的定義,可以利用CREATE FUNCTION語句創(chuàng)建用戶自己定義的函數(shù)，創(chuàng)建語句指定輸入?yún)?shù)的數(shù)據(jù)類型，處理的過程，以及函數(shù)最后的返回值,49,CREATE FUNCTION

16、函數(shù)名 （參數(shù)名稱，數(shù)據(jù)類型） RETURNS 返回數(shù)據(jù)類型 BEGIN 函數(shù)內(nèi)容 RETRUN 表示式 END,語法格式：,50,EG:定義個用來計算員工獎金的函數(shù)，返回員工的獎金(工資的0.3就是獎金),CREATE FUNCTION bonus (salary smallmoney) RETURNS smallmoney BEGIN RETURN(salary *0.3) END,51,2、函數(shù)的調(diào)用,調(diào)用剛剛定義的獎金的函數(shù)求所有教師的獎金 select tname,dbo.bonus(pay) from teachers 注意：在調(diào)用函數(shù)的過程中要指明函數(shù)的擁有者和函數(shù)的名稱和參數(shù),

17、52,用戶定義函數(shù)類型,SQL Server 2000 支持3種用戶定義函數(shù)： 標(biāo)量函數(shù)(Scalar functions)、 內(nèi)嵌表值函數(shù)(Inline table-valued functions ) 多語句表值函數(shù)(Multistatement table-valued functions )。,53,標(biāo)量函數(shù)返回在RETURNS子句中定義的類型的單個數(shù)據(jù)值?？梢允褂盟袠?biāo)量數(shù)據(jù)類型，包括bigint和sql_variant。不支持timestamp數(shù)據(jù)類型、用戶定義數(shù)據(jù)類型和非標(biāo)量類型。 表值函數(shù)返回table。對于內(nèi)嵌表值函數(shù)，沒有函數(shù)主體；表是單個SELECT語句的結(jié)果集。對于多語

18、句表值函數(shù)，在BEGIN.END塊中定義的函數(shù)主體包含TRANSACT-SQL語句，這些語句可生成行并將行插入返回的表中。,54,3、刪除函數(shù),DROP FUNCTION 函數(shù)名稱 DROP FUNCTION bonus,55,4.5 游標(biāo),作用：通常情況下，關(guān)系數(shù)據(jù)庫中的操作總是對 整個記錄集產(chǎn)生影響，例如使用SELECT語句檢 索數(shù)據(jù)表時，將得到所有滿足該語句where子句 中條件的記錄，而在實際應(yīng)用過程中，經(jīng)常需要 每次處理一條或者一部分記錄。在這種情況下， 需要使用游標(biāo)在服務(wù)器內(nèi)部處理結(jié)果集合，他可 以有助于識別一個數(shù)據(jù)集合內(nèi)部指定的記錄，從 而可以有選擇的按記錄執(zhí)行操作,56,一：聲

19、明游標(biāo),DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL READ_ONLY FOR select_statement FOR UPDATE OF column_name ,.n ,57,例子：聲明一個游標(biāo),選取所有籍貫是“寧波”的學(xué)生作為結(jié)果集，為該結(jié)果集聲明只讀游標(biāo) declare pm_cursor cursor scroll read_only for select * from students where bplace=寧波,58,二、打開和使用游標(biāo),打開游標(biāo)： Open 游標(biāo),59,例：使用剛才申明的游

20、標(biāo)讀取結(jié)果集中的所有記錄。,open pm_cursor fetch next from pm_cursor while fetch_status = 0 begin fetch next from pm_cursor end,60,三、關(guān)閉和釋放游標(biāo),在某個游標(biāo)打開以后，SQL SERVER服務(wù)器 會自動特地為這個游標(biāo)開辟一定的內(nèi)存空間， 這些內(nèi)存空間將用來存放游標(biāo)操作的數(shù)據(jù)結(jié)果 集。 因此，如果不再使用某個游標(biāo)，一定要將 該游標(biāo)關(guān)閉，這樣服務(wù)器就會釋放該游標(biāo)所占 用的相關(guān)資源。,61,關(guān)閉游標(biāo)的語法格式： Close 游標(biāo)名稱 如果關(guān)閉了一個游標(biāo)，可以在需要時再次 打開和使用它，而不必重新

21、申明。,62,即使關(guān)閉游標(biāo)結(jié)構(gòu)，還是會占用一定的計算機資源，所以如果一個游標(biāo)不再使用，應(yīng)及時將游標(biāo)釋放，收回被占用的計算機資源。 釋放游標(biāo)的語法格式： Deallocate 游標(biāo)名稱 注意：一旦釋放游標(biāo)，如果要再次使用， 必須重新申明。,63,使用批處理,批處理是指包含一條或者多條Transact- SQL語句的語句組，這樣的語句組從應(yīng)用 程序一次性的發(fā)送到SQL Server服務(wù)器執(zhí) 行。 批處理命令是以GO為結(jié)束標(biāo)志。,64,作業(yè)與上機(2-1)：,書寫語句將teachers表中計算機系教師的工資增長300，并使用全局變量檢查更新過程中是否出錯（所用到的表結(jié)構(gòu)如下：teachers(tno

22、,dept,pay)其中TNO代表教師編號，DEPT代表教師系別，PAY代表教師的工資）,65,Update teachers Set pay=pay+300 Where dept=計算機系 If error=0 Print 更新過程沒有出錯 Else Print 更新過程出錯,66,查看STUDENTS信息表中是否有學(xué)號為0289339名字為李菊的學(xué)生，如果有則顯示已經(jīng)存在該學(xué)生的信息，否則插入該學(xué)生的信息（涉及到的表的結(jié)構(gòu)為students(sno,sname)） 分別輸出sno,cno,grade,在成績后面添加一列，將成績從百分制轉(zhuǎn)化為五級記分制,67,If exists (select * from students where sname=李菊) Begin print 存在此人信息： select * from students where sname=李菊 End Else Begin Print 添加此人信息： insert into students(sno,sname) valuse(0289