这个存储过程啊,正如本人的心思一样复杂诡异...
首先介绍一下表的结构 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;