Sql Server存储过程和游标的配合操作
本段代码主要为了记录存储过程以及游标的使用,防止以后自己忘记
知识点:1、存储过程书写
2、游标书写
3、游标循环更新记录
create proc saletarget
as
declare @oweridname varchar(20)
declare @hmp_fiscalyear int
declare @hmp_businessunitidname varchar(20)
declare @hmp_nationterritoryidname varchar(20)
declare @hmp_currentyeartargetwithvat decimal(18,2)
declare @hmp_month int
declare @hmp_taxtargetamount decimal(18,2)
declare @quarters varchar(10)
declare @ac decimal(18,2)
declare @saleach decimal(10,2)
declare @yaact decimal(18,2)
declare @growth decimal(10,2)
declare @hmp_salestargetdetailId varchar(50)
begin
select * into #temp from(select A.OwnerIdName,A.hmp_fiscalyear,hmp_BusinessUnitIdName,hmp_nationterritoryidName,isnull(hmp_currentyeartargetwithvat,0) as hmp_currentyeartargetwithvat,hmp_month,isnull(hmp_taxtargetamount,0) as hmp_taxtargetamount,cast('' as varchar(10)) as quarters,cast(0 as decimal(18,2)) as ac,cast(0 as decimal(10,2)) as saleach,cast(0 as decimal(18,2)) as yaact,cast(0 as decimal(10,2)) as growth,b.hmp_salestargetdetailId from hmp_target A INNER JOIN hmp_salestargetdetail B ON A.hmp_targetId = B.hmp_targetid where hmp_month is not null)a
DECLARE mycursor CURSOR
FOR
select * from #temp
open mycursor
fetch next from mycursor into @oweridname,@hmp_fiscalyear,@hmp_businessunitidname,@hmp_nationterritoryidname,@hmp_currentyeartargetwithvat,@hmp_month,@hmp_taxtargetamount,@quarters,@ac,@saleach,@yaact,@growth,@hmp_salestargetdetailId
--判断游标的状态
-- 0 fetch语句成功
---1 fetch语句失败或此行不在结果集中
---2 被提取的行不存在
WHILE (@@fetch_status = 0)
begin
--获取本月完成数
select @ac = SUM(hmp_totaltaxprices) from hmp_deliverylistorder where hmp_SystemUseridName = @oweridname and hmp_deliverydate>=dateadd(month, datediff(month, 0, @hmp_fiscalyear+'-'+@hmp_month+'-01'), 0) and hmp_deliverydate<=dateadd(month, datediff(month, 0, dateadd(month, 1, @hmp_fiscalyear+'-'+@hmp_month+'-01')), -1)
--获取去年同期完成数
select @yaact = SUM(hmp_totaltaxprices) from hmp_deliverylistorder where hmp_SystemUseridName = @oweridname and hmp_deliverydate>=dateadd(yy,-1,dateadd(month, datediff(month, 0, @hmp_fiscalyear+'-'+@hmp_month+'-01'), 0)) and hmp_deliverydate<=dateadd(yy,-1,dateadd(month, datediff(month, 0, dateadd(month, 1, @hmp_fiscalyear+'-'+@hmp_month+'-01')), -1))
--目标完成率
if @hmp_taxtargetamount>0
set @saleach = @ac/@hmp_taxtargetamount*100
else
set @saleach = 0
--增长率
if @yaact>0
set @growth = (@ac-@yaact)/@yaact*100
else
set @growth = 0
update #temp set ac=@ac,saleach=@saleach,yaact=@yaact,growth=@growth where hmp_salestargetdetailId=@hmp_salestargetdetailId
fetch next from mycursor into @oweridname,@hmp_fiscalyear,@hmp_businessunitidname,@hmp_nationterritoryidname,@hmp_currentyeartargetwithvat,@hmp_month,@hmp_taxtargetamount,@quarters,@ac,@saleach,@yaact,@growth,@hmp_salestargetdetailId
end
close mycursor
deallocate mycursor
select * from #temp
end
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 【.NET】调用本地 Deepseek 模型