java存储过程的创建与调用

最新推荐文章于 2022-09-09 09:32:57 发布

原创最新推荐文章于 2022-09-09 09:32:57 发布 · 485 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #存储 #exception #session #string #null

java 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一个用于更新项目统计信息的存储过程，该过程能够自动同步项目的预期收集总额、实际收集总额及总发票金额等关键指标。

create or replace procedure updateProject is
begin
update project p set p.total_intend_gather =
(select sum(ig.gather_sum) from intend_gather ig where ig.project_number=p.project_number);

update project p set p.total_actual_gather =
(select sum(ag.gahter_sum) from actual_gather ag where ag.project_number=p.project_number);

update project p set p.total_invoice=
(select sum(invoice.invoice_sum) from invoice invoice
where invoice.intend_id in
(select ig.intend_id from intend_gather ig where ig.project_number=p.project_number));

end updateProject;

Session session = this.getSession();
Transaction tx =null;
try {
    tx = session.beginTransaction();
    Connection con = session.connection();
    String procedure = "{call updateproject() }";
    CallableStatement cstmt = con.prepareCall(procedure);
    cstmt.executeUpdate();
    tx.commit();

} catch (Exception e) {
   tx.rollback();
}