Oracle 存储过程 Java文件应该怎样调用

Question

Oracle 存储过程 我是第一次写，不知道对不对，我想在Java文件中调用存储过程，应该怎么调用，我是这么写的
存储过程

/此函数用于往中间表插数据等操作/

create or replace procedure assessDataMiddle(tcomcode in varchar2,
                                            tcWageNo in varchar2,
                                            tCurrentDate in varchar2,
                                            tCurrentTime in varchar2)
AS
monthStartDate varchar2(10);
monthEndDate   varchar2(10);
tflage     number:=0;看往中间表插数是否成功
v_exception    varchar2(4000); 异常信息



begin
select to_char(trunc(add_months(last_day(to_date(tCurrentDate,'yyyy-mm-dd')), -1) + 1), 'yyyy-mm-dd') into monthStartDate from dual;   本月第一天
select to_char(last_day(to_date(tCurrentDate,'yyyy-mm-dd')), 'yyyy-mm-dd') into monthEndDate from dual;      本月最后一天
打印入参
dbms_output.put_line('分公司名称:'||tcomcode);
dbms_output.put_line('考核年月:'||tcWageNo);

往中间表插入值

insert into  LACountAssessReport_TX_TEMP(WageNo,AlertExecutionDate,comcode,comcodeName,deptcode,deptcodeName,managecome,managecomeName,branchattr_Q,branchattr_Q_Name,branchattr_B,branchattr_B_Name,Agentcode,AgentcodeName,InsideFlagq,Agentseries,NewRank,AssessType,AgentState,StartDate,EndDate,K82,K83,K84)
(select tcWageNo,tCurrentDate,substr(a.managecom, 0, 4),
(SELECT name FROM cms.ldcom where trim(comcode) = substr(a.managecom, 0, 4)),
substr(a.managecom, 0, 6),(SELECT name FROM cms.ldcom where trim(comcode) = substr(a.managecom, 0, 6)),
(select comcode from ldcom where trim(comcode)=b.managecom),
(select name from ldcom where trim(comcode)=b.managecom),
(select managecom from labranchgroup where agentgroup = (select substr(branchseries,1,12) from labranchgroup where agentgroup = b.agentgroup)),
(select name from labranchgroup where agentgroup = (select substr(branchseries,1,12) from labranchgroup where agentgroup = b.agentgroup)),
(select managecom from labranchgroup where agentgroup = (select substr(branchseries,14,12) from labranchgroup where agentgroup = b.agentgroup)),
(select name from labranchgroup where agentgroup = (select substr(branchseries,14,12) from labranchgroup where agentgroup = b.agentgroup)),
a.AGENTCODE,
b.name,
a.insideflag,
(select trim(agentseries) from latree where agentcode = a.agentcode),
a.agentgrade,
a.AssessType,
case b.AGENTSTATE when '01' then '在职' when '02' then '在职' when '03' then '解约' when '04' then '解约' end ,
(select startDate_yjtx(a.agentcode,tcWageNo,'1') from dual),
(select startDate_yjtx(a.agentcode,tcWageNo,'2') from dual),
monthStartDate,
monthEndDate,
'00'
from cms.latree a, cms.laagent b, cms.labranchgroup c where a.agentcode = b.agentcode   
and a.agentgroup = c.agentgroup  and a.branchtype = '2' and a.agentgrade like 'V%'
 and a.managecom like   tcomcode || '%'
and to_char(b.employdate,'yyyymm')  <= tcWageNo  
and (to_char(b.outworkdate,'yyyy-mm-dd') is null or to_char(b.outworkdate,'yyyy-mm-dd') between monthStartDate  and  monthEndDate ));
commit;
异常处理
Exception
when others then
DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode); 
DBMS_OUTPUT.put_line('sqlerrm : ' ||sqlerrm); 
DBMS_OUTPUT.put_line('sqlerrm : ' ||substr(sqlerrm,1,100));是截取前100个字符显示出来。
rollback;
    v_exception := sqlcode || SUBSTR(sqlerrm, 1, 400);
```java

LACountAssessReport_TX_Error
insert into LACountAssessReport_TX_Error values(tcWageNo,
                         tcomcode,
                         v_exception, 
                         tCurrentDate,
                         tCurrentTime);
dbms_output.put_line(' LACountAssessReport_TX_Error 发生异常！ ');                         
commit;

end;

Java 调用



```java
        ExeSQL tExeSQL = new ExeSQL();
        String sql ="select call assessDataMiddle('"+comcode+"','"+cWageNo+"','"+CurrentDate+"','"+CurrentTime+"')from dual";
        System.out.println("插入中间表基本信息函数" +sql);

        if (!tExeSQL.execUpdateSQL(sql)) {
            buildError("calcAsess", tExeSQL.mErrors.getFirstError());
            logger.info("分公司:" + comcode + ",考核年月:" +cWageNo+
                    + "插入中间表基本信息数据失败");
            return false;
        }

框架比较老，不是boot，很多写法不太确定

Answer 1

获取到数据库连接，然后调用 ；类似于下边这样

Session session = this.getSession();
            Connection conn = session.connection();
            java.sql.CallableStatement cstmt = null;
            String callSql = "{call test.test(?,?)}";
            try {
                conn.setAutoCommit(false);
                cstmt = conn.prepareCall(callSql);
                cstmt.setString(1,assessType);
                cstmt.setString(2,userCode);
                cstmt.execute();
                conn.commit();
                conn.setAutoCommit(true);
            } catch (Exception e) {
                e.printStackTrace();
                conn.rollback();
            } finally {
                cstmt.close();
                conn.close();
            }
            
        }