sqlserver：游标

USE AdventureWorks2012;
GO
--1
DECLARE @ProductID INT;
DECLARE @Name NVARCHAR(25);
--2
DECLARE products CURSOR FAST_FORWARD FOR
    SELECT ProductID, Name
    FROM Production.Product;
--3
OPEN products;
--4
FETCH NEXT FROM products INTO @ProductID, @Name;
--5
WHILE @@FETCH_STATUS = 0 
BEGIN
    --5.1
    PRINT @ProductID;
    PRINT @Name;
    --5.2
    FETCH NEXT FROM products INTO @ProductID, @Name;
END
--6
CLOSE products;
DEALLOCATE products;

 

嵌套游标

--1
DECLARE @TeacherID INT;
DECLARE @UserRoleRole NVARCHAR(50);
DECLARE @UniversityCode NVARCHAR(100);
DECLARE @UniversityName NVARCHAR(100);

--2
DECLARE Teachers CURSOR FAST_FORWARD FOR
    select * 
    from tb_e_TeacherBaseInfo a
    where   a.UserRoleCode='02';
--3
OPEN Teachers;
--4
FETCH NEXT FROM Teachers INTO @TeacherID, @UserRoleRole;
--5
WHILE @@FETCH_STATUS = 0 
BEGIN
    --5.1
    PRINT Convert(VARCHAR(100),@TeacherID);
    
        DECLARE Universitys CURSOR FAST_FORWARD FOR
        select UniversityCode, UniversityName 
        from dbo.tb_e_University A
        where exists
        (
            select UniversityCode
            from dbo.tb_r_UniversityTeacher B 
                inner join dbo.vw_ValidTeacherBaseInfo C on B.TeacherID=C.TeacherID
            where B.UniversityCode = A.UniversityCode  and B.TeacherID=@TeacherID
        );
        OPEN Universitys;
        FETCH NEXT FROM Universitys INTO @UniversityCode, @UniversityName;

        WHILE @@FETCH_STATUS = 0 
        BEGIN
            PRINT '      ----' + @UniversityCode + '--' + @UniversityName;
        
            FETCH NEXT FROM Universitys INTO @UniversityCode, @UniversityName;
        END
        CLOSE Universitys;
        DEALLOCATE Universitys;

    --5.2
    FETCH NEXT FROM Teachers INTO @TeacherID, @UserRoleRole;

END
--6
CLOSE Teachers;
DEALLOCATE Teachers;