SQL數(shù)據(jù)庫應用(第七章)

1、第七章 Transact-SQL程序設計學時：6目標學習T-SQL要素，掌握其使用方法，掌握游標的使用重點T-SQL程序設計難點命令的使用SQL數(shù)據(jù)庫應用第七章 Transact-SQL程序設計7.1 Transact-SQL基礎7.2 Transact-SQL要素7.3 使用游標Transact-SQL程序設計使用Transact-SQL進行程序設計是SQL Server的主要應用形式之一。不論是普通的客戶機/服務器應用程序，還是Web應用程序，都應該對涉及數(shù)據(jù)庫中數(shù)據(jù)進行的處理描述成Transact-SQL語句，并通過向服務器端發(fā)送Transact-SQL語句才能實現(xiàn)與SQL Server

2、的通信。本章將首先介紹Transact-SQL的基本知識，然后在此基礎上介紹Transact-SQL基本要素、流程控制語句和游標的使用。Transact-SQL程序設計視圖是一個虛擬表，其結構和數(shù)據(jù)是建立在對表的查詢基礎上的。和表一樣，視圖也包括幾個被定義的數(shù)據(jù)列和多個數(shù)據(jù)行，但就本質(zhì)而言這些數(shù)據(jù)列和數(shù)據(jù)行來源于其所引用的表，所以視圖不是真實存在的基表，而是一張?zhí)摫?。視圖所對應的數(shù)據(jù)并不以實際視圖結構存儲在數(shù)據(jù)庫中，而是基表中數(shù)據(jù)的一個映射。7.1 基礎 Transact-SQL（簡稱T-SQL）語言是SQL Server使用的一種數(shù)據(jù)庫查詢和編程語言，是結構化查詢語言SQL的增強版本，增加了

3、一些非標準的SQL語句，使其功能更強大。 使用T-SQL語句可建立、修改、查詢和管理關系數(shù)據(jù)庫，也可以把T-SQL語句嵌入到某種高級程序設計語言（如VB、VC、DELPHI）中。7.1 基礎 T-SQL的基本成分是語句，由一個或多個語句可以構成一個批處理，由一個或多個批處理可以構成一個查詢腳本（以sql作為文件擴展名）并保存到磁盤文件中，供以后需要時使用。使用語句數(shù)據(jù)定義語言 (DDL) 語句數(shù)據(jù)操作語言 (DML) 語句數(shù)據(jù)控制語言 (DCL) 語句7.2 要素 批處理就是單個或多個T-SQL語句的集合，由應用程序一次性發(fā)送給SQL Server解釋并執(zhí)行批處理內(nèi)的所有語句指令。使用GO命令

4、和使用EXECUTE命令可以將批處理發(fā)送給SQL Server。1、go命令一個批處理的結束標志。2、exec命令用于執(zhí)行用戶定義的函數(shù)以及存儲過程。7.2.1 批處理7.2 要素注釋是程序代碼中不執(zhí)行的文本字符串。它起到注解說明代碼或暫時禁用正在進行診斷調(diào)試的部分語句和批處理的作用。1、行內(nèi)注釋 -注釋文本2、塊注釋 /* 注釋文本 */7.2.2 注釋語句7.2 要素 標識符是用戶編程時使用的名字。我們指定某個東西、人，都要用到它，他或她的名字；在數(shù)學中解方程時，我們也常常用到這樣或那樣的變量名或函數(shù)名。同樣的道理，在電腦語言中，對于變量，常量，函數(shù)，語句塊也有名字，我們統(tǒng)統(tǒng)稱之為標識符。

5、7.2.3 標識符7.2 要素1、標準標識符 標準標識符也稱為常規(guī)標識符，它包含1128個字符，以字母（az或AZ）、下劃線（_）、或#開頭，后續(xù)字符可以是ASCII字符、Unicode字符、符號（_、$、或#），但不能全為下劃線（_）、或#。2、分隔標識符 分隔標識符是包含在雙引號（）或中括號（）內(nèi)的標準標識符或不符合標準標識符規(guī)則的標識符。7.2.3 標識符7.2 要素1、全局變量 全局變量以開頭，由系統(tǒng)定義和維護，不能由用戶創(chuàng)建，對用戶來說是只讀的，大部分的全局變量記錄了SQL Server服務器的當前狀態(tài)信息。全局變量是不可以賦值的。2、局部變量 局部變量以開頭，由用戶定義和賦值，指在

6、T-SQL批處理和腳本中用來保存數(shù)據(jù)值的對象。此外，還允許用table數(shù)據(jù)類型的局部變量來代替臨時表。但不能指定局部變量為text、ntext、image數(shù)據(jù)類型7.2.4 全局變量與局部變量7.2 要素1、全局變量【例7.5】 使用ERROR變量在一個UPDATE語句中檢測限制檢查沖突（錯誤代碼為#547）。USE SalesGO-將編號為1001的員工編號更新為1100UPDATE GoodsSET 進貨員工編號=1100WHERE 進貨員工編號=1001-檢查是否出現(xiàn)限制檢查沖突IF ERROR=547 PRINT 出現(xiàn)限制檢查沖突，請檢查需要更新的數(shù)據(jù)限制GO7.2.4 全局變量與局部

7、變量（1）局部變量的聲明DECLARE 局部變量名 數(shù)據(jù)類型,n（2）局部變量的賦值。使用SET語句賦值的語法格式為： SET 局部變量名=表達式,n使用SELECT語句賦值的語法格式為： SELECT 局部變量名=表達式,n【例7.9】 聲明一個名為now的局部變量并賦值，用此變量返回當前系統(tǒng)的日期和時間。-聲明兩個局部變量DECLARE now datetime-對局部變量賦值SET now=GETDATE()-顯示局部變量的值SELECT now7.2.4 全局變量與局部變量【例7.10】 本例演示了使用查詢給變量賦值的方法。USE SalesGODECLARE cnt intSET c

8、nt=(SELECT count(編號) FROM employees) /*使用查詢給變量賦值，注意這里的查詢語句只能在返回單個值的情況下才能賦值成功*/SELECT cnt AS 公司員工總數(shù) /*將員工總數(shù)值輸出到屏幕*/GO7.2.4 全局變量與局部變量7.2 要素1運算符運算符是執(zhí)行數(shù)學運算、字符串連接以及比較操作的一種符號。（1）算術運算符（2）比較運算符（3）邏輯運算符 （4）字符串串聯(lián)運算符 （5）按位運算符 （6）賦值運算符 （7）一元運算符7.2.5 運算符和表達式7.2 要素1運算符 在SQL Server 2005中，運算符的優(yōu)先等級從高到低如下所示。7.2.5 運算符

9、和表達式運 算 符優(yōu) 先 級 別+（正）、（負）、（按位NOT）一元運算符1*（乘）、/（除）、%（取模）算術運算符2+（加）、（減）算數(shù)運算符3=(等于)、=、=、!=、!、!0 PRINT 還有貨ELSE PRINT 無庫存，請盡快進貨!7.2.5 運算符和表達式【例7.14】 本例演示邏輯運算符ALL的使用。查詢單筆商品銷售量高于王峰最高銷售量的員工姓名、所銷售的商品名稱、售出時間及銷售量。USE SalesGOSELECT 姓名,商品名稱,售出時間,Sell.數(shù)量FROM Employees JOIN Sell ON Employees.編號=Sell.售貨員工編號 JOIN Good

10、s ON Goods.商品編號=Sell.商品編號WHERE Sell.數(shù)量ALL(SELECT Sell.數(shù)量 FROM Employees JOIN Sell ON Employees.編號=Sell.售貨員工編號 JOIN Goods ON Goods.商品編號=Sell.商品編號 WHERE 姓名=王峰)【例7.15】 本例演示多個字符串的串聯(lián)。USE SalesGOSELECT 姓名+ : 電話 AS 姓名及電話FROM Employees7.2 要素 使用T-SQL編程的時候，常常要利用各種流程控制語句去進行順序、分支控制轉移、循環(huán)等操作。T-SQL提供了一組流程控制語句條件控制語

11、句無條件控制語句循環(huán)語句返回狀態(tài)值給調(diào)用例程的語句7.2.6 流程控制語句7.2 要素7.2.6 流程控制語句語 句說 明BEGINEND定義一個語句塊IFELSE如果條件成立，執(zhí)行一個分支，否則執(zhí)行另一個分支WHILE基本循環(huán)語句，指定條件為真時重復執(zhí)行一條語句或語句塊BREAK退出最內(nèi)層的WHILE循環(huán)CONTINUE退出本次WHILE循環(huán)，重新開始一個WHILE循環(huán)CASE(表達式)允許表達式按照條件返回不同的值GOTO轉到指定標簽語句處繼續(xù)執(zhí)行RETURN無條件退出語句。可以給調(diào)用的過程或應用程序返回整型值TRYCATCH錯誤處理語句WAITFOR為語句執(zhí)行設置時間。時間可以是一個延時

12、，也可以是一天中的某個時間點【例7.16】 在WHILE循環(huán)中，包含兩條語句，需要BEGINEND語句將這兩條語句封閉起來組成一個語句塊。DECLARE counter intSELECT counter=0WHILE counter10BEGIN SELECT counter=counter+1 -計數(shù)器循環(huán)累加 PRINT counter -將計數(shù)器的值顯示出屏幕來END7.2.6 流程控制語句【例7.17】 測試Sales數(shù)據(jù)庫的Goods表中是否有“HP噴墨打印機photosmart 7268”這種商品，有的話顯示該商品的信息，否則顯示“該商品無進貨記錄！”USE SalesGODEC

13、LARE Goodname varchar(20)SET Goodname=HP噴墨打印機photosmart 7268IF EXISTS(SELECT * FROM Goods WHERE 商品名稱=Goodname) BEGIN PRINT Goodname+商品的信息如下： SELECT * FROM Goods WHERE 商品名稱=Goodname ENDELSE BEGIN PRINT 該商品無進貨記錄！ ENDGO【例7.18】 判斷Sales數(shù)據(jù)庫是否有商品的庫存量少于10，如果有，則將每一商品都入貨50，直到所有商品的庫存量都多于10或者有商品的庫存量超過200。USE Sa

14、lesGOWHILE EXISTS(SELECT * FROM Goods WHERE 數(shù)量200 BREAK ENDGO7.2.6 流程控制語句【例7.19】 在Sales數(shù)據(jù)庫中查詢每個員工在2005年1月的銷售商品數(shù)量并發(fā)布獎罰信息，銷售商品數(shù)量低于30的為不合格員工，扣除當月提成；30到60之間的，為合格員工；高于60的，為優(yōu)秀員工，憑多出的銷量獲取獎金；其他情況視為無銷量記錄。USE SalesGOSELECT 售貨員工編號,姓名,獎罰信息= CASE WHEN sum(數(shù)量)=30 AND sum(數(shù)量)=60 THEN 優(yōu)秀員工，憑多出的銷量獲取獎金 ELSE 無銷售記錄 END

15、 FROM Sell JOIN Employees ON Employees.編號=Sell.售貨員工編號 WHERE YEAR(售出時間)=2005 AND MONTH(售出時間)=1 GROUP BY 售貨員工編號,姓名GO【例7.20】 在sales數(shù)據(jù)庫中，刪除編號是“1301”的員工信息。USE salesGOBEGIN TRY DELETE FROM employees WHERE 編號=1301END TRYBEGIN CATCH PRINT 出錯信息為：+error_message()End catch7.2.6 流程控制語句【例7.21】（1）使用DELAY關鍵字指定在執(zhí)行S

16、ELECT語句之前等待5秒。USE SalesGOWAITFOR DELAY 00:00:05SELECT 商品名稱,生產(chǎn)廠商,進貨價FROM GoodsWHERE 進貨時間=2005-1-1GO（2）使用TIME關鍵字指定在9時25分50秒執(zhí)行SELECT語句。USE SalesGOWAITFOR TIME 9:25:50SELECT 商品名稱,生產(chǎn)廠商,進貨價FROM GoodsWHERE 進貨時間=2005-1-1GO7.3 使用游標 游標（cursor）是系統(tǒng)為用戶開設的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結果。每個游標區(qū)都有一個名字。用戶可以用SQL語句逐一從游標中獲取記錄，并賦給主

17、變量，交由主語言進一步處理。 在數(shù)據(jù)庫開發(fā)過程中，當你檢索的數(shù)據(jù)只是一條記錄時，你所編寫的事務語句代碼往往使用SELECT INSERT 語句。但是我們常常會遇到這樣情況，即從某一結果集中逐一地讀取一條記錄。那么如何解決這種問題呢？游標為我們提供了一種極為優(yōu)秀的解決方案。 7.3 使用游標游標的概念游標提供了一種從表中檢索數(shù)據(jù)并進行操作的靈活手段，游標主要用在服務器上，處理由客戶端發(fā)送給服務器端的SQL語句，或是批處理、存儲過程、觸發(fā)器中的數(shù)據(jù)處理請求。游標的優(yōu)點在于它可以定位到結果集中的某一行，并可以對該行數(shù)據(jù)執(zhí)行特定操作，為用戶在處理數(shù)據(jù)的過程中提供了很大方便。一個完整的游標由5部分組成，

18、并且這5個部分應符合下面的順序。（1）聲明游標。（2）打開游標。（3）從一個游標中查找信息。（4）關閉游標。（5）釋放游標。7.3 使用游標 1SQL-92標準的游標聲明 基于SQL-92標準的游標聲明語句的語法格式為： DECLARE 游標名稱 INSENSITIVE SCROLL CURSOR FOR SELECT語句 FOR READ ONLY | UPDATEOF 字段名,n【例7.22】 本例是符合SQL-92標準的游標聲明語句。聲明一個游標，用于訪問Sales數(shù)據(jù)庫中的所有進貨商品信息。USE SalesGO-聲明游標DECLARE Goods_cursor CURSOR FOR

19、SELECT * FROM Goods FOR READ ONLY7.3.1 游標聲明7.3 使用游標2T-SQL擴展標準的游標聲明基于T-SQL標準的游標聲明語句的語法格式為：DECLARE 游標名稱 CURSORLOCAL|GLOBAL -指定游標的作用范圍FORWARD_ONLY|SCROLL -指定游標的移動方向STATIC|KEYSET|DYNAMIC|FAST_FORWARD -指定游標的4種類型READ_ONLY|SCROLL_LOCKS|OPTIMISTIC -指定游標或基表的訪問屬性TYPE_WARNING -指定如果游標從所請求的類型隱形轉換為另一種類型， 則給客戶端發(fā)送警

20、告信息FOR SELECT語句 -定義游標的結果集FOR UPDATE OF 字段名,n -指定游標中可以更新的字段7.3.1 游標聲明7.3 使用游標【例7.23】 聲明一個全局滾動動態(tài)游標sales_cursor，它用于獲取所有員工銷售“9”號商品的信息，其中包括員工姓名、銷售數(shù)量和售出時間3列。USE SalesGODECLARE sales_cursor CURSORGLOBAL SCROLL DYNAMICFOR SELECT 姓名,數(shù)量,售出時間 FROM Employees JOIN Sell ON Employees.編號=Sell.售貨員工編號 WHERE 商品編號=97.3.1 游標聲明7.3 使用游標1打開游標OPEN global 游標名稱2讀取數(shù)據(jù)FETCH NEXT|FRIOR|FIRST|LAST|ABSOLUTEn|var|RELATIVEn|varFROMGLOBAL游標名稱INTO 變量名，n7.3.2 打開和讀取游標7.3 使用游標從游標中讀取數(shù)據(jù)SQL語句如下：USE 銷售管理系統(tǒng) -引入數(shù)據(jù)庫DECLARE ReadCursor CURSOR FOR -聲明一個游標SELECT 操作員編號,操作員姓名,操作員性別,操作員住址FROM 操作員信息表OPEN ReadCursor -打開游標FETCH NEXT FRO