Tree-like structure
Stored procedures through recursive construction, similar to BBS replies display, code A total of three paragraphs:
Build tables, insert data, create stored procedure display;
1.
CREATE TABLE article
(
ID number primary Key,
Cont varchar2 (4000),
PID number,
Idleaf number (1),--0 is a non-leaf node, and 1 is a leaf node
Alevel Number (2)
);
2.
INSERT into article values (1, ' Ant vs Elephant ', 0,0,0);
INSERT into article values (2, ' elephants are beaten down ', 1,0,1);
INSERT into article values (3, ' ants are not good ', 1,0,1);
INSERT into article values (4, ' nonsense ', 2,1,2);
INSERT into article values (5, ' no nonsense ', 4,1,3);
INSERT into article values (6, ' How possible ', 1,0,1);
INSERT into article values (7, ' How not possible ', 6,1,2);
INSERT into article values (8, ' probability of great ', 6,1,2);
INSERT into article values (9, ' elephants into the hospital ', 2,0,2);
INSERT into article values (10, ' Nurse is Ant ', 9,1,3);
3.
Create or replace procedure tree (v_pid article.pid%type,v_alevel article.alevel%type)
Is
Cursor C is a SELECT * from article where pid=v_pid;
V_str VARCHAR2 (2048): = ";
Begin
For I in 0..v_alevel loop
V_STR: =v_str| | ' ‘;
End Loop;
For v_article in C loop dbms_output.put_line (v_str| | V_article.cont);
if (v_article.isleaf =0) Then
Tree (V_article.id,v_article.alevel);
End If;
End Loop;
End
The n*4 spaces in front of the result are not displayed:
After setting the space output, the problem is resolved:
Other issues: The first layer of output should not have a space before, set the initial string is empty, I starting from 1.
Set up Oracle Sqlplus Dbms_output.put_line
The following extracts from
http://blog.csdn.net/wbj1234566/article/details/2557515
1 setting dbms_output.put_line output shows set serveroutput on
2 off Dbms_output.put_line output shows SET serveroutput off
3 Set dbms_output buffer size set serveroutput on size number (1000)
4 set only show dbms_output default length set serveroutput on FORMAT truncated
5 Setting the output of a space set serveroutput on FORMAT wrapped
6 canceling the output of a space SET serveroutput on FORMAT word_wrapped
Oracle tree-like recursive PL/SQL Output control includes a space output control