存储过程及游标的使用

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dcms_hy15].[ddddd] AS BEGIN declare @cardTypeId bigint declare @content bigint declare @simTypeId bigint declare @cardProvider varchar(50) declare @inputNum varchar(300) declare @codeGroup bigint declare @lastActiveDate datetime declare @requestId varchar(50) declare @amount bigint declare @count bigint declare @zhaId varchar(50) set @count = 0 declare cursor1 cursor local for select card_type_id_,content_,sim_type_id_,card_provider_,input_num_,code_group_, last_active_date_,request_id_,count(id_)amount_ from dcms_hy15.SIM_CARD_RESOURCE_ where (msisdn_ not like '%000' and msisdn_ not like '%111' and msisdn_ not like '%222' and msisdn_ not like '%333' and msisdn_ not like '%444' and msisdn_ not like '%555' and msisdn_ not like '%666' and msisdn_ not like '%777' and msisdn_ not like '%888' and msisdn_ not like '%999') and status_=4 and unit_type_id_=400 and org_id_='SQFGS' group by card_type_id_,content_,sim_type_id_,card_provider_,input_num_,code_group_, last_active_date_,request_id_ open cursor1 fetch next from cursor1 into @cardTypeId,@content,@simTypeId,@cardProvider,@inputNum,@codeGroup,@lastActiveDate,@requestId,@amount while (@@fetch_status=0) begin set @count = @count + 1 set @zhaId = '20090423_01_01_' + rtrim(ltrim(str(@count))) --添加札记录 INSERT INTO [dcms_hy15].[SIM_CARD_RESOURCE_] ([ORG_ID_], [UNIT_TYPE_ID_], [PACKER_], [AMOUNT_], [IS_LOCKED_], [ZHA_ID_], [CARD_TYPE_ID_], [CONTENT_] , [SIM_TYPE_ID_], [CARD_PROVIDER_], [INPUT_NUM_], [CODE_GROUP_], [LAST_ACTIVE_DATE_], [INPUT_DATE_TIME_] , [IS_INTEGRAL_], [IS_ACTIVE_], [REQUEST_ID_], [STATUS_], [LIFE_STATUS_]) values('SQFGS', 300, @zhaId, @amount, 'N', @zhaId, @cardTypeId, @content, @simTypeId, @cardProvider, @inputNum, @codeGroup, @lastActiveDate, '2009-04-23', 0, 0, @requestId, 2, 101) --更新号码记录 update dcms_hy15.SIM_CARD_RESOURCE_ set zha_id_=@zhaId,wait_back_date_=null,status_=2 where (msisdn_ not like '%000' and msisdn_ not like '%111' and msisdn_ not like '%222' and msisdn_ not like '%333' and msisdn_ not like '%444' and msisdn_ not like '%555' and msisdn_ not like '%666' and msisdn_ not like '%777' and msisdn_ not like '%888' and msisdn_ not like '%999') and (card_type_id_=@cardTypeId and content_=@content and sim_type_id_=@simTypeId and card_provider_=@cardProvider and input_num_=@inputNum and code_group_=@codeGroup and last_active_date_=@lastActiveDate and request_id_=@requestId) and status_=4 and unit_type_id_=400 and org_id_='SQFGS' fetch next from cursor1 into @cardTypeId,@content,@simTypeId,@cardProvider,@inputNum,@codeGroup,@lastActiveDate,@requestId,@amount end close cursor1 deallocate cursor1 END GO