sql游标和动态游标



--普通游标

------------------------------------------------------
declare @sql nvarchar(500) --sql必须是nvarchar类型
declare @task_id bigint
declare @task_ids varchar(30)
declare @orgcode varchar(2)
declare @count int

declare @tableName varchar(100)--这个变量是个varchar

declare @datatableName varchar(100)
declare orgcode_cursor cursor
for
select orgcode from lib_org where len(orgcode)=2 and orgcode <> -1
open orgcode_cursor
fetch next from orgcode_cursor into @orgcode
while(@@fetch_status =0)
begin
---开始取一个省的所有任务id

set @tableName = 'lib_task_p'+@orgcode

--set @datatableName = 'lib_task3_p'+@orgcode
--set @task_ids = '3,6,9'
--set @datatableName = 'lib_task2_p'+@orgcode
-- set @task_ids = '2,5,8'
set @datatableName = 'lib_task1_p'+@orgcode
set @task_ids = '1,4,7'


set @sql = 'select @a=count(*) from '+@tableName+' where uiid not in(select fk_task_id from '+@datatableName+') and yyyy = 2009 and mm in(7,8) and fk_task_level = 3 and fk_tasktype_id in('+@task_ids+') and actstatus<>0'
EXEC sp_executesql @sql,N'@a decimal(18,0) output',@count output

if @count<>0
begin
print 'orgcode:'

print @orgcode
print @count
end

---结束取一个省的所有任务id
fetch next from orgcode_cursor into @orgcode
end
close orgcode_cursor
deallocate orgcode_cursor


--动态游标

declare @sql nvarchar(500)
declare @tablename varchar(20)
declare @task_id bigint
set @tablename = 'lib_org'
--只有下面两句有区别
set @sql = 'declare pcursor cursor for select uiid from '+@tablename
exec (@sql)

open pcursor
fetch next from pcursor into @task_id
while(@@fetch_status=0)
begin
print @task_id
fetch next from pcursor into @task_id
end
close pcursor
deallocate pcursor

---------------------------------------


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值