This article mainly examines the algorithm-recursive programming (tree Generation)---> The code in this article can also use "CONNECT by" in Oracle to complete the requirements.
The examination topics are as follows:
The procedure is as follows:
1. Create a table Deptframwork
Insert into Deptframwork (DeptID, Upperid, Deptname, Deptnum, Createtime, Wholename, EMPLOYEE) VALUES (1, 0, ' Provincial company Power Division ', 100 1, to_date (' 03-02-2010 ', ' dd-mm-yyyy '), ', ');
Insert into Deptframwork (DeptID, Upperid, Deptname, Deptnum, Createtime, Wholename, EMPLOYEE) VALUES (2, 1, ' Science and technology information ', 1002, To_date (' 03-02-2010 ', ' dd-mm-yyyy '), ', ', ');
Insert into Deptframwork (DeptID, Upperid, Deptname, Deptnum, Createtime, Wholename, EMPLOYEE) VALUES (3, 1, ' business associations ', 1003, To_date (' 03-02-2010 ', ' dd-mm-yyyy '), ', ', ');
Insert into Deptframwork (DeptID, Upperid, Deptname, Deptnum, Createtime, Wholename, EMPLOYEE) VALUES (4, 1, ' by law ', 1004, t O_date (' 03-02-2010 ', ' dd-mm-yyyy '), ', ', ');
Insert into Deptframwork (DeptID, Upperid, Deptname, Deptnum, Createtime, Wholename, EMPLOYEE) VALUES (5, 2, ' Legal Office ', 1005, To_date (' 03-02-2010 ', ' dd-mm-yyyy '), ', ', ');
Insert into Deptframwork (DeptID, Upperid, Deptname, Deptnum, Createtime, Wholename, EMPLOYEE) VALUES (6, 2, ' institutional reform department ', 1006, To_date (' 03-02-2010 ', ' dd-mm-yyyy '), ', ', ');
2. Create a table emptableInsert into EmpTable (EMID, Emname, PASSWD, DEPTNO) VALUES (1, ' user_admin ', ' 111111 ', 3);
Insert into EmpTable (EMID, Emname, PASSWD, DEPTNO) VALUES (2, ' user_dep ', ' 111111 ', 3);
Insert into EmpTable (EMID, Emname, PASSWD, DEPTNO) VALUES (3, ' User_sea ', ' 111111 ', 4);
Insert into EmpTable (EMID, Emname, PASSWD, DEPTNO) VALUES (4, ' User_aas ', ' 111111 ', 4);
Insert into EmpTable (EMID, Emname, PASSWD, DEPTNO) VALUES (5, ' User_toc ', ' 111111 ', 5);
Insert into EmpTable (EMID, Emname, PASSWD, DEPTNO) VALUES (6, ' User_soa ', ' 111111 ', 5);
3. Create a tableCREATE TABLE Deptemployee (deptid,emname) as select Et.deptno,et.emname from Deptframwork df,deptemployee et where et.dept No=ef.deptid;
4 Stored ProceduresCreate or Replace procedure DEPT_EMP6 iscursor db_cursor is select * from Deptframwork ORDER by Deptid;cursor Db_cursor_e is select * FROM deptemployee;whole_name varchar (+), Emps varchar ($): =null;flag Boolean:=true;begin for R in Db_cursor Loop if R.upperid=0 then update deptframwork set wholename=r.deptname where Deptid=r.deptid;Dept_emp4(R.deptid,r.deptname); End If; For re in db_cursor_e Loop if Re.deptno=r.deptid then if flag then emps:=re.emname; Flag:=false; else Emps:=emps | | '; ' | | Re.emname; End If; End If; End Loop; Flag:=true; Update deptframwork set employee=emps where DeptID = R.deptid; Emps:=null; End Loop; End DEPT_EMP6;
--Recursive generation of tree structure-------------------------------------------------------------------------------create or replace ProcedureDept_emp4(upper_id number,dept_name varchar) iscursor db_cursor is select * from Deptframwork where upperid=upper_id;whole_name var char (+); Begin for R in Db_cursor Loop Whole_name:=dept_name | | ' > ' | | R.deptname; Update deptframwork set wholename=whole_name where Deptid=r.deptid; Dept_emp4 (R.deptid,whole_name); End Loop;end Dept_emp4;
Company Assessment Questions-(Oracle II)
