存储过程示例

--------------------------------------
--报案三者车信息
--------------------------------------
create or replace procedure reportAuto()
is
begin
       insert into TBL_REPORT_AUTO
       (N_RPTAUTO_ID,N_RPT_ID,N_CLAIM_ID,C_CASE_NO)
       select S_RPTAUTO_ID.Nextval,
       from clmsys.tbl_clm a;
       --commit;
end;

------------------------------------------------------
--立案*TBL_REGISTERED
------------------------------------------------------
create or replace procedure registered(p_dptCde clmsys.tbl_clm.c_dpt_cde%type)
as
       cursor cur_registered(c_dptCde clmsys.tbl_clm.c_dpt_cde%type) is
       select a.c_clm_no
       from clmsys.TBL_CLMRGST a,clmsys.tbl_clm b
       where a.c_clm_no = b.c_clm_no
       and b.c_end_typ='1' and b.c_clm_mrk = 'I'
       and b.c_dpt_cde = c_dptCde;
begin
       open cur_registered();
       loop
            fetch cur_registered into ;
            if cur_registered%notfound then
               dbms_output.put_line('未发现数据。。。');
               exit;
            else
               --立案主档表
               insert into TBL_REGISTERED
               ()
               values
               ();
              
               --立案保单费用表
               insert into TBL_REGISTERED_FEE
               ()
               values
               ();
            end if;
       end loop;
       close cur_registered;
       commit;
end;

 

----------------------------------------
--查勘定损主档表
----------------------------------------
create or replace procedure surevy(p_dptCde clmsys.tbl_clm.c_dpt_cde%type)
as
       v_dispatchId tbl_dispatch.n_dispatch_id%type;
       v_caseNo tbl_claim.c_case_no%type;
       v_surveyTm tbl_survey.t_survey_tm%type;
       v_surveyAddr tbl_survey.c_survey_addr%type;
       v_remark tbl_survey.c_remark%type;
       v_surveyFee tbl_survey.n_survey_fee%type;
       v_verifyFee tbl_survey.n_verify_fee%type;
       v_agentFee tbl_survey.n_agent_fee%type;
       v_otherFee tbl_survey.n_other_fee%type;
       v_tms tbl_survey.n_tms%type;
       v_operEmpCde tbl_survey.c_operate_emp_cde%type;
       v_operEmpNme tbl_survey.c_operate_emp_nme%type;
       v_operDptCde tbl_survey.c_operate_dpt_cde%type;
       v_operDptNme tbl_survey.c_operate_dpt_nme%type;
       v_state tbl_survey.c_state%type;
       cursor cur_surevy(c_dptCde clmsys.tbl_clm.c_dpt_cde%type) is
       select c.n_dispatch_id,a.c_clm_no,a.c_srvy_addr,d.c_srvy_remark,a.n_sry_fee,a.n_chk_fee,
       a.n_agt_fee,a.n_oth_fee,a.n_srvy_tms,d.c_srvy_cde,e.c_emp_nme,d.c_srvydpt_cde,f.c_dpt_nme,
       decode(d.c_wrk_mrk,'0','3460001','1','3460001','2','3460002','3','3460002','4','3460003',
              '5','3460004','6','3460006')
       from clmsys.TBL_SRVY_BASE a,clmsys.tbl_clm b,tbl_dispatch c,
       clmsys.tbl_srvy_task d,tbl_emp e,tbl_dpt f
       where a.c_clm_no = b.c_clm_no(+)
       and a.c_clm_no = c.c_case_no
       and a.c_clm_no = d.c_clm_no(+)
       and d.c_srvy_cde = e.c_emp_cde
       and d.c_srvydpt_cde = f.c_dpt_cde
       and b.c_end_typ='1' and b.c_clm_mrk = 'I'
       and b.c_dpt_cde = c_dptCde;
begin
       open cur_survey(p_dptCde);
       loop
            fetch cur_survey into v_dispatchId,v_caseNo,v_surveyTm,v_surveyAddr,v_remark,v_surveyFee,
            v_verifyFee,v_agentFee,v_otherFee,v_tms,v_operEmpCde,v_operEmpNme,v_operDptCde,v_operDptNme,
            v_state;
           
            if cur_survey%NOTFOUND then
              dbms_output.put_line('未发现数据。。。');
              EXIT;
            else
              insert into tbl_survey
              (N_DISPATCH_ID,c_case_no,T_SURVEY_TM,C_SURVEY_ADDR,C_REMARK,N_SURVEY_FEE,N_VERIFY_FEE,
              N_AGENT_FEE,N_OTHER_FEE,N_TMS,C_OPERATE_EMP_CDE,C_OPERATE_EMP_NME,C_OPERATE_DPT_CDE,
              C_OPERATE_DPT_NME,C_STATE)
              values
              (v_dispatchId,v_caseNo,v_surveyTm,v_surveyAddr,v_remark,v_surveyFee,v_verifyFee,
              v_agentFee,v_otherFee,v_tms,v_operEmpCde,v_operEmpNme,v_operDptCde,v_operDptNme,v_state);
            end if;
       end loop;
       close cur_survey;
       commit;
end;

 

-----------------------------------
--派工表
-----------------------------------
create or replace procedure dispatch(p_dptCde clmsys.tbl_clm.c_dpt_cde%type)
as
       v_caseNo clmsys.tbl_clm.c_clm_no%type;
       v_dispType tbl_dispatch.c_disp_type%type := '0160001'; --内部派工
       v_dispSubType tbl_dispatch.c_disp_subtype%type;
       v_talCar number;
       v_talThrcar number;
       v_surveyEmpcde tbl_dispatch.c_survey_emp_cde%type;
       v_surveyEmpnme tbl_dispatch.c_survey_emp_nme%type;
       v_surveyDptcde tbl_dispatch.c_survey_dpt_cde%type;
       v_surveyDptnme tbl_dispatch.c_survey_dpt_nme%type;
       v_accdntSpot tbl_dispatch.c_accident_addr%type;
       v_srvyAddr tbl_dispatch.c_survey_addr%type;
       v_lcnNo tbl_dispatch.c_lcn_no%type;
       v_taskStat tbl_dispatch.c_task_stat%type;
       v_dispTm tbl_dispatch.t_disp_tm%type;
       v_dispEmpCde tbl_dispatch.c_disp_emp_cde%type;
       v_dispEmpNme tbl_dispatch.c_disp_emp_nme%type;
       v_dispDptCde tbl_dispatch.c_disp_dpt_cde%type;
       v_dispDptNme tbl_dispatch.c_disp_dpt_nme%type;
       v_surveyAmt tbl_dispatch.n_survey_amt%type;
       v_remark tbl_dispatch.c_remark%type;
       cursor cur_dispatch(c_dptCde clmsys.tbl_clm.c_dpt_cde%type) is
       select a.c_clm_no,a.N_TAL_CAR,a.N_TAL_THRCAR,c.c_srvy_cde,d.c_emp_nme,
              c.c_srvydpt_cde,e.c_dpt_nme,a.c_Accdnt_Spot,c.c_srvy_addr,c.c_lcn_no,
              decode(c.c_wrk_mrk,'0','3460001','1','3460001','2','3460002','3','3460002','4','3460003',
              '5','3460004','6','3460006'),c.t_opr_tm,c.c_opr_man,f.c_emp_nme,c.c_opt_dpt,g.c_dpt_nme,
              a.n_Tal_Los,c.c_srvy_remark
       from clmsys.tbl_srvy_base a,clmsys.tbl_clm b,
            clmsys.tbl_srvy_task c,tbl_emp d,tbl_dpt e,
            tbl_emp f,tbl_dpt g
       where a.c_clm_no = b.c_clm_no
             and a.c_clm_no = c.c_clm_no(+)
             and c.c_srvy_cde = d.c_emp_cde
             and c.c_srvydpt_cde = e.c_dpt_cde
             and c.c_opr_man = f.c_emp_cde
             and c.c_opt_dpt = g.c_dpt_cde
             and b.c_end_typ='1' and b.c_clm_mrk = 'I'
             and b.c_dpt_cde = c_dptCde;
begin
       open cur_dispatch(p_dptCde);
       loop
            fetch cur_dispatch into v_caseNo,v_talCar,v_talThrcar,v_surveyEmpcde,v_surveyEmpnme,v_surveyDptcde,
            v_surveyDptnme,v_accdntSpot,v_srvyAddr,v_lcnNo,v_taskStat,v_dispTm,
            v_dispEmpCde,v_dispEmpNme,v_dispDptCde,v_dispDptNme,v_surveyAmt,v_remark;
             if cur_dispatch%NOTFOUND then
               dbms_output.put_line('未发现数据。。。');
               EXIT;
             else
               if v_talCar>0 then
                  --本车
                  dbms_output.put_line('本车总定损：' || v_talCar);
                  v_dispSubType := '0170003';
                  insert into
                  tbl_dispatch(N_DISPATCH_ID,c_case_no,c_disp_type,c_disp_subtype,C_SURVEY_EMP_CDE,c_survey_emp_nme,
                  c_survey_dpt_cde,c_survey_dpt_nme,c_accident_addr,c_survey_addr,c_lcn_no,C_TASK_STAT,T_DISP_TM,
                  C_DISP_EMP_CDE,c_disp_emp_nme,c_disp_dpt_cde,c_disp_dpt_nme,n_Survey_Amt,C_REMARK)
                  values(S_DISPATCH_ID.Nextval,v_caseNo,v_dispType,v_dispSubType,v_surveyEmpcde,v_surveyEmpnme,
                  v_surveyDptcde,v_surveyDptnme,v_accdntSpot,v_srvyAddr,v_lcnNo,v_taskStat,v_dispTm,
                  v_dispEmpCde,v_dispEmpNme,v_dispDptCde,v_dispDptNme,v_surveyAmt,v_remark);
               elsif v_talThrcar >0 then
                  --三者
                  dbms_output.put_line('三者车总定损：' || v_talThrcar);
                  v_dispSubType := '0170004';
                  insert into
                  tbl_dispatch(N_DISPATCH_ID,c_case_no,c_disp_type,c_disp_subtype,c_survey_emp_cde,c_survey_emp_nme,
                  c_survey_dpt_cde,c_survey_dpt_nme,c_accident_addr,c_survey_addr,c_lcn_no,C_TASK_STAT,T_DISP_TM,
                  C_DISP_EMP_CDE,c_disp_emp_nme,c_disp_dpt_cde,c_disp_dpt_nme,n_Survey_Amt,C_REMARK)
                  values(S_DISPATCH_ID.Nextval,v_caseNo,v_dispType,v_dispSubType,v_surveyEmpcde,v_surveyEmpnme,
                  v_surveyDptcde,v_surveyDptnme,v_accdntSpot,v_srvyAddr,v_lcnNo,v_taskStat,v_dispTm,
                  v_dispEmpCde,v_dispEmpNme,v_dispDptCde,v_dispDptNme,v_surveyAmt,v_remark);
               else
                  --查勘
                  dbms_output.put_line('查勘。。。');
                  v_dispSubType := '0170002';
                  insert into
                  tbl_dispatch(N_DISPATCH_ID,c_case_no,c_disp_type,c_disp_subtype,c_survey_emp_cde,c_survey_emp_nme,
                  c_survey_dpt_cde,c_survey_dpt_nme,c_accident_addr,c_survey_addr,c_lcn_no,C_TASK_STAT,T_DISP_TM,
                  C_DISP_EMP_CDE,c_disp_emp_nme,c_disp_dpt_cde,c_disp_dpt_nme,n_Survey_Amt,C_REMARK)
                  values(S_DISPATCH_ID.Nextval,v_caseNo,v_dispType,v_dispSubType,v_surveyEmpcde,v_surveyEmpnme,
                  v_surveyDptcde,v_surveyDptnme,v_accdntSpot,v_srvyAddr,v_lcnNo,v_taskStat,v_dispTm,
                  v_dispEmpCde,v_dispEmpNme,v_dispDptCde,v_dispDptNme,v_surveyAmt,v_remark);
               end if;
             end if;
       end loop;
       close cur_dispatch;
       commit;
end;