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

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

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

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

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

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

6、句1 if 邏輯表達式2 continue 語句2 end,流控語句,declare declare語句用來定義局部變量，定義后的變量值為null。局部變量必須以開始，后跟一個標識符。 定義局部變量的語法如下： declare variable_name datatype , variable_name datatype select語句或set語句給局部變量進行賦值，select語句一次性可以給多個變量賦值，set語句一次只能給一個變量賦值。 select local_variable=expression ,n,流控語句,goto語句 goto語句是無條件轉(zhuǎn)移語句，語法為： goto 標號

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

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

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

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

11、09,02,生物工程（1）班 go,使用企業(yè)管理器,例：創(chuàng)建一個名為p_StuByClass的存儲過程，返回“00電子商務(wù)”班的所有學(xué)生的信息。 在企業(yè)管理器里選擇新建存儲過程命令。,創(chuàng)建存儲過程,使用SQL語句,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ù)的存儲過程,使用SQL語言 創(chuàng)建格式： create

12、 procedure proc_name para_name datatype=default as sql_statements 【例】創(chuàng)建一個名為p_StudentPara的存儲過程，該存儲過程根據(jù)給定的班級名稱顯示該班級所有學(xué)生的信息,SQL語句,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ù)庫 go,思考,若執(zhí)行下列語句，其結(jié)果集為什么？ exec p_studentpara go,多個輸入?yún)?shù),帶有多個輸入?yún)?shù)的存儲過程 創(chuàng)建一個名為p_StuByPara的存儲過程，根據(jù)給定的 班級編碼和學(xué)生學(xué)號，顯示相應(yīng)學(xué)生的信息,SQL語句,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,多個輸入?yún)?shù),分別執(zhí)行下列兩個語句，則結(jié)果集各為什么？ 1. use xk go exec p_studentpara classname=00數(shù)據(jù)庫, stuno=00000021 go 2. use xk go exec p_studentpara 00數(shù)據(jù)庫 ,00000021 go,帶輸出參數(shù)的存儲過程,定義格式同上，同時指明OUTPUT關(guān)鍵字 例：創(chuàng)建存儲過程P_ClassNum，要求能根據(jù)用戶給定的班級代碼，統(tǒng)計該班的人數(shù)，并將人數(shù)通過輸出變量返回給用戶。

15、 如要查詢班級代碼20000002的學(xué)生人數(shù)，如何調(diào)用存儲過程,SQL語句,創(chuàng)建帶有輸出參數(shù)的存儲過程 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語句,執(zhí)行含有輸出參數(shù)的存儲過程 declare classno varchar(8), classnum smallint set classno=20

16、000001 exec p_classnum classno,classnum,練習(xí),【練習(xí)一】創(chuàng)建存儲過程Cou_credit，要求能根據(jù)用戶給定的學(xué)分值，統(tǒng)計滿足該學(xué)分值的課程數(shù)目，并把它返回給調(diào)用程序。 如：要統(tǒng)計2個學(xué)分的課程門數(shù)，該如何調(diào)用上述存儲過程。 【練習(xí)二】創(chuàng)建名為mod_credit的存儲過程，能修改用戶指定課程號的課程學(xué)分值，修改值也由用戶指定。 如：將課程號為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,修改存儲過程,修改格式： alter procedure proc_name para_name datatype =default, with encryption with recompile as sql_statements,修改存儲過程,例 使用Transact-SQL語句修改存儲過程p_studentpara1,使其能根據(jù)用戶提供的班級名稱，