第9章TransactSQL程序设计文档格式.docx
《第9章TransactSQL程序设计文档格式.docx》由会员分享，可在线阅读，更多相关《第9章TransactSQL程序设计文档格式.docx（17页珍藏版）》请在冰豆网上搜索。
,16,1）/*产生一个错误*/
if@@error<
>
select@@erroras'
lasterror'
【例9-5】捕捉例9-4中服务器产生的错误号，并显示出来。
declare@my_errorint
16,1）
select@my_error=@@error
if@my_error<
select@my_erroras'
【例9-6】使用@@spid返回当前用户进程的id。
select@@spidas'
id'
system_useras'
loginname'
useras'
username'
【例9-7】在sales数据库中创建jobs表，并在表中插入带有identity列的行，并且使用@@identity来显示新行中所用的identity值。
createtablejobs（
[job_id][smallint]identity（1,1）notnull,
[job_desc][varchar]（50）collatechinese_prc_ci_asnotnull,
[min_lvl][tinyint]notnull,
[max_lvl][tinyint]notnull
）on[primary]
insertintojobs（job_desc,min_lvl,max_lvl）
values（'
accountant'
12,125）
select@@identityas'
identity'
【例9-8】创建两个过程：
innerproc和outerproc。
outerproc过程用来调用innerproc过程，innerproc过程用来显示@@nestlevel的设置。
①定义innerproc为内嵌过程。
createprocedureinnerprocas
select@@nestlevelas'
innerlevel'
go
②定义outerproc为外层过程。
createprocedureouterprocas
outerlevel'
execinnerproc
③执行outerproc。
executeouterproc
【例9-9】使用“+”将goods表中高于9000元的商品价格增加15元。
selectgoods_name,unit_price,（unit_price+15）asnowprice
fromgoods
whereunit_price>
9000
【例9-10】从表department中读取manager列的各记录的实际长度。
selectdatalength（rtrim（manager））as'
datalength'
，
len（rtrim（manager））as'
len'
fromdepartment
selectsoundex（'
1'
）,soundex（'
a'
计算机'
abc'
），
soundex（'
abcd'
），soundex（'
a12c'
a数字'
）
selectdifference（'
red'
read'
）,difference（soundex（'
ac'
zc'
））,
difference（soundex（'
））
selectcharindex（'
，'
red,white,blue'
）
selectpatindex（'
%abc%'
abc123'
），patindex（'
123'
【例9-11】在同一表达式中使用sin、atan、rand、pi、sign函数。
selectsin（23.45）,atan（1.234）,rand（）,pi（）,sign（-2.34）
【例9-12】用ceiling和floor函数返回大于或等于指定值的最小整数值和小于或等于指定值的最大整数值。
selectceiling（123）,floor（321）,ceiling（12.3）,ceiling（-32.1）,floor（-32.1）
【例9-13】round函数的使用。
selectround（12.34512,3）,round（12.34567,3）,round（12.345,-2）,round（154.321,-2）
selectgetdate（）
【例9-14】使用datediff函数来确定货物是否按时送给客户。
selectgoods_id,datediff（dd,send_date,arrival_date）
frompurchase_order
【例9-15】使用datename函数返回员工的出生日期的月份（mm）名称。
selectemployee_name,（datename（mm,birth_date）
fromemployee
selectsuser_sname（0x01）
【例9-16】使用object_name函数返回已知id号的对象名。
selectobject_name（469576711）
【例9-17】利用object_id函数，根据表名返回该表的id号。
selectnamefromsysindexes
whereid=object_id（'
customer'
【例9-18】创建一个用户定义函数datetoquarter，将输入的日期数据转换为该日期对应的季度值。
如输入'
2006-8-5'
，返回'
3q2006'
，表示2006年3季度。
createfunctiondatetoquarter（@dqdatedatetime）
returnschar（6）
as
begin
return（datename（q,@dqdate）+'
q'
+datename（yyyy,@dqdate））
end
【例9-19】创建用户定义函数goodsq，返回输入商品编号的商品名称和库存量。
createfunctiongoodsq（@goods_idvarchar（30））
returnstable
return（selectgoods_name,stock_quantity
fromgoods
wheregoods_id=@goods_id）
【例9-20】根据输入的订单编号，返回该订单对应商品的编号、名称、类别编号、类别名称。
createfunctiongood_info（@in_o_idvarchar（10））
returns@goodinfotable
（o_idchar（6）,
g_idchar（6）,
g_namevarchar（50）,
c_idchar（6）,
c_namevarchar（20））
declare@g_idvarchar（10）,@g_namevarchar（30）
declare@c_idvarchar（10）,@c_namevarchar（30）
select@g_id=goods_idfromsell_order
whereorder_id1=@in_o_id
select@g_name=goods_name,@c_id=classification_id
fromgoodswheregoods_id=@g_id
select@c_name=classification_name
fromgoods_classificationwhere@c_id=classification_id
insert@goodinfo
values（@in_o_id,@g_id,@g_name,@c_id,@c_name）
return
selectdbo.datetoquarter（'
select*fromdbo.goodsq（'
g00002'
select*fromdbo.good_info（'
s00002'
dropfunctondatetoquarter
【例9-21】显示sales数据库中customer表的编号为c0001的联系人姓名。
usesales
declare@linkman_namechar（8）
select@linkman_name=（selectlinkman_namefromcustomer
wherecustomer_idlike'
c0001'
select@linkman_name
【例9-22】语句块嵌套举例。
declare@errorcodeint,@nowdatedatetime
set@nowdate=getdate（）
insertsell_order（order_date,send_date,arriver_date,custom_id）
values（@nowdate,@nowdate+5,@nowdate+10,'
select@errorcode=@@error
if@errorcode>
当表sell_order插入数据时发生错误!
'
--thisisacomment.wholelinewillbeignored.
selectemployee_name，address--查询所有姓钱的员工
whereemployee_namelike'
钱%'
/*
thisisacommnet．
alltheselineswillbeignored.
*/
/*listallemployees.*/
select*fromemployee
/*
--listallemployees.
【例9-23】判断表goods中supplier_id为s001的商品的平均单价是否大于9799。
if（selectavg（unit_price）fromgoods
wheresupplier_id='
s001'
）>
$9799.0
select'
supplier_id为s001的商品的平均单价比9799大'
else
supplier_id为s001的商品的平均单价比9799小'
【例9-24】用exists确定表department中是否存在“陈晓兵”。
declare@lnamevarchar（40）,@msgvarchar（255）
select@lname='
陈晓兵'
ifexists（select*fromdepartmentwheremanager=@lname）
select@msg='
有人名为'
+@lname
select@msg
begin
select@msg='
没有人名为'
select@msg
【例9-25】嵌套if…else语句的使用。
if（selectsum（order_num）fromsell_order）>
50
print'
他们是最佳的客户'
30
必须与他们保持联络'
再想想办法吧!
!
【例9-26】使用简单case函数将goods表中的商品分类重命名，以使之更易理解。
select
caseclassification_id
when'
p001'
then'
笔记本计算机'
p002'
激光打印机'
p003'
喷墨打印机'
p004'
交换机'
else'
没有这种品牌'
endasclassification，
goods_nameas'
goodsname'
，unit_priceasprice
whereunit_priceisnotnull
【例9-27】根据goods表中库存货物数量与订货量之差，使用case搜索函数判断该商品是否进货。
selectgoods_nameas商品名称,
case
whenstock_quantity-order_quantity<
=3then'
紧急进货'
whenstock_quantity-order_quantity>
3
andstock_quantity-order_quantity<
=10then'
暂缓进货'
10then'
货物充足'
endas进货判断
【例9-28】使用goto语句改变程序流程。
declare@xint
select@x=1
label_1：
select@x
select@x=@x+1
while@x<
6
gotolabel_1
【例9-29】return语句应用示例。
declare@xint,@yint
select@x=1,@y=2
if@x>
@y
waitfordelay'
00:
01:
00'
select*fromgrade
waitfortime'
23:
backupdatabasestudentsdbtostudentsdb_bkp
【例9-30】将goods表中库存数最大的商品每次订购两件，计算如此需要多少次订购才能使库存数不够一次订购。
declare@countint,@maxstockidchar（6）,@maxstockfloat
set@count=0
set@maxstock=（selectmax（stock_quantity）fromgoods）
set@maxstockid=（selectgoods_idfromgoods
wherestock_quantity=@maxstock）
select@maxstockid,@maxstock
while（@maxstock>
（selectorder_quantityfromgoodswheregoods_id=@maxstockid））
updategoods
setorder_quantity=order_quantity+2
wheregoods_id=@maxstockid
set@count=@count+1
select@count
【例9-31】对于goods表，如果平均库存少于12，while循环就将各记录库存增加5%，再判断最高库存是否少于或等于25，是则while循环重新启动并再次将各记录库存增加5%。
当循环不断地将库存增加直到最高库存超过25时，然后退出while循环。
在while中使用break或continue控制循环体的执行。
/*执行循环，直到库存平均值超过12*/
while（selectavg（stock_quantity）fromgoods）<
12
updategoods
setstock_quantity=stock_quantity*1.05
selectmax（stock_quantity）fromgoods
/*如果最大库存值超过25，则用break退出while循环，否则继续循环*/
if（selectmax（stock_quantity）fromgoods）>
25
库存太多了'
break
continue
【例9-32】计算s=1!
+2!
+…+10!
。
declare@sint,@nint,@tint,@cint
/*@s存储阶乘和，@n为外层循环控制变量，@c为内层循环控制变量，@t为@n的阶乘值*/
set@s=0
set@n=1
while@n<
=10
set@c=1
set@t=1
while@c<
=@n
set@t=@t*@c
set@c=@c+1
set@s=@s+@t
set@n=@n+1
select@s,@n
executesp_executesqln'
select*fromsales.dbo.employee'
【例9-33】创建一个视图，使用go命令将createview语句与批处理中的其他语句（如use、select语句等）隔离。
go
--批处理结束标志
createviewemployee_info
--createview语句与其他语句隔离
select*fromemployee_info
【例9-34】使用sql-92标准的游标声明语句声明一个游标，用于访问sales数据库中的goods表的信息。
declaregoods_cursorcursor
for
select*fromgoods
forreadonly
【例9-35】为customer表定义一个全局滚动动态游标，用于访问顾客的编号、姓名、地址、电话信息。
declarecur_customercursor
globalscrolldynamic
for
selectcustomer_id,customer_name,address,telephone
【例9-36】打开例9-35中声明的游标，读取游标中的数据。
opencur_customer
fetchnextfromcur_customer/*取第一个数据行*/
while@@fetch_status=0/*检查@@fetch_status是否还有数据可取*/
fetchnextfromcur_customer
closecur_customer
deallocatecur_customer
【例9-37】定义游标cur_customer，通过cur_customer更新customer表中的customer_name和linkman_name列。
select*fromcustomer
forupdateofcustomer_name,linkman_name/*该两列可更新*/
opencur_customer/*打开cur_customer游标*/
fetchnextfromcur_customer/*将第一行数据放入缓冲区，以便更新操作*/
updatecustomer
setcustomer_name='
南方体育用品公司'
linkman_name='
李强'
wherecurrentofcur_customer
closecur_customer/*关闭cur_customer游标*/
deletefromcustomer
declare@cur_varcursor/*定义游标变量*/
forselect*fromcustomer/*定义游标*/
set@cur_var=cur_customer/*设置游标与游标变量的关联*/
declare@cur_varcursor/*定义游标变量*/
set@cur_var=cursorscrollkeysetfor
select*fromcustomer/*创建游标并与游标变量的关联*/
升级会员 