hibernate调用oracle的存储过程

1.hibernate dao层调用oracle存储过程

@Repository
public class PcdmPeriodChartDao extends BaseDao<PcdmPeriodChart> {
	// 传入存储过程  和参数
	public void executeVoidProcedureSql(final String queryString, final Object[] params) throws Exception {
		Session session = getSession();
		session.doWork(new Work() {
			public void execute(Connection conn) throws SQLException {
				ResultSet rs = null;
				CallableStatement call = conn.prepareCall("{" + queryString + "}");
				if (null != params) {
					for (int i = 0; i < params.length; i++) {
						call.setObject(i + 1, params[i]);
					}
				}
				rs = call.executeQuery();
				call.close();
				rs.close();
			}
		});
	}

}

2.service 调用时

	//修改为存储过程调用

	logger.info("------------------------------开始调用工程交付存储过程------------------------------");

	//PCDMTIMEPRO  是oracle的存储过程的名字

	pcdmPeriodChartDao.executeVoidProcedureSql("call PCDMTIMEPRO()", null);
	logger.info("------------------------------调用工程交付存储过程结束------------------------------");
	//sendMsgForUser();

3.oracle 创建存储过程 PCDMTIMEPRO

CREATE OR REPLACE PROCEDURE "PCDMTIMEPRO"  is
       var_Count number(10);
       var_sysdate date;
 	 		 var_XCWG number(10);
 	 		 var_DZT number(10);
 	 		 var_JGZL number(10);
 	 		 var_CLPH number(10);
 	 		 var_DDYS number(10);
 	 		 var_DDQZ number(10);
 	 		 var_XMJS number(10);
 	 		 var_CLPK number(10);
 	 		 var_XMYS number(10);
 	 		 var_XMWS number(10);
 	 		 CURSOR C_PCDM IS
			 select distincT a.pm_id from pcdm_singe_project a where id in (select d.sige_id from pcdm_period_chart d where d.is_complete ='0');
			 C_ROW C_PCDM%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录  
			 CURSOR C_OFFICE IS
			 select distincT a.OFFICE_ID from pcdm_singe_project a where id in (select d.sige_id from pcdm_period_chart d where d.is_complete ='0');
			 C_ROW1 C_OFFICE%ROWTYPE; 
			 var_count1 number(10);
			 var_count2 number(10);
			 var_count3 number(10);
			 var_count4 number(10);
			 var_count5 number(10);
			 var_count6 number(10);
begin
	
	select sysdate into var_sysdate from dual;
	select TIME into var_XCWG from PCDM_TIME WHERE TYPE = 'XCWG'; 
	select TIME into var_DZT from PCDM_TIME WHERE TYPE = 'DZT'; 
	select TIME into var_JGZL from PCDM_TIME WHERE TYPE = 'JGZL'; 
	select TIME into var_CLPH from PCDM_TIME WHERE TYPE = 'CLPH'; 
	select TIME into var_DDYS from PCDM_TIME WHERE TYPE = 'DDYS'; 
	select TIME into var_DDQZ from PCDM_TIME WHERE TYPE = 'DDQZ'; 
	select TIME into var_XMJS from PCDM_TIME WHERE TYPE = 'XMJS';
	select TIME into var_CLPK from PCDM_TIME WHERE TYPE = 'CLPK';
	select TIME into var_XMYS from PCDM_TIME WHERE TYPE = 'XMYS';
	select TIME into var_XMWS from PCDM_TIME WHERE TYPE = 'XMWS';
	
	update PCDM_PERIOD_CHART set  DRAW_DAYS=ROUND((sysdate-COMPLETION_TIME),1),REMARKS=ROUND((sysdate-COMPLETION_TIME-var_DZT),1) where NODE='XCWG';
	
	update PCDM_PERIOD_CHART set  DATA_DAYS=ROUND((sysdate-DRAW_TIME),1) ,REMARKS=ROUND((sysdate-DRAW_TIME-var_JGZL),1) where NODE='DZT';
	update PCDM_PERIOD_CHART set  DATEEQUI_DAYS=ROUND((sysdate-DATA_TIME),1),REMARKS=ROUND((sysdate-DATA_TIME-var_CLPH),1)  where (NODE='JGZL' or NODE = 'DDYS' or NODE = 'DDQZ') and DATEEQUI_TIME is null;
	update PCDM_PERIOD_CHART set  ACCEPT_DAYS=ROUND((sysdate-DATA_TIME) ,1),REMARKS=ROUND((sysdate-DATA_TIME-var_DDYS),1) where (NODE='JGZL' or NODE = 'CLPH' or NODE = 'DDQZ') and ACCEPT_TIME is null;
	update PCDM_PERIOD_CHART set  VISA_DAYS=ROUND((sysdate-DATA_TIME),1),REMARKS=ROUND((sysdate-DATA_TIME-var_DDQZ),1)  where (NODE='JGZL' or NODE = 'CLPH' or NODE = 'DDYS') and VISA_TIME is null;
	
	update PCDM_PERIOD_CHART t set  SETTL_DATA_DAYS=ROUND((sysdate-(select greatest(DATEEQUI_TIME,ACCEPT_TIME,VISA_TIME)  from PCDM_PERIOD_CHART a where t.id=a.id)),1) 
	    ,REMARKS=ROUND((sysdate-(select greatest(DATEEQUI_TIME,ACCEPT_TIME,VISA_TIME)  from PCDM_PERIOD_CHART a where t.id=a.id)-var_XMJS),1) where 
			t.NODE = 'CLPH' or t.NODE = 'DDYS' or t.NODE = 'DDQZ' 
			and t.DATEEQUI_TIME is not null  and t.ACCEPT_TIME is not null AND t.DATEEQUI_TIME is not null;
			
	update PCDM_PERIOD_CHART set  DATA_FLAT_DAYS=ROUND((sysdate-SETTL_DATA_TIME),1),REMARKS=ROUND((sysdate-SETTL_DATA_TIME-var_CLPK),1) where NODE='XMJS';
	update PCDM_PERIOD_CHART set  PROJECT_ACCEPT_DAYS=ROUND((sysdate-DATA_FLAT_TIME),1),REMARKS=ROUND((sysdate-DATA_FLAT_TIME-var_XMYS),1)  where NODE='CLPK';
	update PCDM_PERIOD_CHART set  PROJECT_EXTADUI_DAYS=ROUND((sysdate-PROJECT_ACCEPT_TIME),1) ,REMARKS=ROUND((sysdate-PROJECT_ACCEPT_TIME-var_XMWS),1) where NODE='XMYS';
	
--删除当天记录  保证一天同一个人和部门只有一条记录
DELETE FROM PCDM_TIMEOUT_INFO t WHERE to_char(t.TIME,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');

--根据项目经理查询个数
	FOR C_ROW IN C_PCDM LOOP
	  	select count(*) into var_count1 from pcdm_period_chart a where a.REMARKS> -2 and a.REMARKS < -1 and a.sige_id in  (select id from pcdm_singe_project b where b.pm_id = C_ROW.pm_id);
	  	select count(*) into var_count2 from pcdm_period_chart a where a.REMARKS> -1 and a.REMARKS < 0 and a.sige_id in  (select id from pcdm_singe_project b where b.pm_id = C_ROW.pm_id);
	  	select count(*) into var_count3 from pcdm_period_chart a where a.REMARKS> 0 and a.REMARKS < 1 and a.sige_id in  (select id from pcdm_singe_project b where b.pm_id = C_ROW.pm_id);
	  	select count(*) into var_count4 from pcdm_period_chart a where a.REMARKS> 1 and a.REMARKS < 2 and a.sige_id in  (select id from pcdm_singe_project b where b.pm_id = C_ROW.pm_id);
	  	select count(*) into var_count5 from pcdm_period_chart a where a.REMARKS> 2 and a.REMARKS <3 and a.sige_id in  (select id from pcdm_singe_project b where b.pm_id = C_ROW.pm_id);
	  	select count(*) into var_count6 from pcdm_period_chart a where a.REMARKS> 3  and a.sige_id in  (select id from pcdm_singe_project b where b.pm_id = C_ROW.pm_id);
	  	
	  	insert into PCDM_TIMEOUT_INFO (ID,TYPE,USER_ID,OFFICE_ID,TWO_LATER_NUMS,ONE_LATER_NUMS,TIME_OUT,ONE_TIMEOUT_NUMS,TWO_TIMEOUT_NUMS,THR_MORE_TIMEOUT_NUMS,TIME) values(
			sys_guid(), 2, C_ROW.pm_id, null, var_count1, var_count2, var_count3, var_count4, var_count5, var_count6,sysdate);
	END LOOP;
	--根据部门查询个数
	FOR C_ROW1 IN C_OFFICE LOOP
	  	select count(*) into var_count1 from pcdm_period_chart a where a.REMARKS> -2 and a.REMARKS < -1 and a.sige_id in  (select id from pcdm_singe_project b where b.OFFICE_ID = C_ROW1.OFFICE_ID);
	  	select count(*) into var_count2 from pcdm_period_chart a where a.REMARKS> -1 and a.REMARKS < 0 and a.sige_id in  (select id from pcdm_singe_project b where b.OFFICE_ID = C_ROW1.OFFICE_ID);
	  	select count(*) into var_count3 from pcdm_period_chart a where a.REMARKS> 0 and a.REMARKS < 1 and a.sige_id in  (select id from pcdm_singe_project b where b.OFFICE_ID = C_ROW1.OFFICE_ID);
	  	select count(*) into var_count4 from pcdm_period_chart a where a.REMARKS> 1 and a.REMARKS < 2 and a.sige_id in  (select id from pcdm_singe_project b where b.OFFICE_ID = C_ROW1.OFFICE_ID);
	  	select count(*) into var_count5 from pcdm_period_chart a where a.REMARKS> 2 and a.REMARKS <3 and a.sige_id in  (select id from pcdm_singe_project b where b.OFFICE_ID = C_ROW1.OFFICE_ID);
	  	select count(*) into var_count6 from pcdm_period_chart a where a.REMARKS> 3  and a.sige_id in  (select id from pcdm_singe_project b where b.OFFICE_ID = C_ROW1.OFFICE_ID);
	  	
	  	insert into PCDM_TIMEOUT_INFO (ID,TYPE,USER_ID,OFFICE_ID,TWO_LATER_NUMS,ONE_LATER_NUMS,TIME_OUT,ONE_TIMEOUT_NUMS,TWO_TIMEOUT_NUMS,THR_MORE_TIMEOUT_NUMS,TIME) values(
			sys_guid(), 1, NULL, C_ROW1.OFFICE_ID, var_count1, var_count2, var_count3, var_count4, var_count5, var_count6,sysdate);
	END LOOP;


end PCDMTIMEPRO;