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

首先介绍一下表的结构 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:1521/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:\oraclexe\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;