资源描述:
《实验5数据库编程技术》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、实验5:数据库编程技术第7个实殓.游标与存储过程弟7章实殓十二请完成以下实验内容:(1)创建游标,逐行显示表Customer.的记录,并用WHILE结构来测试@@Fetch_Status的返回值。输出格式如下:,客户编号屮••…屮客户名称,+,-—•+•客户地址屮…••屮客户电话屮——屮客户邮编*+*——*declare@nochar(9)z@navarchar(40)z@adchar(40)z@tevarchar(20)Z@zichar(6),@textchar(100)declarecustcursorforselectc
2、ustomerno,customername,address,telephone,zipfromcustomerselect@text=replicate(1=1,38)+1客户信,自J+replicate('=',38)print@textselect@text=space(84)print@textselect@text='客丿%話号'+''+'客八名丽;'+'1+1客丿''地111:'+''+'客户电话'+'客户邮编'+''print@textopencustfetchcustinto@no,@na,@ad,@te,@z
3、iwhile(@@fetch_status=0)beginselect@text=@no+space(5)+convert(char(20),@na)+space(5)+convert(char(10),@ad)+space(3)+convert(char(13)z@te)+space(7)+@ziprint@textfetchcustinto@noz@naz@adz@tez@ziendclosecustdeallocatecust丨息客户编号-C20050001C20050002C20050003C200S0004C2006
4、0001C20060002C20070001C20070002C20070003C20080001b司-司司司卩司公司一公公室公制公限公称限限究限研发有限名有有研有脑开份有户份份物原城份电术股份客股股生商商股市技达般一一隆海一地合昌界事度一统兴上五大联南世万红市市市市市市市市市市津津京海京海昌海津亠乐天天北上北上南上天北•一客户电话——一一客户邮编-022-3S66021220012022-3S€2452220301010-2121000108001021-4532187210100010・11G5152100803021-
5、456S4512101000791-4412152330046021-4S64512210230022-4533141220400010-5421S85100800⑵利用游标修改OrderMastfr表中Ordersum的值。declare@ordernochar(12)z@ordersumnumeric(9z2)declareordermascursorforselecta・orderno,sum(b.quantity*b・price)fromordermastera,orderdetailbwherea・orde:rno=
6、b・ordernogroupbya.ordernoopenordermasfetchordermasinto@orderno/@ordersumwhile(@@fetch_status=O)beginupdateordermastersetordersum=@ordersumwhereorderno=@ordernofetchordermasinto@ordernoz@ordersumendcloseordermasdeallocateordermas结果查看:select*fromordermaster启结果匕消息order
7、NocustomerNosalerNoorderDateorderSuminvoiceNo1_j200801090001IC20050001E20050022008-01-0900:00:00.0003103.5010000000012200801090002C20050004E20050032008-01-0900:00:00.0008310.0010000000023200801090003C20080001E20050022008-01-0900:00:00.0001O05.S0100000000342008021900
8、01C20050001E20050032008-02-1900:00:00.00013232.0010000000045200802190002C20070002E20080022008-02-1900:00:00.0004291.8010000000056200803010