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: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;