关于数据库查询优化的一个例子（责任中心例子）

CREATE OR REPLACE FUNCTION "GET_EMPCENTER"(PI_EMPOID     Number,
                                           PI_GROUP      Number,
                                           PI_UNITID     Number,
                                           PS_YEAR       Varchar2,
                                           PS_MONTH      Varchar2,
                                           PS_DELIVERNUM Number)
--返回交流、挂职类人员所对应的成本部门（不一定是责任中心）

 Return Number Is

  li_unit1         Number := 0;
  ls_date1         Char(6);
  ls_month1        Char(3);
  li_count1        Number;
  ls_date          Char(6);
  ls_month         Char(3);
  li_count         Number;
  li_dutycenter    Number := 0; --财务责任中心
  li_orgunit       Number := 0; --财务机构
  li_id            Number;
  li_pid           Number;
  li_parentorgunit Number := 0; --机构oid

Begin

  -- 先判断发放记录表中是否已经插入过本发放月的本员工 如果以后反映效率低，此处，以及以后的count(*)都可以改为使用游标代替，可以减少一半的查询量

  select Count(*)
    into li_count1
    from tb_cnb_dutycenterjudge pay
   where pay.c_month = PS_MONTH
     and pay.c_year = PS_YEAR
     and pay.c_unitid = PI_UNITID
     and pay.c_groupid = PI_GROUP
     and pay.c_empoid = PI_EMPOID
     and pay.c_delivernum = PS_DELIVERNUM;

  if li_count1 > 0 then
 
    select pay.c_payunitid
      into li_dutycenter
      from tb_cnb_dutycenterjudge pay
     where pay.c_month = PS_MONTH
       and pay.c_year = PS_YEAR
       and pay.c_unitid = PI_UNITID
       and pay.c_groupid = PI_GROUP
       and pay.c_empoid = PI_EMPOID
       and pay.c_delivernum = PS_DELIVERNUM
       and rownum = 1;
 
    return li_dutycenter;
  end if;

  ls_month1 := '0' || ps_month;
  ls_date1  := ps_year || substr(ls_month1, length(ls_month1) - 1);

  --判断是否在个人责任中心中维护，如未维护则直接返回员工所在部门
  Select Count(*)
    Into li_count1
    From TB_CNB_EMPDUTY
   Where C_EMPID = PI_EMPOID
     And C_GROUPID = PI_GROUP
     And C_EFECTIVETIME <= ls_date1;
  If li_count1 = 0 Then
    li_unit1 := PI_UNITID;
  End If;

  if li_count1 > 0 Then
 
    --如责任中心中维护个人信息，则取生效时间之前最近生效的一条记录
    Select C_DUTYCENTREID
      Into li_unit1
      From (Select *
              From TB_CNB_EMPDUTY
             Where C_EMPID = PI_EMPOID
               And C_GROUPID = PI_GROUP
               And C_EFECTIVETIME <= ls_date1
             Order By C_EFECTIVETIME Desc) a
     Where rownum = 1;
 
  End If;
  --以上获取了员工的成本部门信息li_unit1,接下来要用员工的成本部门去获取员工所在的成本中心
  ls_month := '0' || ps_month;
  ls_date  := ps_year || substr(ls_month, length(ls_month) - 1);

  li_id := li_unit1;
  --
  --如果是责任中心，直接返回
  Select Count(*)
    Into li_count
    From tb_cnb_dutycenter d
   Where d.c_unitid = li_id
     And d.c_efectivetime <= ls_date;

  If li_count > 0 Then
    Select a.c_unitid, a.c_compid
      Into li_dutycenter, li_orgunit
      From (Select *
              From TB_CNB_DUTYCENTER DC
             Where DC.C_UNITID = li_id
               And C_EFECTIVETIME <= ls_date
             Order By C_EFECTIVETIME Desc) a
     Where rownum = 1;
    insert into tb_cnb_dutycenterjudge
    values
      (PI_EMPOID,
       PI_GROUP,
       PI_UNITID,
       PS_YEAR,
       PS_MONTH,
       li_dutycenter,
       PS_DELIVERNUM);
 
    Return li_dutycenter;
 
  End If;

  --先根据员工OID查出其对应的机构，放入li_parentorgunit中作为待查条件
  select C_ORGID
    into li_parentorgunit
    from tb_inf_employee emp
   where emp.c_oid = PI_EMPOID;

  if li_parentorgunit <> PI_UNITID then
 
    Loop
   
      select count(*)
        into li_count1
        From tb_org_unitrelation
     
       Where c_orgunitid = li_id
         And c_hiberarchyid = 1
         And c_status = 1;
   
      if li_count1 = 0 then
     
        insert into tb_cnb_dutycenterjudge
        values
          (PI_EMPOID,
           PI_GROUP,
           PI_UNITID,
           PS_YEAR,
           PS_MONTH,
           PI_UNITID,
           PS_DELIVERNUM);
        Return PI_UNITID;
      end if;
   
      Select c_parentunitid
        Into li_pid
        From tb_org_unitrelation
       Where c_orgunitid = li_id
         And c_hiberarchyid = 1
         And c_status = 1;
   
      Exit When li_pid = li_parentorgunit;
   
      --如果不是责任中心，循环查找直到找到责任中心
   
      --先判断是否是责任中心
      Select Count(*)
        Into li_count
        From tb_cnb_dutycenter d
       Where d.c_unitid = li_pid
         And d.c_efectivetime <= ls_date;
   
      If li_count > 0 Then
     
        Select a.c_unitid, a.c_compid
          Into li_dutycenter, li_orgunit
          From (Select *
                  From TB_CNB_DUTYCENTER DC
                 Where DC.C_UNITID = li_pid
                   And C_EFECTIVETIME <= ls_date
                 Order By C_EFECTIVETIME Desc) a
         Where rownum = 1;
        Exit;
      Else
        li_id := li_pid;
      End If;
   
    End Loop;
 
  end if;

  -- 如果最终没有得出责任中心的值，则把员工所在组织机构赋值给责任中心
  if li_dutycenter = 0 Then
 
    li_dutycenter := PI_UNITID;
 
  End if;
  insert into tb_cnb_dutycenterjudge
  values
    (PI_EMPOID,
     PI_GROUP,
     PI_UNITID,
     PS_YEAR,
     PS_MONTH,
     li_dutycenter,
     PS_DELIVERNUM);

  Return li_dutycenter;

End GET_EMPCENTER;