SQL 必须弄懂的东西

Question

create procedure c_dzh_assignOwner_Distribution(@handleby char(6)) 
as 
begin 
    declare
         @peo_staff char(6)
        ,@peo_assigncount int
        ,@peo_rolegroup char(6)
        ,@peo_objectivetype char(6)
        ,@midvar1 int
        ,@customer_guid char(36)
        ,@newguid char(36)
        ,@custlists varchar(100) 
    declare cur_sor1 cursor for 
    select cur_staff,assigncount,cur_rolegroup,objectivetype_id 
    from c_dzh_assigninfo 
    where handleby=@handleby 
    open  cur_sor1 
    fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
    /* Pos 1 */
    while @@fetch_status=0 
    set @midvar1=0 -- 这句应该在 Pos 1 位置上吧? 或者 Pos 1'?
    begin 
        /* Pos 1' */
        declare cur_sor2 cursor for 
        select customer_guid 
        from c_dzh_Owner_Distribution 
        where handleby=@handleby 
        open  cur_sor2 
        fetch next from cur_sor2 into @customer_guid 
        while @@fetch_status=0 
        begin 
            set @newguid=newid()    
            insert into objective (
                 objective_guid
                ,objectivetype_id
                ,handleby_id
                ,handlegroup_id
                ,expireddate
                ,customer_guid
                ,objectivestatus
                ,createdby
                ,createddate
                ,modifiedby
                ,modifieddate)    
            values (
                 @newguid
                ,@peo_objectivetype
                ,@peo_staff
                ,@peo_rolegroup
                ,'2050-01-01 00:00:00'
                ,@customer_guid
                ,'OPEN'
                ,@handleby
                ,getdate()
                ,@handleby
                ,getdate())
                
            insert into oblist (
                 oblist_guid
                ,objective_guid
                ,objectivetype_id
                ,expectstarttime
                ,expectendtime
                ,obstatus
                ,max_attempt
                ,handleby_id
                ,handlegroup_id
                ,createdby
                ,createddate
                ,modifiedby
                ,modifieddate)    
            values (
                 newid()
                ,@newguid
                ,@peo_objectivetype
                ,getdate()
                ,'2050-01-01 00:00:00'
                ,'OPEN'
                ,'5000'
                ,@peo_staff
                ,@peo_rolegroup
                ,@handleby
                ,getdate()
                ,@handleby
                ,getdate())
                
            update c_dzh_Owner_Distribution set
                 flag=1 
            where customer_guid=@customer_guid 
            and handleby=@handleby 
            and flag=0 
            
            update customer set
                 owner=@peo_staff 
            where customer_guid=@customer_guid 
            
            update ''+@custlists+'' set
                 flagassign=1 
            where customer_guid=@customer_guid 
            
            set @midvar1=@midvar1+1 
        fetch next from cur_sor2 into @customer_guid 
        end 
        /* 下面这个段落: 当@midvar1!=@peo_assigncount时，无法进行Fetch next, 如何从cur_sor1 中取得数据呢? */
        if(@midvar1=@peo_assigncount) begin 
            fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
        end 
        
        /* Pos 2 */
    end 
    close @cur_sor1 -- 应该是 close cur_sor1 
    deallocate @cur_sor1  -- 应该是 deallocate cur_sor1 
    /* 下面这两行 Pos 2 位置*/
    close @cur_sor2  -- 应该是 close cur_sor2
    deallocate @cur_sor2  -- 应该是 deallocate cur_sor2
end 

Answer 1

  end 
        /* 下面这个段落: 当@midvar1!=@peo_assigncount时，无法进行Fetch next, 如何从cur_sor1 中取得数据呢? */
        if(@midvar1=@peo_assigncount) begin 
            fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
        end 
那这个地方该怎么弄呢???

Answer 2

你希望在@midvar1!=@peo_assigncount时做什么动作呢？

Answer 3

create procedure c_dzh_assignOwner_Distribution(@handleby char(6)) 
as 
begin 
    declare
         @peo_staff char(6)
        ,@peo_assigncount int
        ,@peo_rolegroup char(6)
        ,@peo_objectivetype char(6)
        ,@midvar1 int
        ,@customer_guid char(36)
        ,@newguid char(36)
        ,@custlists varchar(100) 
    -- cur_sor1 对c_dzh_assigninfo 在handleby=@handleby条件下的每条记录进行遍历
    declare cur_sor1 cursor for 
    select cur_staff,assigncount,cur_rolegroup,objectivetype_id 
    from c_dzh_assigninfo 
    where handleby=@handleby 
    open  cur_sor1 
    fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
    /* Pos 1 */
    while @@fetch_status=0 
    begin 
        set @midvar1=0
        -- cur_sor2 对c_dzh_Owner_Distribution 在handleby=@handleby条件下的每条记录进行遍历
        -- 取得每个customer_guid 
        declare cur_sor2 cursor for 
        select customer_guid 
        from c_dzh_Owner_Distribution 
        where handleby=@handleby 
        open  cur_sor2 
        fetch next from cur_sor2 into @customer_guid 
        while @@fetch_status=0 
        begin 
            -- 赋值、变更
            set @newguid=newid()    
            insert into objective (
                 objective_guid
                ,objectivetype_id
                ,handleby_id
                ,handlegroup_id
                ,expireddate
                ,customer_guid
                ,objectivestatus
                ,createdby
                ,createddate
                ,modifiedby
                ,modifieddate)    
            values (
                 @newguid
                ,@peo_objectivetype
                ,@peo_staff
                ,@peo_rolegroup
                ,'2050-01-01 00:00:00'
                ,@customer_guid
                ,'OPEN'
                ,@handleby
                ,getdate()
                ,@handleby
                ,getdate())
                
            insert into oblist (
                 oblist_guid
                ,objective_guid
                ,objectivetype_id
                ,expectstarttime
                ,expectendtime
                ,obstatus
                ,max_attempt
                ,handleby_id
                ,handlegroup_id
                ,createdby
                ,createddate
                ,modifiedby
                ,modifieddate)    
            values (
                 newid()
                ,@newguid
                ,@peo_objectivetype
                ,getdate()
                ,'2050-01-01 00:00:00'
                ,'OPEN'
                ,'5000'
                ,@peo_staff
                ,@peo_rolegroup
                ,@handleby
                ,getdate()
                ,@handleby
                ,getdate())
                
            update c_dzh_Owner_Distribution set
                 flag=1 
            where customer_guid=@customer_guid 
            and handleby=@handleby 
            and flag=0 
            
            update customer set
                 owner=@peo_staff 
            where customer_guid=@customer_guid 
            
            update ''+@custlists+'' set
                 flagassign=1 
            where customer_guid=@customer_guid 
            
            set @midvar1=@midvar1+1 
        fetch next from cur_sor2 into @customer_guid 
        end 
        close cur_sor2
        deallocate cur_sor2        /* 下面这个段落: 当@midvar1!=@peo_assigncount时，无法进行Fetch next, 如何从cur_sor1 中取得数据呢? */
        -- 需要楼主确认
        -- if(@midvar1=@peo_assigncount) begin 
            fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
        -- end 
    end 
    close cur_sor1
    deallocate cur_sor1
end 

Answer 4

create procedure c_dzh_assignOwner_Distribution(@handleby char(6)) 
as 
begin 
declare @peo_staff char(6),@peo_assigncount int,@peo_rolegroup char(6),@peo_objectivetype char(6),@midvar1 int, 
@customer_guid char(36),@newguid char(36),@custlists varchar(100),@sql nvarchar(500) 
declare cur_sor1 cursor for select cur_staff,assigncount,cur_rolegroup,objectivetype_id from c_dzh_assigninfo where handleby=@handleby 
open  cur_sor1 fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
while @@fetch_status=0 
set @midvar1=0 -----------这句话应该放在begin里面
begin 
declare cur_sor2 cursor for select customer_guid from c_dzh_Owner_Distribution where handleby=@handleby 
open  cur_sor2 fetch next from cur_sor2 into @customer_guid 
while @@fetch_status=0 
begin 
select @newguid=newid()    
insert into objective    
(objective_guid,objectivetype_id,handleby_id,handlegroup_id,expireddate, 
customer_guid,objectivestatus,createdby,createddate,modifiedby,modifieddate)    
values    
(@newguid,@peo_objectivetype,@peo_staff,@peo_rolegroup,'2050-01-01 00:00:00', 
@customer_guid,'OPEN',@handleby,getdate(),@handleby,getdate())    
insert into oblist    
(oblist_guid,objective_guid,objectivetype_id,expectstarttime,expectendtime,obstatus,max_attempt,handleby_id,handlegroup_id,createdby,createddate,modifiedby,modifieddate)    
values    
(newid(),@newguid,@peo_objectivetype,getdate(),'2050-01-01 00:00:00','OPEN','5000',@peo_staff,@peo_rolegroup,@handleby,getdate(),@handleby,getdate())    
update c_dzh_Owner_Distribution set flag=1 where customer_guid=@customer_guid and handleby=@handleby and flag=0 
update customer set owner=@peo_staff where customer_guid=@customer_guid 
set @sql='update '''+@custlists+''' set  flagassign=1 where customer_guid='''+@customer_guid+'''' 
exec sp_executesql @sql 
set @midvar1=@midvar1+1 
fetch next from cur_sor2 into @customer_guid 
   end 
if(@midvar1=@peo_assigncount) 
begin 
fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype 
end 
end 
close cur_sor1 
deallocate cur_sor1 
close cur_sor2 
deallocate cur_sor2 
end

Answer 5

似乎可以这样:
create procedure c_dzh_assignOwner_Distribution(@handleby char(6)) 
as 
begin 
    declare
         @peo_staff char(6)
        ,@peo_assigncount int
        ,@peo_rolegroup char(6)
        ,@peo_objectivetype char(6)
        ,@midvar1 int
        ,@customer_guid char(36)
        ,@newguid char(36)
        ,@custlists varchar(100) 
    declare cur_sor1 cursor for 
    select cur_staff,assigncount,cur_rolegroup,objectivetype_id , customer_guid
    from c_dzh_assigninfo a, c_dzh_Owner_Distribution b
    where a.handleby=@handleby and b.handleby=@handleby 
    open  cur_sor1 
    fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype , @customer_guid 
    while @@fetch_status=0 begin 
        -- 赋值、变更
        set @newguid=newid()    
        insert into objective (
             objective_guid
            ,objectivetype_id
            ,handleby_id
            ,handlegroup_id
            ,expireddate
            ,customer_guid
            ,objectivestatus
            ,createdby
            ,createddate
            ,modifiedby
            ,modifieddate)    
        values (
             @newguid
            ,@peo_objectivetype
            ,@peo_staff
            ,@peo_rolegroup
            ,'2050-01-01 00:00:00'
            ,@customer_guid
            ,'OPEN'
            ,@handleby
            ,getdate()
            ,@handleby
            ,getdate())
            
        insert into oblist (
             oblist_guid
            ,objective_guid
            ,objectivetype_id
            ,expectstarttime
            ,expectendtime
            ,obstatus
            ,max_attempt
            ,handleby_id
            ,handlegroup_id
            ,createdby
            ,createddate
            ,modifiedby
            ,modifieddate)    
        values (
             newid()
            ,@newguid
            ,@peo_objectivetype
            ,getdate()
            ,'2050-01-01 00:00:00'
            ,'OPEN'
            ,'5000'
            ,@peo_staff
            ,@peo_rolegroup
            ,@handleby
            ,getdate()
            ,@handleby
            ,getdate())
            
        update c_dzh_Owner_Distribution set
             flag=1 
        where customer_guid=@customer_guid 
        and handleby=@handleby 
        and flag=0 
        
        update customer set
             owner=@peo_staff 
        where customer_guid=@customer_guid 
        
        update ''+@custlists+'' set
             flagassign=1 
        where customer_guid=@customer_guid 
            
    fetch next from cur_sor1 into @peo_staff,@peo_assigncount,@peo_rolegroup,@peo_objectivetype , @customer_guid 
    end 
    close cur_sor1
    deallocate cur_sor1
end 请LZ试试

Answer 6

还有一个问题 :
        ...
        update ''+@custlists+'' set
             flagassign=1 
        where customer_guid=@customer_guid 
        ...
: 想更新的是什么表?

Answer 7

除去上面的问题，完全可以直接insert & update，而不用游标
create procedure c_dzh_assignOwner_Distribution(@handleby char(6))
as
begin
    select
         objective_guid     = newid()
        ,objectivetype_id   = objectivetype_id
        ,handleby_id        = cur_staff
        ,handlegroup_id     = cur_rolegroup
        ,expireddate        = '2050-01-01 00:00:00'
        ,customer_guid      = customer_guid
        ,objectivestatus    = 'OPEN'
        ,createdby          = @handleby
        ,createddate        = getdate()
        ,modifiedby         = @handleby
        ,modifieddate       = getdate()
    into #objective
    from c_dzh_assigninfo a, c_dzh_Owner_Distribution b
    where a.handleby=@handleby and b.handleby=@handleby    insert into objective
    select
         objective_guid
        ,objectivetype_id
        ,handleby_id
        ,handlegroup_id
        ,expireddate
        ,customer_guid
        ,objectivestatus
        ,createdby
        ,createddate
        ,modifiedby
        ,modifieddate
    from #objective
    
    insert into oblist
    select
         oblist_guid        = newid()
        ,objective_guid
        ,objectivetype_id
        ,expectstarttime    = getdate()
        ,expectendtime      = '2050-01-01 00:00:00'
        ,obstatus           = 'OPEN'
        ,max_attempt        = '5000'
        ,handleby_id
        ,handlegroup_id
        ,createdby
        ,createddate
        ,modifiedby
        ,modifieddate
    from #objective    update c_dzh_Owner_Distribution set
         flag=1
    where handleby=@handleby
    and flag=0    update customer set
         owner=@peo_staff
    from customer a
    join #objective b
    on a.customer_guid=b.customer_guid    /* 这段的逻辑不清楚，未加处理
    update ''+@custlists+'' set
         flagassign=1
    where customer_guid=@customer_guid
    */
    drop table #objective
end