在数据库里用过程和函数验证字段

原创
2014/02/13 09:34
阅读数 104

1、整篇package

声明 

create or replace package pkg_verify_filedtl_info is

  

  procedure p_main(i_trans_id number);

  

  procedure p_processValidate(i_detail_id number);

  

  function p_validatePolicyNo(i_detail_id number) return varchar2;

  

  function p_validateNext(i_detail_id number) return varchar2;

  

  function p_validateInsuredDateBuild(i_detail_id number) return varchar2;

  

  function p_validateCiEffExpDate(i_detail_id number) return varchar2;


end pkg_verify_filedtl_info;

实现 

create or replace package body pkg_verify_filedtl_info is


  procedure p_main(i_trans_id number) is

    l_count number;

  begin

    --to verify wheather summary info is ok

    select count(1)

    into   l_count

    from   t_ci_upload_log t 

    where  t.trans_id = i_trans_id

           -- err_code = 1 means summer info verfiy sucess

           and t.err_code = 1;

    

    if(l_count>1) then

      for v_trans_detail in(

        select d.detail_id

        from   t_ci_upload_log t,

               t_ci_upload_file_detail d

        where  t.trans_id = i_trans_id

               and t.err_code = 1

               and t.detail_id = d.detail_id

      )loop

        p_processValidate(v_trans_detail.detail_id);

      end loop;

    end if;

  end;

  

  procedure p_processValidate(i_detail_id number) is

      l_result varchar2(100);

  begin

      l_result := p_validatePolicyNo(i_detail_id);

      

      if(l_result = 'ok') then

        l_result := p_validateNext(i_detail_id);

      end if;

      

      if(l_result = 'ok') then

        l_result := p_validateInsuredDateBuild(i_detail_id);

      end if;

      

      if(l_result = 'ok') then

        l_result :=p_validateCiEffExpDate(i_detail_id);

      end if;

      if(l_result != 'ok') then

        update t_ci_upload_log d

        set    d.err_code = l_result

        where  d.detail_id = i_detail_id;

        

        update t_ci_upload_file_detail d

        set    d.process_status = 2

        where  d.detail_id = i_detail_id;

      end if;

  end;

  

  function p_validatePolicyNo(i_detail_id number) return varchar2 

  as

    l_count number;

    l_result varchar2(100);

  begin

    select count(1)

    into   l_count

    from   t_ci_upload_file_detail d,

           t_policy_general g 

    where  d.detail_id = i_detail_id

           and d.policy_no = g.policy_no

           and g.contract_status_code=2;

    

    if(l_count=0) then

      --100:Policy No. not  exsits

      l_result := '100';

    else 

      l_result := 'ok';

    end if;

    

    return l_result;

  end;

  

  function p_validateNext(i_detail_id number) return varchar2 

  as

    l_count number;

    l_result varchar2(100);

  begin

    select count(1)

    into   l_count

    from   t_ci_upload_file_detail d

    where  d.detail_id = i_detail_id

           and d.insured_cpf is null;

    

    if(l_count>0) then

      --105  means   insured  cpf is null

      l_result := '105';

    else 

      l_result := 'ok';

    end if;

    

    return l_result;

  end;


    function p_validateInsuredDateBuild(i_detail_id number) return varchar2 

  as

    l_count number;

    l_result varchar2(100);

  begin

    select count(1)

    into   l_count

    from   t_ci_upload_file_detail d

    where  d.detail_id = i_detail_id

    and (TO_DATE(TO_CHAR(sysdate, 'dd/MM/yyyy'), 'dd/MM/yyyy')-TO_DATE(TO_CHAR(d.insured_date_build,'dd/MM/yyyy'), 'dd/MM/yyyy'))/365 <70;


    

    if(l_count>0) then

    --101  means   insured is  less than 70 years

      l_result := '101';

    else 

      l_result := 'ok';

    end if;

    

    return l_result;

  end;

  ---validate Ci EffDate and expdate

     function p_validateCiEffExpDate(i_detail_id number) return varchar2 

  as

    l_movementType number;

    l_Idays number;

    l_Edays number;

    l_retroactiveDays number;

    l_CalculationByProRate number;

    l_result varchar2(100);

  begin

    select t.movement_type

    into   l_movementType

    from   t_ci_upload_file_detail t

    where  t.detail_id=i_detail_id;

    

    select to_date(t.competence,'mmyyyy')-d.inclusion_date as days

    into   l_Idays

    from   t_ci_upload_file_detail d,

           t_ci_upload_file_summary t

    where  d.detail_id = i_detail_id

          

           and t.trans_id=d.trans_id;

    select to_date(t.competence,'mmyyyy')-d.Exclusion_Date as days

    into   l_Edays

    from   t_ci_upload_file_detail d,

           t_ci_upload_file_summary t

    where  d.detail_id = i_detail_id

          

           and t.trans_id=d.trans_id;      

    select TO_NUMBER(t.retroactive_day) 

    into   l_retroactiveDays

    from   t_cts_mp_contract t,

           t_ci_upload_file_detail d,

           t_mp_contract m

    where  t.mp_contract_id=m.mp_contract_id

           

           and d.policy_no=m.mp_contract_code

           and d.detail_id=i_detail_id;

           

    select t.calculate_by_prorate

    into   l_CalculationByProRate

    from   t_cts_mp_contract t,

           t_ci_upload_file_detail d,

           t_mp_contract m

    where  t.mp_contract_id=m.mp_contract_id

          

           and d.policy_no=m.mp_contract_code

           and d.detail_id=i_detail_id;

   

    if(l_movementType=1) then

    if(l_Idays-l_retroactiveDays>0) then

    --103  means   certificate inclusion effective date  is wrong generated

           l_result := '103';

    else 

           l_result := 'ok';

          if(l_CalculationByProRate=1) then

             update t_ci_upload_file_detail t 

             set    t.eff_date=t.inclusion_date

             where  t.detail_id=i_detail_id;

          else 

             update t_ci_upload_file_detail t 

             set    t.eff_date=trunc(t.inclusion_date,'mm')

             where  t.detail_id=i_detail_id;

          end if;

    end if;

    end if;

      if(l_movementType=2) then

    if(l_Edays-l_retroactiveDays>0) then

    --104  means   certificate excusion EXPIRY date  is wrong generated

           l_result := '104';

    else 

           l_result := 'ok';

          if(l_CalculationByProRate=1) then

             update t_ci_upload_file_detail t 

             set    t.Exp_Date=t.Exclusion_Date

             where  t.detail_id=i_detail_id;

          else 

             update t_ci_upload_file_detail t 

             set    t.Exp_Date=to_date(to_char(last_day(t.exclusion_date),'ddmmyyyy'),'ddmmyyyy')

             where  t.detail_id=i_detail_id;

          end if;

    end if;

    end if;

    if(l_movementType=3) then

   

          if(l_CalculationByProRate=1) then

             update t_ci_upload_file_detail t 

             set    t.eff_date= (select sysdate from dual)

             where  t.detail_id=i_detail_id;

          else 

             update t_ci_upload_file_detail t 

             set    t.eff_date=(select trunc(sysdate,'mm')  from dual)

             where  t.detail_id=i_detail_id;

          end if;

    end if;

   

    return l_result;

  end;

end pkg_verify_filedtl_info;

在Java文件中调用    调用存储过程 或者没有返回值的function :

public void processAllFile(Long transId) throws Exception {


DBean db = new DBean();


Connection con = null;


CallableStatement cs = null;


try {


db.connect();


con = db.getConnection();


cs = con


.prepareCall("{Call pkg_verify_filedtl_info.p_main(?)}");


cs.setLong(1, transId);

cs.execute();


} catch (Exception e) {


throw e;


} finally {


DBUtils.clear(null, cs, db);


}


};

调用 pkg 里面有返回值的function 

 

 String policyId = null;

 DBean db = new DBean();

   CallableStatement cs = null;

   try {

     db.connect();

     Connection con = db.getConnection();

     String expression = "{ ? = call pkg_verify_filedtl_info.f_getCertNoByCNPJ(?,?) }";

     cs = con.prepareCall(expression);

     cs.registerOutParameter(1, java.sql.Types.LONGVARCHAR);

     cs.setString(2, mpNo);

     cs.setString(3, insuredCpf);

     cs.execute();

     policyId = cs.getString(1);

   } catch (Exception ex) {

     throw ExceptionFactory.parse(ex);

   } finally {

   cs.close();

   db.close();

   }


展开阅读全文
加载中
点击加入讨论🔥(2) 发布并加入讨论🔥
打赏
2 评论
0 收藏
0
分享
返回顶部
顶部