How to write tree-level code in Oracle

Oracle
This is in 2002 years to a garment factory to do the time of the system to write, please criticize correct!

CREATE TABLE Dept (
Departno VARCHAR2 (10),
Departname varchar2 (20),
Topno VARCHAR2 (10));

INSERT into Dept values (' 001 ', ' sensible will ', ' 0 ');
INSERT into Dept values (' 002 ', ' president's office ', ' 001 ');
INSERT into Dept values (' 003 ', ' Finance Department ', ' 001 ');
INSERT into Dept values (' 004 ', ' marketing department ', ' 002 ');
INSERT into Dept values (' 005 ', ' PR ', ' 002 ');
INSERT into Dept values (' 006 ', ' Sales department ', ' 002 ');
INSERT into Dept values (' 007 ', ' Phi ', ' 006 ');
INSERT into Dept values (' 008 ', ' Business development Department ', ' 004 ');
INSERT into Dept values (' 009 ', ' Sales section ', ' 007 ');

CREATE TABLE tempdept
(
Tempdeptno char (4),/* Department number * *
Tempdeptname varchar2 (20),/* Department Name * *
Uppertempdeptno char (4),/* Superior Department Number * *
Uppertempdeptname varchar2 (20),/* Superior Department Name * *
Tempdeptlevel Number (4)/* Department level * *
);

Select Departname
From Dept
Connect by Prior Departno=topno
Start with topno= ' 0 ';

Select A.departname Top, B.departname next
From Dept A,dept b
where A.departno=b.topno;

Select Lpad (Departno,level*2+length (departno), ' \ ')
From Dept
Start with topno= ' 0 '
Connect by prior Departno=topno;

Select Departno,departname,level | | ' \'|| Topno
From Dept
Start with topno= ' 0 '
Connect by prior Departno=topno;

Select Lpad (Departno,level*2+length (Departno), "")
From Dept
Start with topno= ' 0 '
Connect by prior Departno=topno;

Select Lpad (Departno,level*3, "")
From Dept
Start with topno= ' 0 '
Connect by prior Departno=topno;

Create or Replace function Getlevel (tempno varchar2)
return VARCHAR2
Is
Result Varchar2 (20);
Cursor Curlevel (curtempno varchar2)
Is
Select Departno
From Dept
Connect by Prior Departno = Topno
Start with topno = Curtempno;
Begin
Open curlevel;
For V_sor in Curlevel (Tempno) loop
Result:=result | | '/' || V_sor.curtempno;
End Loop;
return (result);
Close Curlevel;
Dbms_output.put_line (' success ');
End Getlevel;


rem************************************************************
The tree structure formed between the rem********** generation departments ******************
rem********** function: Automatically generates the department after the input department ******************
Tree relationship between rem********** and its child departments () ******************
rem********** Input Parameters: Department number Paradeptid ******************
rem************************************************************

CREATE OR REPLACE Procedure Getallsubdeptid (Paradeptid varchar2)
Is
DeptNo1 VARCHAR2 (4); /* Storage Department Number * *
UpperDeptNo1 VARCHAR2 (4); * * Deposit The department's superior department number * *
UpperDeptNo2 VARCHAR2 (4); /* Deposit inquiries at the same level of higher department number * *
UpperDeptNo3 VARCHAR2 (4); /* The number of the parent department that holds the query * *
Level number; /* Department Level * *
Dsql varchar2 (100); /* Dynamic SQL String * *
CURSOR Getdept is
Select Departno,topno
From Dept
where Departno!= Paradeptid;
BEGIN
--dsql: = ' CREATE TABLE tempdept (Tempdeptno,tempdeptname,uppertempdeptno,
--uppertempdeptname,tempdeptlevel) ';
--execute IMMEDIATE Dsql;
Dsql: = ' TRUNCATE TABLE tempdept ';
Dbms_output.put_line (0);
EXECUTE IMMEDIATE Dsql;
Dbms_output.put_line (1);
INSERT into tempdept values (paradeptid,null,null,null,0);
Dbms_output.put_line (2);
Open getdept;
Fetch getdept into deptno1,upperdeptno1;
while (SQLCODE = 0)
LOOP
Level: = 1;
UpperDeptNo3: = UpperDeptNo1;
While (UpperDeptNo1 are not NULL)
Loop
if (UpperDeptNo1 = Paradeptid) Then
INSERT into tempdept values (deptno1,null,upperdeptno3,null,level);
Exit
Else
UpperDeptNo2: = UpperDeptNo1;
Select Topno into UpperDeptNo1
From Dept
where departno= UpperDeptNo2;
If UpperDeptNo2!= UpperDeptNo1
Then
Level: = level+1;
End If;
End If;
End LOOP;
Fetch getdept into deptno1,upperdeptno1;
End LOOP;
Close getdept;
End;
/