Oracle Magazine, September/October 2018
Database Developer and DBA ETL ORACLE MAGAZINE SEPTEMBER OCTOBER 2018 91 EMPNO ENAME MGR 7876 ADAMS 7788 Obviously this approach is untenable in general The number of levels in the hierarchy is never known in advance so it is never known how many nested inline views are required to entirely traverse the hierarchy However this approach does reveal that processing a hierarchy via SQL is a recursive operation in that a set of output data from one query becomes the input into a similar wrapping query This leads naturally to the new form of hierarchy processing that is available from Oracle Database 11g Release 2 onward the ANSI standard SQL approach of using recursive common table expressions CTEs SIMPLER SQL WITH WITH Before I describe recursive CTEs it is worth covering the simpler case of nonrecursive CTEs First a virtual table consisting of a query expression can be defined with the WITH clause enabling that table to be referenced throughout the remainder of the query Listing 6 shows an example of the WITH clause that refers to a virtual table named LAST_ HIRE which is the query expression for deriving the most recent hiring date per department in the EMP table Listing 6 Basic usage of the WITH clause SQL WITH last_ hire AS 2 select deptno max hiredate 3 from emp
You must have JavaScript enabled to view digital editions.
