欢迎加入站点基地设计开发交流群:30720409
详细内容
sqlserver嵌套游标的使用方法
发布日期:2014-01-10     点击:2980     字体:[ ]

该SP使用了嵌套游标,因为里层的游标调用了另一个存储过程,而且该存储过程对里层游标查询造成了影响,所以,里层游标用了临时表

 

IF EXISTS(SELECT * FROM sysobjects WHERE type='P' AND name='usp_Copy_UserInfo_All')
DROP PROCEDURE usp_Copy_UserInfo_All
GO

create procedure usp_Copy_UserInfo_All
AS
BEGIN
DECLARE @PERSONID NVARCHAR(40)
DECLARE @NEWPERSONID NVARCHAR(40)
DECLARE @SUBCOUNT INT
CREATE TABLE #tempTable
(oid NVARCHAR(40))
SET @SUBCOUNT=0

DECLARE CURSOR_GET_PERSONID CURSOR
FOR

--找出所有主要身份
SELECT PERSONID FROM PSNACCOUNT WHERE JOBCHARACTER='41_01' AND EMPLOYEEID IN
(SELECT EMPLOYEEID FROM PSNACCOUNT WHERE JOBCHARACTER='41_02')
OPEN CURSOR_GET_PERSONID
FETCH NEXT FROM CURSOR_GET_PERSONID INTO @PERSONID
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @PERSONID 主要身份
    DELETE FROM #tempTable
    --找出该用户的兼职身份
 INSERT INTO #tempTable(OID) SELECT PERSONID FROM PSNACCOUNT WHERE EMPLOYEEID IN
 (SELECT EMPLOYEEID FROM PSNACCOUNT WHERE PERSONID=@PERSONID) AND JOBCHARACTER='41_02'

--这里定义里层游标 
DECLARE CURSOR_GET_NEW_PERSONID CURSOR
 FOR
    SELECT OID FROM #tempTable
 
 OPEN CURSOR_GET_NEW_PERSONID
 FETCH NEXT FROM CURSOR_GET_NEW_PERSONID INTO @NEWPERSONID
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @SUBCOUNT = @SUBCOUNT + 1
        SELECT Convert(NVARCHAR(10),@SUBCOUNT) + '__' + @NEWPERSONID
  EXEC usp_Copy_UserInfo @PERSONID,@NEWPERSONID
  FETCH NEXT FROM CURSOR_GET_NEW_PERSONID INTO @NEWPERSONID
  END


 CLOSE CURSOR_GET_NEW_PERSONID
 DEALLOCATE CURSOR_GET_NEW_PERSONID
 SET @SUBCOUNT=0
 
FETCH NEXT FROM CURSOR_GET_PERSONID INTO @PERSONID
END
 CLOSE CURSOR_GET_PERSONID
 DEALLOCATE CURSOR_GET_PERSONID

END

用户评论
昵称 
内容  *
验证码   
   
Copyright © 2010 zdbase.com All Rights Reserved. 苏ICP备15039389号-1 可人软件设计