SQL_Server數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用(第五講)——編程基礎(chǔ)和存.ppt

1、第5講 編程基礎(chǔ)和存儲(chǔ)過(guò)程,常量和變量,常量： 字符串常量 數(shù)值常量 日期常量 uniqueidentifier常量 注：引用數(shù)值常量不用單引號(hào)，引用日期、字符串常 量時(shí)需要加單引號(hào),常量,字符串常量 字符串常量包含在單引號(hào)內(nèi)，由字母數(shù)字字符（az、AZ和09）以及特殊字符（如！、和#）組成。 如果字符串常量中包含一個(gè)單引號(hào)，如： Im a student,常量,數(shù)值常量 二進(jìn)制常量：具有前綴0X，并且是十六進(jìn)制數(shù)字字符串。例如0X12EF、0XFF。 bit常量：使用0或1表示。 integer常量：整數(shù)常量。 decimal常量：可以包含小數(shù)點(diǎn)的數(shù)值常量。 float常量和real常量：使

2、用科學(xué)記數(shù)法表示，例如101.5E6。 money常量：貨幣常量，以$作為前綴，可以包含小數(shù)點(diǎn)。如$12.53,變量,變量：在程序運(yùn)行過(guò)程中其值是變化的量 局部變量（以開(kāi)頭；使用DECLARE語(yǔ)句聲明） 【問(wèn)題】 DECLARE var1 int,var2 money SELECT var1=50,var2=$29.95 SELECT var1,var2 全局變量（也稱(chēng)配置函數(shù)，是系統(tǒng)定義的變量，以開(kāi)頭；用戶(hù)只能使用） 例如：version rowcount,練習(xí)2,問(wèn)題： 編寫(xiě)程序計(jì)算兩個(gè)整數(shù)之和 declare i tinyint, j tinyint, sum tinyint set i

3、=50 set j=60 select sum=i+j print sum go,例題,打印Course表中有多少種類(lèi)型的課程，要求聲明局部變量， 進(jìn)行賦值，然后打印變量?jī)?nèi)容。 use xk go declare user_msg varchar(50) select user_msg=在Course表中有+convert(varchar(3),(select count(distinct kind) from Course)+種類(lèi)型的課程。 print user_msg go,練習(xí),打印“計(jì)算機(jī)應(yīng)用工程系”下共有多少個(gè)班級(jí)，要求聲明局部變量，進(jìn)行賦值，然后打印變量?jī)?nèi)容。,運(yùn)算符,算術(shù)運(yùn)算符（

4、+、*、/、%） 一元運(yùn)算符（+、 、） 比較運(yùn)算符（=、=、!=、!） 邏輯運(yùn)算符（ALL、 AND 、ANY、 BETWEEN 、 EXISTS 、 IN、LIKE、NOT、OR、SOME） 字符串連接運(yùn)算符（） 賦值運(yùn)算符（=） 位運(yùn)算符（&、|、）,批處理,大多數(shù)Create命令要在單個(gè)批命令中執(zhí)行，但Create database、Create table、Create index例外。,流控語(yǔ)句,【問(wèn)題】如何用SQL語(yǔ)句編程實(shí)現(xiàn)：求1+2+3+4+100=? 分析：WHILE、BEGINEND、IFELSE、GOTO的使用用法 其他流控語(yǔ)句的使用用法 BRAEK：在循環(huán)語(yǔ)句中，用于

5、退出本層循環(huán) CONTINUE：在循環(huán)語(yǔ)句中，結(jié)束本次循環(huán)，重新轉(zhuǎn)到下一次循環(huán)條件的判斷,流控語(yǔ)句,If 語(yǔ)句 if 邏輯表達(dá)式 sql_statement | statement_block else sql_statement | statement_block Beginend 用來(lái)定義語(yǔ)句塊，必須成對(duì)出現(xiàn)。 While、break和continue While 邏輯表達(dá)式 語(yǔ)句塊 功能：當(dāng)邏輯表達(dá)式為真時(shí)，執(zhí)行循環(huán)體，直到邏輯表達(dá)式為假。,流控語(yǔ)句,break語(yǔ)句退出while循環(huán)，coutinue語(yǔ)句跳過(guò)語(yǔ)句塊的所有其他語(yǔ)句，開(kāi)始下一次循環(huán)。 while 邏輯表達(dá)式1 begin 語(yǔ)

6、句1 if 邏輯表達(dá)式2 continue 語(yǔ)句2 end,流控語(yǔ)句,declare declare語(yǔ)句用來(lái)定義局部變量，定義后的變量值為null。局部變量必須以開(kāi)始，后跟一個(gè)標(biāo)識(shí)符。 定義局部變量的語(yǔ)法如下： declare variable_name datatype , variable_name datatype select語(yǔ)句或set語(yǔ)句給局部變量進(jìn)行賦值，select語(yǔ)句一次性可以給多個(gè)變量賦值，set語(yǔ)句一次只能給一個(gè)變量賦值。 select local_variable=expression ,n,流控語(yǔ)句,goto語(yǔ)句 goto語(yǔ)句是無(wú)條件轉(zhuǎn)移語(yǔ)句，語(yǔ)法為： goto 標(biāo)號(hào)

7、 goto 語(yǔ)句將程序無(wú)條件轉(zhuǎn)去執(zhí)行標(biāo)號(hào)所在行的語(yǔ)句。標(biāo)號(hào)通常放在一個(gè)語(yǔ)句的前面。標(biāo)號(hào)后面加冒號(hào)（：） Return語(yǔ)句 Return語(yǔ)句實(shí)現(xiàn)無(wú)條件退出批命令、存儲(chǔ)過(guò)程或觸發(fā)器。,流控語(yǔ)句,case表達(dá)式 用于計(jì)算多個(gè)條件并為每個(gè)條件返回單個(gè)值，以簡(jiǎn)化SQL表達(dá)式。語(yǔ)法如下： case input_expression when when_expression then result_expression n else else_result_expression end,流控語(yǔ)句,對(duì)課程進(jìn)行分類(lèi)統(tǒng)計(jì)，要求顯示課程類(lèi)別、課程名稱(chēng)、報(bào)名人數(shù)并計(jì)算各類(lèi)平均報(bào)名人數(shù)，按照類(lèi)別和報(bào)名人數(shù)升序排列，用c

8、ase語(yǔ)句實(shí)現(xiàn)。,流控語(yǔ)句,select 課程分類(lèi)= case kind when 工科 then 工程類(lèi)課程 when 文科 then 文科類(lèi)課程 when 信息 then 信息類(lèi)課程 when 外語(yǔ) then 外語(yǔ)類(lèi)課程 when 其他 then 其他類(lèi)課程 end, couname as 課程名稱(chēng), willnum as 報(bào)名人數(shù) from course where willnum15 order by kind, willnum compute avg(willnum) by kind go,流控語(yǔ)句,print print語(yǔ)句可在屏幕上顯示用戶(hù)的信息以及char、varchar數(shù)據(jù)類(lèi)

9、型變量的內(nèi)容。其他數(shù)據(jù)類(lèi)型必須先進(jìn)行類(lèi)型轉(zhuǎn)換。,練習(xí)3,編寫(xiě)程序，計(jì)算n!（n=10），并將結(jié)果顯示出來(lái),存儲(chǔ)過(guò)程概述,問(wèn)題 要把某完成功能的SQL做成類(lèi)似C語(yǔ)言的函數(shù)，供需要時(shí)調(diào)用，如何做？ 什么是存儲(chǔ)過(guò)程？ 是一組被編譯在一起的T-SQL語(yǔ)句的集合，它們被集合 在一起以完成一個(gè)特定的任務(wù)。,概述,存儲(chǔ)過(guò)程的分類(lèi) 系統(tǒng)存儲(chǔ)過(guò)程 擴(kuò)展存儲(chǔ)過(guò)程（提供從SQL Server到外部程序的接口，以便進(jìn)行各種維護(hù)活動(dòng)） 用戶(hù)自定義的存儲(chǔ)過(guò)程,存儲(chǔ)過(guò)程的作用,從以下幾個(gè)方面考慮： 模塊化編程： 快速執(zhí)行： 減少網(wǎng)絡(luò)通信量 安全機(jī)制,存儲(chǔ)過(guò)程和視圖的比較,組成部分,存儲(chǔ)過(guò)程的三個(gè)組成部分： 所有的輸入?yún)?shù)以

10、及傳給調(diào)用者的輸出參數(shù)。 被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其它存儲(chǔ)過(guò)程的語(yǔ)句。 返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。,創(chuàng)建存儲(chǔ)過(guò)程,在SQL Server中，可以使用兩種方法創(chuàng)建存儲(chǔ)過(guò)程 ： 利用SQL Server 企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程。 使用Transact-SQL語(yǔ)句中的CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程。,使用創(chuàng)建向?qū)?例：使用創(chuàng)建存儲(chǔ)過(guò)程向?qū)?chuàng)建一存儲(chǔ)過(guò)程insert_class_1，用來(lái)向class表中插入數(shù)據(jù)。,執(zhí)行存儲(chǔ)過(guò)程,執(zhí)行此存儲(chǔ)過(guò)程，向class 表中插入一行記錄。 use xk go exec insert_class_1 200100

11、09,02,生物工程（1）班 go,使用企業(yè)管理器,例：創(chuàng)建一個(gè)名為p_StuByClass的存儲(chǔ)過(guò)程，返回“00電子商務(wù)”班的所有學(xué)生的信息。 在企業(yè)管理器里選擇新建存儲(chǔ)過(guò)程命令。,創(chuàng)建存儲(chǔ)過(guò)程,使用SQL語(yǔ)句,create procedure p_stubyclass as select stuno,stuname,student.classno, classname from student,class where student.classno=class.classno and classname=00電子商務(wù) go,創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過(guò)程,使用SQL語(yǔ)言 創(chuàng)建格式： create

12、 procedure proc_name para_name datatype=default as sql_statements 【例】創(chuàng)建一個(gè)名為p_StudentPara的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程根據(jù)給定的班級(jí)名稱(chēng)顯示該班級(jí)所有學(xué)生的信息,SQL語(yǔ)句,use xk go create procedure p_studentpara classname char(20)=00電子商務(wù) as select student.*,classname from student,class where student.classno=class.classno and classname=classna

13、me go exec p_studentpara classname=00數(shù)據(jù)庫(kù) go,思考,若執(zhí)行下列語(yǔ)句，其結(jié)果集為什么？ exec p_studentpara go,多個(gè)輸入?yún)?shù),帶有多個(gè)輸入?yún)?shù)的存儲(chǔ)過(guò)程 創(chuàng)建一個(gè)名為p_StuByPara的存儲(chǔ)過(guò)程，根據(jù)給定的 班級(jí)編碼和學(xué)生學(xué)號(hào)，顯示相應(yīng)學(xué)生的信息,SQL語(yǔ)句,use xk go create procedure p_studentpara classname char(20),stuno char(8) as select student.*,classname from student,class where student.c

14、lassno=class.classno and classname=classname and stuno=stuno go,多個(gè)輸入?yún)?shù),分別執(zhí)行下列兩個(gè)語(yǔ)句，則結(jié)果集各為什么？ 1. use xk go exec p_studentpara classname=00數(shù)據(jù)庫(kù), stuno=00000021 go 2. use xk go exec p_studentpara 00數(shù)據(jù)庫(kù) ,00000021 go,帶輸出參數(shù)的存儲(chǔ)過(guò)程,定義格式同上，同時(shí)指明OUTPUT關(guān)鍵字 例：創(chuàng)建存儲(chǔ)過(guò)程P_ClassNum，要求能根據(jù)用戶(hù)給定的班級(jí)代碼，統(tǒng)計(jì)該班的人數(shù)，并將人數(shù)通過(guò)輸出變量返回給用戶(hù)。

15、 如要查詢(xún)班級(jí)代碼20000002的學(xué)生人數(shù)，如何調(diào)用存儲(chǔ)過(guò)程,SQL語(yǔ)句,創(chuàng)建帶有輸出參數(shù)的存儲(chǔ)過(guò)程 use xk go create procedure p_classnum classno varchar(8), classnum smallint output as set classnum= (select count(*) from student where classno=classno) print classnum,SQL語(yǔ)句,執(zhí)行含有輸出參數(shù)的存儲(chǔ)過(guò)程 declare classno varchar(8), classnum smallint set classno=20

16、000001 exec p_classnum classno,classnum,練習(xí),【練習(xí)一】創(chuàng)建存儲(chǔ)過(guò)程Cou_credit，要求能根據(jù)用戶(hù)給定的學(xué)分值，統(tǒng)計(jì)滿(mǎn)足該學(xué)分值的課程數(shù)目，并把它返回給調(diào)用程序。 如：要統(tǒng)計(jì)2個(gè)學(xué)分的課程門(mén)數(shù)，該如何調(diào)用上述存儲(chǔ)過(guò)程。 【練習(xí)二】創(chuàng)建名為mod_credit的存儲(chǔ)過(guò)程，能修改用戶(hù)指定課程號(hào)的課程學(xué)分值，修改值也由用戶(hù)指定。 如：將課程號(hào)為001的課程的學(xué)分改為5。,練習(xí)一,use xk go create procedure cou_credit credit decimal(3,0) , counum smallint output as set

17、 counum=(select count(*) from course where credit=credit) print counum,練習(xí)一,declare credit decimal(3,0),counum smallint set credit=2 exec cou_credit credit,counum,練習(xí)二,use xk go create procedure mod_credit couno char(8),credit decimal(3,0) as update course set credit=credit where couno=couno go,練習(xí)二,use xk go declare couno char(8), credit decimal(3,0) set couno=001 set credit=5 exec mod_credit couno,credit,修改存儲(chǔ)過(guò)程,修改格式： alter procedure proc_name para_name datatype =default, with encryption with recompile as sql_statements,修改存儲(chǔ)過(guò)程,例 使用Transact-SQL語(yǔ)句修改存儲(chǔ)過(guò)程p_studentpara1,使其能根據(jù)用戶(hù)提供的班級(jí)名稱(chēng)，