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,很多写法不太确定