山西財貿職業(yè)技術學院

1、數據庫原理與SQL Server 第5章 Transact-SQL編程1第5章 Transact-SQL編程5.1 進一步了解編程語言 Transact-SQL5.2 設計程序流程控制語句 5.3 分行處理程序游標實訓 Transact-SQL語言編程25.1 進一步了解編程語言 Transact-SQL5.1.1 定義批處理語句 批處理是一個或多個SQL語句的集合，從程序一次性發(fā)送到SQL Server 2000并編譯為一個可執(zhí)行單元，一次性執(zhí)行。如果一個批處理中任何一條語句有語法錯誤，則整個批處理將不能編譯和執(zhí)行。 語法格式： GO35.1.2 變量SQL Server將變量分為局部變量和

2、全局變量。1.局部變量 局部變量是用戶定義，必須以開頭，在程序內聲 明，并只能在該程序內使用。 (1)局部變量的聲明 DECLATE ,n (2)局部變量的賦值 SET|SELECT = 4例5-1 聲明一個datetime類型的局部變量。 DECLARE date_var datetime例5-2 聲明兩個局部變量。 DECLARE var1 int , var2 money例5-3 用SET語句和SELECT語句為局部變量賦值。 DECLARE var1 datetime,var2 char(10) SET var1 = getdate() SELECT var2 = convert(ch

3、ar(10),var1,102)例5-4 用SET語句將查詢結果賦給局部變量并用SELECT語句顯示局部變量的值。 declare date_var datetime set date_var=(select min(birthday) from s) select date_var as min_birthday5.1.2 變量52.全局變量 全局變量是SQL Server系統(tǒng)內部使用的變量，以開頭。 例5-5 用全局變量查看SQL Server的版本、當前所使用的SQL Server服務器的名稱以及所使用的服務名稱等信息。 腳本：print 目前所用SQL Server的版本信息如下:pr

4、int VERSIONprint 目前SQL Server服務器名稱為：+SERVERNAMEprint 目前所用服務器為：+SERVICENAME5.1.2 變量65.1.3 注釋語句 注釋語句是對程序代碼的說明或暫時禁用，是程序代碼中不編譯執(zhí)行的語句。 語法格式： - 或 /* */75.1.4 輸出語句 需要查看程序結果時，可以使用輸出語句。 語法格式： PRINT 例5-6 輸出變量的值。 腳本： declare date_var datetime set date_var=(select min(birthday) from s) print date_var 85.1.5 定義語句

5、塊語句 在控制流程中需要執(zhí)行兩條或兩條以上的語句，應該將這些語句定義為一個語句塊（稱為復合語句）。 語法格式： BEGIN | END 95.2 設計程序流程控制語句 一、選擇結構二、循環(huán)結構三、轉移語句四、等待語句五、返回語句105.2.1 選擇結構選擇結構可以使用條件語句來實現(xiàn)。語法格式： IF | ELSE |11例5-7 查詢學號為1001的學生。腳本： if exists(select sno from s where sno=0001) print 找到 else print 未找到例5-8 條件語句的嵌套。腳本：5.2.1 選擇結構125.2.2 循環(huán)結構 循環(huán)結構可以使用循環(huán)語

6、句來實現(xiàn)。 語法格式： WHILE | 中斷語句：BREAK 短路語句：CONTINUE 13例5-9 求110之間的素數和。腳本：例5-10 求100200之間的全部素數。腳本：5.2.2 循環(huán)結構145.2.3 轉移語句 轉移語句將程序的執(zhí)行流程無條件轉移到指定的標號處。 語法格式： GOTO 定義標號時，應在標號名后面加上冒號。 GOTO語句常用在循環(huán)語句和條件語句內，使程序跳出循環(huán)或進行分支處理。15例5-11 求10的階乘。腳本：DECLARE s int,times int set s=1set times=1label1:set s=s*timesset times=times+

7、1if times=10 goto label1print 結果為:+str(s)165.2.4 等待語句 等待語句掛起一個程序中語句的執(zhí)行，直到指定的某一時間點到來或在一定的時間間斷之后才繼續(xù)執(zhí)行。 語法格式： WAITFOR DELAY |TIME 其中，時間間隔以及時間均為datetime類型，格式為“hh:mm:ss”，分別說明等待的時間長度和時間點，在time內不能指定日期。 17例5-12 設置等待一小時后執(zhí)行查詢。腳本： begin waitfor delay 1:00:00 select * from s end 例5-13 設置到十點整執(zhí)行查詢。腳本： begin waitf

8、or time 10:00:00 select * from s end5.2.4 等待語句185.2.5 返回語句 返回語句結束執(zhí)行，使程序無條件返回，其后面的語句不再執(zhí)行。 語法格式： RETURN 存儲過程可以使用RETURN語句向調用者返回一個整數值。在SQL Server 2000中，存儲過程返回值為0時，表示存儲過程成功執(zhí)行。 195.3 分行處理程序游標 在數據庫開發(fā)過程中，使用SELECT語句查詢，得到一個結果集，對這個結果集中的不同數據行，可能要做不同的處理。也就是要逐一處理每一個數據行。游標提供了一種比較好的解決方案。205.3.1 游標概述 1.概念 游標是一種能從包括多

9、條數據記錄的結果集中每次提取一條記錄的機制。2.種類 Transact-SQL游標、 API游標、客戶游標。 211. Transact-SQL游標 Transact-SQL游標是由DECLARE CURSOR語句定義的，主要用在Transact-SQL腳本、存儲過程和觸發(fā)器中。Transact-SQL游標主要用在服務器上，由從客戶端發(fā)送給服務器的Transact-SQL 語句或是批處理、存儲過程、觸發(fā)器中的Transact-SQL進行管理。Transact-SQL游標不支持提取數據塊或多行數據。 222. API游標 API游標支持在OLE DB、ODBC以及DB_library中使用游標函

10、數，主要用在服務器上。每一次客戶端應用程序調用API游標函數，SQL Server的OLE DB提供者、ODBC驅動程序或DB_library的動態(tài)鏈接庫DLL，都會將這些客戶請求傳送給服務器，以對API游標進行處理。 233. 客戶游標 客戶游標主要用在當需要在客戶機上緩存結果集時使用。在客戶游標中有一個默認的結果集，被用來在客戶機上緩存整個結果集。客戶游標僅支持靜態(tài)游標而非動態(tài)游標，由于服務器游標并不支持所有Transact-SQL語句或批處理，所以客戶游標常常僅被用作服務器游標的輔助。因為在一般情況下，服務器游標能支持絕大多數的游標操作。245.3.2 使用游標操作步驟:1. 聲明游標（

11、變量）。2. 打開游標。3. 從一個游標中提取信息。4. 關閉（釋放）游標。251. 聲明游標語法格式：DECLARE INSENSITIVE SCROLL CURSORFOR FOR READ ONLY | UPDATE OF ,.n 26例5-14 聲明標準游標。腳本：declare cur_c cursor for select cno,cname,credit from c例5-15 聲明只讀游標。腳本：declare cur_c cursor for select cno,cname,credit from c for read only例5-16 聲明更新游標。腳本：declare

12、 cur_c cursor for select cno,cname,credit from c for update 1. 聲明游標272. 聲明游標變量語法格式： DECLARE CURSOR建立游標變量與游標之間的關聯(lián)。方法1：先聲明游標和游標變量，然后用SET語句將游標賦給游標變量。 例：DECLARE c1 CURSOR FOR SELECT * FROM s DECLARE cur_var CURSOR SET cur_var = c1 方法2：不聲明游標，直接用SET語句將游標定義信息賦給游標變量。 例： DECLARE cur_var CURSOR SET cur_var =

13、CURSOR FOR SELECT * FROM s當游標變量和游標關聯(lián)后，就可用游標變量代替游標名稱。 283. 打開游標 游標聲明后，如果要從游標中讀取數據，必須打開游標。 語法格式： OPEN GLOBAL |其中，GLOBAL表示要打開的是一個全局游標。294. 讀取游標中的數據 當游標被打開后，就可以從游標中逐行地讀取數據。 語法格式： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar |RELATIVE n|nvar FROM GLOBAL | INTO ,.n 305. 關閉游標使用CLOSE命令關閉游標(2) 自動關閉游標31(1) 使用C

14、LOSE命令關閉游標 處理完游標中數據后，必須關閉游標來釋放數據結果集和定位于數據記錄上的鎖。 語法格式: CLOSE GLOBAL | CLOSE語句可以關閉游標，但不釋放游標的數據結構。如果要再次使用游標，可用OPEN命令重新打開。32(2) 自動關閉游標 如果在聲明游標與釋放游標之間使用了事務結構，則在結束事務時游標會自動關閉。 避免自動關閉的方法是使用語句： SET CURSOR_CLOSE_ON_COMMIT OFF 336. 釋放游標 用CLOSE命令關閉游標并沒有釋放游標占用的數據結構。使用DEALLOCATE命令將釋放游標占用的數據結構，游標使用的任何資源也隨之釋放。 語法格式

15、： DEALLOCATE GLOBAL | 游標的關閉指釋放游標的結果集所占用的資源，游標的釋放指釋放游標占用的所有資源，當然也包括結果集占用的資源。 34例5-17 DEALLOCATE的作用。 腳本： 6. 釋放游標355.3.3 游標應用實例 1. 用于游標的UPDATE語句的語法格式： UPDATE SET = |NULL|,n WHERE CURRENT OF 2. 用于游標的DELETE語句的語法格式： DELETE FROM WHERE CURRENT OF 36例5-18 游標的定位修改。 腳本： 例5-19 游標的定位刪除。 腳本：例5-20 將課程號為“c001”的課程成績上浮5%。 腳本： 5.3.3