ORACLE存储过程 练习系列一 关键字 部门树

这个存储过程啊,正如本人的心思一样复杂诡异...

首先介绍一下表的结构 HR_DEPARTMENT 表

主要列可以归为以下几项：

select T1.CPNY_ID    AS COMPANY_ID,
       T1.DEPTID     DEP_ID,
       T1.deptno     DEPT_NO,
       T1.dept_level DEPT_LEVEL,
       t2.deptno     PARENT_DEPT_NO,
       t2.dept_level PARENT_LEVEL
  from hr_department t1, hr_department t2
 where t1.parent_dept_no = t2.deptno
   and T1.cpny_id = 'C11';

准备工作 ：

将hr_department 表从公司的生产环境导出到本地

设置本地（oracle client端）的字符集

#set nls_lang=SIMPLIFIED CHINESE_CHINA.AL32UTF8

 

导出语句

D:\oraclexe\app\oracle\product\11.2.0\server\bin>exp ss_hr/ss_hr@XXXX.XXXX.XXXX:1
521/SSHR file='hr.dmp' tables=(hr_department)

导入语句

D:\oraclexe\app\oracle\product\11.2.0\server\bin>imp ss_hr/ss_hr@XE file ='D:\or
aclexe\app\oracle\product\11.2.0\server\bin\hr.dmp' full=y ignore=y;

再次登录sqlplus 就可以看到可爱的表啦！！

练习 一 查找DEPTID 重复的列

create or replace procedure 
P_CALCULATE_DATAITEM( in_test varchar2)is
   v_deptid hr_department.deptid%type;
  v_num_each_row integer;
  v_sum integer;
  /**NAME : F_CALCULATE_DATAITEM
  *PURPOSE : --查询hr_department指定列 的 重复记录总数量 以及每一条重复列各自的数量
  *IMPUT : -- 待查询重复数据的列的名字
call  P_CALCULATE_DATAITEM('');  *OUTPUT : -- N/A 
  *Author : -- CICI 
  *CreateDate : -- 2012、12、30
  *UpdateDate : -- 
  ************************************************************/
   CURSOR cur IS
   --将重复数据的 deptid 以及重复数量 装入存储过程
    select deptid,count(*)
      from hr_department
     where deptid in
          
           (select deptid
              from hr_department
             group by deptid
            having count(deptid) > 1)
     group by deptid;
begin
 OPEN cur;
 v_num_each_row:=0;
 v_sum:=0;
 LOOP
 --遍历存储过程 将结果取出
   FETCH cur INTO v_deptid, v_num_each_row;--//先放到记录变量
   EXIT WHEN cur%NOTFOUND;
   v_sum:=v_sum+v_num_each_row; 
   DBMS_OUTPUT.PUT_LINE('部门ID '||v_deptid||' , 个数是是'||v_num_each_row);
 END LOOP;
  DBMS_OUTPUT.PUT_LINE('部门ID 有重复记录的总数量为 '||v_sum );
 CLOSE cur; 
end P_CALCULATE_DATAITEM;

 练习二 查出某个法人内部某个部门制定级别的父级别部门编号

create or replace function GET_PARENT_DEPTNO_BY_LEVEL(IN_DEPTNO     HR_DEPARTMENT.DEPTNO%TYPE,
                                                      IN_CPNY_ID    HR_DEPARTMENT.CPNY_ID%TYPE,
                                                      IN_DEPT_LEVEL HR_DEPARTMENT.DEPT_LEVEL%TYPE)
  return varchar2 is
  V_PARENT_DEPT_NO HR_DEPARTMENT.DEPTNO%TYPE;
  /*************************************************************** 
  *NAME : GET_PARENT_DEPTNO_BY_LEVEL
  *PURPOSE : --给出部门编号 公司ID 查找相应level级别的父级部门编号
   *Author : --WangChao
  *CreateDate : -- 
  *UpdateDate : -- 
  ************************************************************/
begin
  DECLARE
    --存储当前部门的level
    V_LEVEL HR_DEPARTMENT.DEPT_LEVEL%TYPE;
  
  BEGIN
    IF IN_DEPTNO IS NOT NULL THEN
      V_PARENT_DEPT_NO := IN_DEPTNO;
    END IF;
    IF IN_DEPT_LEVEL IS NOT NULL THEN
      --取得当前部门的级别
      SELECT DEPT_LEVEL
        INTO V_LEVEL
        FROM HR_DEPARTMENT T
       WHERE T.DEPTNO = IN_DEPTNO
         AND T.CPNY_ID = IN_CPNY_ID;
      --如果当前部门的level 小于要取得的父级部门level 即当前部门是上级部门
      --则不符合业务逻辑 返回空串 
      IF V_LEVEL < IN_DEPT_LEVEL THEN
        V_PARENT_DEPT_NO := '';
      
        RETURN V_PARENT_DEPT_NO;
        --当前部门level 和父级别level相等 
        --返回当前部门
      ELSIF V_LEVEL = IN_DEPT_LEVEL THEN
        V_PARENT_DEPT_NO := IN_DEPTNO;
      
        RETURN V_PARENT_DEPT_NO;
      
      ELSE
        LOOP
          --遍历寻找父级部门
          SELECT T.DEPT_LEVEL, T.parent_dept_no
            INTO V_LEVEL, V_PARENT_DEPT_NO
            FROM HR_DEPARTMENT T
           WHERE T.DEPTNO = V_PARENT_DEPT_NO
             AND T.CPNY_ID = IN_CPNY_ID;
          EXIT WHEN(V_LEVEL - 1 <= IN_DEPT_LEVEL);
        END LOOP;
      
      END IF;
    
    END IF;
  END;

  RETURN V_PARENT_DEPT_NO;
end GET_PARENT_DEPTNO_BY_LEVEL;