利用游标实现递归(函数)

 涉及到的数据库表（SSC_Function）结构：

 

create function [dbo].[GetFunctionInfo]

(

    @PARENT_ID as varchar(100) --父ID

)

returns @FunctionTreeInfo table

(

    FUNCTION_ID varchar(100) not null,

    FUNCTION_NAME nvarchar(100),

    FUNCTION_DESC nvarchar(100),

    FUNCTION_ORDER int,

    PARENT_ID varchar(100),

    FUNCTION_LEVEL int

)

as

begin

    --声明相应变量,用于接收游标中的值

    declare @FUNCTION_ID varchar(100)

    declare @FUNCTION_NAME nvarchar(100)

    declare @FUNCTION_DESC nvarchar(100)

    declare @FUNCTION_ORDER int

    declare @PARENT_ID2 varchar(100)

    declare @FUNCTION_LEVEL int

   

    --声明一个游标,读取功能表中PARENT_ID为目前传入参数值的记录

    declare cur_function cursor

    for

    select * from SSC_Function where PARENT_ID=@PARENT_ID

    order by FUNCTION_ORDER

   

    --打开游标

    open cur_function

 

    --读取游标中记录的第一条

    fetch next from cur_function

    into @FUNCTION_ID,@FUNCTION_NAME,@FUNCTION_DESC,@FUNCTION_ORDER,@PARENT_ID2,@FUNCTION_LEVEL

 

    while @@fetch_status=0

    begin

       --将数据插入到表中

       insert into @FunctionTreeInfo

        values(@FUNCTION_ID,@FUNCTION_NAME,@FUNCTION_DESC,@FUNCTION_ORDER,@PARENT_ID2,@FUNCTION_LEVEL)

 

       --递归

       insert @FunctionTreeInfo select * from GetFunctionInfo(@FUNCTION_ID)

      

       --继续读取下一条记录

       fetch next from cur_function

       into @FUNCTION_ID,@FUNCTION_NAME,@FUNCTION_DESC,@FUNCTION_ORDER,@PARENT_ID2,@FUNCTION_LEVEL

    end

 

    --关闭游标

    close cur_function

    deallocate cur_function 

 

    return

end