内嵌游标问题

So_CooL 2009-09-26 02:10:42
执行游标一直执行没反应，是什么问题呢。
Alter procedure P_getFlowRecordBySearch
(
@ordNo varchar(50),
@systemCode varchar(50),
@uid varchar(30),
@beginDt datetime,
@endDt datetime,
@status varchar(2),
@Applicant varchar(30)
)
As

Begin

Declare @temp table (Guid uniqueidentifier,status varchar(5))
Declare @Guid uniqueidentifier
Declare @node int
declare @isActionValue varchar(20)
declare @TempValue varchar(20)
declare mycurosr cursor for
select GUID ,ordNo ,node from sysFlow where applicant = @applicant and uid >= @beginDt and uid <= @endDt
open mycurosr
fetch next from mycurosr into @Guid,@ordNo,@node
while @@fetch_status = 0
set @TempValue = ''
declare TempCursor cursor for select isnull(isAction,'N') from sysflow where ordNo = @ordNo group by isAction
open TempCursor
fetch next from TempCursor into @isActionValue
while @@fetch_status = 0

declare @RowCount1 int
declare @RowCount11 int

declare @RowCount2 int
declare @RowCount22 int

set @TempValue = @TempValue + @isActionValue

if(@TempValue = 'N')
insert into @temp(Guid,status) values (@Guid,'N')--待处理

if @TempValue ='Y'
insert into @temp(Guid,status) values (@Guid,'Y')--已完成

if len(@TempValue) >= 2

select @RowCount1 = count(*) from sysflow where ordNo = @ordNo and node =@node -1 and isaction is not null and actionDate is not null
select @RowCount11 = count(*) from sysflow where ordNo = @ordNo and Applicant = @applicant and isAction is null and actionDate is null
if @RowCount1 = 1 and @RowCount11 = 1
insert into @temp(Guid,status) values (@Guid,'R')--处理中

select @RowCount2 = count(*) from sysflow where ordNo = @ordNo and Applicant = @applicant and isAction is not null and actionDate is not null
select @RowCount22 = count(*) from sysflow where ordNo = @ordNo and node = @node + 1 and isaction is null and actionDate is null
if @RowCount2 = 1 and @RowCount22 = 1
insert into @temp(Guid,status) values (@Guid,'F')--已处理

fetch next from TempCursor into @isActionValue
close TempCursor
fetch next from mycurosr into @Guid ,@ordNo ,@node

select distinct A.GUID as Id,A.ordNo,A.systemName,A.FrmName,
dbo.f_getUserChineseNameBySysFlowUid(A.uid) as uid,A.uidDt as uidDt,dbo.f_getCurrentApprovers(A.ordNo) as CurrentApprove,ActionDate,
dbo.f_getCurrentApproversStatus(A.ordNo) as Status,dbo.f_getIsApprove(A.ordNo,A.Applicant) as IsApprove
from sysflow A inner join @temp B on A.guid = B.Guid
where A.ordNo = isnull(@ordNo,A.ordNo)
and A.systemCode = isnull(@systemCode,A.systemCode)
and A.uid = isnull(@uid,A.uid) and A.uidDt >= isnull(@beginDt,A.uidDt)
and A.uidDt <= isnull(@endDt,A.uidDt)
and A.Applicant = isnull(@Applicant,A.Applicant)
and B.status = isnull(@status,B.status)
order by A.uidDt desc

close mycurosr

End
...全文
92 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
bancxc 2009-09-28
  • 打赏
  • 举报
 回复
deallocate
yangdingyu8686 2009-09-28
  • 打赏
  • 举报
 回复
太乱了 还是先在IF 和WHILE下面加上
BEGIN
END
看看吧
So_CooL 2009-09-28
  • 打赏
  • 举报
 回复
where条件这样写是否有问题,为什么加上and A.systemCode = isnull(@systemCode,A.systemCode)
就没记录了

where A.ordNo = isnull(@ordNo,A.ordNo)
and A.systemCode = isnull(@systemCode,A.systemCode)
and A.uid = isnull(@uid,A.uid)
and A.uidDt >= @beginDt
and A.uidDt <= @endDt
and A.uidDt >= isnull(@beginDt,A.uidDt)
and A.uidDt <= isnull(@endDt,A.uidDt)
and A.Applicant = isnull(@Applicant,A.Applicant)
and B.status = isnull(@status,B.status)
order by A.uidDt desc
无锡阔微信息科技 2009-09-27
  • 打赏
  • 举报
 回复
太长了
So_CooL 2009-09-26
  • 打赏
  • 举报
 回复
如果是日期类型的不能写成这样的
and A.uidDt >= isnull(@beginDt,A.uidDt)
and A.uidDt <= isnull(@endDt,A.uidDt)
吗？
where A.ordNo = isnull(@ordNo,A.ordNo)
and A.systemCode = isnull(@systemCode,A.systemCode)
and A.uid = isnull(@uid,A.uid)
-- and A.uidDt >= isnull(@beginDt,A.uidDt)
-- and A.uidDt <= isnull(@endDt,A.uidDt)
and A.Applicant = isnull(@Applicant,A.Applicant)
and B.status = isnull(@status,B.status)
luoyoumou 2009-09-26
  • 打赏
  • 举报
 回复 


--其实：我觉得你这完全没有必要用嵌套游标，外层用While循环，里层游标不变就OK了！

--游标嵌套是最可怕的事情，它会成倍的影响你的速度！[/
luoyoumou1202 2009-09-26
  • 打赏
  • 举报
 回复
Alter procedure P_getFlowRecordBySearch
(
@ordNo varchar(50),
@systemCode varchar(50),
@uid varchar(30),
@beginDt datetime,
@endDt datetime,
@status varchar(2),
@Applicant varchar(30)
)
As

Begin

Declare @temp table (Guid uniqueidentifier,status varchar(5))
Declare @Guid uniqueidentifier
Declare @node int
declare @isActionValue varchar(20)
declare @TempValue varchar(20)

declare mycurosr cursor for
select GUID ,ordNo ,node from sysFlow where applicant = @applicant and uid >= @beginDt and uid <= @endDt
open mycurosr
fetch next from mycurosr into @Guid,@ordNo,@node
while @@fetch_status = 0
set @TempValue = ''
declare TempCursor cursor for select isnull(isAction,'N') from sysflow where ordNo = @ordNo group by isAction
open TempCursor
fetch next from TempCursor into @isActionValue
while @@fetch_status = 0
declare @RowCount1 int
declare @RowCount11 int
declare @RowCount2 int
declare @RowCount22 int
set @TempValue = @TempValue + @isActionValue

if(@TempValue = 'N')
insert into @temp(Guid,status) values (@Guid,'N')--待处理

if @TempValue ='Y'
insert into @temp(Guid,status) values (@Guid,'Y')--已完成

if len(@TempValue) >= 2
select @RowCount1 = count(*) from sysflow where ordNo = @ordNo and node =@node -1 and isaction is not null and actionDate is not null
select @RowCount11 = count(*) from sysflow where ordNo = @ordNo and Applicant = @applicant and isAction is null and actionDate is null

if @RowCount1 = 1 and @RowCount11 = 1
insert into @temp(Guid,status) values (@Guid,'R')--处理中
select @RowCount2 = count(*) from sysflow where ordNo = @ordNo and Applicant = @applicant and isAction is not null and actionDate is not null
select @RowCount22 = count(*) from sysflow where ordNo = @ordNo and node = @node + 1 and isaction is null and actionDate is null

if @RowCount2 = 1 and @RowCount22 = 1
insert into @temp(Guid,status) values (@Guid,'F')--已处理

fetch next from TempCursor into @isActionValue
close TempCursor

fetch next from mycurosr into @Guid ,@ordNo ,@node

select distinct A.GUID as Id,A.ordNo,A.systemName,A.FrmName,
dbo.f_getUserChineseNameBySysFlowUid(A.uid) as uid,A.uidDt as uidDt,dbo.f_getCurrentApprovers(A.ordNo) as CurrentApprove,ActionDate,
dbo.f_getCurrentApproversStatus(A.ordNo) as Status,dbo.f_getIsApprove(A.ordNo,A.Applicant) as IsApprove
from sysflow A inner join @temp B on A.guid = B.Guid
where A.ordNo = isnull(@ordNo,A.ordNo)
and A.systemCode = isnull(@systemCode,A.systemCode)
and A.uid = isnull(@uid,A.uid) and A.uidDt >= isnull(@beginDt,A.uidDt)
and A.uidDt <= isnull(@endDt,A.uidDt)
and A.Applicant = isnull(@Applicant,A.Applicant)
and B.status = isnull(@status,B.status)
order by A.uidDt desc

close mycurosr

End


---你先在外层游标set @TempValue = ''
---然后又在里层游标里面set @TempValue = @TempValue + @isActionValue 　（这不成了追加吗？）
---追加后，你再去判断@TempValue的值，里层游标一次循环后，@TempValue 里面还仅仅只有一个 N 吗？
luoyoumou1202 2009-09-26
  • 打赏
  • 举报
 回复 
--SELECT 语句为什么放在外层游标里面？
luoyoumou1202 2009-09-26
  • 打赏
  • 举报
 回复 
--SELECT 语句为什么放在游标里面？
华夏小卒 2009-09-26
  • 打赏
  • 举报
 回复
表示这之间的代码，都执行完，再进行下一次循环
So_CooL 2009-09-26
  • 打赏
  • 举报
 回复
为什么要加Being end
华夏小卒 2009-09-26
  • 打赏
  • 举报
 回复
沟沟说的对，你这么长的代码，里面只用了一个begin 。。。end

在所有单个模块中都要用
-狙击手- 2009-09-26
  • 打赏
  • 举报
 回复
while @@fetch_status = 0
begin
...
...
end
playwarcraft 2009-09-26
  • 打赏
  • 举报
 回复
SQL 語法里

while XXX
BEGIN
....
END
unity内嵌网页BrowserUI
适用于做unity内嵌网页，只需要下载导入unity即可。功能 - 将网站渲染为2D或3D对象 - Chromium后端; 一流的支持：HTML，CSS和JavaScript - 从Unity 调用JavaScript¹ - 从JavaScript调用Unity¹ - 透明或不透明的...
Unity内嵌网页插件
- Mipmap仿真着色器：更快的纹理更新，无闪烁 - 拦截新窗口并为其创建场景对象 - 极简主义JSON库 - <视频>支持Chromium的开放编解码器（h.264 / mp3未获得许可） - 渲染SVG - 自定义游标 - Adob​​e Flash的实验...
SQL Server 2014 索引和游标
主讲内容： 第一讲 课程简介 第二讲 索引简介 第三讲 创建索引 第四讲 查看索引 第五讲 索引管理 第六讲 全文索引 等课程
Embedded Browser 2.0.0(Unity内嵌网页插件)
- 自定义游标 - Adob​​e Flash的实验支持，页面检查器 - 编辑/删除cookie - 可自定义的鼠标/键盘输入 - 包括：鼠标单击，FPS或GUI - 包括：VR（在Windows上测试HTC Vive和Oculus Touch） - 包括：...
SQL21日自学通
第七天子查询内嵌的SQL 子句136 目标136 建立一个子查询136 在子查询中使用汇总函数140 子查询的嵌套141 相关子查询144 EXISTS ANY ALL 的使用147 总结151 问与答151 校练场152 练习153 第一周回顾154 预览154 第...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章