感谢前几天大家的帮助,贡献一段实现ORACLE中CONNECT BY的代码

mahongxi 2005-03-19 05:02:11

建立表:
CREATE TABLE [TT_TEST9] (
[BH] [numeric](10, 0) NOT NULL ,
[FBH] [numeric](10, 0) NULL ,
[NAME] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[DL] [numeric](2, 0) NULL
) ON [PRIMARY]
GO

编号　父编号姓名　...
1 0 a 0
2 1 b 0
3 2 c 0
4 2 d 0
5 1 f 0
6 0 g 0
要求选出所有顶级编号为1的记录(即父编号或父编号的父编号.. 为1)
ORACLE中:
select * from TT_Test9 start with bh = 1 connect by fbh = prior bh
SQLServer中:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION sp_TT_TEST9_hierarchy1(@current int)
RETURNS @ctest TABLE (BH int,FBH int, NAME varchar(200), DL int, level int, rownum int IDENTITY(1,1))
AS
BEGIN
declare @id int
DECLARE @ctest1 TABLE(BH int,FBH int, NAME varchar(200), DL int, level int)
DECLARE @level int;
SELECT @level = 1;

INSERT INTO @ctest1 select *,@level from TT_TEST9 where bh = @current
DECLARE cur CURSOR FOR select bh from @ctest1
open cur
FETCH NEXT FROM cur into @id
WHILE ( @@FETCH_STATUS = 0)
BEGIN
select @level = level from @ctest1 where bh = @id;
insert into @ctest1 select *, @level + 1 from TT_TEST9 where fbh = @id order by bh;
FETCH NEXT FROM cur into @id
END

INSERT INTO @ctest SELECT * FROM @ctest1

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

用的时候:
select * from sp_TT_TEST9_hierarchy1(1)

...全文