Implement stored procedures in the Oracle Tree Structure

This article mainly describes how to implement the stored procedure in the Oracle tree structure, including the actual operation steps for creating the IT table, and how to insert data to the relevant data, submit related transactions, and create a stored procedure p.

Step 1: Create a table IT

 
 

  
  
create table IT  
  
  
(  
  
  
id number primary key ,  
  
  
cont varchar2(255),  
  
  
pid number,  
  
  
isleaf number(1),   
 
 

0 indicates that non-leaf node 1 indicates a leaf node.

 
 

  
  
grade number(2)  
  
  
);  
 
 

Step 2: insert data into the data and submit the transaction

Insert into IT values (1, 'Hello all ', 0, 0, 0 );

Insert into IT values (2, 'you', 1, 0, 1 );

Insert into IT values (3. 'May you succeed in your studies ', 2, 1, 2 );

Commit; -- submit a transaction

 
 

  
  
select * from it 
 
 

Step 3: Create a stored procedure p

 
 

  
  
create or replace procedure p (v_pid IT.pid%type, v_level binary_integer)  
  
  
is  
  
  
cursor c is select * from IT where pid = v_pid;  
  
  
v_preStr varchar2(1024) := '';  
  
  
begin  
  
  
for i in 1..v_level  
  
  
loop  
  
  
v_preStr := v_preStr || '**$';  
  
  
end loop;  
  
  
for v_IT in c  
  
  
loop  
  
  
dbms_output.put_line(v_preStr || v_IT.cont);  
  
  
if(v_IT.isleaf = 0) then  
  
  
p(v_IT.id, v_level + 1);  
  
  
end if;  
  
  
end loop;  
  
  
end;  
 
 

Step 4: Start serveroutput and use the exec command to display the tree in pl/SQL

Run in Command Window

 
 

  
  
SQL> set serveroutput on  
  
  
SQL> exec p(0,0);  
 
 

The preceding content describes the stored procedure of the Oracle tree structure. We hope it will help you in this regard.