层级查找并将层级拆分成多列

来源：这里教程网时间：2026-03-03 18:11:43 作者：

level, connect_by_isleaf, connect_by_iscycle伪列: level 就是这个数据属于哪一个等级，比如PRESIDENT为1，MANAGER为2 connect_by_isleaf 就是树的最末端的值，或者说这个树枝下已经没有树叶了 connect_by_iscycle 导致出现死循环的那个树枝 通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询. 自从Oracle 9i开始，可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以 “path”或者层次元素列表的形式显示出来。 自从Oracle 10g 中，还有其他更多关于层次查询的新特性。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。 那么你就可以利用伪列函数 CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”， 如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。 在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点）， Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。 而在 Oracle 10g 中，只要指定 “NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列—— CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。 1、层级查找领导与员工的关系

create or replace view a as 
select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '-')  path1,
         lpad(' ', level * 2 - 1) || sys_connect_by_path(empno, '-')  path2,
         ltrim(ltrim(lpad(' ', level * 2 - 1) || sys_connect_by_path(empno, '-')),'-' ) path3
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null

将path3 列的内容拆分成多列进行存储。拆分多列需要创建一个函数

create or replace function f_new_rowit(in_text varchar2,--要截取的字符串
                                       fh      varchar2,--截取识别符号
                                       n       number)--按第几个符号截取
                                        return varchar2 is
  Result varchar2(4000);
begin
  if n > 1 then
    SELECT substr(in_text,
                   
                  decode(instr(in_text, fh, n - 1, n - 1),
                         0,
                         0,
                         instr(in_text, fh, n - 1, n - 1) + 1),
                   
                  decode(sign(instr(in_text, fh, n, n) -
                              instr(in_text, fh, n - 1, n - 1)),
                         1,
                         (instr(in_text, fh, n, n) -
                         instr(in_text, fh, n - 1, n - 1)) - 1,
                         -1,
                         length(in_text),
                         0,
                         0)
                   
                  )
      into Result
      FROM dual;
  else
    select substr(in_text, 0, instr(in_text, fh, 1, 1) - 1)
      into Result
      from dual;
  end if;
  return(Result);
end f_new_rowit;

使用函数进行一列分成多列

select max(rank) from a;

这里可以看出可以最多拆分4列调用函数进行拆分

select rank,
       path1,
       path2,
       path3,
       f_new_rowit(path3, '-', 1) v1,
       f_new_rowit(path3, '-', 2) v2,
       f_new_rowit(path3, '-', 3) v3,
       f_new_rowit(path3, '-', 4) v4
  from a;