Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Advanced Oracle SQL #3

Hierarchical queries with recursive WITH clause
by

Rodion Mironov

on 12 December 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Advanced Oracle SQL #3

Recursive WITH
vs
CONNECT BY Features Links
and practice Basic
syntax -- in red - first level of hierarchy
-- (like START WITH in CONNECT BY)
WITH sub (city) AS
(select destination from trains where origin = 'Kyiv'
union all
select trains.destination
from sub
inner join trains on sub.city = trains.origin
)
select distinct city
from sub; result := empty;
current_level := <first part of UNION ALL>;
while (current_level not empty) {
result := result UNION ALL current_level;
current_level := <second part of UNION ALL (select...from current_level)>;
}; Pseudo code Select all subordinates of Steven King;
Select all managers of employee with phone_number = '650.505.2876'. Don't show that employee himself.
For each 'Programmer' show list of his managers from top to bottom (like '/King/..../TheProgrammer').
List all second-level subordinates of Steven King (direct subordinates are first-level subordinates).
For each employee show his salary and summary salary of all his managers. Preserve tree structure in output.
Generate list of dates from sysdate to last day of the year. Documentation Explanation by Владимир Пржиялковский http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#BCEJGIBG http://citforum.ru/database/oracle/recursive/ Connect By
known as "hierarchical query";
Oracle-specific syntax;
available since Oracle v2 (1979); Recursive WITH
Known as "recursive common table expression" or "recursive subquery factoring";
part of ANSI SQL-99 standard;
in Oracle since 11g R2 (2009); WITH clause reminder: WITH MyTab(id) as
(select 1 from dual)
select * from MyTab What cities can we reach from Kyiv? -- in red - recursive part of query
-- like CONNECT BY clause
WITH sub (city) AS
(select destination from trains where origin = 'Kyiv'
union all
select trains.destination
from sub
inner join trains on sub.city = trains.origin
)
select distinct city
from sub; -- in red - main query
WITH sub (city) AS
(select destination from trains where origin = 'Kyiv'
union all
select trains.destination
from sub
inner join trains on sub.city = trains.origin
)
select distinct city
from sub; -- replacement for LEVEL presudocolumn
with sub (city,lvl) as
(select destination,1 from trains where origin = 'Kyiv'
union all
select t.destination, s.lvl + 1
from sub s
inner join trains t on s.city = t.origin
)
select * from sub; Recursive WITH basic syntax allows more than CONNECT BY;
it does not need some specific features like LEVEL pseudocolumn and SYS_CONNECT_BY_PATH;
it simplifies cumulative calculations. -- replacement for SYS_CONNECT_BY_PATH
with sub (city,route) as
(select destination,'/' || origin || '/' || destination
from trains where origin = 'Kyiv'
union all
select t.destination, s.route || '/' || t.destination
from sub s
inner join trains t on s.city = t.origin
)
select * from sub; -- cumulative SUM
with sub (city,duration) as
(select destination,duration_min
from trains where origin = 'Kyiv'
union all
select t.destination, s.duration + t.duration_min
from sub s
inner join trains t on s.city = t.origin
)
select * from sub; Using previous values Ordering Cycles -- ordering (DEPTH first)
with sub (city,lvl) as
(select destination,1 from trains where origin = 'Kyiv'
union all
select t.destination, s.lvl + 1
from sub s
inner join trains t on s.city = t.origin
)
search depth first by city asc set order_col
select LPAD(' ',(lvl-1)*3,'-') || city, order_col
from sub
order by order_col; -- ordering (BREADTH first)
with sub (city,lvl) as
(select destination,1 from trains where origin = 'Kyiv'
union all
select t.destination, s.lvl + 1
from sub s
inner join trains t on s.city = t.origin
)
search breadth first by city asc set order_col
select LPAD(' ',(lvl-1)*3,'-') || city, order_col
from sub
order by order_col; SEARCH DEPTH|BREADTH FIRST - show siblings or children first;
BY <ordering expression> - ordering for nodes at one level;
SET <column name> - name of column to save ordering criteria. 1 2 3 4 5 6 -- cycle error
with sub (id, route) as
(select id, '/' || id
from cycle_test
where id = 1
union all
select c.id, s.route || '/' || c.id
from sub s
inner join cycle_test c on s.id = c.parent_id
)
select * from sub; -- correct cycle processing
with sub (id, route) as
(select id, '/' || id
from cycle_test
where id = 1
union all
select c.id, s.route || '/' || c.id
from sub s
inner join cycle_test c on s.id = c.parent_id
)
cycle id set is_cycle to '1' default '0'
select * from sub; CYCLE <column(s)> SET <indicator> TO <cycle value> DEFAULT <nocycle value>;
cycle is detected if <column(s)> of row is/are equal to <column(s)> of some ancestor; Recursive WITH vs CONNECT BY summary:
standard (the same in MS SQL 2005, DB2 etc.);
more features
convinient cumulative calculations;
initial and recursive clauses might be based on different data sets;
"complicated" syntax. http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#BABCDJDB Examples
Full transcript