今天寫了一個游標(biāo)，但是不知道怎么在這游標(biāo)里定義一個變量來記錄執(zhí)行時影響條數(shù)，并插入兩條數(shù)據(jù)時就跳出循環(huán)結(jié)束游標(biāo)。

沒有想出轍，實屬頭疼。附代碼如下：

--游標(biāo) declare @userID uniqueidentifier--userid declare y_curr cursor for select top 2 userID from Hope_Users_db.dbo.u_Users order by addTime descopen y_curr --打開游標(biāo) fetch next from y_curr into @userID ----開始循環(huán)游標(biāo)變量 while(@@fetch_status=0)---返回被FETCH 語句執(zhí)行的最后游標(biāo)的狀態(tài)，而不是任何當(dāng)前被連接打開的游標(biāo)的狀態(tài)。 beginif exists (select * from Q_enterprise where userID = @userID)beginprint @userIDendelsebeginprint @userIDinsert into Q_enterpriseselect distinct u.userID, u.userType,u.SyncCharityId as CharityId,u.userTrueName,u.userLogo,0 as donationAmount,e.enterpriseContactMobile,e.enterprisePurpose,0 as enterpriseLongitude,0 as enterpriseLatitude,'' as Infoproportion,0 as sharesum,0 as praisesum,0 as Donationsum,0 as Farvritessum,0 as Donationsum30,u.addTime,'' as updatetimefrom Hope_Users_db.dbo.u_Users uinner join Hope_Donation_DB.dbo.d_Donation don u.userID=d.userIDinner join Hope_Users_db.dbo.u_Enterprise eon u.userID = e.userIDwhere u.userID=@userIDendfetch next from y_curr into @userID --開始循環(huán)游標(biāo)變量 end close y_curr--關(guān)閉游標(biāo) deallocate y_curr --釋放游標(biāo) go

游標(biāo)實例二：循環(huán)讀取數(shù)據(jù)表并進(jìn)行各種計算并添加數(shù)據(jù)到臨時表，結(jié)果輸出臨時表

USE [CenterDB] GO/****** Object: StoredProcedure [dbo].[Pro_GetSalary] Script Date: 2017/5/23 9:26:30 ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO--讀取工單詳細(xì),并計算每個工單機(jī)長的提成 ALTER PROCEDURE [dbo].[Pro_GetSalary](@sTime nvarchar(255),--查詢工單EndTime的開始時間節(jié)點@eTime nvarchar(255),--查詢工單EndTime的結(jié)束時間節(jié)點@pageIndex int,--開始頁碼@pageSize int, --結(jié)束頁碼@recordCount int output --總記錄數(shù) ) AS BEGINdeclare@FinishEName nvarchar(40),--操作人名稱@DevName varchar(100),--設(shè)備名稱@PrtTotalPageCount int,--單手印數(shù)@PrtSumCount2 int,--總印數(shù)@EndTime DateTime,--結(jié)束時間@ProductName varchar(1202),--印件名稱@knifeCount float,--刀數(shù)@KnifeMoney float,--切刀的提成@setCount float, --套數(shù)@setMoney float,--印數(shù)的提成@kaiDu nvarchar(255), --開度@baiNumber int, --百位數(shù)字@PrtPCount int, --正數(shù)（名片張數(shù)）@DevGroupName nvarchar(255),--機(jī)組@boxNumber int,--名片盒數(shù)@ShouShu varchar(50),--手?jǐn)?shù)@MPStyle varchar(50),--拼版方式@strSql nvarchar(max), --拼接sql@startNumber int,--開始頁碼@endNumber int --結(jié)束頁碼--BEGIN TRAN --聲明工單事務(wù)set @strSql = 'declare pNumber_cursor CURSOR FOR 'set @strSql = @strSql + ' select A.FinishEName,A.DevName,A.PrtTotalPageCount,A.PrtSumCount2,A.EndTime,A.ProductName,A.PrtPCount,A.ShouShu,A.MPStyle,B.DevGroupName,B.KaiDu';set @strSql = @strSql +' from QuePlanArrage as A inner join DevGroup B on A.DevName = B.DevName where 1=1';if @sTime != '' and @eTime != ''beginset @strSql = @strSql +' and A.EndTime between cast('''+@sTime+' 00:00:00.000'' as datetime) and cast('''+@eTime+' 23:59:59.999'' as datetime)';endif @sTime != '' and @eTime = ''beginset @strSql = @strSql +' and A.EndTime >= cast('''+@sTime+' 00:00:00.000'' as datetime)';endif @sTime = '' and @eTime != ''beginset @strSql = @strSql +' and A.EndTime <= cast('''+@eTime+' 23:59:59.999'' as datetime)';endset @strSql = @strSql + ' and A.State=13 and (A.JobID=''9'' or A.JobID=''10'') Order by A.DevName,A.ID' ;EXEC sp_executesql @strSql--新建臨時表if object_id(N'##SalaryInfo',N'U') is not nullbegindrop table ##SalaryInfoendcreate table ##SalaryInfo(ID int identity(1,1),FinishEName nvarchar(40),DevName varchar(100) null,PrtTotalPageCount int,PrtSumCount2 int,EndTime DateTime,ProductName varchar(1202),SetNumber int,KnifeMoney Decimal(12,1),setMoney Decimal(12,1),Salary Decimal(12,1))--打開游標(biāo)open pNumber_cursorWHILE @@FETCH_STATUS = 0 --返回被FETCH語句執(zhí)行的最后游標(biāo)的狀態(tài)，而不是任何當(dāng)前被連接打開的游標(biāo)的狀態(tài)。begin--開始循環(huán)游標(biāo)變量FETCH NEXT FROM pNumber_cursor INTO @FinishEName,@DevName,@PrtTotalPageCount,@PrtSumCount2,@EndTime,@ProductName,@PrtPCount,@ShouShu,@MPStyle,@DevGroupName,@KaiDuif @DevName!=null or @DevName != ''--說明當(dāng)前行有數(shù)據(jù)begin--執(zhí)行sql操作--計算總印數(shù)結(jié)束--根據(jù)總印數(shù)計算套數(shù)開始set @baiNumber = @PrtSumCount2/100%10;if @baiNumber <2beginset @PrtSumCount2 = cast((cast(@PrtSumCount2/1000 as varchar)+'000') as int);endif @PrtSumCount2 <3300beginset @setCount = 1;endelsebeginset @setCount = round(cast(@PrtSumCount2 as float)/cast(3000 as float),1); end--根據(jù)總印數(shù)計算套數(shù)結(jié)束--根據(jù)套數(shù)計算提成開始if @DevGroupName = 'AAAAA'beginset @setMoney = @setCount * 17;endif @DevGroupName = 'BBBBB'beginif CHARINDEX('名片',@ProductName)>0beginset @setMoney = @setCount *16;endelsebeginset @setMoney = @setCount *23;endendif @DevGroupName ='CCCCC' beginset @setMoney = @setCount*13;endif @DevGroupName ='DDDDD' beginset @setMoney = @setCount*17;endif @DevGroupName ='EEEEE'beginset @setMoney = @setCount*19.5;end--根據(jù)套數(shù)計算提成結(jié)束if charindex('不干膠',@ProductName)>0 --類別為不干膠Begindeclare @exKnife int --基數(shù)--轉(zhuǎn)換刀數(shù)if @kaiDu = '八開'beginset @exKnife = 1;endelse if @kaiDu = '四開'beginset @exKnife = 2;endelsebeginset @exKnife = 3;endif @PrtSumCount2%500 > 50beginset @knifeCount =(@PrtSumCount2/500+1)*@exKnife;endelsebeginset @knifeCount = (@PrtSumCount2/500)*@exknife;endset @KnifeMoney = @knifeCount*1.9;EndElse if CHARINDEX('名片',@productName)>0 --類別為名片Beginset @boxNumber = @PrtPCount/100;if @boxNumber <2.5beginset @knifeCount = 1;endelsebeginset @knifeCount = @boxNumber/2.5;endset @KnifeMoney = @knifeCount*4.2;EndElse --類別為彩頁Begindeclare @baseNumber int, @exchangeKnife int --基數(shù)--轉(zhuǎn)換刀數(shù)if @kaiDu='全開'beginset @baseNumber = 1000;set @exchangeKnife = 3;endif @kaiDu ='對開'beginset @baseNumber=1000;set @exchangeKnife = 1;endif @kaiDu ='四開'beginset @baseNumber = 2000;set @exchangeKnife = 1;endif @kaiDu ='八開'beginset @baseNumber = 2500;set @exchangeKnife = 1;endif @kaiDu ='十六開'beginset @baseNumber = 5000;set @exchangeKnife = 1;endif @kaiDu ='三十二開及其以上'beginset @baseNumber = 10000;set @exchangeKnife = 1;end--彩頁：開始計算刀數(shù)if @PrtSumCount2 < @baseNumberbeginset @knifeCount=1*@exchangeKnifeendelse if @PrtSumCount2%@baseNumber>100beginset @knifeCount=(@PrtSumCount2/@baseNumber+1)*@exchangeKnifeendelsebeginset @knifeCount = (@PrtSumCount2/@baseNumber)*@exchangeKnifeendset @KnifeMoney = @knifeCount*2;End--刀數(shù)和套數(shù)均已有結(jié)果--判斷是否已有臨時表，無則新建添加有則添加 insert into ##SalaryInfo(FinishEName,DevName,PrtTotalPageCount,PrtSumCount2,EndTime,ProductName,SetNumber,KnifeMoney,setMoney,Salary) values(@FinishEName,@DevName,@PrtTotalPageCount,@PrtSumCount2,@EndTime,@ProductName,@setCount,@KnifeMoney,@setMoney,(@KnifeMoney+@setMoney))endend--分頁代碼if @pageIndex = 1beginset @startNumber = 1;endelsebeginset @startNumber = (@PageIndex-1)*@pageSize + 1;end set @endNumber = @pageIndex*@pageSize;--計算總記錄數(shù)DECLARE @strCountSql NVARCHAR(1000)SET @strCountSql = 'Select @reCount = count(1) FROM (select FinishEName,DevName from ##SalaryInfo group by FinishEName,DevName) A'EXEC sp_executesql @strCountSql, N'@reCount int OUTPUT', @recordCount OUTPUT--select FinishEName,DevName,SUM(SetNumber) as SetNumberSum,SUM(PrtTotalPageCount) as PrtTotalPageCountSum,SUM(PrtSumCount2) as PrtSumCount2Sum,SUM(KnifeMoney) as KnifeMoneySum,SUM(setMoney) as setMoneySum,SUM(Salary) as SalarySum--from ##SalaryInfo --group by FinishEName,DevNameselect * from (select ROW_NUMBER()over(order by DevName) as rownum,FinishEName,DevName,SUM(SetNumber) as SetNumberSum,SUM(PrtTotalPageCount) as PrtTotalPageCountSum,SUM(PrtSumCount2) as PrtSumCount2Sum,SUM(KnifeMoney) as KnifeMoneySum,SUM(setMoney) as setMoneySum,SUM(Salary) as SalarySum from ##SalaryInfo where 1=1 group by FinishEName,DevName) AS Dwhere rownum between @startNumber and @endNumberCLOSE pNumber_cursor--關(guān)閉游標(biāo)DEALLOCATE pNumber_cursor--釋放游標(biāo)--刪除臨時表drop table ##SalaryInfoENDGO

?

轉(zhuǎn)載于:https://www.cnblogs.com/Wbely/p/4193976.html

超強(qiáng)干貨來襲 云風(fēng)專訪：近40年碼齡，通宵達(dá)旦的技術(shù)人生

總結(jié)

以上是生活随笔為你收集整理的游标实例的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。