《T_SQL程序設計_游標_存儲過程》由會員分享，可在線閱讀，更多相關《T_SQL程序設計_游標_存儲過程（93頁珍藏版）》請在裝配圖網上搜索。

1、單擊此處編輯母版標題樣式,單擊此處編輯母版文本樣式,第二級,第三級,第四級,第五級,*,第,7,第7章 Transact-SQL程序設計,數(shù)據庫應用教程,本章主要內容,常用內置函數(shù),變量與表達式,程序控制語句,游標及其使用,用戶自定義函數(shù),主要內容,存儲過程及其使用,7.1,變量與表達式,7.1.1,變量,1,全局變量,SQL Server,使用全局變量來記錄,SQL Server,服務器的活動狀態(tài)，它由系統(tǒng)定義并保留在系統(tǒng)中，用戶和程序不需聲明就可隨時讀取，但不能改變它的值。全局變量的名稱以兩個,字符開頭。,SQL Server,提供了,30,多個全局變量。,7.1,變量與表達式,7.1.1

2、,變量,【,例,7-1】,用,ERROR,變量檢查錯誤。在查詢分析器中輸入下列代碼并執(zhí)行。,USE,CollegeMIS,GO,DECLARE ERRORNO INT /*,定義一個局部變量,ERRORNO*/,INSERT INTO,Student(StuNo,StuName,StuID,),VALUES(050301001,王高林,324382475454342321),SET ERRORNO=ERROR /*,把錯誤號臨時保存起來*,/,IF ERRORNO0 /*,如果插入語句出錯，輸出出錯信息及錯誤號*,/,PRINT ,插入錯誤！,+,錯誤號,+,CONVERT(varchar,E

3、RRORNO,),/*,CONVERT(varchar,ERRORNO,),的作用是把,ERRORNO,轉換為,varchar,類型*,/,7.1,變量與表達式,7.1.1,變量,（,1,）,ERROR,變量,在,SQL Server,中執(zhí)行一條,Transact-SQL,語句之后，如果語句成功執(zhí)行，那么,ERROR,變量的值為,0,，如果出現(xiàn)一個錯誤，則該變量的值是一個不為,0,的錯誤號，有關錯誤號的具體含義，讀者可查閱,sysmessages,系統(tǒng)表,。,（,2,）,PRINT,語句量,【格式】,PRINT any ASCII text | ,local_variable,| FUNCTI

4、ON |,string_expr,【,功能,】,輸出字符串、局部變量、全局變量或函數(shù)的返回值、字符串表達式的值。,7.1,變量與表達式,7.1.1,變量,2,局部變量,（,1,）局部變量的聲明,例如：,DECLARE ,StuNo,char(9) /*,定義一個名為,StuNo,的局部變量，數(shù)據類型是,char(9) */,DECLARE Birthday,datetime,/*,定義一個名為,Birthday,的局部變量，數(shù)據類型是,datetime,*/,DECLARE Height decimal(5,3)/*,定義一個名為,Height,的局部變量，數(shù)據類型是,decimal(5,3)

5、*/,【格式】,DECLARE ,局部變量名 數(shù)據類型,n,【,功能,】,聲明由“局部變量名”指定的局部變量。,7.1,變量與表達式,7.1.1,變量,2,局部變量,（,2,）局部變量的賦值,【格式,1,】,SET =,【,功能,1】,把“表達式”的值賦給“,變量名”指定的局部變量。,【格式,2,】,SET =,【,功能,2】,把“表達式”的值賦給“,變量名”指定的局部變量。,【格式,3,】,SELECT ,，,，,，,=,FROM WHERE ,【,功能,3】,把從“表名”指定的表中，按照,WHERE,子句中“條件”選取記錄，把計算出來的“表達式”的值賦給“,變量名”指定的局部變量。如果,S

6、ELECT,語句返回多個數(shù)值，則局部變量取最后一個返回值 。,【,例,7-2】,求出學號為“,060301002”,的學生的選課門數(shù)并輸出。,USE,CollegeMIS,GO,DECLARE XH AS char(9) /*,存放學號*,/,DECLARE NUMBER AS,int,/*,存放課程門數(shù)*,/,SET XH=060301002 /*,給變量賦值*,/,/*,通過,SELECT,語句得到學號為“,060301002”,的學生的選課門數(shù)并賦值給,NUMBER*/,SELECT NUMBER=COUNT(*),FROM,SelectCourse,WHERE,StuNo,=XH,PR

7、INT 060301002,號學生選了,+CONVERT(char(2),NUMBER)+,門課,7.1,變量與表達式,7.1.2,運算符與表達式,1,算術運算符與算術表達式,7.1,變量與表達式,7.1.2,運算符與表達式,2,位運算符與位表達式,位運算符用于對整型數(shù)進行按拉運算，有位與（,&,）、位或（,|,）、位異或（,）、位取反（,）等運算符。用位運算符把整型量連接起來的式子稱位表達式，位表達式的結果是一個整型數(shù)。,（,1,）位與運算符（,&,）。參加運算的兩位都為,1,，則結果為,1,，否則結果為,0,。一個位與,1,位與，結果為該位，與,0,位與，結果為,0,。所以程序中通常使用它

8、將一個數(shù)的某些位清零。,（,2,）位或運算符（,|,）。參加運算的兩個位只要有一個為,1,，那么運算結果為,1,。一個位與,1,位或，結果為,1,，與,0,位或，結果不變。所以在程序中常用它將一個數(shù)的某些位置,1,。,7.1,變量與表達式,7.1.2,運算符與表達式,2,位運算符與位表達式,（,3,）位異或運算符（,）。參加運算的兩個位相同，結果為,0,，不同結果為,1,。一個位與,1,異或，結果把它取反，一個位與,0,異或，結果不變。所以在程序中常用它把一個數(shù)的某些位取反。,（,4,）位取反運算符（,）。,是一個單目運算符，用來對一個整形數(shù)按位取反。,例如,，,a,和,b,的類型都是,int

9、,，假設,a,的值為,-5,，,b,的值為,223,，求,a|b,。首先把,a,的值轉換成補碼為：,1111,1111,1111,1011,，,223,的二進制是：,0000,0000,1101 1111,，這兩個數(shù)位或后的機器碼是,1111,1111,1111,1111,。該值是,-1,。,7.1,變量與表達式,7.1.2,運算符與表達式,3,關系（比較）運算符與關系表達式,7.1,變量與表達式,7.1.2,運算符與表達式,4,邏輯運算符與邏輯表達式,7.1,變量與表達式,7.1.2,運算符與表達式,4,邏輯運算符與邏輯表達式,例如,，有下列表達式（假設,x = 3,，,y = 5,，,a

10、= 2,，,b = -3,）：,xa AND yb AND xb) AND NOT (xy) ,結果為,FALSE,7.1,變量與表達式,7.1.2,運算符與表達式,5,字符運算符與字符表達式,字符運算符只有一個“,+”,，稱為字符串連接運算符，它把兩個字符串連接在一起構成一個新的字符串。用字符串連接運算符連接字符串所組成的式子稱字符表達式。,例如表達式,I am+ a teacher.,的運算結果是,I am a teacher.,。,6,運算符的優(yōu)先級,在一個表達式中，運算符的優(yōu)先級決定了運算的順序，,Transact-SQL,語言中，各運算符的優(yōu)先級如下。,（）, & | *,、,/,、

11、,% +,、,- ,關系運算符 ,NOT AND OR,在一個表達式中，應先算括號里再算括號外。先算優(yōu)先級高的運算符，再算優(yōu)先級低的運算符，相同優(yōu)先級的運算符按自左向右的順序進行運算。,7.2,常用內置函數(shù),7.2.1,字符串函數(shù),7.2,常用內置函數(shù),7.2.1,字符串函數(shù),7.2,常用內置函數(shù),7.2.2,數(shù)學函數(shù),7.2,常用內置函數(shù),7.2.2,數(shù)學函數(shù),7.2,常用內置函數(shù),7.2.3,日期函數(shù),7.2,常用內置函數(shù),7.2.3,日期函數(shù),7.2,常用內置函數(shù),7.2.4,類型轉換函數(shù),7.2,常用內置函數(shù),7.2.5,系統(tǒng)函數(shù),7.3,程序控制語句,7.3.1 BEGINEND,語

12、句,【,格式,】,BEGIN,END,【,功能,】,把“語句,1”“,語句,n”,的多條語句組合成一個語句塊，使它們能夠整體執(zhí)行。,7.3,程序控制語句,7.3.2 IFELSE,語句,【,格式,】,IF ,ELSE,【,功能,】,如果條件為,TRUE,執(zhí)行語句,1,，如果條件為,FALSE,執(zhí)行語句,2,。如果省略“,ELSE,語句,2 ”,，則條件為,FALSE,時什么也不執(zhí)行。,【,例,7-3】,求出課程號為“,0002”,的課程的優(yōu)秀人數(shù)并輸出。,USE,CollegeMIS,GO,DECLARE ,CourseNo,AS char(4) /*,存放課程號*,/,DECLARE ,Go

13、odNum,AS,int,/*,存放對應課程號的優(yōu)秀人數(shù)*,/,SET ,CourseNo,=0002 /*,給變量賦值*,/,IF EXISTS(SELECT * FROM /*,如果該門課有優(yōu)秀的記錄*,/,SelectCourse,WHERE,CourseNo,=,CourseNo,AND Score=90),BEGIN,/*,通過,SELECT,語句得到,0002,號課程的優(yōu)秀人數(shù)并賦值給,GoodNum,*/,SELECT ,GoodNum,=,Count(StuNo,),FROM,SelectCourse,WHERE,CourseNo,=,CourseNo,AND Score=90

14、,PRINT 0002,號課程優(yōu)秀人數(shù)為,+CONVERT(char(2),GoodNum)+,人,END,ELSE,PRINT 0002,號課沒有人獲得優(yōu)秀成績,7.3,程序控制語句,7.3.3 CASE,表達式,1,簡單,CASE,表達式,【格式】,CASE ,WHEN THEN ,WHEN THEN ,WHEN THEN ,ELSE ,END,【,功能,】,用“比較表達式”的值依次與,WHEN,子句中的“比較值,1”,、“比較值,2”,、,、“比較值,n”,比較，直到找到第一個與“比較表達式”的值完全相同的“比較值”時，便將對應,WHEN,子句指定的“結果表達式”作為,CASE,表達式的

15、值返回。,【,例,7-4】,從,SelectCourse,表中查詢出所有學生的選課信息，包括學號、課程名（用課程名代替課號）和成績，并按成績降序排列。,USE,CollegeMIS,GO,SELECT,StuNo,CourseNo,=,CASE,CourseNo,WHEN 0001 THEN ,計算機應用基礎,WHEN 0002 THEN ,程序設計基礎,WHEN 0003 THEN ,操作系統(tǒng),ELSE ,計算機網絡技術,END,SCORE,FROM,SelectCourse,ORDER BY Score DESC,7.3,程序控制語句,7.3.3 CASE,表達式,2,搜索,CASE,表達

16、式,【格式】,CASE,WHEN THEN ,WHEN THEN ,WHEN WHEN ,ELSE ,END,【,功能,】,依次計算,WHEN,子句后的“邏輯表達式,1”,、“邏輯表達式,2”,、,、“邏輯表達式,n”,，直到找到第一個值為,TRUE,的“邏輯表達式”，把該,WHEN,子句后的“結果表達式”作為,CASE,表達式的值返回。如果所有的“邏輯表達式”的值均為,FALSE,，則檢查是否有,ELSE,子句存在。如果有,ELSE,子句，便將,ELSE,子句之后的“結果表達式”作為,CASE,表達式值返回；如果不存在,ELSE,子句，便返回一個,NULL,值作為,CASE,表達式的值。,【

17、,例,7-5】,從,SelectCourse,表中查詢出所有學生的選課信息，包括學號、課程名（用課程名代替課號）和成績（用成績等級代替成績，成績等級為優(yōu)秀、良好、中等、及格和不及格），并按學號降序排列。,SELECT,StuNo,CourseNo,=,CASE,CourseNo,WHEN 0001 THEN ,計算機應用基礎,WHEN 0002 THEN ,程序設計基礎,WHEN 0003 THEN ,操作系統(tǒng),ELSE ,計算機網絡技術,END,Score,=,CASE,WHEN Score=90 THEN ,優(yōu)秀,WHEN Score=80 THEN ,良好,WHEN Score=70 T

18、HEN ,中等,WHEN Score=60 THEN ,良好,ELSE ,不及格,END,FROM,SelectCourse,ORDER BY,StuNo,DESC,7.3,程序控制語句,7.3.4 WHILE,語句,【格式】,WHILE ,語句,【,功能,】,首先測試“邏輯表達式”的值，如果為,TRUE,，則執(zhí)行“語句”（即循環(huán)體）；然后再測試“邏輯表達式”，如果為,TRUE,，再執(zhí)行語句；,；直到某次測試“邏輯表達式”的值時，發(fā)現(xiàn)該表達式的值為,FALSE,時，退出循環(huán)，執(zhí)行,WHILE,語句后面的語句。,7.3,程序控制語句,7.3.4 WHILE,語句,【格式】,WHILE ,BEGI

19、N,CONTINUE,BREAK,END,【,功能,】,首先測試“邏輯表達式”的值，如果為,TRUE,，則執(zhí)行,BEGINEND,語句塊（即循環(huán)體）；然后再測試“邏輯表達式”，如果為,TRUE,，再執(zhí)行,BEGINEND,語句塊；,；直到某次測試“邏輯表達式”的值時，發(fā)現(xiàn)該表達式的值為,FALSE,時，退出循環(huán)，執(zhí)行,WHILE,語句后面的語句。如果在執(zhí)行,BEGINEND,語句塊的過程中，遇到,CONTINUE,語句可使程序忽略,CONTINUE,之后的語句，直接跳回到,WHILE,命令行，重新測試條件，決定是否執(zhí)行下一次循環(huán)。若遇到,BREAK,語句將提前直接退出循環(huán)，跳轉到循環(huán)之后的語句

20、 。,【,例,7-6】,編寫程序輸出,1,2,+2,2,+3,2,+10,2,的值,。,DECLARE S AS,int,T,AS,int,DECLARE I AS,smallint,SET S=0,SET I=1,WHILE I=10,BEGIN,SET T=I*,I,SET S=S+T,SET I=I+1,END,PRINT ,和為：,+CONVERT(char(6),S),【,例,7-7】,編寫程序輸出,1,到,100,之間所有能被,3,整除的數(shù)的和。,DECLARE S AS,int,DECLARE I AS,smallint,SET S=0,SET I=0,WHILE I100,BE

21、GIN,SET I=I+1,IF I % 30 /*,如果,I,的值不能被,3,整除*,/,CONTINUE /*,開始下一次循環(huán)*,/,SET S=S+I /*,如果,I,的值能被,3,整除，把,I,的值加到,S,中*,/,END,PRINT ,和為：,+CONVERT(char(6),S),【,例,7-8】,編寫程序輸出,3,到,100,之間所有素數(shù)的和（所謂素數(shù)是指只能被,1,和其本身整除的數(shù)）。,DECLARE S AS,int,DECLARE I AS,smallint,J,AS,smallint,SET S=0,SET I=3,WHILE I=99 /*,外層循環(huán)從,3,到,99*

22、/,BEGIN,SET J=2,WHILE J=I-1 /*,內層循環(huán)用來判斷,I,是不是素數(shù)*,/,BEGIN,IF I % J=0 /*,如果,I,能夠被,J,整除，則不是素數(shù)*,/,BREAK /*,退出循環(huán)*,/,SET J=J+1,END,IF J=I /*,符合該條件則,I,是素數(shù)*,/,SET S=S+I /*,把素數(shù)加到和,S,中*,/,SET I=I+2/*I,的值加,2,，原因是只有奇數(shù)才可能是素數(shù)*,/,END,PRINT ,和為：,+CONVERT(char(6),S) /*,輸出和*,/,7.3,程序控制語句,7.3.5 WAITFOR,語句,【格式,1】,WAITF

23、OR DELAY HH:MM:SS ,【,功能,】,計算機暫停由“,HH:MM:SS”,指定的時間間隔后再繼續(xù)執(zhí)行下面的語句。,【,格式,2 】,WAITFOR DELAY TIME HH:MM:SS,【,功能,】,計算機暫停到“,HH:MM:SS”,指定的時間時才繼續(xù)執(zhí)行下面的語句。,7.4,用戶自定義函數(shù),7.4.1,用戶自定義函數(shù)的創(chuàng)建、查看、修改與刪除,1,用戶自定義函數(shù)的創(chuàng)建,（,1,）使用,Transact-SQL,語句創(chuàng)建函數(shù),【,格式,】,CREATE FUNCTION ,owner_name,.,function_name,( ,parameter_name,AS,scala

24、r_parameter_data_type, = default ,.n ),RETURNS,scalar_return_data_type, WITH , .n , AS ,BEGIN,function_body,RETURN,scalar_expression,END,【,功能,】,定義由“,function_name,”,指定名稱的標量函數(shù)。,【,例,7-7】,為,CollegeMIS,數(shù)據庫創(chuàng)建一個名為,ScoreGrade,的用戶自定義函數(shù)，該函數(shù)用來對百分制成績進行判斷，并返回相應的等級。成績大于等于,90,分時，返回的等級為“優(yōu)秀”；成績大于等于,80,分而小于,90,分時，返回

25、的等級為“良好”；成績大于等于,70,分而小于,80,分時，返回的等級為“中等”；成績大于等于,60,分而小于,70,分時，返回的等級為“及格”；其它情況返回不及格。并調用該函數(shù)顯示出所有選修了“,0002”,號課的人的成績等級。,CREATE FUNCTION,ScoreGrade(Score,tinyint,) RETURNS varchar(8),BEGIN,DECLARE ,GradeStr,varchar(8),SET ,GradeStr,=,Case,WHEN Score=90 THEN ,優(yōu)秀,WHEN Score=80 THEN ,良好,WHEN Score=70 THEN ,

26、中等,WHEN Score=60 THEN ,及格,ELSE ,不及格,END,RETURN ,GradeStr,END,USE,CollegeMIS,GO,SELECT,S.StuNo,as,學號,S.StuName,AS,姓名,dbo.ScoreGrade(SC.Score,) AS,等級,FROM,SelectCourse,SC INNER JOIN Student S,ON,SC.StuNo,=,S.StuNo,WHERE,CourseNo,=0002,GO,7.4,用戶自定義函數(shù),7.4.1,用戶自定義函數(shù)的創(chuàng)建、查看、修改與刪除,1,用戶自定義函數(shù)的創(chuàng)建,（,2,）使用企業(yè)管理器創(chuàng)

27、建函數(shù),【,例,7-10】,為,CollegeMIS,數(shù)據庫創(chuàng)建一個名為,FailureNum,的用戶自定義函數(shù)，該函數(shù)的作用是根據用戶輸入的課程號，統(tǒng)計該門課不及格（小于,60,分）的人數(shù)。,7.4,用戶自定義函數(shù),7.4.1,用戶自定義函數(shù)的創(chuàng)建、查看、修改與刪除,2,用戶自定義函數(shù)的查看,（,1,）通過企業(yè)管理器查看,【,格式,1】,EXEC,sp_help,（,2,）使用系統(tǒng)存儲過程查看用戶自定義函數(shù)信息,【,功能,】,查看“用戶自定義函數(shù)名”指定的函數(shù)的概要信息。,如語句：,exec,sp_help,FailureNum,【,格式,2】,EXEC,sp_helptext,【,功能,】

28、,查看“用戶自定義函數(shù)名”指定的函數(shù)的定義文本信息。,7.4,用戶自定義函數(shù),7.4.1,用戶自定義函數(shù)的創(chuàng)建、查看、修改與刪除,3,用戶自定義函數(shù)的修改,【,格式,】,ALTER FUNCTION ,owner_name,. ,function_name,( ,parameter_name,scalar_parameter_data_type, = default ,.n ),RETURNS,scalar_return_data_type, WITH ,.n , AS ,BEGIN,function_body,RETURN,scalar_expression,END,【,功能,】,修改由,f

29、unction_name,作為函數(shù)名指定的用戶自定義函數(shù)。,【,例,】,現(xiàn)在規(guī)定考試成績低于,55,分為不及格，則可使用如下語句對,FailureNum,函數(shù)進行修改。,ALTER FUNCTION ,dbo.FailureNum, (,CourseNo,char(4),RETURNS,int,AS,BEGIN,DECLARE Num,int,SELECT Num=,Count(StuNo,),FROM,SelectCourse,WHERE,CourseNo,=,CourseNo,AND Score55,RETURN Num,END,7.4,用戶自定義函數(shù),7.4.1,用戶自定義函數(shù)的創(chuàng)建、查

30、看、修改與刪除,4,用戶自定義函數(shù)的刪除,（,1,）使用企業(yè)管理器刪除用戶自定義函數(shù),【,格式,】,DROP FUNCTION ,owner_name,.,function_name, ,.n ,（,2,）使用,TransactSQL,語句刪除用戶自定義函數(shù),【,功能,】,刪除由,function_name,作為函數(shù)名指定的用戶自定義函數(shù)。,例如,，,要刪除,FailureNum,函數(shù)，只需執(zhí)行如下語句：,USE,CollegeMIS,GO,DROP FUNCTION,dbo.FailureNum,GO,7.4,用戶自定義函數(shù),7.4.2,用戶自定義函數(shù)的類型,2,標量函數(shù),標量函數(shù)返回值的類

31、型為,SQL Server 2000,的系統(tǒng)數(shù)據類型，但不可以是,text,、,ntext,、,image,、,cursor,、,timestamp,、,table,等類型。標量函數(shù)的函數(shù)體語句定義在,BEGIN,END,語句內。,7-4-1,節(jié)中創(chuàng)建的,FailureNum,函數(shù)和,ScoreGrade,函數(shù)均為標量函數(shù)。,7.4,用戶自定義函數(shù),7.4.2,用戶自定義函數(shù)的類型,1,內嵌表值函數(shù),內嵌表值函數(shù)的返回值是一個表。該類函數(shù)的函數(shù)體不使用,BEGINEND,語句，而是通過,RETURN,語句返回,SELECT,查詢得到的一個結果集，其功能相當于一個參數(shù)化的視圖，因此在使用上，內嵌

32、表值函數(shù)就可以當成一個虛表來使用。,【格式】,CREATE FUNCTION ,owner_name,.,function_name,( ,parameter_name,AS,scalar_parameter_data_type, = default ,.n ),RETURNS TABLE, WITH , .n , AS ,RETURN ( select-stmt ) ,【,功能,】,創(chuàng)建由,function_name,作為函數(shù)名指定的用戶自定義的內嵌表值函數(shù)。,【,例,7-11】,為,CollegeMIS,數(shù)據庫創(chuàng)建一個名為,StuNo_Score,的內嵌表值函數(shù)，其作用是輸入一個學號，查詢

33、出該學生的所學選課成績信息，包括學號、姓名、課號、課程名和成績。,USE,CollegeMIS,go,CREATE FUNCTION,StuNo_Score,(StuNo,char(9),RETURNS table,AS,RETURN,(,SELECT,S.StuNo,AS,學號,S.StuName,AS,姓名,C.CourseNo,AS,課程號,C.CourseName,AS,課程名,SC.Score,AS,成績,FROM,Student,S,Course,C,SelectCourse,SC,WHERE,S.StuNo,=,StuNo,AND,S.StuNo,=,SC.StuNo,AND,S

34、C.CourseNo,=,C.CourseNo,),USE,CollegeMIS,GO,SELECT * FROM,StuNo_Score,(2009010102),GO,7.4,用戶自定義函數(shù),7.4.2,用戶自定義函數(shù)的類型,3,多語句表值函數(shù),多語句表值函數(shù)可以看作標量函數(shù)和內嵌表值函數(shù)的聯(lián)合，它集中了這兩個函數(shù)的優(yōu)點。其函數(shù)值也是一個表，但函數(shù)體也用,BEGINEND,語句定義，作為函數(shù)返回值的,表中的數(shù)據由函數(shù)體中的語句插入,。,因此，多語句表值函數(shù),可以進行多次查詢操作,，彌補了內嵌表值函數(shù)的不足。,【,例,7-12】,為,CollegeMIS,數(shù)據庫創(chuàng)建一個名為,Score_To

35、tal,的多語句表值函數(shù)，函數(shù)的功能是,輸入一個,年份,，統(tǒng)計出該年級學生各門課的平均分、最高分和最低分。,USE,CollegeMIS,GO,CREATE FUNCTION,Score_Total,(year,char(2),RETURNS,cjhz,TABLE,(,課程號,char(4),課程名,varchar(30),平均分,decimal(4,1),最高分,tinyint,最低分,tinyint,),AS,BEGIN,DECLARE T1,TABLE,(CNo,char(4),PJF decimal(4,1),ZGF,tinyint,ZDF,tinyint,),INSERT T1,SE

36、LECT,CourseNo,AVG(Score,) AS,平均分,Max(SCORE,) AS,最高分,MIN(SCORE) AS,最低分,FROM,SelectCourse,WHERE,StuNo,Like year+%,GROUP BY,CourseNo,INSERT ,cjhz,SELECT,C.CourseNo,C.CourseName,T.PJF,T.ZGF,T.ZDF,FROM Course C INNER JOIN T1 T,ON,C.CourseNo,=,T.CNo,RETURN,END,GO,USE,CollegeMIS,GO,SELECT * FROM dbo.Score_

37、Total(2008),GO,7.5,游標及其使用,通常情況下，執(zhí)行,SELECT,語句得到的總是一個記錄的集合，大部分,SQL,語句都把這個記錄的集合作為一個整體進行處理。,但是，有時用戶也需要對這些數(shù)據集合中的每一行進行操作。要在服務器端實現(xiàn)該功能，就要用到游標。,游標提供了一種在服務器內部處理結果集的方法，它可以識別一個數(shù)據集內部指定的行，從而可以有選擇地按行采取操作 。,7.5,游標及其使用,7.5.1,游標的聲明,1,使用,SQL-92,標準的語法聲明游標,【格式】,DECLARE,cursor_name, INSENSITIVE SCROLL CURSOR,FOR,select_s

38、tatement, FOR READ ONLY | UPDATE OF,column_name, ,.n ,【,功能,】,聲明由,cursor_name,作為游標名指定的游標。,7.5,游標及其使用,7.5.1,游標的聲明,2,使用,Transact-SQL,擴展語法聲明游標,【格式】,DECLARE,cursor_name,CURSOR, LOCAL | GLOBAL , FORWARD_ONLY | SCROLL , STATIC | KEYSET | DYNAMIC | FAST_FORWARD , READ_ONLY | SCROLL_LOCKS | OPTIMISTIC , TYPE

39、_WARNING ,FOR,select_statement, FOR UPDATE OF,column_name, ,.n ,【,功能,】,聲明由,cursor_name,作為游標名指定的游標。,【,例,7-13】,定義一個游標用來從,SelectCourse,表中提取數(shù)據，此游標為局部、滾動游標。游標數(shù)據來源于,SelectCourse,表，為成績是優(yōu)秀的所有記錄 。,USE,CollegeMIS,GO,DECLARE,GoodScorecur,CURSOR,LOCAL SCROLL,FOR,SELECT *,FROM,SelectCourse,WHERE Score=90,7.5,游標及

40、其使用,7.5.2,游標的打開、關閉與釋放,1,打開游標,【格式】,OPEN GLOBAL ,cursor_name, |,cursor_variable_name,【,功能,】,打開由,cursor_name,作為游標名指定的游標或由游標變量名,cursor_variable_name,引用的游標。,【,說明,】,：,（,1,）如果游標是靜態(tài)游標，即是以,STATIC,選項聲明的，,OPEN,語句將創(chuàng)建一個臨時表來放置結果集。如果游標是鍵值驅動游標，即是以,KEYSET,選項聲明的，,OPEN,語句將創(chuàng)建一個臨時表來放置關鍵字值。這些臨時表都儲存在,tempdb,數(shù)據庫中。,（,2,）打開游

41、標的時候，服務器將執(zhí)行,SELECT,語句，獲取游標中的數(shù)據。,（,3,）游標打開后，可以使用全局變量,CURSOR_ROWS,來返回當前申明的游標可以操作的數(shù)據行的數(shù)量。取值有四種情況：,-m,、,-1,、,0,、,n,。,7.5,游標及其使用,7.5.2,游標的打開、關閉與釋放,2,關閉游標和釋放游標,【格式】,CLOSE GLOBAL ,cursor_name, |,cursor_variable_name,【,功能,】,關閉由,cursor_name,作為游標名指定的游標或由游標變量名,cursor_variable_name,引用的游標。,【,格式,】,DEALLOCATE GLOB

42、AL ,cursor_name, | ,cursor_variable_name,【,功能,】,釋放由,cursor_name,作為游標名指定的游標或由游標變量名,cursor_variable_name,引用的游標。,例如：,CLOSE,GoodScorecur,DEALLOCATE,GoodScorecur,7.5,游標及其使用,7.5.3,游標的使用,1,使用游標讀取數(shù)據,【格式】,FETCH, NEXT | PRIOR | FIRST | LAST,| ABSOLUTE n | ,nvar,| RELATIVE n | ,nvar,FROM, GLOBAL ,cursor_name,

43、| ,cursor_variable_name, INTO ,variable_name, ,.n ,【,功能,】,從由,cursor_name,作為游標名指定的游標或由游標變量名,cursor_variable_name,引用的游標中讀取一行數(shù)據。,說明：,（,1,）,NEXT| PRIOR| FIRST| LAST,：,NEXT,表示返回當前行的下一行，這是默認選項；,PRIOR,表示返回當前行的上一行；,FIRST,表示返回數(shù)據集的第一行；,LAST,表示返回數(shù)據集的最后一行。,（,2,）,ABSOLUTE n | ,nvar,：返回游標數(shù)據集的第,n,行，如果,n,為正，從數(shù)據集的開頭

44、算起；如果,n,為負，則從數(shù)據集的末尾算起。,（,3,）,RELATIVE n | ,nvar,：返回數(shù)據集中相對于當前記錄的第,n,行。如果,n,為負，將從當前行開始往回數(shù)。,對于,SCROLL,關鍵字聲明的游標，可以很方便地移動游標的記錄指針的位置，有下列語句：,OPEN,GoodScorecur,/*,打開游標*,/,FETCH NEXT FROM,GoodScorecur,/*,提取下一條記錄*,/,FETCH PRIOR FROM,GoodScorecur,/*,提取前一條記錄*,/,FETCH FIRST FROM,GoodScorecur,/*,提取第一條記錄*,/,FETCH

45、LAST FROM,GoodScorecur,/*,提取最后一條記錄*,/,FETCH ABSOLUTE 6 FROM,GoodScorecur,/*,提取第六條記錄*,/,FETCH RELATIVE -5 FROM,GoodScorecur,/*,提取當前記錄前面的第五條記錄*,/,注意：每次滾動操作都應檢查,FETCH_STATUS,的值，以確保新位置的有效性。若,FETCH_STATUS,的值不為,0,，則操作無效。,說明：,（,4,）,FROM,：,表明下一個關鍵字是游標，數(shù)據就取自該游標。,（,5,）,INTO ,variable_name, ,.n ,：把從游標中讀取的數(shù)據保存在

46、變量中以備以后使用。其中變量的數(shù)據類型必須與從游標中返回的數(shù)據行的相應字段的數(shù)據類型完全匹配，否則將產生錯誤。,每次執(zhí)行,FETCH,語句后，,FETCH,語句的執(zhí)行情況都將在全局變量,FETCH_STATUS,中反映出來，,FETCH_STATUS,變量有三種取值：,0,（表示存取成功）、,-1,（表示沒有取出數(shù)據，因為記錄指針的位置超出了結果集）和,-2,（表示取出的行不再是結果集的成員）。,因此，在對已讀取數(shù)據行操作之前，可通過檢查全局變量,FETCH_STATUS,的值，檢查通過游標取出的數(shù)據行的合法性。,【,例,7-14】,把例,7-13,中定義的,SelectCoursecur,游

47、標中的數(shù)據按行讀出并顯示出來。,USE,CollegeMIS,GO,DECLARE XH char(9),JSH Char(6),DECLARE KCH char(4),CJ,tinyint,DECLARE,GoodScorecur,CURSOR,LOCAL SCROLL,FOR,SELECT * FROM,SelectCourse,WHERE Score=90,OPEN,GoodScorecur,PRINT ,學號,+ +,教師號,+ +,課程號,+ +,成績,FETCH,NEXT FROM,GoodScorecur,INTO XH,JSH,KCH,CJ,WHILE FETCH_STATUS

48、=0,BEGIN,PRINT XH+ +JSH+ +KCH+ +CONVERT(char(3),CJ),FETCH NEXT FROM,GoodScorecur,INTO XH,JSH,KCH,CJ,END,CLOSE,GoodScorecur,DEALLOCATE,GoodScorecur,GO,7.5,游標及其使用,7.5.3,游標的使用,2,使用游標修改數(shù)據,若要用游標對基礎表的數(shù)據行進行更新操作，需要在申明游標時使用,UPDATE,關鍵字。用游標修改數(shù)據時，須使用“,WHERE CURRENT OF,游標名”來定位要修改的數(shù)據行，修改完畢后，游標基礎表中相應的數(shù)據行同樣會得到修改。,【

49、,例,7-15】,給數(shù)據表,CollegeMIS,的聲明一個可更新的游標，游標的數(shù)據為,SelectCourse,表中成績大于等于,90,分的記錄，并限定可以更新的字段為,Score,。然后將該字段的值減,1,。,USE,CollegeMIS,GO,DECLARE XH char(9),JSH Char(6),DECLARE KCH char(4),CJ,tinyint,DECLARE,GoodScorecur,CURSOR,LOCAL SCROLL,FOR,SELECT *,FROM,SelectCourse,WHERE Score=90,FOR UPDATE OF Score,OPEN,G

50、oodScorecur,FETCH,NEXT FROM,GoodScorecur,INTO XH,JSH,KCH,CJ,WHILE FETCH_STATUS=0,BEGIN,UPDATE,SelectCourse,SET,Score=Score-1,/*,根據游標修改表中的數(shù)據*,/,WHERE CURRENT OF,GoodScorecur,/*,定位在修改的數(shù)據行*,/,FETCH NEXT FROM,GoodScorecur,INTO XH,JSH,KCH,CJ,END,CLOSE,GoodScorecur,DEALLOCATE,GoodScorecur,SELECT * /*,顯示修改

51、后的表中的相關數(shù)據*,/,FROM,SelectCourse,WHERE Score=90,GO,7.5,游標及其使用,7.5.3,游標的使用,3,使用游標刪除數(shù)據,若要用游標刪除基礎表的數(shù)據行，需要在申明游標時使用,UPDATE,關鍵字。同時由于刪除數(shù)據行時涉及到數(shù)據行的所有字段，因此在,UPDATE,關鍵字后應包括所有字段，或省略字段名。,用游標刪除基礎表的數(shù)據行時，須使用“,WHERE CURRENT OF,游標名”來定位要刪除的數(shù)據行。刪除完畢后，游標基礎表中相應的數(shù)據行同樣會得到刪除。,【,例,7-16】,給數(shù)據表,CollegeMIS,的聲明一個可更新的游標，游標的數(shù)據為,Sele

52、ctCourse,表中成績大于等于,90,分的記錄。然后利用該游標將基礎表中的所有大于等于,90,分的記錄刪除。,USE,CollegeMIS,GO,DECLARE XH char(9),JSH Char(6),DECLARE KCH char(4),CJ,tinyint,DECLARE,GoodScorecur,CURSOR,LOCAL SCROLL,FOR,SELECT * FROM,SelectCourse,WHERE Score=90,FOR UPDATE,OPEN,GoodScorecur,FETCH NEXT FROM,GoodScorecur,INTO XH,JSH,KCH,CJ

53、,WHILE FETCH_STATUS=0,BEGIN,DELETE FROM,SelectCourse,/*,根據游標刪除表中的數(shù)據*,/,WHERE CURRENT OF,GoodScorecur,/*,定位在刪除的數(shù)據行*,/,FETCH NEXT FROM,GoodScorecur,INTO XH,JSH,KCH,CJ,END,CLOSE,GoodScorecur,DEALLOCATE,GoodScorecur,SELECT * /*,顯示刪除后的表中的相關數(shù)據*,/,FROM,SelectCourse,WHERE Score=90 /*,顯示結果為空*,/,GO,7.6,存儲過程及其

54、使用,7.6.1,存儲過程概述,存儲過程是一種數(shù)據庫對象，是為了實現(xiàn)某個特定任務，將一組預編譯的,SQL,語句以一個存儲單元的形式存儲在服務器上，供用戶調用。,存儲過程在第一次執(zhí)行時進行編譯，在以后的操作中多次調用，而不必重新書寫語句，因此利用它可以提高工作效率。,它可以作為一個獨立的數(shù)據庫對象存儲在數(shù)據庫中，也可以作為一個單元供用戶在應用程序中調用。,存儲過程與其他編程語言中的過程相似。有如下,特點,：,接受輸入參數(shù)并以輸出參數(shù)的形式將多個值返回至調用過程或批處理。,包含執(zhí)行數(shù)據庫操作（包括調用其他過程）的編程語句。,向調用過程或批處理返回狀態(tài)值，以表明成功或失?。ㄒ约笆≡颍?。,1,、存

55、儲過程的概念,2,、,存儲過程的類型,在,SQL Server,中存儲過程可以分為,五類,。即系統(tǒng)存儲過程、本地存儲過程、臨時存儲過程、遠程存儲過程和擴展存儲過程。,系統(tǒng)存儲過程：,系統(tǒng)存儲過程存儲在,master,數(shù)據庫中，并以“,sp_”,為前綴，主要用來從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理,SQL Server,提供幫助，為用戶查看數(shù)據庫對象提供方便。比如用來查看數(shù)據庫對象信息的系統(tǒng)存儲過程,sp_help,。,本地存儲過程：,本地存儲過程是用戶根據需要，在自己的普通數(shù)據庫中創(chuàng)建的存儲過程。,臨時存儲過程：,臨時存儲過程通常分為,局部臨時存儲過程,和,全局臨時存儲過程,。創(chuàng)建局部臨時存儲

56、過程時，要以“,#”,作為過程名稱的第一個字符。創(chuàng)建全局臨時存儲過程時，要以 “,#”,作為過程名稱的前兩個字符。臨時存儲過程在連接到早期版本時很有用，這些早期版本不支持再次使用,T-SQL,語句或批處理執(zhí)行計劃。連接到,SQL Server 2005,的應用程序應使用,sp_executesql,系統(tǒng)存儲過程，而不使用臨時存儲過程。,遠程存儲過程：,遠程存儲過程是,SQL Server 2005,的一個傳統(tǒng)功能，是指非本地服務器上的存儲過程?，F(xiàn)在只有在分布式查詢中使用此存儲過程。,擴展存儲過程：,擴展存儲過程以“,xp,_”,為前綴，它是關系數(shù)據庫引擎的開放式數(shù)據服務層的一部分，可以使用戶在

57、動態(tài)數(shù)據庫（,DLL,）文件所包含的函數(shù)中實現(xiàn)邏輯功能，從而擴展了,T-SQL,的功能，并且可以像調用,T-SQL,過程那樣從,T-SQL,語句調用這些參數(shù)。,下面主要介紹本地存儲過程的創(chuàng)建、執(zhí)行、修改、刪除等操作。,7.6,存儲過程及其使用,7.6.2,存儲過程的創(chuàng)建與管理,1,創(chuàng)建存儲過程,（,1,）使用對象資源管理器創(chuàng)建存儲過程,【,例,7-17】,為,CollegeMIS,數(shù)據庫創(chuàng)建一個名稱為,P1_XSCX,的存儲過程，該存儲過程返回所有計算機應用專業(yè)的男學生的基本情況，包括學號、姓名、身份證號、性別、電話號碼等信息。,操作步驟如下：, 在“對象資源管理器”窗格中，展開“數(shù)據庫”結點

58、。, 單擊相應的數(shù)據庫（這里選擇,CollegeMIS,數(shù)據庫）。依次展開“可編程性”、“存儲過程”結點。右擊“存儲過程”結點，在彈出的快捷菜單中選擇“新建存儲過程”命令。, 打開創(chuàng)建存儲過程的初始界面，如圖,9-1,所示。, 將初始代碼清除，輸入存儲過程文本，根據題意輸入如下語句：,SELECT,StuNo,AS,學號,StuName,AS,姓名,StuID,AS,身份證號,Sex AS,性別,Telephone AS,電話號碼,FROM Student WHERE Major=,計算機應用, AND Sex=,男,輸入完成后，單擊“分析”按鈕，檢查語法是否正確。, 如果沒有任何錯誤，單擊“

59、執(zhí)行”按鈕，將在數(shù)據庫中創(chuàng)建存儲過程。,7.6,存儲過程及其使用,7.6.2,存儲過程的創(chuàng)建與管理,1,創(chuàng)建存儲過程,（,2,）使用查詢分析器執(zhí)行,TransactSQL,語句創(chuàng)建存儲過程,【格式】,CREATE PROCEDURE,procedure_name, ; number , parameter,data_type, VARYING = default OUTPUT , ,.n , WITH, RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION , FOR REPLICATION ,AS,sql_statement, .n ,【,功能,】,創(chuàng)建由“,procedure_name,”,作為過程名指定的存儲過程。,【,例,7-18】,為,CollegeMIS,數(shù)據庫創(chuàng)建一個名稱為,P2_CJCX,的存儲過程，該存儲過程返回所有計算機應用專業(yè)的學生的學習情況，包括學號、姓名、課程號、課程名和成績信息。,USE,CollegeMIS,GO,CREATE PROCEDURE P2_CJCX /*,創(chuàng)建存儲過程*,/,AS,S