Mark jiaguoxinzhi linyee
因为sql用得少,,经常会忘记怎么写。。
找得多了,,不如自己整个简单的标例,备忘。
declare @chlid bigint --定义变量用于保存游标对应记录的值
declare @pPath varchar --定义变量
declare cur2 cursor for select channelid,ChannelPath from dbo.Channel where ChannelDeep=2 --游标指向记录集
open cur2 --打开
fetch next from cur2 into @chlid,@pPath --获取下一句,,
while @@FETCH_STATUS=0 --状态未结束?
begin
--print @chlid
--print @pPath
update dbo.Channel set ChannelDeep=3,ChannelPath=@pPath+CAST(ChannelParent as varchar(20))+'/' where ChannelParent=@chlid --执行更新
fetch next from cur2 into @chlid,@pPath --继续获取下一句
end
close cur2 --关闭
deallocate cur2 --释放
路径中含本身
--UPDATE [dbo].[Channel] SET [ChannelDeep] = 0 ,[ChannelPath] ='/'+CAST(ChannelId as varchar(20))+'/' WHERE ChannelParent=0
declare @chlid bigint --定义变量用于保存游标对应记录的值
declare @pPath varchar --定义变量
declare cur1 cursor for select channelid,ChannelPath from dbo.Channel where ChannelDeep=2 --游标指向记录集
open cur1 --打开
fetch next from cur1 into @chlid,@pPath --获取下一句,,
while @@FETCH_STATUS=0 --状态未结束?
begin
--print @chlid
--print @pPath
update dbo.Channel set ChannelDeep=3,ChannelPath=@pPath+CAST(ChannelId as varchar(20))+'/' where ChannelParent=@chlid --执行更新
fetch next from cur1 into @chlid,@pPath --继续获取下一句
end
close cur1 --关闭
deallocate cur1 --释放
GO
再完善点点
declare @chlid bigint --定义变量用于保存游标对应记录的值
declare @pPath varchar(255) --定义变量
SET nocount ON --忽略行数显示
declare cur1 cursor --声明游标
for select channelid,ChannelPath from dbo.Channel where ChannelDeep=3 --游标指向记录集
open cur1 --打开
fetch next from cur1 into @chlid,@pPath --获取下一句,,
while @@FETCH_STATUS=0 --状态未结束?
begin
print @chlid
print @pPath
update dbo.Channel set ChannelDeep=4,ChannelPath=@pPath+CAST(ChannelId as varchar(20))+'/' where ChannelParent=@chlid --执行更新
fetch next from cur1 into @chlid,@pPath --继续获取下一句
end
close cur1 --关闭
deallocate cur1 --释放
SET nocount OFF --打开计数