公司考核题-（ORACLE篇二）

本篇主要考察的是算法--递归编程（树形的生成）--->本篇中的代码也可以使用ORACLE中的“CONNECT BY"来完成要求。

考核题目如下：

程序如下：

1.创建表deptframwork

insert into deptframwork (DEPTID, UPPERID, DEPTNAME, DEPTNUM, CREATETIME, WHOLENAME, EMPLOYEE)

values (1, 0, '省公司电力本部', 1001, to_date('03-02-2010', 'dd-mm-yyyy'), '', '');

insert into deptframwork (DEPTID, UPPERID, DEPTNAME, DEPTNUM, CREATETIME, WHOLENAME, EMPLOYEE)

values (2, 1, '科技信息部', 1002, to_date('03-02-2010', 'dd-mm-yyyy'), '', '');

insert into deptframwork (DEPTID, UPPERID, DEPTNAME, DEPTNUM, CREATETIME, WHOLENAME, EMPLOYEE)

values (3, 1, '企业协会', 1003, to_date('03-02-2010', 'dd-mm-yyyy'), '', '');

insert into deptframwork (DEPTID, UPPERID, DEPTNAME, DEPTNUM, CREATETIME, WHOLENAME, EMPLOYEE)

values (4, 1, '经法部', 1004, to_date('03-02-2010', 'dd-mm-yyyy'), '', '');

insert into deptframwork (DEPTID, UPPERID, DEPTNAME, DEPTNUM, CREATETIME, WHOLENAME, EMPLOYEE)

values (5, 2, '法律事务处', 1005, to_date('03-02-2010', 'dd-mm-yyyy'), '', '');

insert into deptframwork (DEPTID, UPPERID, DEPTNAME, DEPTNUM, CREATETIME, WHOLENAME, EMPLOYEE)

values (6, 2, '体制改革处', 1006, to_date('03-02-2010', 'dd-mm-yyyy'), '', '');

2.创建表emptable

insert 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 table deptemployee(deptid,emname) as 

select et.deptno,et.emname from deptframwork df,deptemployee et 

where et.deptno=ef.deptid;

4存储过程

create or replace procedure dept_emp6 is

cursor db_cursor is select * from deptframwork order by deptid;

cursor db_cursor_e is select * from deptemployee;

whole_name varchar(100);

emps varchar(200):=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;

-- 递归生成树形结构-------------------------------------------------------------------------------

create or replace procedure  dept_emp4(upper_id number,dept_name varchar) is

cursor db_cursor is select * from deptframwork where upperid=upper_id;

whole_name varchar(100);

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;