第9章T-SQL程序設(shè)計.ppt

1、第九章T-SQL程序設(shè)計,9.1 數(shù)據(jù)與表達式 9.2 函數(shù) 9.3 流程控制命令 9.4 用戶自定義函數(shù) 9.5小結(jié),9.1 數(shù)據(jù)與表達式,9.1.1 數(shù)據(jù)類型 (長度、精度) 字符型、二進制型、邏輯型、日期時間型、數(shù)字型、其他類型 自定義數(shù)據(jù)類型 9.1.2變量 9.1.3注釋符 9.1.4運算符及表達式,9.1.1.7用戶定義數(shù)據(jù)類型,用戶自定義數(shù)據(jù)類型的好處 定義用戶自定義數(shù)據(jù)類型 使用用戶自定義數(shù)據(jù)類型,9.1.1.7 用戶自定義數(shù)據(jù)類型,創(chuàng)建自定義數(shù)據(jù)類型的好處： 利用自定義數(shù)據(jù)類型可使不同表不重復(fù)出現(xiàn)的各列數(shù)據(jù)具有相同的特性。如Student表中學號，SC中學號 如果把規(guī)則和缺省

2、捆綁到一個自定義的數(shù)據(jù)類型上，那么該規(guī)則和缺省也適合于采用此用戶自定義數(shù)據(jù)類型的每個列。,創(chuàng)建用戶自定義數(shù)據(jù)類型,()使用系統(tǒng)存儲過程sp_addtype創(chuàng)建 sp_addtype typename=type_name, phystype= system_data_type ,nulltype=null | not null | identity ,owner=owner_name type_name：是用戶定義的數(shù)據(jù)類型的名字。 Phystype：是用戶自定義數(shù)據(jù)類型所基于的系統(tǒng)數(shù)據(jù)類型，可以包括長度、精度。 nulltype = null_type:用戶自定義數(shù)據(jù)類型的性質(zhì)，分別為允許空值

3、、不允許為空值、具有標識列性質(zhì)，默認為not null 。 owner = owner_name:指定新數(shù)據(jù)類型的創(chuàng)建者或所有者。當沒有指定時，owner_name 為當前用戶。,創(chuàng)建用戶自定義數(shù)據(jù)類型（續(xù)）,例：為Sales數(shù)據(jù)庫創(chuàng)建個不允許為NULL值的test_add用戶定義數(shù)據(jù)類型。 USE Sales GO EXEC sp_addtype test_add,Varchar(10),NOT NULL GO (2)使用SQL Server管理平臺創(chuàng)建自定義數(shù)據(jù)類型 進入SQL Server管理平臺 進入相應(yīng)數(shù)據(jù)庫 右擊“用戶自定義數(shù)據(jù)類型” 點擊“新建用戶自定義數(shù)據(jù)類型” 后出現(xiàn)“用戶自

4、定義數(shù)據(jù)類型”窗口 輸入要定義的數(shù)據(jù)名稱，選擇數(shù)據(jù)類型，輸入數(shù)據(jù)長度，確定是否允許為空 點擊“確定”。,用戶自定義類型的使用,1.查看用戶自定義數(shù)據(jù)類型 可以用系統(tǒng)存儲過程sp_help或管理平臺來查看用戶定義的數(shù)據(jù)類型的特征。語法格式： exec sp_help 自定義數(shù)據(jù)類型名稱 2.刪除用戶定義數(shù)據(jù)類型 使用過程sp_droptype 來刪除一個已經(jīng)定義且未被使用的用戶自定義數(shù)據(jù)類型。語法格式： exec sp_droptype 自定義數(shù)據(jù)類型名 必須注意不能刪除正在被表或其他數(shù)據(jù)庫使用的用戶自定義數(shù)據(jù)類型。,9.1.2變量,類型 局部變量 局部變量一般在批處理中被聲明、定義、賦值和引用

5、，批處理結(jié)束后，局部變量就消失了。 可以保存程序執(zhí)行過程中的中間數(shù)據(jù)值，保存由存儲過程返回的數(shù)據(jù)值等。 全局變量 全局變量在整個SQL Server系統(tǒng)內(nèi)使用。存儲的通常是一些SQL Server的配置設(shè)定值和統(tǒng)計數(shù)據(jù)。 操作 定義（聲明） 賦值 顯示 引用,局部變量,1定義 必須先用DECLARE命令定義后才可以使用。 DECLARE variable_name data_type ,variable_name data_type 初值為NULL 2賦值 SET variable_name = expression SELECT variable_name = expression ,var

6、iable_name = expression .,局部變量,例 DECLARE int_var int SELECT int_var =12 /*給int_var賦值*/ SELECT int_var /*將int_var的值輸出到屏幕上*/ Print a SET與SELECT 命令區(qū)別： SET 命令一次只能給一個變量賦值 例 DECLARE a int,b int SELECT a =12,b=13 SET a=12 SET b=13,變量（續(xù)）,3.局部變量的顯示 select variable_name 例 定義一個整型局部變量和一個可變長字符型局部變量并賦值。 declare v

7、ariable_int int ,variable_char varchar(20) set variable_int=40 set variable_char=Welcome to Shijiazhuang select variable_int select variable_char,全局變量,由系統(tǒng)提供且預(yù)先聲明的變量,任何程序均可以調(diào)用,但不能顯式的被聲明或賦值 存儲SQL SERVER的配置設(shè)定值和統(tǒng)計數(shù)據(jù) 用戶可以在程序中用全局變量來測試系統(tǒng)的設(shè)定值或者命令執(zhí)行后的狀態(tài)值。 ERROR, VERSION, SERVERNAME見P172表9-2 用select命令來顯示其值的 全

8、局變量是在服務(wù)器級定義的。 用戶只能使用預(yù)先定義的全局變量。 引用全局變量時，必須以標記符“”開頭。 全局變量對用戶來說是只讀的。 局部變量的名稱不能與全局變量的名稱相同,error P173,如果error為非0值，則表明執(zhí)行過程中產(chǎn)生了錯誤，此時應(yīng)當在程序中采取相應(yīng)的措施加以處理。 error的值與rowcount一樣，會隨著每一條SQL Server語句的變化而改變。 例9-4 使服務(wù)器產(chǎn)生服務(wù)，并顯示錯誤號。 raiserror(miscellaneous error message,16,1) /*產(chǎn)生一個錯誤*/ if error0 SELECT error as last err

9、or 運行結(jié)果： 服務(wù)器: 消息 50000，級別 16，狀態(tài) 1，行 1 miscellaneous error message last error 0,error,例9-5 捕捉例9-4中服務(wù)器產(chǎn)生的錯誤號，并顯示出來。,DECLARE my_error int RAISERROR(miscellaneous error message,16,1) SELECT my_error=error IF my_error0 SELECT my_error as last error 運行結(jié)果： 服務(wù)器: 消息 50000，級別 16，狀態(tài) 1，行 2 miscellaneous error m

10、essage last error 50000,9.1.3注釋符,1單行注釋： “-” 在語句中，使用兩個連字符“-”開頭，則從此開始的整行或者行的一部分就成為了注釋，注釋在行的末尾結(jié)束。 -This is a comment.Whole line will be ignored. SELECT employee_name, address -查詢所有姓錢的員工 FROM employee WHERE employee_name LIKE 錢% 注釋的部分不會被SQL Server執(zhí)行。,多行注釋,2塊注釋： “/*/”，/* 用于注釋文字的開頭，*/用于注釋文字的結(jié)尾，可在程序中標識多行文字

11、為注釋。 /* This is a commnet All these lines will be ignored. */ /* List all employees.*/ SELECT * FROM employee 注釋并沒有長度限制。SQL Server文檔禁止嵌套多行注釋，但單行注釋可以嵌套在多行注釋中。 /* -List all employees. SELECT * FROM employee */,9.1.4 運算符與表達式,1 算術(shù)運算符與表達式 2 位運算符與表達式 3 比較運算符與表達式 4 邏輯運算符與表達式 5 連接運算符與表達式 6 運算符的優(yōu)先級,9.1.4.1 算

12、術(shù)運算符與表達式,算術(shù)表達式 5+2 5*2 5%2,9.1.4.2位運算符與表達式,位表達式 52, 5&2,5|2 其結(jié)果為：7, 0,7,按位與（&）:兩個位為時，結(jié)果為，否則為 按位或（|）：只要一個位為時，結(jié)果為，否則為 按位異或（）：兩個位值不同時為，否則為 位運算符用來對整型數(shù)據(jù)或者二進制數(shù)據(jù)（image數(shù)據(jù)類型除外）之間執(zhí)行位操作。要求在位運算符左右兩側(cè)的操作數(shù)不能同時是二進制數(shù)據(jù)。,9.1.4.3比較運算符與表達式,比較運算結(jié)果為邏輯值 如:2=3的結(jié)果為false,9.1.4.4 邏輯運算符與表達式,邏輯運算符 NOT:取反 AND:兩個操作數(shù)全真為真,否則為假 OR:兩個

13、操作數(shù)全假為假,否則為真 如： NOT TRUE TRUE AND FALSE TRUE OR FALSE,9.1.4.5字符串串聯(lián)運算符及表達式,”+” 表達式 中國+ 共產(chǎn)黨 結(jié)果:中國共產(chǎn)黨 Dr.+Computer 運算結(jié)果:Dr. Computer,9.1.4.6 運算符的優(yōu)先級,括號：（）； 取反： 位運算符：，&，|； 乘、除、求模運算符：*，/，%； 加減運算符：，； 邏輯運算符：NOT； 邏輯運算符：AND； 邏輯運算符：OR SELECT 52+3*2,9.2函數(shù),函數(shù) 函數(shù)是組編譯好的Transact-SQL語句，它們可以帶一個或一組數(shù)值做參數(shù)，也可不帶參數(shù)，它返回一個數(shù)

14、值、數(shù)值集合，或執(zhí)行一些操作。 函數(shù)能夠重復(fù)執(zhí)行一些操作，從而避免不斷重寫代碼。 內(nèi)置函數(shù) 1字符串函數(shù) 2數(shù)學函數(shù) 3日期時間函數(shù) 4類型轉(zhuǎn)換函數(shù) 5系統(tǒng)函數(shù) 用戶自定義函數(shù),9.2.1 字符串函數(shù),功能：字符串函數(shù)用來實現(xiàn)對字符型數(shù)據(jù)的轉(zhuǎn)換、查找、分析等操作，通常用做字符串表達式的一部分。 取子串函數(shù) LEFT(),RIGHT(),SUBSTRING() SELECT LEFT(ABC,2), RIGHT(ABC,2), SUBSTRING(ABC,2,1),9.2.1 字符串函數(shù),字符串長度計算 LEN(),DATALENGTH() datalength函數(shù)主要用于判斷可變長字符串的長度

15、，對于定長字符串將返回該列的長度。要得到字符串的真實長度，通常需要使用rtrim函數(shù)截去字符串尾部的空格。 Len函數(shù)可以獲取字符串的字符個數(shù)，而不是字節(jié)數(shù)，也不包含尾隨空格。 SELECT LEN(A ),LEN( A), LEN(ABC) SELECT DATALENGTH(ABC) ,DATALENGTH( ),9.2.1 字符串函數(shù)（續(xù)）,大小寫轉(zhuǎn)換函數(shù) UPPER(),LOWER() SELECT UPPER(Abc),LOWER(ABC) 重復(fù)字符函數(shù) SPACE(),REPLICATE() SELECT DATALENGTH (SPACE(5), REPLICATE(AB,3)

16、去除首尾空格函數(shù) LTRIM(),RTRIM() SELECT LTRIM( ABC ),RTRIM( ABC ),9.2.1 字符串函數(shù)（續(xù)）,字符串替換函數(shù) STUFF() SELECT STUFF(aBCde,2,2,bc) 字符串轉(zhuǎn)置函數(shù) REVERSE() SELECT REVERSE(ABC),9.2.1字符串函數(shù)（續(xù)）,字符串查找函數(shù) CHARINDEX() 用于在串內(nèi)找出與指定串匹配的串，如果找到的話，返回第一個匹配的位置 Charindex(expr1, expr2, start_location) expr1是待查找的字符串 expr2是用來搜索expr1的字符表達式， s

17、tart_location是在expr2中查找expr1的開始位置，如果此值省略、為負或為0，均從起始位置開始查找。 SELECT CHARINDEX(bc,abcde),Patindex 返回在指定表達式中模式第一次出現(xiàn)的起始位置，如果模式?jīng)]有，則返回0 Patindex(%pattern%, expression) pattern是字符串，%字符必須出現(xiàn)在模式的開頭和結(jié)尾。expression通常是搜索指定子串的表達式或列。 Select patindex(%abc%,abc123),patindex(123,abc123),9.2.1 常用函數(shù),9.2.2 數(shù)學函數(shù),數(shù)學函數(shù)用來實現(xiàn)各種

18、數(shù)學運算，如指數(shù)運算、對數(shù)運算、三角運算等，其操作數(shù)為數(shù)值型數(shù)據(jù)，如int、float、real、money等 表9-8列出了SQL Server的數(shù)學函數(shù)。,9.2.2 數(shù)學函數(shù),數(shù)學函數(shù)示例,SELECT ROUND(12.3456,2) SELECT LOG10(100) SELECT CEILING(12.5),FLOOR(12.5),9.2.1 常用函數(shù),9.2.3 日期時間函數(shù),日期函數(shù)用來操作datetime和smalldatetime類型的數(shù)據(jù)，執(zhí)行算術(shù)運算。與其他函數(shù)一樣，可以在SELECT語句和WHERE子句以及表達式中使用日期函數(shù)。,9.2.1 常用函數(shù),表9-9 SQL

19、 Server的日期函數(shù),9.2.1 常用函數(shù),表9-10 SQL Server的日期部分(P182),9.2.3 日期時間函數(shù),獲取當前日期時間函數(shù) GETDATE(),CURRENT_TIMESTAMP SELECT GETDATE(),CURRENT_TIMESTAMP 日期計算(例9-14) DATEDIFF(),DATEADD() DATEDIFF(格式,DATE1,DATE2) DATEADD(格式,NUMBER,DATE2) 例9-14:使用Datediff函數(shù)來確定貨物是否按時送給客戶。 Select goods_id,Datediff(dd,send_date,arrival

20、_date) from purchase_order,9.2.3 日期時間函數(shù),日期處理(例9-15) DATEPART(),DATENAME() SELECT DATEPART(YY,GETDATE(), DATENAME(DW,GETDATE() 例9-15：使用Datename函數(shù)返回員工的出生日期的月份(mm)名稱。 Select employee_name,(Datename(mm,birth_date) from employee,9.2.3 日期時間函數(shù),獲取當前日期時間的年、月、日的值 YEAR(),MONTH(),DAY() SELECT YEAR(GETDATE(), MO

21、NTH(GETDATE(), DAY(GETDATE(),9.2.1 常用函數(shù),系統(tǒng)函數(shù),系統(tǒng)函數(shù)用于獲取有關(guān)計算機系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息。與其他函數(shù)一樣，可以在SELECT和WHERE子句以及表達式中使用系統(tǒng)函數(shù)。 表9-11列出了SQL Server的系統(tǒng)函數(shù)。(P184),9.2.1 常用函數(shù),Round函數(shù),Round(numeric_expr, int_expr): 把表達式四舍五入到int_expr 指定的精度。 int_expr為負數(shù)時，將小數(shù)點左邊第int_expr位四舍五入。 例9-13 Select round(12.34512,3), round(12.34

22、567,3), round(12.345,-2), round(154.321,-2),用戶自定義函數(shù),類型 標量函數(shù) 內(nèi)嵌表值函數(shù) 多語句表值函數(shù) 創(chuàng)建 調(diào)用 刪除,標量函數(shù),含義 返回一個確定類型的標量值。 其函數(shù)類型為SQL Server2005的系統(tǒng)數(shù)據(jù)類型 可接受多個參數(shù) 函數(shù)體語句定義在BEGINEND語句內(nèi)。 創(chuàng)建 調(diào)用,創(chuàng)建標量函數(shù),語法： CREATEFUNCTION Functionname(Parameters) Returns Datatype AS BEGIN Sqlstatement RETURN Expression END,例9-18 創(chuàng)建一個用戶定義函數(shù)Dat

23、etoQuarter，將輸入的日期數(shù)據(jù)轉(zhuǎn)換為該日期對應(yīng)的季度值。如輸入2006-8-5，返回3Q2006，表示2006年3季度。,CREATE FUNCTION DatetoQuarter(dqdate datetime) RETURNS char(6) AS BEGIN RETURN(datename(qq,dqdate)+Q+datename(yy,dqdate) END,標量函數(shù)舉例,內(nèi)嵌表值函數(shù),返回值是一個表，不使用BEGINEND語句，返回的表是return中的select命令查詢的結(jié)果。相當于一個參數(shù)化的視圖。 創(chuàng)建內(nèi)嵌表值函數(shù)語法： CREATEFUNCTION Functio

24、nname(Parameters) Returns Table AS RETURN （Sqlstatement）,內(nèi)嵌表值函數(shù),例9-19 創(chuàng)建用戶定義函數(shù)goodsq，返回輸入商品編號的商品名稱和庫存量。 CREATE FUNCTION goodsq(goods_id varchar(30) RETURNS TABLE AS RETURN(SELECT goods_name,stock_quantity FROM goods WHERE goods_id =goods_id),執(zhí)行用戶定義函數(shù),使用用戶定義函數(shù)的方法和使用內(nèi)置函數(shù)一樣 例： SELECT dbo. DatetoQuarter

25、(2009-5-18) SELECT * from dbo. Goodsq(g00002),多語句表值函數(shù),創(chuàng)建一個表變量（返回值在多個表中） 創(chuàng)建語法 CREATE FUNCTION FunctionName(parameters) RETURNS Tablename Table(Columns) AS BEGIN Insert sqlstatement RETURN END,例9-20 根據(jù)輸入的訂單編號，返回該訂單對應(yīng)商品的編號、名稱、類別編號、類別名稱,CREATE FUNCTION good_info(in_o_id varchar(10) RETURNS goodinfo TABL

26、E (o_id char(6), g_id char(6), g_name varchar(50), c_id char(6), c_name varchar(20) AS BEGIN DECLARE g_id varchar(10),g_name varchar(30) DECLARE c_id varchar(10),c_name varchar(30) SELECT g_id=goods_id FROM sell_order WHERE order_id1=in_o_id SELECT g_name=goods_name,c_id=classification_id FROM goods

27、 WHERE goods_id=g_id SELECT c_name=classification_name FROM goods_classification WHERE c_id=classification_id INSERT goodinfo VALUES(in_o_id,g_id,g_name,c_id,c_name) RETURN END,執(zhí)行用戶定義函數(shù),使用用戶定義函數(shù)的方法和使用內(nèi)置函數(shù)一樣 例： SELECT * from dbo. Good_info(s00002),修改和刪除用戶定義函數(shù),修改：ALTER FUNCTION 刪除： DROP FUNCTION,9.3流程

28、控制語句,BEGINEND IFELSE CASE GOTO WHLIE、BREAK和CONTINUE RETURN WAITFOR 批處理與腳本,9.3.1BEGINEND,其語法格式： BEGIN 命令行|程序塊 END BEGINEND 用來設(shè)定一個程序塊，程序塊內(nèi)的所有程序視為一個單元執(zhí)行。 BEGINEND 經(jīng)常在條件語句（如IFELSE）中使用。 在BEGINEND 中可嵌套另外的BEGINEND 來定義另一程序塊。,9.3.2IF ELSE,語法格式： IF 命令行|程序塊 ELSE 命令行|程序塊 其中: 可以是各種表達式的組合，但表達式的值必須是邏輯值“真”或“假”。 ELS

29、E 子句是可選的，最簡單的IF 語句沒有ELSE 子句部分。 IFELSE 用來判斷當某一條件成立時執(zhí)行某段程序，條件不成立時執(zhí)行另一段程序。 如果不使用程序塊，IF 或ELSE 只能執(zhí)行一條命令。 IF ELSE 可以進行嵌套，在Transact-SQL 中最多可嵌套32 級。,9.3.3CASE語句,CASE 語句有兩種語句格式： 格式1：簡單CASE語句 CASE WHEN THEN WHEN THEN ELSE END 該語句的執(zhí)行過程是： 將CASE后面表達式的值與各WHEN子句中的表達式的值進行比較， 如果二者相等，則返回THEN后的表達式的值，然后跳出CASE語句，否則返回ELS

30、E子句中的表達式的值。 ELSE子句是可選項。當CASE語句中不包含ELSE子句時，如果所有比較失敗時，CASE語句將返回NULL。,簡單Case語句舉例,例9-26 使用簡單CASE函數(shù)將goods表中的商品分類重命名，以使之更易理解。,SELECT CASE classification_id WHEN P001 THEN 筆記本計算機 WHEN P002 THEN 激光打印機 WHEN P003 THEN 噴墨打印機 WHEN P004 THEN 交換機 ELSE 沒有這種品牌 END AS Classification, goods_name AS Goods Name, unit_p

31、rice AS Price FROM goods WHERE unit_price IS NOT NULL,CASE格式,格式2： CASE WHEN THEN WHEN THEN ELSE END 該語句的執(zhí)行過程是： 首先測試WHEN后的表達式的值 如果其值為真，則返回THEN后面的表達式的值，否則測試下一個WHEN子句中的表達式的值 如果所有WHEN子句后的表達式的值都為假，則返回ELSE后的表達式的值 如果在CASE語句中沒有ELSE子句，則CASE表達式返回NULL。,Case 搜索函數(shù)舉例,SELECT goods_name AS 商品名稱, CASE WHEN stock_qua

32、ntity-order_quantity3 and stock_quantity-order_quantity10 THEN 貨物充足 END AS 進貨判斷 FROM goods,例9-27 根據(jù)goods表中庫存貨物數(shù)量與訂貨量之差，使用CASE搜索函數(shù)判斷該商品是否進貨。,9.3.4WHILECONTINUEBREAK,其語法如下： WHILE BEGIN BREAK CONTINUE 命令行或程序塊 END WHILE 命令在設(shè)定的條件成立時，會重復(fù)執(zhí)行命令行或程序塊。 CONTINUE 命令可以讓程序跳過CONTINUE 命令之后的語句，回到WHILE 循環(huán)的第一行，繼續(xù)進行下一次循

33、環(huán)。 BREAK 命令則讓程序完全跳出循環(huán)，結(jié)束WHILE 命令的執(zhí)行。 WHILE 語句也可以嵌套。,WHILECONTINUEBREAK(圖),CONTINUE,BREAK,WHILE,Y,N,9.3.6WAITFOR,其語法如下： WAITFOR DELAY | TIME WAITFOR 命令用來暫時停止程序執(zhí)行，直到所設(shè)定的等待時間已過或所設(shè)定的時間已到才繼續(xù)往下執(zhí)行。 其中時間必須為DATETIME 類型的數(shù)據(jù)，但不能包括日期。 各關(guān)鍵字含義如下： 1DELAY：用來設(shè)定等待的時間，最多可達24 小時 2TIME：用來設(shè)定等待結(jié)束的時間點 【例】等待1 小時2 分零3 秒后才執(zhí)行SE

34、LECT 語句。 waitfor delay 01:02:03 select * from students,9.3.7RETURN,語法如下： RETURN 整數(shù)值 RETURN 命令用于結(jié)束當前程序的執(zhí)行，返回到上一個調(diào)用它的程序或其它程序。 在括號內(nèi)可指定一個返回值，只能在過程中使用。 如果沒有指定返回值，SQL Server 系統(tǒng)會根據(jù)程序執(zhí)行的結(jié)果返回一個內(nèi)定值.,例9-29 RETURN語句應(yīng)用示例。,DECLARE x int,y int SELECT x=1,y=2 IF xy RETURN ELSE RETURN,9.3.5GOTO,語法如下： GOTO label Label: GOTO 命令用來改變程序執(zhí)行的流程，使程序跳到標有標識符的指定的程序行再繼續(xù)往下執(zhí)行。 作為跳轉(zhuǎn)目標的標識符可為數(shù)字與字符的組合。但必須以“：”結(jié)尾。 在GOTO 命令行，標識符后不必跟“：” 例9-28,Goto舉例,DECLARE x int SELECT x=1 label_1: SELECT x SELECT x=x+1 IF x6 GOTO label_1,9.3.8批的概念（batch）,批處理 1:所謂批處理是從客戶機傳遞到服務(wù)器上的一組完整的數(shù)據(jù)和sql指令，一個批處理是條或多條Transact-SQL語句的集合。 2:批處理的所有語句被作為一個整體而被成組分