Home -> Usenet -> c.d.o.tools -> Re: PL/SQL procedure question

Re: PL/SQL procedure question

From: fredericks <free101_at_picusnet.com>
Date: Sun, 24 Jan 1999 00:48:20 -0500
Message-ID: <36aab3bc.0_at_news.itribe.net> 













jacko,


 


You can also make a one cursor procedure that 
can do what you want...


 


Put the two table join cursor in a procedure.  



The procedure needs 
parameters of parent_id in mode, level# in out mode. 


The cursor needs to select all the children for a given parent 
(you can also specify in level ordering if you need it 
).  The other tables should be joined(or outer joined if appropriate) 
.


The procedure body should increment the level on entry by 
one.


Use a cursor for loop to retrieve each child for the specified 
parent.


In the loop the child should should be listed 
(dbms_output.put_line) with the correct LPAD indent.


After listing the child, the procedure should 
call itself recursively using the current child_id and level as 
the parameters.  The recursive call will continue "walking down the 
hierarchy" like the SQL connect by clause does.


End the for loop.


Decrement the level by one before procedure exit.


 


Start by listing the root without indenting 
external to the procedure, then call the procedure with the root_id and 0 as 
parameters.


 


This recursive call procedure can overcome two 
major limitations of the Oracle Connect by clause..


-- Multiple tables can be 
joined


-- Any ordering can be specified within each level by the 
order by clause in the cursor, without disturbing the sequencing required for 
correct indenting of the hierarchy.


 


HTH


 


Mark


 



    
Jason Pepper wrote in message <36A9B3AC.55A1B2D2_at_steamer.u-net.com>...
You 
    will need to create a temporary summary table that contains the primary 
    column , the foreign key column and the data column as a minimum then use a 
    treewalk to produce the report you want. 
    
Like this.. 
    
SQL> create table documents (pid number, cid number, doc_name 
    varchar2(50)); 
    
SQL> / 
    
       
    PID        CID DOC_NAME 
    
---------- ---------- 
    -------------------------------------------------- 
    
        
    -1            Root 
    element 
         
    1         -1 Little Women 
    
         
    2          1 Dr Dolittle 
    
         
    3          1 20000 Leagues 
    under the Sea 
         
    4         -1 Ian Botham - My 
    Autobiography 
         
    5          4 Oracle made Easy 
    ! 
         
    6         -1 Using Linux 
    
         
    7          6 Teach yourself 
    Java 
         
    8          6 Oracle Performance 
    Tuning 
         
    9          6 Oracle DBA 
    Survival Guide 
        
    10          6 Oracle CDM 
    Handbook 
    
11 rows selected. 
  
    
  
    
SQL> get 1 
  1  select lpad(' 
    ',4*(level-1))||doc_name Document 
  2  from 
    documents 
  3  connect by prior pid=cid 
    
  4* start with pid = -1 
    
SQL> _at_1 
    
DOCUMENT 
    
-------------------------------------------------------------------------------- 
    
Root element 
    Little Women 
    
        Dr Dolittle 
    
        20000 Leagues under the 
    Sea 
    Ian Botham - My Autobiography 
    
        Oracle made Easy ! 
    
    Using Linux 
    
        Teach yourself Java 
    
        Oracle Performance 
    Tuning 
        Oracle DBA 
    Survival Guide 
        
    Oracle CDM Handbook 
    
11 rows selected. 
  
  
    
jacko4418_at_my-dejanews.com 
    wrote: 
    
I need to write a PL/SQL procedure that will 
        produce an indented output based 
on a child/parent 
        relationship:  Two tables involved a master table that 
stores 
        documents doc_no is PK and  a child/parent table that stores the 
        
associated docs for a given doc_no. The report needs to explode up 
        to 15 
levels. So for the doc_no passed to the procedure, I need the 
        direct 
descendants and each of its child records up to 15 levels. I 
        want to 
establish the levels and report them in an indented format 
        Any ideas?--I set 
up 15 cursors and looped through but I am not 
        getting the results I need 
        
gcj_at_hotmail.com 
        
-----------== Posted via Deja News, The Discussion Network 
        ==---------- 
http://www.dejanews.com/       
        Search, Read, Discuss, or Start Your Own
-- 
Regards

Jason
*-------------------------------------------------*
| Jason Pepper          jpepper_at_steamer.u-net.com |
|                                                 |
|   ... look out Bill, the Penguin is coming      |
|                                                 |
*-------------------------------------------------*
  


Received on Sun Jan 24 1999 - 06:48:20 CET












Original text of this message