2022年数据库游标定义 .pdf

《2022年数据库游标定义 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库游标定义 .pdf（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1、用游标实现以下功能1）计算订单的总额useNorthWind GO DECLAREeachmoney,totalmoney settotal=0 DECLAREtotalcurCURSOR LOCALSCROLL FORSELECT(UnitPrice*Quantity*(1-Discount)aseachFROM Order Details opentotalcur FETCHNEXTFROM totalcurintoeachWHILEFETCH_STATUS=0 BEGINsettotal=total+eachFETCHNEXT FROM totalcurintoeach END CL

2、OSEtotalcur DEALLOCATEtotalcur printtotalGO2）打印出所有金额大于1000 元的订单useNorthWind GO 名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 5 页 -declareidint declareprintcurCURSOR LOCALSCROLL FORSELECTOrderIDFROM OrdersopenprintcurFETCHNEXTFROM printcurintoid WHILEFETCH_STATUS=0 BEGIN declareeachtotalmoneydeclaresum money setsum

3、=0 declareeachcurCURSOR LOCALSCROLL FORSELECT(UnitPrice*Quantity*(1-Discount)aseachtotalFROM Order DetailswhereOrder Details.OrderID=id openeachcur FETCHNEXT FROM eachcurintoeachtotal WHILEFETCH_STATUS=0 BEGIN setsum=sum+eachtotal FETCHNEXT FROM eachcurintoeachtotal if(sum=1000)printconvert(char(10)

4、,id)+此ID 下的订单额不小于1000 END CLOSEeachcur DEALLOCATEeachcur FETCHNEXT FROM printcurintoid END CLOSEprintcur DEALLOCATEprintcur GO名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 5 页 -2、给定一个供应商的编号，求出所有受影响的订单useNorthWind go ifOBJECT_ID(affect)isnotnull dropprocedureaffect go 名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 5 页 -createprocedureaffect anysupplieridint as begin select*fromProducts,Orders,Order DetailswhereOrder Details.ProductID=Products.ProductIDandOrders.OrderID=Order Details.OrderIDandProducts.SupplierID=anysupplierid end goexecaffect5名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 5 页 -名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 5 页 -