搜索
实验六其它数据库对象的管理1.begintransactioninsertintoxiangmubiaovalues('zy2011006','112202002','y005',10000,'张三','07-01-2011','07-04-2011','07-25-2011','作业公司作业一队','堵漏',7000,2500,1000,1400,11900,'李四','07-26-2011',11900,'王五','07-28-2011');insertintocailiaofeibiaovalues('zy2011006','wm001',200,10);insertintocailiaofeibiaovalues('zy20110006','wm002',200,10);insertintocailiaofeibiaovalues('zy20110006','wm003',200,10);insertintocailiaofeibiaovalues('zy20110006','wm004',200,10);rollbackbegintransactiongoinsertintoxiangmubiaovalues('zy2011006','112202002','y005',10000,'张三','07-01-2011','07-04-2011','07-25-2011','作业公司作业一队','堵漏',7000,2500,1000,1400,11900,'李四','07-26-2011',11900,'王五','07-28-2011');if@@error0beginprint'第一个插入出错'rollbackendelsebeginprint'第一个插入成功'commitendgobegintransaction;begintryinsertintocailiaofeibiaovalues('zy2011006','wm001',200,10);print'第二个插入成功'commitendtrybegincatchprint'错误';if@@TRANCOUNT0rollbacktransaction;endcatchif@@trancount0committransaction;gobegintransactiongoinsertintocailiaofeibiaovalues('zy20110006','wm002',200,10)if@@error0beginprint'第三个插入出错'rollbackendelsebeginprint'第三个插入成功'commitendgobegintransactiongoinsertintocailiaofeibiaovalues('zy20110006','wm003',200,10)if@@error0beginprint'第四个插入出错'rollbackendelsebeginprint'第四个插入成功'commitendgobegintransactiongoinsertintocailiaofeibiaovalues('zy20110006','wm004',200,10)if@@error0beginprint'第五个插入出错'rollbackendelsebeginprint'第五个插入成功'commitendGo2.begintransactionDECLARECustomerCursorscrollCURSORFORselect*fromxiangmubiao;OPENCustomerCursor;declare@zuoyexiangmubianhaochar(12)declare@yusuandanweichar(13)declare@jinghaochar(5)declare@yusuanjinechar(10)declare@yusuanrenchar(6)declare@yusuanriqichar(10)declare@kaigongriqichar(10)declare@wangongriqichar(10)declare@shigongdanweimingchengchar(10)declare@shigongneirongchar(8)declare@cailiaofeichar(10)declare@rengongfeichar(10)declare@shebeifeichar(10)declare@qitafeiyongchar(10)declare@jiesuanjinechar(10)declare@jiesuanrenchar(6)declare@jiesuanriqichar(10)declare@ruzhangjinechar(10)declare@ruzhangrenchar(8)declare@ruzhangriqichar(10)print'作业项目编号'+'预算单位'+'井号'+'预算金额'+'预算人'+'预算日期'+'开工日期'+'完工日期'+'施工单位名称'+'施工内容'+'材料费'+'人工费'+'设备费'+'其它费用'+'结算金额'+'结算人'+'结算日期'+'入账金额'+'入账人'+'入账日期'fetchfirstfromCustomerCursorinto@zuoyexiangmubianhao,@yusuandanwei,@jinghao,@yusuanjine,@yusuanren,@yusuanriqi,@kaigongriqi,@wangongriqi,@shigongdanweimingcheng,@shigongneirong,@cailiaofei,@rengongfei,@shebeifei,@qitafeiyong,@jiesuanjine,@jiesuanren,@jiesuanriqi,@ruzhangjine,@ruzhangren,@ruzhangriqiwhile(@@fetch_status=0)beginprint+@zuoyexiangmubianhao+@yusuandanwei+@jinghao+@yusuanjine+''+@yusuanren+''+@yusuanriqi+''+@kaigongriqi+''+@wangongriqi+''+@shigongdanweimingcheng+''+@shigongneirong+@cailiaofei+@rengongfei+@shebeifei+@qitafeiyong+@jiesuanjine+''+@jiesuanren+''+@jiesuanriqi+@ruzhangjine+''+@ruzhangren+@ruzhangriqifetchnextfromCustomerCursorinto@zuoyexiangmubianhao,@yusuandanwei,@jinghao,@yusuanjine,@yusuanren,@yusuanriqi,@kaigongriqi,@wangongriqi,@shigongdanweimingcheng,@shigongneirong,@cailiaofei,@rengongfei,@shebeifei,@qitafeiyong,@jiesuanjine,@jiesuanren,@jiesuanriqi,@ruzhangjine,@ruzhangren,@ruzhangriqiendrollback;3.ifOBJECT_ID('_someDepCost','p')isnotnulldropprocedure_someDepCost;gocreateprocedure_someDepCost@dwdmvarchar(40),@starttimedatetime,@endtimedatetimeas--定义局部临时变量declare@dwmcvarchar(40);declare@ys_moneymoney,@js_moneymoney,@rz_moneymoney,@wjs_moneymoney,@wrz_moneymoney;select@dwmc=danweimingchengfromdanweidaimabiaowheredanweidaima=@dwdmset@ys_money=(selectsum(yusuanjine)fromxiangmubiaowhereyusuandanweilike@dwdm+'%'and(yusuanriqibetween@starttimeand@endtime))set@js_money=(selectsum(jiesuanjine)fromxiangmubiaowhereyusuandanweilike@dwdm+'%'and(jiesuanriqibetween@starttimeand@endtime))set@rz_money=(selectsum(ruzhangjine)fromxiangmubiaowhereyusuandanweilike@dwdm+'%'and(ruzhangriqibetween@starttimeand@endtime)and(ruzhangjineisnotnull))set@wjs_money=(selectsum(yusuanjine)fromxiangmubiaowhere(yusuanjinebetween@starttimeand@endtime)and(jiesuanjineisnull))set@wrz_money=(selectsum(jiesuanjine)fromxiangmubiaowhere(jiesuanriqibetween@starttimeand@endtime)and(ruzhangjineisnull))--null处理set@wjs_money=@ys_money-@js_moneyset@wrz_money=@js_money-@rz_moneyif@ys_moneyisnullset@ys_money=0if@js_moneyisnullset@js_money=0if@rz_moneyisnullset@rz_money=0if@wjs_moneyisnullset@wjs_money=0if@wrz_moneyisnullset@wrz_money=0declare@resultvarchar(100)set@result=convert(varchar,@ys_money)+''+convert(varchar,@js_money)+''+convert(varchar,@rz_money)+''+convert(varchar,@wjs_money)+''+convert(varchar,@wrz_money)--输出结果print@dwmc+'单位'+convert(varchar,@starttime,102)+'--'+convert(varchar,@endtime,102)+'成本运营情况'print'预算金额结算金额入账金额未结算金额未入账金额'print@resultgo--测试输出--declare@temp1datetime,@temp2datetime--set@temp1=convert(datetime,'2011-5-1');--set@temp2=convert(datetime,'2011-5-29');--execute_someDepCost@dwdm='1122',@starttime=@temp1,@endtime=@temp2;--execute_someDepCost'112201001',@temp1,@temp2execute_someDepCost@dwdm='1122',@starttime='2011-5-1',@endtime='2011-5-29';e