如何使用光标............？ [英] How to use cursor............?

问题描述

declare @emp varchar(50)
declare @oldfscode varchar(50)
declare @newfsname varchar(50)
declare @fstype varchar(50)
declare @parentcode varchar(50)
declare @areaname varchar(50)
declare @areacode varchar(50)
declare @parentype int
declare @grade varchar(50)
declare @qual varchar(50)
declare @add1 varchar(50)
declare @add2 varchar(50)
declare @add3 varchar(50)
declare @add4 varchar(50)
declare @pin char(50)
declare @phone char(50)
declare @email varchar(50)
declare @div char(50)
declare @newcode varchar(50)
declare @desg varchar(50)


declare toupdate cursor for 
select
empcode,fscode,fsname,type,parentcode,area,
areacode,parenttype,grade,qual,add1,add2,add3,add4
,pin,phone,email,div
,newcode,todes from r

open toupdate 
fetch next from  toupdate into
@emp,@oldfscode,@newfsname,@fstype,@parentcode,@areaname,@areacode,
@parentype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,@email,
@div,@newcode,@desg
while @@FETCH_STATUS=0
begin
   update Tbl_FS_Mst set C_Name='vacant - '+' '+@desg+' '+'('+@areaname+')',C_EmpNo='000000' where C_Code=@oldfscode
   update tbl_fs_emp_rel set d_date_to='2015/03/03' where c_fs_code=@oldfscode
   insert into Tbl_FS_Mst 
   select  @newcode,@newfsname,@fstype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,
'','',@areacode,@parentcode,@emp,'','','','',0,@email,0,GETDATE(),GETDATE(),'admin',@parentype,@div
  insert into tbl_fs_emp_rel select @newcode,@emp,GETDATE(),null,GETDATE()
  update Tbl_Doc_Stock_Chem_Add_Mst set C_FsCode=@newcode where C_FsCode=@oldfscode
  update Tbl_Cust_Div set c_fs_code=@newcode where c_fs_code=@oldfscode
  update Tbl_STP set C_FS_Code=@newcode where C_FS_Code=@oldfscode
end
close toupdate
deallocate toupdate

只需要一条记录。它可能会出现什么问题..

Only one record it takes..What problems it could be..

推荐答案

这就是它应该做的事情：一个Cursor用于一次获取一行数据进行处理。

按顺序要移到下一行，你必须明确使用FETCH NEXT命令。



见这里：http://en.wikipedia.org/wiki/Cursor_(databases) [ ^ ]

That's what it is supposed to do: a Cursor is used to fetch data one row at a time for processing.
In order to move to the next row, you have to explicitly use the FETCH NEXT command.

See here: http://en.wikipedia.org/wiki/Cursor_(databases)[^]

获取Next行以便在循环之前。

<无线电通信/>

Fetch the Next row in order to preceed the loop.

declare @toupdate cursor for
select
empcode,fscode,fsname,type,parentcode,area,
areacode,parenttype,grade,qual,add1,add2,add3,add4
,pin,phone,email,div
,newcode,todes from r

open @toupdate
fetch next from  @toupdate into
@emp,@oldfscode,@newfsname,@fstype,@parentcode,@areaname,@areacode,
@parentype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,@email,
@div,@newcode,@desg
while @@FETCH_STATUS=0
begin
  --Query


  fetch next from  @toupdate into
@emp,@oldfscode,@newfsname,@fstype,@parentcode,@areaname,@areacode,
@parentype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,@email,
@div,@newcode,@desg
end
close @toupdate
deallocate @toupdate

在这种情况下不需要游标：

There's no need for a cursor in this case:

UPDATE
    T
SET
    C_Name = 'vacant - ' + ' ' + S.todes + ' ' + '(' + S.area + ')',
    C_EmpNo = '000000'
FROM
    Tbl_FS_Mst As T
    INNER JOIN r As S
    ON S.fscode = T.C_Code
;

UPDATE
    T
SET
    d_date_to = '2015/03/03'
FROM
    tbl_fs_emp_rel As T
    INNER JOIN r As S
    ON S.fscode = T.c_fs_code
;

INSERT INTO Tbl_FS_Mst
SELECT
    newcode,
    fsname,
    type,
    grade,
    qual,
    add1,
    add2,
    add3,
    add4,
    pin,
    phone,
    '',
    '',
    areacode,
    parentcode,
    empcode,
    '',
    '',
    '',
    '',
    0,
    email,
    0,
    GETDATE(),
    GETDATE(),
    'admin',
    parenttype,
    div
FROM
    r
;

INSERT INTO tbl_fs_emp_rel
SELECT
    newcode,
    empcode,
    GETDATE(),
    Null,
    GETDATE()
FROM
    r
;

UPDATE
    T
SET
    C_FsCode = S.newcode
FROM
    Tbl_Doc_Stock_Chem_Add_Mst As T
    INNER JOIN r As S
    ON S.fscode = T.C_FsCode
;

UPDATE
    T
SET
    c_fs_code = S.newcode
FROM
    Tbl_Cust_Div As T
    INNER JOIN r As S
    ON S.fscode = T.c_fs_code
;

UPDATE
    T
SET
    C_FS_Code = S.newcode
FROM
    Tbl_STP As T
    INNER JOIN r As S
    ON S.fscode = T.C_FS_Code
;

这篇关于如何使用光标............？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！