This is written when it is a system for a garment factory in 2002. Please make more criticism!
Create Table Dept (Departno varcha2 (10), DepartName Varchar2 (20), Topno varchar2 (10));
INSERT INTO Dept Values ('001', 'understands', '0'); INSERT INTO Dept Values ('002', 'President's Office', '001'); Insert Into Dept Values ('003', "Finance Department ',' 001 '); INSERT INTO Dept Values (' 004 ',' Market Department ',' 002 '); Insert INTO Dept Values (' 005 ',' Public Related Department ',' 002 '); Insert Into Dept Values '006', 'sales department', '002'); Insert Into Dept Values ('007,' Distribution Office ',' 006 '); Insert Into Dept Values (' 008 ", 'Business Development Version', '004 '); INSERT INTO Dept Values (' 009 ',' Sales ", '007');
Create Table Tempdept (Tempdeptno Char (4), / * Department Number * / TempdeptName VARCHAR2 (20), / * Department Name * / Uppertempdeptno Char (4), / * Superior Department Number * / UppertemPdeptname Varchar2 (20), / * Super Level Department Name * / TempdePTlevel Number (4) / * Level * /);
Select DepartNameFrom DeptConnect by prior departno = Topnostart with topno = '0';
Select A.DepartName Top, B.DepartName Nextfrom Dept A, Dept bwhere a.departno = b.topno;
Select LPAD (departno, level * 2 length (departno), '/') from deptstart with topno = '0'connect by prior departno = topno;
Select Departno, DepartName, Level || '/' || Topnofrom DeptStart with Topno = '0'Connect by prior departno = topno;
Select LPad (departno, level * 2 length (departno), '') from deptstart with topno = '0'connect by prior departno = Topno;
Select LPAD (Departno, Level * 3, ') from deptstart with topno =' 0'Connect by prior departno = topno;
create or replace function GetLevel (TempNo varchar2) return varchar2IS 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; remote; remote; remote; *********************************************************** * Rem ********************************************************************************************************************* : Automatically generates the department ********************************************************************************************************************* *************** Rem ********** Enter parameters: department number paradeptid ************************* * Rem ************************************************ ************
