使用java spring中的JdbcTemplate调用oracle中的存储过程(该存储过程有入参还出参)：
存储过程：
CREATE OR REPLACE PROCEDURE "HIS_BZ_QC"."PROC_APP_REGIST" (
Hosnum In Varchar2,--医院编号
App_Hid In Varchar2,--号源ID
App_Id_No In Varchar2,--身份证号
App_Patient_Name In Varchar2,--就诊人姓名
App_Phone In Varchar2,
App_Patient_Id In Varchar2,--病人ID
App_Dept_Code In Varchar2,--科室编号
App_Dept_Name In Varchar2,--科室名称
App_Doctor_Code In Varchar2,--医生编号
App_Doctor_Name In Varchar2,--医生姓名
App_Reg_Date In Varchar2,--预约号源日期
App_Hb_Time In Varchar2,--号源时段
App_Clinic_Duration In Varchar2,--号源午别
App_Trade_Order_No In Varchar2,--趣医订单号
App_Diag_Fee In Varchar2,--诊疗费
App_Pharmacy_Fee In Varchar2,--药事费
His_Operator In Varchar2,--HIS操作员编号
His_Operator_Name In Varchar2,--HIS操作员姓名
Free_Treat_Flag In Varchar2,--挂号费别
Result_Code Out Varchar2,--预约返回结果
Error_Msg Out Varchar2,--错误信息
His_Order_No Out Varchar2,--HIS交易流水号
Verify_Code Out Varchar2,--取号验证码
Clinic_No Out Varchar2,--门诊号
Visit_No Out Varchar2,--就诊序号
Ser_No Out Varchar2--发票号
) As
T_Err_Item Exception;
T_Error_Msg Varchar2(1000);
T_Regid Varchar2(100);
T_Invoiceid Varchar2(100);
T_Patientid Bas_Patients.Patientid%Type;
T_Chgdetailid Chg_Details.Chgdetailid%Type;
T_Payid Chg_Paydetails.Payid%Type;
T_Patname Bas_Patients.Patname%Type;
T_Sex Bas_Patients.Sex%Type;
T_Dateofbirth Bas_Patients.Dateofbirth%Type;
T_Idnum Bas_Patients.Idnum%Type;
T_Phonenumber Bas_Patients.Phonecall%Type;
T_Clctimes Bas_Patients.Clctimes%Type := 1;
T_Currentnum Clc_Sch.M_Currentnum%Type;
T_Datereg Clc_Reg.Datereg%Type;
T_Invoicenum Bas_Invoice.Currentnum%Type;
T_Num Number;
T_Am Varchar2(2);
T_His_Operator Bas_Patients.Operator%Type;--HIS操作员编号
T_His_Operator_Name Bas_Patients.Operatorname%Type;--HIS操作员姓名
T_Hosnum Clc_Sch.Hosnum%Type;
T_Takeid Bas_Invoice.Takeid%Type;
T_Regage Clc_Reg.Regage%Type;
Begin
--判断排班和医院编号是否匹配
Select A.Hosnum Into T_Hosnum From Clc_Sch A Where Schid = App_Hid;
If T_Hosnum != Hosnum Then
T_Error_Msg := '排班和医院编号不匹配！';
Raise T_Err_Item;
End If;
--获取操作员编号
Select His_Operator Into T_His_Operator From Dual;
If Sql%Rowcount = 0 Then
T_Error_Msg := '获取操作员编号错误！';
Raise T_Err_Item;
End If;
--获取操作员姓名
Select His_Operator_Name Into T_His_Operator_Name From Dual;
If Sql%Rowcount = 0 Then
T_Error_Msg := '获取操作员姓名错误！';
Raise T_Err_Item;
End If;
--判断是否有患者信息，无，新建，有，返回。
--无患者信息
Select Count(*) Into T_Num From Bas_Patients A Where A.Patname = App_Patient_Name And A.Idnum = App_Id_No;
If T_Num = 0 Then
--获取patientid
Select Seq_Patientid.Nextval Into T_Patientid From Dual;
--获取性别
Select Decode(Mod(Substr(App_Id_No,-2,1),2),1,'男',0,'女') Into T_Sex From Dual;
--获取出生年月
Select to_date(Substr(App_Id_No,7,8),'yyyy-mm-dd') Into T_Dateofbirth From Dual;
--获取年龄
Select (Extract(Year From Systimestamp) - Extract(Year From (Select To_Timestamp(T_Dateofbirth,'yyyy-mm-dd') From Dual))) into T_Regage From Dual;
--创建患者信息
Insert Into Bas_Patients(Hosnum,Patientid,Clctimes,Inptimes,Patname,Sex,Dateofbirth,Instype,Idtype,Idnum,Phonecall,Docdate,Operator,Operatorname)
Values(Hosnum,T_Patientid,1,0,App_Patient_Name,T_Sex,T_Dateofbirth,'自费','身份证',App_Id_No,App_Phone,Sysdate,T_His_Operator,T_His_Operator_Name);
--插入患者就诊卡类型
Insert Into Bas_Patient_Ids Values(Hosnum,T_Patientid,'身份证',T_Patientid,Null,Null,Null);
Insert Into Bas_Patient_Ids Values(Hosnum,T_Patientid,'就诊卡',App_Id_No,Null,Null,Null);
If Sql%Rowcount = 0 Then
T_Error_Msg := '插入患者就诊卡类型失败！';
Raise T_Err_Item;
end if;
--有患者信息
ElsIf T_Num >= 1 Then
--获取病人ID和就诊次数
Select A.Patientid,A.Clctimes Into T_Patientid,T_Clctimes From Bas_Patients A Where A.Patname = App_Patient_Name And A.Idnum = App_Id_No And Rownum = 1 Order By Clctimes Desc;
--获取出生年月
Select to_date(Substr(App_Id_No,7,8),'yyyy-mm-dd') Into T_Dateofbirth From Dual;
--获取年龄
Select (Extract(Year From Systimestamp) - Extract(Year From (Select To_Timestamp(T_Dateofbirth,'yyyy-mm-dd') From Dual))) into T_Regage From Dual;
Else
T_Error_Msg := '患者信息出错！';
Raise T_Err_Item;
End If;
--获取排班挂号当前号序
Select Decode(App_Clinic_Duration,'上午',M_Currentnum,'下午',A_Currentnum) Into T_Currentnum From Clc_Sch A Where A.Schid = App_Hid;
--获取当前时间上下午
--Select To_Char(Sysdate,'AM') From Dual;
--获取性别
Select Decode(Mod(Substr(App_Id_No,-2,1),2),1,'男',0,'女') Into T_Sex From Dual;
--更新排班号序
Update Clc_Sch A Set M_Currentnum = Nvl(M_Currentnum,0) + 1 Where A.Schid = App_Hid And '上午' = App_Clinic_Duration ;
Update Clc_Sch A Set A_Currentnum = Nvl(A_Currentnum,0) + 1 Where A.Schid = App_Hid And '下午' = App_Clinic_Duration ;
--获取发票号
Select Lpad(Currentnum,10,'0'),Takeid Into T_Invoicenum,T_Takeid From Bas_Invoice A Where A.Invoicetype = '挂号发票' And A.Operator = T_His_Operator And A.Empid = T_His_Operator And Lpad(A.Currentnum,10,'0')<Lpad(A.Endnum,10,'0') And A.Hosnum = Hosnum And Rownum = 1 Order By Takedate Desc;
--更新发票号
Update Bas_Invoice A Set Currentnum = Lpad(Currentnum+1,10,'0') Where A.Invoicetype = '挂号发票' And A.Operator = T_His_Operator And A.Empid = T_His_Operator and LPAD(a.Currentnum,10,'0')<LPAD(a.endnum,10,'0') and a.hosnum = Hosnum and a.Takeid = T_Takeid ;
--获取T_Regid
Select Sys_Guid() Into T_Regid From Dual;
--获取T_Invoiceid
Select Sys_Guid() Into T_Invoiceid From Dual;
--获取T_Payid
Select Sys_Guid() Into T_Payid From Dual;
--获取挂号时间
Select Sysdate Into T_Datereg From Dual;
--获取上下午
Select Decode(To_Char(Sysdate,'AM'),'上午','M','下午','A') Into T_Am From Dual;
--插入挂号信息
Insert Into Clc_Reg(Hosnum,Regid,Patientid,Clctimes,Instype,Schid,Datereg,Regno,Clcdate,Clctime,Operator,Operatorname,Invoiceid,Regage,Regsex,Regpatname,Nodecode)
Values(Hosnum,T_Regid,T_Patientid,T_Clctimes,'自费',App_Hid,T_Datereg,T_Currentnum+1,To_Date(App_Reg_Date,'yyyy/mm/dd'),T_Am,T_His_Operator,T_His_Operator_Name,T_Invoiceid,T_Regage,T_Sex,App_Patient_Name,Hosnum);
If Sql%Rowcount = 0 Then
T_Error_Msg := '插入挂号信息失败！';
Raise T_Err_Item;
end if;
--插入 收费明细表 Chg_Details
Insert Into Chg_Invoice(
Hosnum,
Invoiceid ,
Invoiceno ,
Chgdate ,
Clcorinp ,
Patientid ,
Instype ,
Billno ,
Discount ,
Diagname ,
Conditiondes ,
Totalcost ,
Selfcare ,
Selfcost ,
Payment ,
Relationno ,
Opayamt ,
Cashin ,
Change ,
Operator ,
Reportdate ,
Reportid ,
Regid ,
Dtmainid ,
Cancelflag ,
Totalcost_A ,
Selfdebt ,
Banlance_Ty ,
Banlance_Oy,
Payway)
Values(Hosnum,
T_Invoiceid,
T_Invoicenum,
Sysdate,
'门诊',
T_Patientid,
'自费',
'',
'',
'',
'',
App_Diag_Fee+App_Pharmacy_Fee,
'',
'',
'趣医APP',
'',
'',
'',
'',
T_His_Operator,
'',
'',
T_Regid,
'',
'',
App_Diag_Fee+App_Pharmacy_Fee,
0,
0,
0,
'趣医支付');
If Sql%Rowcount = 0 Then
T_Error_Msg := '插入收费明细表失败！';
Raise T_Err_Item;
end if;
--Chg_Invoice 收费发票表
--获取Chgdetailid
Select Chg_Details_Seq.Nextval Into T_Chgdetailid From Dual;
Insert Into Chg_Details(Hosnum,
Chgdetailid,
Invoiceid,
Itemcode,
Itemname,
Recipeid,
Parentid,
Qty,
Unit,
Unitprice3,
Amt3,
Medlevel,
Payrate,
Toself,
Selfcare,
Selfpay,
Discount,
Gentype,
Patientid,
Accountitem,
Invoiceitem,
Sheetdept,
Deptname,
Sheetward,
Sheetwardname,
Sheetdoctor,
Doctorname,
Excdept,
Excdeptname,
Giventime,
Givenempid,
Givenemp,
Givenwindow,
Insid,
Accountamt,
Medamt,
Selfdebt,
Chgtype,
Sheetdate,
Recipeno,
Dtmainid,
Accountitemname,
Invoiceitemname,
Medcode,
Sheetid,
Unitprice,
AMT,
Nodecode,
Chgdetailid_O,
INVOICEID_O,
Otherrecede,
Clinicdept,
Clinicdoctorid,
Clinicdoctorname,
Unitpprice,
Clinicno,
Clinicredeemno,
Recordername,
Recorder,
Recordtime,
Regid,
Emr_Fee_Category)
Values(Hosnum,
T_Chgdetailid,
T_Invoiceid,
Null,--字典中没有维护好，目前先置null
'挂号费',
Null,
Null,
1,
'次',
Null,
Null,
Null,
0,
Null,
0,
0,
0,
'自动',
T_Patientid,
'10',
'04',
'126',
'收费室',
Null,
Null,
T_His_Operator,
T_His_Operator_Name ,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
0,
0,
0,
'其它',
to_date(App_Reg_Date,'yyyy/mm/dd'),
Null,
Null,
'挂号收入',
'挂号费',
Null,
Null,
App_Diag_Fee+App_Pharmacy_Fee,
App_Diag_Fee+App_Pharmacy_Fee,
Hosnum,
Null,
Null,
Null,
Null,
Null,
Null,
App_Diag_Fee+App_Pharmacy_Fee,
Null,
Null,
Null,
Null,
Null,
Null,
Null);
If Sql%Rowcount = 0 Then
T_Error_Msg := '插入收费发票表失败！';
Raise T_Err_Item;
end if;
--Chg_Paydetails 支付明细表
Insert Into Chg_Paydetails(Payid,
Hosnum,
Invoiceid,
Paytype,
Rsheetno,
Amt,
Payunit,
Paysheetno,
Paydate,
Operator,
Operatorname,
Comments,
Amt_G,
Paytypecode,
Nodecode,
Patientid
)
Values(T_Payid,
Hosnum,
T_Invoiceid,
'趣医支付',
Null,
App_Diag_Fee+App_Pharmacy_Fee,
Null,
T_Invoiceid,
Sysdate,
T_His_Operator,
T_His_Operator_Name ,
Null,
Null,
'1',
Hosnum,
T_Patientid );
If Sql%Rowcount = 0 Then
T_Error_Msg := '插入支付明细表失败！';
Raise T_Err_Item;
end if;
--返回结果
Result_Code:='0000';
Error_Msg:='挂号成功';
His_Order_No:=T_Regid;
Verify_Code:='';
Clinic_No:=T_Patientid;
Visit_No:=T_Currentnum + 1;
SER_NO:=T_Invoicenum;
Commit;
Exception
When T_Err_Item Then
Result_Code:='0004';
Error_Msg:=T_Error_Msg;
His_Order_No:='';
Verify_Code:='';
Clinic_No:='';
Visit_No:='';
Ser_No:='';
Rollback;
When Others Then
Result_Code:='0005';
Error_Msg:=Sqlerrm;
His_Order_No:='';
Verify_Code:='';
Clinic_No:='';
Visit_No:='';
Ser_No:=dbms_utility.format_error_backtrace();
Rollback;
End;
/调用该存储过程的Java代码：
@SuppressWarnings({ "unchecked", "rawtypes" })
public Map<String, Object> executCall(RegisterInfo registerInfo) {
String sqlStr = " {CALL PROC_APP_REGIST(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
Map<String,Object> resultMaps = new HashMap<String, Object>();
resultMaps = (Map<String, Object>) this.getJdbcTemplate().execute(sqlStr, new CallableStatementCallback() {
@Override
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
Map<String,Object> outMap = new HashMap<String, Object>();
//创建规范的入参，和声明出参
buildProduceParameter(registerInfo, cs);
cs.execute();
outMap.put("Result_Code", cs.getString("Result_Code"));
outMap.put("Error_Msg", cs.getString("Error_Msg"));
outMap.put("His_Order_No", cs.getString("His_Order_No"));
outMap.put("Verify_Code", cs.getString("Verify_Code"));
outMap.put("Clinic_No", cs.getString("Clinic_No"));
outMap.put("Visit_No", cs.getString("Visit_No"));
outMap.put("Ser_No", cs.getString("Ser_No"));
return outMap;
}
/**
*
* <pre>
* 任务：
* 描述：存储过程参数声明创建
* @param registerInfo
* @param cs
* @throws SQLException
* </pre>
*/
private void buildProduceParameter(RegisterInfo registerInfo,
CallableStatement cs) throws SQLException {
cs.setString("Hosnum", registerInfo.getHosNum());
cs.setString("App_Hid",registerInfo.gethId());
cs.setString("App_Id_No",registerInfo.getAppIdNo());
cs.setString("App_Patient_Name",registerInfo.getAppPatientName());
cs.setString("App_Phone","appphone");
cs.setString("App_Patient_Id",registerInfo.getAppPatientId());
cs.setString("App_Dept_Code",registerInfo.getAppDeptCode());
cs.setString("App_Dept_Name",registerInfo.getAppDeptName());
cs.setString("App_Doctor_Code",registerInfo.getAppDoctorCode());
cs.setString("App_Doctor_Name",registerInfo.getAppDoctorName());
cs.setString("App_Reg_Date",registerInfo.getAppRegDate());
cs.setString("App_Hb_Time",registerInfo.getAppHbTime());
cs.setString("App_Clinic_Duration",registerInfo.getAppClinicDuration());
cs.setString("App_Trade_Order_No",registerInfo.getAppTradeOrderNo());
cs.setString("App_Diag_Fee",registerInfo.getAppDiagFee());
cs.setString("App_Pharmacy_Fee",registerInfo.getAppPharmacyFee());
cs.setString("His_Operator",registerInfo.getHisOperator());
cs.setString("His_Operator_Name",registerInfo.getHisOperatorName());
cs.setString("Free_Treat_Flag","普通挂号费");
cs.registerOutParameter("Result_Code", Types.VARCHAR);
cs.registerOutParameter("Error_Msg", Types.VARCHAR);
cs.registerOutParameter("His_Order_No", Types.VARCHAR);
cs.registerOutParameter("Verify_Code", Types.VARCHAR);
cs.registerOutParameter("Clinic_No", Types.VARCHAR);
cs.registerOutParameter("Visit_No", Types.VARCHAR);
cs.registerOutParameter("Ser_No", Types.VARCHAR);
}
});
return resultMaps;
}
2330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
