Oracle Stored Procedure exercise Series 1-Keyword Department tree

This stored procedure is as complicated and strange as my mind...

First, we will introduce the structure of the hr_department table.

The main columns can be classified into the following items:

 
SelectT1.cpny _ idAsCompany_id, t1.deptid dep_id, t1.deptno dept_no, t1.dept _ Level dept_level, t2.deptno parent_dept_no, t2.dept _ Level parent_levelFromHr_department T1, hr_department T2WhereT1.parent _ dept_no=T2.deptnoAndT1.cpny _ id= 'C11';
 

Preparations:

Export the hr_department table from the company's production environment to the local

Set the character set of the local (Oracle client)

 
# SetNls_lang=Simplified chinese_china.al32utf8
 

 

Export Statement

D: \ oraclexe \ app \ oracle \ product \11.2.0\ Server \ bin>ExpSs_hr/Ss_hr@ Xxxx. Xxxx. xxxx:1521/SshrFile='HR. dmp'Tables=(Hr_department)
 

Import Statement

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;
 

Log on to sqlplus again to see the cute table !!

Exercise 1 searchDeptidDuplicate Columns

 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: -- query the total number of duplicate records in the specified hr_department column and the number of each duplicate column * imput: -- call p_calculate_dataitem (''); * output: -- N/A * Author: -- Cici * createdate: -- 2012, 12, 30 * updatedate: --*************************************** ********************  */     Cursor Cur Is     --  Store the deptid and number of duplicates in the stored procedure.      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  --  Traverse the stored procedure to retrieve the result     Fetch CurInto V_deptid, v_num_each_row; --  // Put it in record variable first     Exit   When Cur %  Notfound; v_sum:  = V_sum +  V_num_each_row; dbms_output.put_line (  '  Department ID  '  | V_deptid | '  , The number is  '  |  V_num_each_row );  End  Loop; dbms_output.put_line (  '  The total number of duplicate records for the department ID is  '  |  V_sum );  Close  Cur;  End P_calculate_dataitem;

Exercise 2: find out the number of the parent department specified by a department in a legal person

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: -- returns the Department ID. The company ID is used to find the parent department Number * Author: -- wangchao * createdate: -- * updatedate: --*************************************** ********************  */  Begin    Declare      --  Store the level of the current Department 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        --  Obtain the level of the current Department        Select  Dept_level  Into  V_level  From  Hr_department t  Where T. deptno =  In_deptno  And T. cpny_id = In_cpny_id;  --  If the level of the current Department is less than the level of the parent department to be obtained, that is, the current department is a superior department.        --  Returns an empty string that does not conform to the business logic.        If V_level < In_dept_level Then  V_parent_dept_no:  =   ''  ;  Return  V_parent_dept_no;  -- The current department level and parent level are equal.          --  Return to current department Elsif v_level = In_dept_level Then  V_parent_dept_no:  =  In_deptno;  Return  V_parent_dept_no;  Else  Loop  --  Traverse to find the parent department            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;