第9章TransactSQL程序設(shè)計(jì)

1、第 9 章 transact-sql程序設(shè)計(jì)exec sp_addtype test_add,'varchar(10)','not null'declare int_var intselect int_var =12/*給 int_var 賦值 */select int_var /*將 int_var 的值輸出到屏幕上*/在一條語(yǔ)句中可以同時(shí)對(duì)幾個(gè)變量進(jìn)行賦值，例如，declare lastname char(8),firstname char(8),birthdate datetimeselect lastname='smith',firstn

2、ame='david',birthdate='1985 - 2- 20'select lastname,firstname,birthdate【例 9- 1】使用 select 語(yǔ)句從 customer 表中檢索出顧客編號(hào)為c0002 的行，再將顧客的名字賦給變量customer。declare customer varchar(40),curdate datetimeselect customer=customer_name,curdate=getdate()from customerwhere customer_id='c0002'【例9-

3、2】將 sell_order 表中的 transporter_id 列值為 “ t001 ”、 goods_id 列值為 “ g00003”的 order_num 列的值賦給局部變量order_num。declare order_num floatupdate sell_orderset order_num=order_num*2/*order_num 為局部變量，order_num 為 sell_order 表中的列名稱*/where transporter_id='t001' and goods_id='g00003'【例9- 3】計(jì)算 employee 表

4、的記錄數(shù)并賦值給局部變量rows。declare rows intset rows=(select count(*) from employee)select rows【例9- 4】使服務(wù)器產(chǎn)生服務(wù)，并顯示錯(cuò)誤號(hào)。raiserror('miscellaneous error message',16,1) /* 產(chǎn)生一個(gè)錯(cuò)誤*/if error<>0select error as 'last error'【例9- 5】捕捉例 9- 4 中服務(wù)器產(chǎn)生的錯(cuò)誤號(hào)，并顯示出來(lái)。declare my_error intraiserror('miscell

5、aneous error message',16,1)select my_error=errorif my_error<>0select my_error as 'last error'【例9- 6】使用 spid 返回當(dāng)前用戶進(jìn)程的id。select spid as 'id',system_user as 'login name',user as 'user name'【例9-7】在 sales數(shù)據(jù)庫(kù)中創(chuàng)建jobs 表，并在表中插入帶有identity 列的行，并且使用 identity 來(lái)顯示新行中所用的i

6、dentity 值。create table jobs (job_id smallint identity (1, 1) not null ,job_desc varchar (50) collate chinese_prc_ci_as not null ,min_lvl tinyint not null ,max_lvl tinyint not null) on primaryinsert into jobs(job_desc,min_lvl,max_lvl)values('accountant',12,125)select identity as 'identity

7、'【例 9- 8】 創(chuàng)建兩個(gè)過(guò)程：innerproc 和 outerproc。 outerproc 過(guò)程用來(lái)調(diào)用innerproc 過(guò)程， innerproc 過(guò)程用來(lái)顯示nestlevel 的設(shè)置。 定義 innerproc 為內(nèi)嵌過(guò)程。create procedure innerproc asselect nestlevel as 'inner level'go 定義 outerproc 為外層過(guò)程。create procedure outerproc asselect nestlevel as 'outer level'exec innerproc

8、go 執(zhí)行outerproc。execute outerprocgo【例 9-9】 使用“ +”將 goods表中高于9000 元的商品價(jià)格增加15 元。select goods_name,unit_price,(unit_price+15) as nowpricefrom goodswhere unit_price>9000【例9- 10】從表 department 中讀取manager列的各記錄的實(shí)際長(zhǎng)度。select datalength(rtrim(manager) as 'datalength' ， len(rtrim(manager) as 'len&

9、#39;from departmentselect soundex('1'),soundex('a'),soundex('計(jì)算機(jī)'),soundex('abc')，soundex ('abcd')， soundex('a12c'),soundex('a 數(shù)字 ')select difference('red','read'),difference(soundex('ac'),soundex('zc'), differen

10、ce(soundex('abc'),soundex('abcd')select charindex(','， 'red,white,blue')select patindex('%abc%','abc123')， patindex('123','abc123')【例 9- 11】 在同一表達(dá)式中使用sin、 atan、 rand、 pi、 sign 函數(shù)。select sin(23.45),atan(1.234),rand(),pi(),sign(-2.34)【例 9

11、- 12】 用 ceiling 和 floor 函數(shù)返回大于或等于指定值的最小整數(shù)值和小于或等于指定值的最大整數(shù)值。select ceiling(123),floor(321),ceiling(12.3),ceiling(-32.1),floor(-32.1)【例 9- 13】round 函數(shù)的使用。select round(12.34512,3),round(12.34567,3),round(12.345,-2),round(154.321,-2)select getdate()【例 9- 14】使用 datediff 函數(shù)來(lái)確定貨物是否按時(shí)送給客戶。select goods_id,dat

12、ediff(dd,send_date,arrival_date)from purchase_order【例 9- 15】使用datename函數(shù)返回員工的出生日期的月份(mm)名稱。select employee_name,(datename(mm,birth_date)from employeeselect suser_sname(0x01)【例 9- 16】使用 object_name 函數(shù)返回已知id 號(hào)的對(duì)象名。select object_name(469576711)【例 9- 17】利用 object_id 函數(shù)，根據(jù)表名返回該表的id 號(hào)。select name from sys

13、indexeswhere id=object_id('customer')【例9- 18】創(chuàng)建一個(gè)用戶定義函數(shù)datetoquarter， 將輸入的日期數(shù)據(jù)轉(zhuǎn)換為該日期對(duì)應(yīng)的季度值。如輸入'2006- 8- 5'，返回'3q2006'，表示2006 年3季度。create function datetoquarter(dqdate datetime)returns char(6)asbeginreturn(datename(q,dqdate)+'q'+datename(yyyy,dqdate)end【例 9- 19】創(chuàng)建用戶定義函

14、數(shù)goodsq，返回輸入商品編號(hào)的商品名稱和庫(kù)存量。create function goodsq(goods_id varchar(30) returns tableasreturn(select goods_name,stock_quantityfrom goodswhere goods_id =goods_id)【例 9- 20】 根據(jù)輸入的訂單編號(hào)，返回該訂單對(duì)應(yīng)商品的編號(hào)、名稱、類別編號(hào)、 類別名稱。create function good_info(in_o_id varchar(10) returns goodinfo table( o_id char(6),g_id char(6

15、),g_name varchar(50),c_id char(6),c_name varchar(20)asbegindeclare g_id varchar(10),g_name varchar(30)declare c_id varchar(10),c_name varchar(30)select g_id=goods_id from sell_orderwhere order_id1=in_o_idselect g_name=goods_name,c_id=classification_idfrom goods where goods_id=g_idselect c_name=class

16、ification_namefrom goods_classification where c_id=classification_idinsert goodinfovalues(in_o_id,g_id,g_name,c_id,c_name) returnendselect dbo.datetoquarter ('2006-8-5')select * from dbo.goodsq('g00002')select * from dbo.good_info('s00002')drop functon datetoquarter9- 21】 顯示s

17、ales數(shù)據(jù)庫(kù)中customer表的編號(hào)為c0001 的聯(lián)系人姓名。use salesgodeclare linkman_name char(8)beginselect linkman_name=(select linkman_name from customer where customer_id like 'c0001')select linkman_nameend9- 22】語(yǔ)句塊嵌套舉例。declare errorcode int,nowdate datetimebeginset nowdate=getdate()insert sell_order(order_date

18、,send_date,arriver_date,custom_id)values(nowdate,nowdate+5,nowdate+10,'c0002')select errorcode=errorif errorcode>0beginraiserror(' 當(dāng)表 sell_order 插入數(shù)據(jù)時(shí)發(fā)生錯(cuò)誤!',16,1)returnendend* - this is a comment.whole line will be ignored.select employee_name， address - 查詢所有姓錢(qián)的員工from employeewher

19、e employee_name like ' 錢(qián) %'/*this is a commnet all these lines will be ignored.* /* list all employees.*/select * from employee/* - list all employees.select * from employee* /9- 23】 判斷表 goods中 supplier_id 為 s001 的商品的平均單價(jià)是否大于9799。if (select avg(unit_price) from goodswhere supplier_id='s0

20、01')>$9799.0select 'supplier_id 為 s001 的商品的平均單價(jià)比9799 大 'elseselect 'supplier_id 為 s001 的商品的平均單價(jià)比9799 小 '10- 24】 用 exists 確定表 department 中是否存在“陳曉兵”。declare lname varchar(40),msg varchar(255)select lname='陳曉兵'if exists(select * from department where manager=lname)beginse

21、lect msg='有人名為'+lnameselect msgendelsebeginselect msg='沒(méi)有人名為'+lnameselect msgend11- 25】 嵌套if else語(yǔ)句的使用。if (select sum(order_num) from sell_order)>50print '他們是最佳的客戶'elseif (select sum(order_num) from sell_order)>30print '必須與他們保持聯(lián)絡(luò)'elseprint '再想想辦法吧!'【例 9-

22、 26】使用簡(jiǎn)單case函數(shù)將goods 表中的商品分類重命名，以使之更易理解。selectcase classification_idwhen 'p001' then '筆記本計(jì)算機(jī)'when 'p002' then '激光打印機(jī)'when 'p003' then '噴墨打印機(jī)'when 'p004' then '交換機(jī) 'else '沒(méi)有這種品牌'end as classification，goods_name as 'goods name

23、，' unit_price as pricefrom goodswhere unit_price is not null【例 9- 27】根據(jù) goods表中庫(kù)存貨物數(shù)量與訂貨量之差，使用 case搜索函數(shù)判斷該商select goods_name as 商品名稱,casewhen stock_quantity - order_quantity<=3 then ' 緊急進(jìn)貨'when stock_quantity - order_quantity>3and stock_quantity- order_quantity<=10 then ' 暫緩

24、進(jìn)貨'when stock_quantity - order_quantity>10 then ' 貨物充足'end as 進(jìn)貨判斷from goods【例 9- 28】使用 goto 語(yǔ)句改變程序流程。declare x intselect x=1label_1 ：select xselect x=x+1while x<6goto label_1【例 9- 29】return 語(yǔ)句應(yīng)用示例。declare x int,y intselect x=1,y=2if x>yreturnelsereturnwaitfor delay '00:01:0

25、0'select * from gradewaitfor time '23:00'backup database studentsdb to studentsdb_bkp【例 9-30】 將 goods 表中庫(kù)存數(shù)最大的商品每次訂購(gòu)兩件，計(jì)算如此需要多少次訂購(gòu)才能使庫(kù)存數(shù)不夠一次訂購(gòu)。declare count int,maxstockid char(6),maxstock floatset count=0set maxstock=(select max(stock_quantity) from goods)set maxstockid=(select goods_id

26、 from goodswhere stock_quantity=maxstock)select maxstockid,maxstockwhile (maxstock>(select order_quantity from goods where goods_id=maxstockid)beginupdate goodsset order_quantity=order_quantity+2where goods_id=maxstockidset count=count+1endselect count【例9- 31】對(duì)于 goods 表， 如果平均庫(kù)存少于12， while 循環(huán)就將各記錄

27、庫(kù)存增加5%，再判斷最高庫(kù)存是否少于或等于25， 是則 while 循環(huán)重新啟動(dòng)并再次將各記錄庫(kù)存增加5%。當(dāng)循環(huán)不斷地將庫(kù)存增加直到最高庫(kù)存超過(guò)25 時(shí)，然后退出while 循環(huán)。在while 中使用break 或 continue 控制循環(huán)體的執(zhí)行。/*執(zhí)行循環(huán)，直到庫(kù)存平均值超過(guò)12*/while(select avg(stock_quantity) from goods)<12beginupdate goodsset stock_quantity=stock_quantity*1.05select max(stock_quantity) from goods/* 如果最大庫(kù)存值超

28、過(guò)25，則用break 退出 while 循環(huán)，否則繼續(xù)循環(huán)*/if(select max(stock_quantity) from goods)>25beginprint '庫(kù)存太多了'breakendelsecontinueend【例9- 32】計(jì)算 s=1!+2!+ +10!。declare s int,n int,t int,c int/*s 存儲(chǔ)階乘和，n 為外層循環(huán)控制變量，c 為內(nèi)層循環(huán)控制變量，t 為 n 的階乘值 */set s=0set n=1while n<=10beginset c=1set t=1 while c<=n beginse

29、t t=t*cset c=c+1endset s=s+tset n=n+1endselect s,nexecute sp_executesql n'select * from sales.dbo.employee'【例9- 33】創(chuàng)建一個(gè)視圖，使用 go 命令將 create view 語(yǔ)句與批處理中的其他語(yǔ)句（如use、 select語(yǔ)句等）隔離。use salesgo-批處理結(jié)束標(biāo)志create view employee_infoasselect * from employeego-create view 語(yǔ)句與其他語(yǔ)句隔離select * from employee_i

30、nfogo【例9- 34】使用 sql- 92 標(biāo)準(zhǔn)的游標(biāo)聲明語(yǔ)句聲明一個(gè)游標(biāo)，用于訪問(wèn)sales數(shù)據(jù)庫(kù)中的 goods 表的信息。use salesgodeclare goods_cursor cursorforselect * from goodsfor read only【例9- 35】為 customer 表定義一個(gè)全局滾動(dòng)動(dòng)態(tài)游標(biāo)，用于訪問(wèn)顧客的編號(hào)、姓名、地址、電話信息。declare cur_customer cursorglobal scroll dynamicforselect customer_id,customer_name,address,telephonefrom c

31、ustomer【例9- 36】打開(kāi)例 9- 35中聲明的游標(biāo)，讀取游標(biāo)中的數(shù)據(jù)。open cur_customerfetch next from cur_customer/* 取第一個(gè)數(shù)據(jù)行*/while fetch_status = 0/* 檢查 fetch_status 是否還有數(shù)據(jù)可取*/beginfetch next from curcustomerendclose cur_customerdeallocate cur_customer【 例 9-37 】 定義 游標(biāo) cur_customer， 通 過(guò) cur_customer 更 新 customer 表 中的 customer_n

32、ame 和 linkman_name 列。declare cur_customer cursorforselect * from customerfor update of customer_name,linkman_name/* 該兩列可更新*/open cur_customer/* 打開(kāi) cur_customer 游標(biāo) */fetch next from cur_customer /* 將第一行數(shù)據(jù)放入緩沖區(qū)，以便更新操作*/update customerset customer_name='南方體育用品公司',linkman_name=' 李強(qiáng) 'wher

33、e current of cur_customerclose cur_customer/* 關(guān)閉cur_customer 游標(biāo) */delete from customer/*定義游標(biāo)變量*/where current of cur_customerdeclare cur_var cursordeclare curcustomer cursorfor select * from customerset cur_var=cur_customer/*定義游標(biāo)*/* 設(shè)置游標(biāo)與游標(biāo)變量的關(guān)聯(lián)*/declare cur_var cursor/* 定義游標(biāo)變量*/set cur_var=cursor s

34、croll keyset forselect * from customer /* 創(chuàng)建游標(biāo)并與游標(biāo)變量的關(guān)聯(lián)*/【例9-38】通過(guò)游標(biāo)變量來(lái)操作例9-35 所聲明的游標(biāo)cur_customer，操作完成后刪除游標(biāo)變量。declare cur_var cursorset cur_var =cur_customeropen cur_varfetch next from cur_varclose cur_vardeallocate cur_vardeallocate cur_var 語(yǔ)句執(zhí)行后，就不能重新open cur_var 了?！纠?9- 39】定義可以任意移動(dòng)的游標(biāo)。聲明 scroll 游標(biāo)cur_customer。declare cur_customer cursorscrollread_onlyforselect * from customeropen cur_customerfetch next from cur_custo