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

1、第6章 T-SQL程序設(shè)計(jì),工商管理學(xué)院電子商務(wù)教研室 滕 巖(rock_) Autumn 2010,批處理、腳本 局部變量、全局變量 函數(shù) 流程控制語(yǔ)句,本章綱要,存儲(chǔ)過(guò)程的基本概念 管理存儲(chǔ)過(guò)程,6.1.1批處理、腳本,當(dāng)要完成的任務(wù)不能由單獨(dú)的SQL語(yǔ)句來(lái)完成時(shí)，SQL Server使用批處理、腳本、存儲(chǔ)過(guò)程等來(lái)組織多條SQL語(yǔ)句,批處理就是一個(gè)或多個(gè)T-SQL語(yǔ)句的集合,使用GO語(yǔ)句作為批處理結(jié)束的標(biāo)志,腳本包含一系列按順序執(zhí)行的批處理，可存儲(chǔ)為腳本文件,如果在一個(gè)批處理中包含任何語(yǔ)法錯(cuò)誤，則整個(gè)批處理就不能被編譯和執(zhí)行；如果在一個(gè)批處理中某句有執(zhí)行錯(cuò)誤，它僅影響該句的執(zhí)行，并不影響其

2、他語(yǔ)句執(zhí)行,學(xué)生-課程數(shù)據(jù)庫(kù)中,print 學(xué)生信息如下： select * from student print 學(xué)生表記錄個(gè)數(shù)為： select count(*) from student GO,學(xué)生表：Student (Sno, Sname, Ssex, Sage, Sdept) 課程表：Course (Cno, Cname, Cpno, Ccredit) 學(xué)生選課表：SC (Sno, Cno, Grade,USE studentcourse GO,6.1.1批處理、腳本,.CSV 結(jié)果集合,.sql 腳本文件,.rpt 查詢(xún)報(bào)告,局部變量是一個(gè)能夠保存特定數(shù)據(jù)類(lèi)型實(shí)例的對(duì)象，是程序中各

3、類(lèi)型數(shù)據(jù)的最佳臨時(shí)存儲(chǔ)處，用于在Transact-SQL語(yǔ)句之間傳遞數(shù)據(jù),變量的數(shù)據(jù)類(lèi)型可以是系統(tǒng)數(shù)據(jù)類(lèi)型，也可以是用戶(hù)自定義的數(shù)據(jù)類(lèi)型，但不能把局部變量定義為text，ntext或image數(shù)據(jù)類(lèi)型,局部變量是用戶(hù)自定義的，它的名稱(chēng)以開(kāi)頭,6.1.2局部變量、全局變量,declare 局部變量名 數(shù)據(jù)類(lèi)型 ,n,declare name char(10) declare phone char(20), birth datetime,例6.4：聲明兩個(gè)局部字符型變量，并對(duì)它們進(jìn)行賦值，然后將變量的值顯示出來(lái)。（今天的日期為：系統(tǒng)日期,例：聲明一個(gè)局部整型變量，存儲(chǔ)學(xué)生表記錄個(gè)數(shù),6.1.2局部

4、變量、全局變量,set 局部變量名=表達(dá)式 ,n select 局部變量名=表達(dá)式 ,n,declare date char(20), str varchar(30,set date = getdate(,set str = 今天的日期為,print str + date,select str + date,declare geshu int,select geshu = count(*) from student,如果select語(yǔ)句返回了多個(gè)值，則局部變量將取得該select語(yǔ)句所返回的最后一個(gè)值； Select中若省略“=表達(dá)式”，則會(huì)將局部變量的值輸出，類(lèi)似于print語(yǔ)句的功能,局部

5、變量的作用域是指可以引用該變量的范圍,其作用域從聲明它們的地方開(kāi)始，到聲明它們的批處理或存儲(chǔ)過(guò)程的結(jié)尾,聲明局部變量的批處理或存儲(chǔ)過(guò)程結(jié)束時(shí)，局部變量會(huì)自動(dòng)清除,6.1.2局部變量、全局變量,全局變量是SQL Server系統(tǒng)提供并賦值的變量,用戶(hù)不能建立全局變量，也不能用set語(yǔ)句來(lái)修改全局變量,全局變量通常用來(lái)保存一些系統(tǒng)信息，如SQL Server的版本信息、SQL Server服務(wù)的相關(guān)信息等,6.1.2局部變量、全局變量,全局變量名,print VERSION -顯示版本信息 print SERVERNAME -顯示服務(wù)器名稱(chēng),使用方法,在要查看的名稱(chēng)上雙擊,6.1.3函數(shù),和存儲(chǔ)過(guò)

6、程功能相似，重點(diǎn)掌握存儲(chǔ)過(guò)程,每個(gè)函數(shù)都封裝了一定的功能,每個(gè)函數(shù)都有一個(gè)名稱(chēng)，名稱(chēng)之后是一對(duì)小括號(hào)，大部分函數(shù)在小括號(hào)中需要一個(gè)或多個(gè)參數(shù),系統(tǒng)函數(shù),用戶(hù)定義函數(shù),SQL Server提供了數(shù)百個(gè)內(nèi)置（系統(tǒng)）函數(shù),可分為字符串函數(shù)、數(shù)學(xué)函數(shù)、日期和函數(shù)函數(shù)、數(shù)據(jù)類(lèi)型轉(zhuǎn)換函數(shù)等,使用Create Function語(yǔ)句創(chuàng)建,6.1.3函數(shù),使用幫助自學(xué)系統(tǒng)函數(shù),日期和時(shí)間函數(shù),Beginend語(yǔ)句塊,6.1.4流程控制語(yǔ)句,ifelse語(yǔ)句塊,case表達(dá)式,while語(yǔ)句,數(shù)據(jù)類(lèi)型轉(zhuǎn)換函數(shù),USE marketGO,If (select 庫(kù)存量 from goods where 貨品名稱(chēng)=彩電

7、)30 begin declare number1 int set number1=(select 庫(kù)存量 from goods where 貨品名稱(chēng)=彩電) print 彩電的庫(kù)存量為：+ cast(number1 as varchar(10) print 該商品的庫(kù)存量已不足，提醒進(jìn)貨！ end GO,6.1.4流程控制語(yǔ)句,goods(貨品名稱(chēng)，庫(kù)存量，供應(yīng)商，狀態(tài)，價(jià)格,應(yīng)用情況：當(dāng)要執(zhí)行兩個(gè)或兩個(gè)以上T-SQL語(yǔ)句時(shí)；常用于if 或 else子句；while循環(huán),當(dāng)彩電的庫(kù)存量低于30時(shí)，發(fā)出提醒信息,If exists(select * from goods where 狀態(tài)=0)

8、 begin select 貨品名稱(chēng),庫(kù)存量,狀態(tài) from goods where 狀態(tài)=0 print 商場(chǎng)正在對(duì)以上產(chǎn)品進(jìn)行調(diào)整，尚不能出售！ end else begin select 貨品名稱(chēng),庫(kù)存量,狀態(tài) from goods where 狀態(tài)=1 print 商場(chǎng)產(chǎn)品可以出售！ end,6.1.4流程控制語(yǔ)句,goods(貨品名稱(chēng)，庫(kù)存量，供應(yīng)商，狀態(tài)，價(jià)格,select 供應(yīng)商, 貨品= case 貨品名稱(chēng) when book then 圖書(shū) when pen then 鉛筆 when desk then 課桌 else 貨品名稱(chēng) end from goods,6.1.4流程控

9、制語(yǔ)句,goods(貨品名稱(chēng)，庫(kù)存量，供應(yīng)商，狀態(tài)，價(jià)格,select 貨品名稱(chēng),庫(kù)存量,供應(yīng)商,狀態(tài)= case 狀態(tài) when 0 then 正在調(diào)整，不能出售 when 1 then 正常銷(xiāo)售 end from goods,select 貨品名稱(chēng),供應(yīng)商,價(jià)格評(píng)價(jià)= case when (價(jià)格=5000) then 高檔商品 when (價(jià)格=1500) then 中高檔商品 when (價(jià)格=900) then 中檔商品 when (價(jià)格=100) then 低檔商品 else 小商品 end from goods,6.1.4流程控制語(yǔ)句,goods(貨品名稱(chēng)，庫(kù)存量，供應(yīng)商，狀態(tài)，價(jià)

10、格,declare i int, e int set i=1 set e=0 while 1=1 begin if i%7=0 begin set e=e+1 set i=i+1 continue end,6.1.4流程控制語(yǔ)句,if i=6000 break else set i=i+1 end print 1-6000之間能夠被7整除的整數(shù)共有 +convert(varchar,e)+ 個(gè),求出16000之間所有能被7整除的整數(shù)個(gè)數(shù),批處理、腳本 局部變量、全局變量 函數(shù) 流程控制語(yǔ)句,本章綱要,存儲(chǔ)過(guò)程的基本概念 管理存儲(chǔ)過(guò)程,6.2.1存儲(chǔ)過(guò)程的基本概念,存儲(chǔ)過(guò)程是SQL Server

11、應(yīng)用最靈活的技術(shù),存儲(chǔ)過(guò)程是已經(jīng)存儲(chǔ)在SQL Server服務(wù)器中的一組預(yù)編譯過(guò)的T-SQL語(yǔ)句 ，存儲(chǔ)過(guò)程可以接受參數(shù)，用戶(hù)通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它,例：EXEC sp_helpdb StudentCourse,參數(shù),用戶(hù)定義存儲(chǔ)過(guò)程,系統(tǒng)存儲(chǔ)過(guò)程,主要存儲(chǔ)在master 數(shù)據(jù)庫(kù)中并以sp_為前綴,在任何數(shù)據(jù)庫(kù)中都可以調(diào)用，在調(diào)用時(shí)不必在存儲(chǔ)過(guò)程前加上數(shù)據(jù)庫(kù)名,由用戶(hù)創(chuàng)建的，是用來(lái)完成某項(xiàng)任務(wù)的T-SQL語(yǔ)句的組合,存儲(chǔ)在創(chuàng)建時(shí)指定的數(shù)據(jù)庫(kù)中，只能在此數(shù)據(jù)庫(kù)中調(diào)用,6.2.1存儲(chǔ)過(guò)程的基本概念,模塊化的程序設(shè)計(jì)； 執(zhí)行速度快； 減少網(wǎng)絡(luò)通信量； 保證

12、系統(tǒng)的安全性,6.2.1存儲(chǔ)過(guò)程的優(yōu)點(diǎn),6.2.2管理存儲(chǔ)過(guò)程,刪掉，改成自己定義的存儲(chǔ)過(guò)程名,as后添加為實(shí)現(xiàn)功能編寫(xiě)的SQL語(yǔ)句,若創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程：create procedure grade_cno as select * from sc where cno = c1,exec grade_cno （只能查出定義好的c1課程的學(xué)生成績(jī),Create Procedure 存儲(chǔ)過(guò)程名 參數(shù)名 參數(shù)的數(shù)據(jù)類(lèi)型,.nAS 任意數(shù)量的Transct-SQL 語(yǔ)句,6.2.2管理存儲(chǔ)過(guò)程,執(zhí)行存儲(chǔ)過(guò)程：EXECUTE/EXEC 存儲(chǔ)過(guò)程名 參數(shù)1, 參數(shù)2,例：創(chuàng)建帶有參數(shù)的存儲(chǔ)過(guò)程grad

13、e_cno，查詢(xún)指定課程的學(xué)生成績(jī),create procedure grade_cno kch varchar(5) as select * from sc where cno = kch,exec grade_cno c2,不使用輸出參數(shù)，查詢(xún)結(jié)果以網(wǎng)格的形式顯示出來(lái)； 使用輸出參數(shù)，可以把查詢(xún)結(jié)果和字符串組合，形成方便用戶(hù)閱讀的結(jié)果,例：創(chuàng)建帶有參數(shù)的存儲(chǔ)過(guò)程grade_sc，查詢(xún)指定學(xué)生在指定課程上的成績(jī),6.2.2管理存儲(chǔ)過(guò)程,create procedure grade_sc xh varchar(10),kch varchar(5), cj float output as sel

14、ect cj = grade from sc where sno = xh and cno = kch,exec grade_sc 14001, c2,declare g float exec grade_sc 14001, c2, g print g,output,練習(xí)：1. 創(chuàng)建并執(zhí)行帶輸入和輸出參數(shù)的存儲(chǔ)過(guò)程p_cj，查詢(xún)指定學(xué)號(hào)(作為輸入?yún)?shù))學(xué)生所選課程的平均成績(jī)和最高成績(jī)(兩個(gè)作為輸出參數(shù))；2. 寫(xiě)出調(diào)用該存儲(chǔ)過(guò)程的例子，并顯示“xx號(hào)學(xué)生選修課的平均成績(jī)?yōu)閤x，最高成績(jī)?yōu)閤x,6.2.2管理存儲(chǔ)過(guò)程,1.Create procedure p_cj xh varchar(10),pjcj float output, zgcj float output as select pjcj=avg(grade), zgcj=max(grade) from sc where sno=xh,2. declare avg float , max float declare a varchar(10) set a=14003 exec p_cj a, avg output, max output print a+號(hào)學(xué)生選修課的平均成績(jī)?yōu)?+ ca