SQL用递归函数构造树

--===================================================

--创建一个递归调用函数

--===================================================

IF OBJECT_ID (N'digui') IS NOT NULL

   DROP FUNCTION digui

GO

 

create   function   digui(@bmdm numeric(4,0))--参数部门代码

  

returns   @bmTable   table    

(

    i_bmdm numeric(4,0),--部门代码

    v_bmmc varchar(100),--部门名称

    v_bmjc varchar(100),--部门简称

    i_jb   numeric(4,0),--级别

    i_sjdm numeric(4,0) --上级部门代码

)

as    

 

begin

 

    --先把部门代码等于@bmdm的记录插入到返回表中

    insert   @bmTable(i_bmdm,v_bmmc,v_bmjc,i_jb,i_sjdm)  

    select   i_bmdm,v_bmmc,v_bmjc,i_jb,i_sjdm from PT_D_BM

    where i_bmdm = @bmdm

     

    --如果存在子部门的话

    if exists(select 1 from PT_D_BM where i_sjdm=@bmdm)  

    begin 

       declare @i_bmdm numeric(4,0)

       declare @v_bmmc varchar(100)

       declare @v_bmjc varchar(100)

       declare @i_jb   numeric(4,0)

       declare @i_sjdm numeric(4,0)

     

       --声明一个游标，根据@bmdm选择其下级的部门

       declare cursor_bmTable cursor for  

       select i_bmdm,v_bmmc,v_bmjc,i_jb,i_sjdm from PT_D_BM

       where  i_sjdm=@bmdm order by i_xh for read only

       

       open   cursor_bmTable  

       fetch cursor_bmTable into @i_bmdm,@v_bmmc,@v_bmjc,@i_jb,@i_sjdm  

       

       --当执行成功时

       while @@fetch_Status=0  

           begin

              insert @bmTable select * from dbo.digui(@i_bmdm) --递归调用

              fetch cursor_bmTable into @i_bmdm,@v_bmmc,@v_bmjc,@i_jb,@i_sjdm

           end

       close     cursor_bmTable   --关闭游标

       deallocate cursor_bmTable   --释放游标

    end  

    return  

end

 

/*创建表[PT_D_BM]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[PT_D_BM](

    [I_BMDM] [numeric](4, 0) NOT NULL,--部门代码(有用)

    [V_BMMC] [varchar](100)         NULL,--部门名称(有用)

    [V_BMJC] [varchar](100)         NULL,--部门简称

    [V_BMJX] [varchar](100)         NULL,--部门简写

    [I_XH] [numeric](4, 0)          NULL,--序号(有用)

    [I_SJDM] [numeric](4, 0)    NULL,--上级部门代码(有用)

    [I_JB] [numeric](4, 0)          NULL,--级别(有用)

    [C_SFYX] [char](1)              NULL,--是否有效

    [C_BS] [char](1)            NULL,--公司标识

    [I_GSXZ] [numeric](4, 0)    NULL,--公司性质

    [I_GLID] [numeric](4, 0)    NULL,--管理ID

    [V_FACTORY_CODE] [varchar](4)   NULL,--工厂代码

    [V_SYS_CODE] [varchar](40)      NULL --系统代码

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

*/

/*这样调用函数*/

select * from dbo.digui(151)