数据库初级培训(第2讲).pptVIP

* Keywords and Clauses Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH clauses. In the syntax: SELECT Is the standard SELECT clause. LEVEL For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. FROM table Specifies the table, view, or snapshot containing the columns. You can  select from only one table. WHERE Restricts the rows returned by the query without affecting other rows of  the hierarchy. condition Is a comparison with expressions. START WITH Specifies the root rows of the hierarchy (where to start). This clause is required for a true hierarchical query. CONNECT BY Specifies the columns in which the relationship between parent and child PRIOR rows exist. This clause is required for a hierarchical query. The SELECT statement cannot contain a join or query from a view that contains a join. * select ename emp_name,job,sys_connect_by_path(ename,/) scbp from emp start with mgr is null connect by prior empno = mgr; SELECT deptno, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename, ,)) KEEP(DENSE_RANK LAST ORDER BY curr), ,) AS concatenated FROM (SELECT deptno, ename, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) AS curr, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) - 1 AS prev FROM emp) GROUP BY deptnoCONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno START WITH curr = 1; * Pruning Branches You can use the WHERE and CONNECT BY clauses to prune the tree; that is, to control which nodes or rows are displayed. The predicate you use acts as a Boolean condition. Examples Starting at the root, walk from the top down, and eliminate employee Higgins in the result, but process the child rows. SELECT department_id, employee_id,last_name, job_id, salary FROM employees WHERE last_name != Higgins START