SpringjdbcTemplate调用oracle存储过程

CREATE OR REPLACE PROCEDURE  proc_xqxn_qxsj
AS

CURSOR csr_xqxn_xh IS 
SELECT cjdj.xnxq,cjdj.xjh FROM t_cj_cjdj cjdj  GROUP BY cjdj.xnxq,xjh;   

CURSOR csr_xh_cj_lx IS
SELECT a.xjh,a.cjflid,a.xk FROM t_xnxq_qxsj_tmp a GROUP BY a.xjh,a.cjflid,a.xk HAVING COUNT(*) >1; --学号、成绩类型、学科 ,xqxn

BEGIN
  
  
     /*  
     存储过程清洗学年学期总评成绩数据
       需要新建三张表 1、t_xnxq_qxsj_tmp
                      2、t_xnxq_qxsj2_tmp
                      3、t_xnxq_final_tmp
  */
  
  

--2.查当前学期学年学生名单,state=2的所有数据
---------------------------------------------------------------------
DELETE t_xnxq_qxsj_tmp t;
COMMIT;

FOR cur_1 IN csr_xqxn_xh LOOP--ID1学年学期，ID2学号
INSERT INTO t_xnxq_qxsj_tmp(xjh,score,cjflid,cjflname,cjflWeight,xk,xqxn)
SELECT cjdj.xjh 学生号,
       cjdj.score 分数,
       lx.d_id_  成绩分类ID,
       lx.name  成绩分类NAME,
       lx.weight 成绩分类权重,
       cjdj.xk 学科,
       cjdj.xnxq  学期学年
       FROM t_cj_cjdj cjdj,t_cj_lx lx
WHERE cjdj.xjh =cur_1.xjh  --学号
AND cjdj.xnxq=cur_1.xnxq    --学年学期
AND cjdj.state=2         --状态为2 是有效数据
AND cjdj.type=lx.d_id_;
END LOOP;
COMMIT;


--3.一个学生 state为2的 有2条以上的 屏蔽掉,根据学号科目去重
----------------------------------------------------------------------
--某个学生某个成绩有错误，直接从被选筛查表中删除本学期的成绩

FOR cur_1 IN csr_xh_cj_lx LOOP
  DELETE FROM t_xnxq_qxsj_tmp t2
WHERE t2.xjh=cur_1.xjh --学号
AND t2.xk=cur_1.xk;  --科目
END LOOP;
COMMIT;


DELETE t_xnxq_qxsj2_tmp t;
COMMIT;

INSERT INTO  t_xnxq_qxsj2_tmp(xjh,calscore,cjflid,cjflname,xk,xqxn) 
SELECT ct.xjh,ct.score*ct.cjflweight/100,ct.cjflid,ct.cjflname,ct.xk,ct.xqxn FROM t_xnxq_qxsj_tmp ct;

FOR cur_1 IN(   --删除 缺少一次成绩类型的数据，常规三次
SELECT ct.xjh,ct.xk FROM t_xnxq_qxsj2_tmp ct GROUP BY ct.xjh,ct.xk HAVING COUNT(*) <>3) LOOP
             DELETE t_xnxq_qxsj2_tmp ct WHERE ct.xjh =cur_1.xjh AND ct.xk=cur_1.xk;
END LOOP;

DELETE t_xnxq_final_tmp t;
COMMIT;

INSERT INTO t_xnxq_final_tmp(id,xjh,sumscore,xkname,xqxn)
SELECT sys_guid(),
temp.xjh 学号,temp.总和,xk.name,temp.xqxn  FROM (
SELECT t1.xjh,SUM(t1.calscore) 总和,t1.xk 科目,t1.xqxn FROM 
t_xnxq_qxsj2_tmp
 t1 GROUP BY t1.xjh,t1.xk,t1.xqxn
 ) temp,T_KJ_BASE_DISCIPLINE xk
WHERE   temp.科目=xk.id;


FOR cur_1 IN( SELECT d_id_,NAME,MAX,MIN,pass FROM t_cj_dj ) LOOP --赋值成绩评测数据
          UPDATE t_xnxq_final_tmp t2 SET t2.cjdj=cur_1.name,t2.pass=cur_1.pass
          WHERE t2.sumscore BETWEEN cur_1.min AND cur_1.max;
                              
END LOOP;
COMMIT;

END proc_xqxn_qxsj;
 

上面是存过

 public boolean ajaxCalculationPro() {
        try{
            jdbcTemplate.getJdbcTemplate().execute("call PROC_XQXN_QXSJ()");
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }

        return true;
    }

//这里是调用过程

在调用的时候注意 存储过程即使没有参数，后面也要加上()  这是语法