Novas funcionalidades com CONNECT BY
12/01/2009
A partir do Oracle 10g temos mais algumas funcionalidades no uso da cláusula CONNECT BY dentro dos comandos SELECT. Essas mudanças se aplicam a queries hiearquicas permitindo o retorno de não apenas PAIS, FILHOS mas também “ancestrais”. São 3 as novas cláusulas disponíveis com CONNECT BY.
connect_by_iscycle
Determina se a linha corrente tem uma linha filha que também é seu ancestral.
SQL> SELECT ename Emp, CONNECT_BY_ISCYCLE leaf, mgr "Manager", 2 LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(empno, '/') "Path" 3 FROM emp 4 Where level-1 < 3 5 CONNECT BY NOCYCLE PRIOR empno = mgr; EMP LEAF Manager Pathlen Path ---------- ---------- ------- ---------- ------------------------------ SCOTT 0 7566 0 /7788 ADAMS 0 7788 1 /7788/7876 FORD 0 7566 0 /7902 SMITH 0 7902 1 /7902/7369 ALLEN 0 7698 0 /7499 WARD 0 7698 0 /7521 MARTIN 0 7698 0 /7654 TURNER 0 7698 0 /7844 JAMES 0 7698 0 /7900 MILLER 0 7782 0 /7934 ADAMS 0 7788 0 /7876 JONES 0 7839 0 /7566 SCOTT 0 7566 1 /7566/7788 ADAMS 0 7788 2 /7566/7788/7876 FORD 0 7566 1 /7566/7902 SMITH 0 7902 2 /7566/7902/7369 BLAKE 0 7839 0 /7698 ALLEN 0 7698 1 /7698/7499 WARD 0 7698 1 /7698/7521 MARTIN 0 7698 1 /7698/7654 TURNER 0 7698 1 /7698/7844 JAMES 0 7698 1 /7698/7900 CLARK 0 7839 0 /7782 MILLER 0 7782 1 /7782/7934 SMITH 0 7902 0 /7369 KING 0 0 /7839 JONES 0 7839 1 /7839/7566 SCOTT 0 7566 2 /7839/7566/7788 FORD 0 7566 2 /7839/7566/7902 BLAKE 0 7839 1 /7839/7698 ALLEN 0 7698 2 /7839/7698/7499 WARD 0 7698 2 /7839/7698/7521 MARTIN 0 7698 2 /7839/7698/7654 TURNER 0 7698 2 /7839/7698/7844 JAMES 0 7698 2 /7839/7698/7900 CLARK 0 7839 1 /7839/7782 MILLER 0 7782 2 /7839/7782/7934 37 rows selected SQL>
connect_by_isleaf
Determina se a linha corrente é uma FOLHA na árvore definida pela operação connect by.
SQL> SELECT ename "Emp", CONNECT_BY_ISLEAF leaf, mgr "Manager", 2 LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(empno, '/') "Path" 3 FROM emp 4 Where level-1 < 3 5 CONNECT BY NOCYCLE PRIOR empno = mgr; Emp LEAF Manager Pathlen Path ---------- ---------- ------- ---------- ------------------------------ SCOTT 0 7566 0 /7788 ADAMS 1 7788 1 /7788/7876 FORD 0 7566 0 /7902 SMITH 1 7902 1 /7902/7369 ALLEN 1 7698 0 /7499 WARD 1 7698 0 /7521 MARTIN 1 7698 0 /7654 TURNER 1 7698 0 /7844 JAMES 1 7698 0 /7900 MILLER 1 7782 0 /7934 ADAMS 1 7788 0 /7876 JONES 0 7839 0 /7566 SCOTT 0 7566 1 /7566/7788 ADAMS 1 7788 2 /7566/7788/7876 FORD 0 7566 1 /7566/7902 SMITH 1 7902 2 /7566/7902/7369 BLAKE 0 7839 0 /7698 ALLEN 1 7698 1 /7698/7499 WARD 1 7698 1 /7698/7521 MARTIN 1 7698 1 /7698/7654 TURNER 1 7698 1 /7698/7844 JAMES 1 7698 1 /7698/7900 CLARK 0 7839 0 /7782 MILLER 1 7782 1 /7782/7934 SMITH 1 7902 0 /7369 KING 0 0 /7839 JONES 0 7839 1 /7839/7566 SCOTT 0 7566 2 /7839/7566/7788 FORD 0 7566 2 /7839/7566/7902 BLAKE 0 7839 1 /7839/7698 ALLEN 1 7698 2 /7839/7698/7499 WARD 1 7698 2 /7839/7698/7521 MARTIN 1 7698 2 /7839/7698/7654 TURNER 1 7698 2 /7839/7698/7844 JAMES 1 7698 2 /7839/7698/7900 CLARK 0 7839 1 /7839/7782 MILLER 1 7782 2 /7839/7782/7934 37 rows selected SQL>
connect_by_root
Retorna o valor da coluna da linha Raíz.
SQL> SELECT ename "Emp", CONNECT_BY_ROOT mgr "Manager", 2 LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(empno, '/') "Path" 3 FROM emp 4 Where level-1 < 3 5 CONNECT BY NOCYCLE PRIOR empno = mgr; Emp Manager Pathlen Path ---------- ---------- ---------- ------------------------------ SCOTT 7566 0 /7788 ADAMS 7566 1 /7788/7876 FORD 7566 0 /7902 SMITH 7566 1 /7902/7369 ALLEN 7698 0 /7499 WARD 7698 0 /7521 MARTIN 7698 0 /7654 TURNER 7698 0 /7844 JAMES 7698 0 /7900 MILLER 7782 0 /7934 ADAMS 7788 0 /7876 JONES 7839 0 /7566 SCOTT 7839 1 /7566/7788 ADAMS 7839 2 /7566/7788/7876 FORD 7839 1 /7566/7902 SMITH 7839 2 /7566/7902/7369 BLAKE 7839 0 /7698 ALLEN 7839 1 /7698/7499 WARD 7839 1 /7698/7521 MARTIN 7839 1 /7698/7654 TURNER 7839 1 /7698/7844 JAMES 7839 1 /7698/7900 CLARK 7839 0 /7782 MILLER 7839 1 /7782/7934 SMITH 7902 0 /7369 KING 0 /7839 JONES 1 /7839/7566 SCOTT 2 /7839/7566/7788 FORD 2 /7839/7566/7902 BLAKE 1 /7839/7698 ALLEN 2 /7839/7698/7499 WARD 2 /7839/7698/7521 MARTIN 2 /7839/7698/7654 TURNER 2 /7839/7698/7844 JAMES 2 /7839/7698/7900 CLARK 1 /7839/7782 MILLER 2 /7839/7782/7934 37 rows selected SQL>
Tags: Connect By, Oracle 10g, SQL
