plsql oracle父子

发布于 2024-08-06 15:54:54 字数 198 浏览 5 评论 0原文

我在 Oracle 9i 数据库表中具有父子关系，

如下所示：

parent | child  
1      | 2  
2      | 3
2      | 4
null   | 1
1      | 8

我有一个绝对父级（例如子级 1），并且我需要该父级的所有子级的 csv 列表或结果集。

原文

I have a parent-child relationship in an Oracle 9i database-table

like:

parent | child  
1      | 2  
2      | 3
2      | 4
null   | 1
1      | 8

I have an absolute parent (e.g. child 1) and i need a csv list or resultset of all childs of this parent.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

可是我不能没有你 2024-08-13 15:54:54

使用 SYS_CONNECY_BY_PATH 将为您提供以逗号分隔的整个层次结构：

SELECT SYS_CONNECT_BY_PATH(parent, ',') "PATH" 
  FROM table 
 START WITH child = 1 
CONNECT BY PRIOR child = parent;

更多选项此处

Using SYS_CONNECY_BY_PATH will give you the whole hierarchy comma separated:

SELECT SYS_CONNECT_BY_PATH(parent, ',') "PATH" 
  FROM table 
 START WITH child = 1 
CONNECT BY PRIOR child = parent;

Further options here

回复收藏 0 原文

鱼忆七猫命九 2024-08-13 15:54:54

我们刚刚脱离了 Oracle，但我在 SQL Server 中为您编写了这个过程（除了 CURSOR 声明之外，它们应该非常相似）。

CREATE PROCEDURE ShowRelationships
@parent AS int
AS
 PRINT 'Parent =  ' + CAST(@parent AS varchar(3))

 DECLARE @child AS int;
 DECLARE cur_children CURSOR
 FOR
  SELECT child
  FROM PCREL
  WHERE parent = @parent;

 OPEN cur_children;
 FETCH NEXT FROM cur_children INTO @child;

 IF (@child IS NULL)
 BEGIN
  PRINT CAST(@parent AS varchar(3)) + ' has no children...';
 END

 WHILE @@FETCH_STATUS = 0
 BEGIN
  PRINT 'Child = ' + CAST(@child AS varchar(3))

  FETCH NEXT FROM cur_children INTO @child;
 END

 CLOSE cur_children;
 DEALLOCATE cur_children;


 SELECT TOP 1 @child = child
 FROM PCREL
 WHERE parent = @parent;

 EXECUTE ShowRelationships @child;

GO

We just moved off Oracle but I wrote this procedure for you in SQL Server (they should be very similar minus the CURSOR declarations).

CREATE PROCEDURE ShowRelationships
@parent AS int
AS
 PRINT 'Parent =  ' + CAST(@parent AS varchar(3))

 DECLARE @child AS int;
 DECLARE cur_children CURSOR
 FOR
  SELECT child
  FROM PCREL
  WHERE parent = @parent;

 OPEN cur_children;
 FETCH NEXT FROM cur_children INTO @child;

 IF (@child IS NULL)
 BEGIN
  PRINT CAST(@parent AS varchar(3)) + ' has no children...';
 END

 WHILE @@FETCH_STATUS = 0
 BEGIN
  PRINT 'Child = ' + CAST(@child AS varchar(3))

  FETCH NEXT FROM cur_children INTO @child;
 END

 CLOSE cur_children;
 DEALLOCATE cur_children;


 SELECT TOP 1 @child = child
 FROM PCREL
 WHERE parent = @parent;

 EXECUTE ShowRelationships @child;

GO

回复收藏 0 原文

~没有更多了~