Oracle tree-like recursive PL/SQL Output control includes a space output control

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.