SQL-Server數(shù)據(jù)庫技術(shù)及應(yīng)用(第五講)-編程基礎(chǔ)和存儲(chǔ)過程

第5講編程根底和存儲(chǔ)過程常量和變量常量：字符串常量數(shù)值常量日期常量uniqueidentifier常量注：引用數(shù)值常量不用單引號，引用日期、字符串常量時(shí)需要加單引號常量字符串常量字符串常量包含在單引號內(nèi)，由字母數(shù)字字符〔a~z、A~Z和0~9〕以及特殊字符〔如！、@和#〕組成。如果字符串常量中包含一個(gè)單引號，如：‘I’’mastudent’常量數(shù)值常量

二進(jìn)制常量：具有前綴0X，并且是十六進(jìn)制數(shù)字字符串。例如0X12EF、0XFF。

bit常量：使用0或1表示。

integer常量：整數(shù)常量。

decimal常量：可以包含小數(shù)點(diǎn)的數(shù)值常量。

float常量和real常量：使用科學(xué)記數(shù)法表示，例如101.5E6。

money常量：貨幣常量，以$作為前綴，可以包含小數(shù)點(diǎn)。如$12.53變量變量：在程序運(yùn)行過程中其值是變化的量局部變量〔以@開頭；使用DECLARE語句聲明〕【問題】DECLARE@var1int,@var2moneySELECT@var1=50,@var2=$29.95SELECT@var1,@var2全局變量〔也稱配置函數(shù)，是系統(tǒng)定義的變量，以@@開頭；用戶只能使用〕例如：@@version@@rowcount練習(xí)2問題：編寫程序計(jì)算兩個(gè)整數(shù)之和declare@itinyint,@jtinyint,@sumtinyintset@i=50set@j=60select@sum=@i+@jprint@sumgo例題打印Course表中有多少種類型的課程，要求聲明局部變量，進(jìn)行賦值，然后打印變量內(nèi)容。usexkgodeclare@user_msgvarchar(50)select@user_msg=‘在Course表中有’+convert(varchar(3),(selectcount(distinctkind)fromCourse))+’種類型的課程?！痯rint@user_msggo練習(xí)打印“計(jì)算機(jī)應(yīng)用工程系”下共有多少個(gè)班級，要求聲明局部變量，進(jìn)行賦值，然后打印變量內(nèi)容。運(yùn)算符算術(shù)運(yùn)算符〔+、－、*、/、%〕一元運(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í)行，但Createdatabase、Createtable、Createindex例外。流控語句【問題】如何用SQL語句編程實(shí)現(xiàn)：求1+2+3+4+…+100=?分析：WHILE、BEGIN…END、IF…ELSE…、GOTO的使用用法其他流控語句的使用用法BRAEK：在循環(huán)語句中，用于退出本層循環(huán)CONTINUE：在循環(huán)語句中，結(jié)束本次循環(huán)，重新轉(zhuǎn)到下一次循環(huán)條件的判斷流控語句If語句if邏輯表達(dá)式{sql_statement|statement_block}[else{sql_statement|statement_block}]Begin…end用來定義語句塊，必須成對出現(xiàn)。While、break和continueWhile邏輯表達(dá)式語句塊功能：當(dāng)邏輯表達(dá)式為真時(shí)，執(zhí)行循環(huán)體，直到邏輯表達(dá)式為假。流控語句break語句退出while循環(huán)，coutinue語句跳過語句塊的所有其他語句，開始下一次循環(huán)。while邏輯表達(dá)式1begin語句1if邏輯表達(dá)式2continue語句2end流控語句declare

declare語句用來定義局部變量，定義后的變量值為null。局部變量必須以@開始，后跟一個(gè)標(biāo)識符。定義局部變量的語法如下：declare@variable_namedatatype[,@variable_namedatatype]…select語句或set語句給局部變量進(jìn)行賦值，select語句一次性可以給多個(gè)變量賦值，set語句一次只能給一個(gè)變量賦值。select{@local_variable=expression}[,…n]流控語句goto語句goto語句是無條件轉(zhuǎn)移語句，語法為：goto標(biāo)號goto語句將程序無條件轉(zhuǎn)去執(zhí)行標(biāo)號所在行的語句。標(biāo)號通常放在一個(gè)語句的前面。標(biāo)號后面加冒號〔：〕Return語句Return語句實(shí)現(xiàn)無條件退出批命令、存儲(chǔ)過程或觸發(fā)器。

流控語句case表達(dá)式用于計(jì)算多個(gè)條件并為每個(gè)條件返回單個(gè)值，以簡化SQL表達(dá)式。語法如下：caseinput_expressionwhenwhen_expressionthenresult_expression[…n]elseelse_result_expressionend流控語句對課程進(jìn)行分類統(tǒng)計(jì)，要求顯示課程類別、課程名稱、報(bào)名人數(shù)并計(jì)算各類平均報(bào)名人數(shù)，按照類別和報(bào)名人數(shù)升序排列，用case語句實(shí)現(xiàn)。流控語句select‘課程分類’=casekindwhen‘工科’then‘工程類課程’when‘文科’then‘文科類課程’when‘信息’then‘信息類課程’when‘外語’then‘外語類課程’when‘其他’then‘其他類課程’end,counameas‘課程名稱’,willnumas‘報(bào)名人數(shù)’fromcoursewherewillnum>15orderbykind,willnumcomputeavg(willnum)bykindgo流控語句printprint語句可在屏幕上顯示用戶的信息以及char、varchar數(shù)據(jù)類型變量的內(nèi)容。其他數(shù)據(jù)類型必須先進(jìn)行類型轉(zhuǎn)換。練習(xí)3編寫程序，計(jì)算n!〔n=10〕，并將結(jié)果顯示出來存儲(chǔ)過程概述問題要把某完成功能的SQL做成類似C語言的函數(shù)，供需要時(shí)調(diào)用，如何做？什么是存儲(chǔ)過程？是一組被編譯在一起的T-SQL語句的集合，它們被集合在一起以完成一個(gè)特定的任務(wù)。概述存儲(chǔ)過程的分類系統(tǒng)存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程〔提供從SQLServer到外部程序的接口，以便進(jìn)行各種維護(hù)活動(dòng)〕用戶自定義的存儲(chǔ)過程存儲(chǔ)過程的作用從以下幾個(gè)方面考慮：模塊化編程：快速執(zhí)行：減少網(wǎng)絡(luò)通信量平安機(jī)制存儲(chǔ)過程和視圖的比較視圖存儲(chǔ)過程語句只能是SELECT語句可以包含程序流、邏輯以及SELECT語句輸入、返回結(jié)果不能接受參數(shù)，只能返回結(jié)果集可以有輸入輸出參數(shù)，也可以有返回值典型應(yīng)用多個(gè)表格的連接查詢完成某個(gè)特定的較復(fù)雜的任務(wù)組成局部存儲(chǔ)過程的三個(gè)組成局部：所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲(chǔ)過程的語句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。創(chuàng)立存儲(chǔ)過程在SQLServer中，可以使用兩種方法創(chuàng)立存儲(chǔ)過程：利用SQLServer企業(yè)管理器創(chuàng)立存儲(chǔ)過程。使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)立存儲(chǔ)過程。使用創(chuàng)立向?qū)Ю菏褂脛?chuàng)立存儲(chǔ)過程向?qū)?chuàng)立一存儲(chǔ)過程insert_class_1，用來向class表中插入數(shù)據(jù)。執(zhí)行存儲(chǔ)過程執(zhí)行此存儲(chǔ)過程，向class表中插入一行記錄。usexkgoexecinsert_class_1'20010009','02','生物工程〔1〕班'go使用企業(yè)管理器例：創(chuàng)立一個(gè)名為p_StuByClass的存儲(chǔ)過程，返回“00電子商務(wù)”班的所有學(xué)生的信息。在企業(yè)管理器里選擇新建存儲(chǔ)過程命令。創(chuàng)立存儲(chǔ)過程

使用SQL語句createprocedurep_stubyclassasselectstuno,stuname,student.classno,classnamefromstudent,classwherestudent.classno=class.classnoandclassname='00電子商務(wù)'go創(chuàng)立帶輸入?yún)?shù)的存儲(chǔ)過程使用SQL語言創(chuàng)立格式：createprocedureproc_name@para_namedatatype=[default]assql_statements【例】創(chuàng)立一個(gè)名為p_StudentPara的存儲(chǔ)過程，該存儲(chǔ)過程根據(jù)給定的班級名稱顯示該班級所有學(xué)生的信息SQL語句usexkgocreateprocedurep_studentpara@classnamechar(20)=‘00電子商務(wù)’asselectstudent.*,classnamefromstudent,classwherestudent.classno=class.classnoandclassname=@classnamegoexecp_studentpara@classname='00數(shù)據(jù)庫'go思考假設(shè)執(zhí)行以下語句，其結(jié)果集為什么？execp_studentparago多個(gè)輸入?yún)?shù)帶有多個(gè)輸入?yún)?shù)的存儲(chǔ)過程創(chuàng)立一個(gè)名為p_StuByPara的存儲(chǔ)過程，根據(jù)給定的班級編碼和學(xué)生學(xué)號，顯示相應(yīng)學(xué)生的信息SQL語句usexkgocreateprocedurep_studentpara@classnamechar(20),@stunochar(8)asselectstudent.*,classnamefromstudent,classwherestudent.classno=class.classnoandclassname=@classnameandstuno=@stunogo多個(gè)輸入?yún)?shù)分別執(zhí)行以下兩個(gè)語句，那么結(jié)果集各為什么？1.usexkgoexecp_studentpara@classname=‘00數(shù)據(jù)庫’,@stuno='00000021'go2.usexkgoexecp_studentpara‘00數(shù)據(jù)庫','00000021'go

帶輸出參數(shù)的存儲(chǔ)過程定義格式同上，同時(shí)指明OUTPUT關(guān)鍵字例：創(chuàng)立存儲(chǔ)過程P_ClassNum，要求能根據(jù)用戶給定的班級代碼，統(tǒng)計(jì)該班的人數(shù)，并將人數(shù)通過輸出變量返回給用戶。如要查詢班級代碼’20000002’的學(xué)生人數(shù)，如何調(diào)用存儲(chǔ)過程SQL語句創(chuàng)立帶有輸出參數(shù)的存儲(chǔ)過程usexkgocreateprocedurep_classnum@classnovarchar(8),@classnumsmallintoutputasset@classnum=(selectcount(*)fromstudentwhereclassno=@classno)print@classnumSQL語句執(zhí)行含有輸出參數(shù)的存儲(chǔ)過程declare@classnovarchar(8),@classnumsmallintset@classno=‘20000001’execp_classnum@classno,@classnum練習(xí)【練習(xí)一】創(chuàng)立存儲(chǔ)過程Cou_credit，要求能根據(jù)用戶給定的學(xué)分值，統(tǒng)計(jì)滿足該學(xué)分值的課程數(shù)目，并把它返回給調(diào)用程序。如：要統(tǒng)計(jì)2個(gè)學(xué)分的課程門數(shù)，該如何調(diào)用上述存儲(chǔ)過程?！揪毩?xí)二】創(chuàng)立名為mod_credit的存儲(chǔ)過程，能修改用戶指定課程號的課程學(xué)分值，修改值也由用戶指定。如：將課程號為‘001’的課程的學(xué)分改為5。練習(xí)一usexkgocreateprocedurecou_credit@creditdecimal(3,0),@counumsmallintoutputasset@counum=(selectcount(*)fromcoursewherecredit=@credit)print@counum練習(xí)一declare@creditdecimal(3,0),@counumsmallintset@credit=2execcou_credit@credit,@counum練習(xí)二usexkgocreateproceduremod_credit@counochar(8),@creditdecimal(3,0)asupdatecourseset