第9章TransactSQL程序设计.docx
《第9章TransactSQL程序设计.docx》由会员分享，可在线阅读，更多相关《第9章TransactSQL程序设计.docx（17页珍藏版）》请在冰豆网上搜索。
第9章TransactSQL程序设计
第9章transact-sql程序设计
execsp_addtypetest_add,'varchar（10）','notnull'
declare@int_varint
select@int_var=12/*给@int_var赋值*/
select@int_var/*将@int_var的值输出到屏幕上*/
在一条语句中可以同时对几个变量进行赋值，例如，
declare@lastnamechar（8）,@firstnamechar（8）,@birthdatedatetime
select@lastname='smith',@firstname='david',@birthdate='1985-2-20'
select@lastname,@firstname,@birthdate
【例9-1】使用select语句从customer表中检索出顾客编号为c0002的行，再将顾客的名字赋给变量@customer。
declare@customervarchar（40）,@curdatedatetime
select@customer=customer_name,@curdate=getdate（）
fromcustomer
wherecustomer_id='c0002'
【例9-2】将sell_order表中的transporter_id列值为“t001”、goods_id列值为“g00003”的order_num列的值赋给局部变量@order_num。
declare@order_numfloat
updatesell_order
set@order_num=order_num*2
/*@order_num为局部变量，order_num为sell_order表中的列名称*/
wheretransporter_id='t001'andgoods_id='g00003'
【例9-3】计算employee表的记录数并赋值给局部变量@rows。
declare@rowsint
set@rows=（selectcount（*）fromemployee）
select@rows
【例9-4】使服务器产生服务，并显示错误号。
raiserror（'miscellaneouserrormessage',16,1）/*产生一个错误*/
if@@error<>0
select@@erroras'lasterror'
【例9-5】捕捉例9-4中服务器产生的错误号，并显示出来。
declare@my_errorint
raiserror（'miscellaneouserrormessage',16,1）
select@my_error=@@error
if@my_error<>0
select@my_erroras'lasterror'
【例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
select@@nestlevelas'outerlevel'
execinnerproc
go
③执行outerproc。
executeouterproc
go
【例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'）,soundex（'计算机'）,soundex（'abc'），
soundex（'abcd'），soundex（'a12c'）,soundex（'a数字'）
selectdifference（'red','read'）,difference（soundex（'ac'）,soundex（'zc'））,
difference（soundex（'abc'）,soundex（'abcd'））
selectcharindex（','，'red,white,blue'）
selectpatindex（'%abc%','abc123'），patindex（'123','abc123'）
【例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
as
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））
as
begin
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
end
selectdbo.datetoquarter（'2006-8-5'）
select*fromdbo.goodsq（'g00002'）
select*fromdbo.good_info（'s00002'）
dropfunctondatetoquarter
【例9-21】显示sales数据库中customer表的编号为c0001的联系人姓名。
usesales
go
declare@linkman_namechar（8）
begin
select@linkman_name=（selectlinkman_namefromcustomer
wherecustomer_idlike'c0001'）
select@linkman_name
end
【例9-22】语句块嵌套举例。
declare@errorcodeint,@nowdatedatetime
begin
set@nowdate=getdate（）
insertsell_order（order_date,send_date,arriver_date,custom_id）
values（@nowdate,@nowdate+5,@nowdate+10,'c0002'）
select@errorcode=@@error
if@errorcode>0
begin
raiserror（'当表sell_order插入数据时发生错误!
',16,1）
return
end
end
--thisisacomment.wholelinewillbeignored.
selectemployee_name，address--查询所有姓钱的员工
fromemployee
whereemployee_namelike'钱%'
/*
thisisacommnet．
alltheselineswillbeignored.
*/
/*listallemployees.*/
select*fromemployee
/*
--listallemployees.
select*fromemployee
*/
【例9-23】判断表goods中supplier_id为s001的商品的平均单价是否大于9799。
if（selectavg（unit_price）fromgoods
wheresupplier_id='s001'）>$9799.0
select'supplier_id为s001的商品的平均单价比9799大'
else
select'supplier_id为s001的商品的平均单价比9799小'
【例9-24】用exists确定表department中是否存在“陈晓兵”。
declare@lnamevarchar（40）,@msgvarchar（255）
select@lname='陈晓兵'
ifexists（select*fromdepartmentwheremanager=@lname）
begin
select@msg='有人名为'+@lname
select@msg
end
else
begin
select@msg='没有人名为'+@lname
select@msg
end
【例9-25】嵌套if…else语句的使用。
if（selectsum（order_num）fromsell_order）>50
print'他们是最佳的客户'
else
if（selectsum（order_num）fromsell_order）>30
print'必须与他们保持联络'
else
print'再想想办法吧!
!
'
【例9-26】使用简单case函数将goods表中的商品分类重命名，以使之更易理解。
select
caseclassification_id
when'p001'then'笔记本计算机'
when'p002'then'激光打印机'
when'p003'then'喷墨打印机'
when'p004'then'交换机'
else'没有这种品牌'
endasclassification，
goods_nameas'goodsname'，unit_priceasprice
fromgoods
whereunit_priceisnotnull
【例9-27】根据goods表中库存货物数量与订货量之差，使用case搜索函数判断该商品是否进货。
selectgoods_nameas商品名称,
case
whenstock_quantity-order_quantity<=3then'紧急进货'
whenstock_quantity-order_quantity>3
andstock_quantity-order_quantity<=10then'暂缓进货'
whenstock_quantity-order_quantity>10then'货物充足'
endas进货判断
fromgoods
【例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
return
else
return
waitfordelay'00:
01:
00'
select*fromgrade
waitfortime'23:
00'
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））
begin
updategoods
setorder_quantity=order_quantity+2
wheregoods_id=@maxstockid
set@count=@count+1
end
select@count
【例9-31】对于goods表，如果平均库存少于12，while循环就将各记录库存增加5%，再判断最高库存是否少于或等于25，是则while循环重新启动并再次将各记录库存增加5%。
当循环不断地将库存增加直到最高库存超过25时，然后退出while循环。
在while中使用break或continue控制循环体的执行。
/*执行循环，直到库存平均值超过12*/
while（selectavg（stock_quantity）fromgoods）<12
begin
updategoods
setstock_quantity=stock_quantity*1.05
selectmax（stock_quantity）fromgoods
/*如果最大库存值超过25，则用break退出while循环，否则继续循环*/
if（selectmax（stock_quantity）fromgoods）>25
begin
print'库存太多了'
break
end
else
continue
end
【例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
begin
set@c=1
set@t=1
while@c<=@n
begin
set@t=@t*@c
set@c=@c+1
end
set@s=@s+@t
set@n=@n+1
end
select@s,@n
executesp_executesqln'select*fromsales.dbo.employee'
【例9-33】创建一个视图，使用go命令将createview语句与批处理中的其他语句（如use、select语句等）隔离。
usesales
go
--批处理结束标志
createviewemployee_info
as
select*fromemployee
go
--createview语句与其他语句隔离
select*fromemployee_info
go
【例9-34】使用sql-92标准的游标声明语句声明一个游标，用于访问sales数据库中的goods表的信息。
usesales
go
declaregoods_cursorcursor
for
select*fromgoods
forreadonly
【例9-35】为customer表定义一个全局滚动动态游标，用于访问顾客的编号、姓名、地址、电话信息。
declarecur_customercursor
globalscrolldynamic
for
selectcustomer_id,customer_name,address,telephone
fromcustomer
【例9-36】打开例9-35中声明的游标，读取游标中的数据。
opencur_customer
fetchnextfromcur_customer/*取第一个数据行*/
while@@fetch_status=0/*检查@@fetch_status是否还有数据可取*/
begin
fetchnextfromcur_customer
end
closecur_customer
deallocatecur_customer
【例9-37】定义游标cur_customer，通过cur_customer更新customer表中的customer_name和linkman_name列。
declarecur_customercursor
for
select*fromcustomer
forupdateofcustomer_name,linkman_name/*该两列可更新*/
opencur_customer/*打开cur_customer游标*/
fetchnextfromcur_customer/*将第一行数据放入缓冲区，以便更新操作*/
updatecustomer
setcustomer_name='南方体育用品公司',linkman_name='李强'
wherecurrentofcur_customer
closecur_customer/*关闭cur_customer游标*/
deletefromcustomer
wherecurrentofcur_customer
declare@cur_varcursor/*定义游标变量*/
declarecur_customercursor
forselect*fromcustomer/*定义游标*/
set@cur_var=cur_customer/*设置游标与游标变量的关联*/
declare@cur_varcursor/*定义游标变量*/
set@cur_var=cursorscrollkeysetfor
select*fromcustomer/*创建游标并与游标变量的关联*/
升级会员 