存储过程示例
--------------------------------------
--报案三者车信息
--------------------------------------
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;
