java调用mysql存储过程（用临时表更新信息表）

存储过程

CREATE PROCEDURE `importPlusCustInfo`(IN `nowYear` int,IN `clearYear` int,OUT `status` int)
BEGIN
	#将临时表中的年度积分更新到客户表中，若客户表中不存在，这插入一条新数据
	#传入两个参数：nowYear 当前年份，clearYear 结转年份，传出一个参数 status 0成功，-1失败
	#若nowYear-clearYear=2，更新YEAR_PLUS_THR;
	#若nowYear-clearYear=1，更新YEAR_PLUS_TWO;
	#若nowYear-clearYear=0，更新YEAR_PLUS_ONE;
	DECLARE certNo VARCHAR(100) DEFAULT '';#证件号码
	DECLARE custName VARCHAR(200) DEFAULT '';#客户姓名
	DECLARE yearPlus int DEFAULT 0;
	DECLARE yearPlusOne int DEFAULT 0;#年度积分一
	DECLARE yearPlusTwo int DEFAULT 0;#年度积分二
	DECLARE yearPlusThr int DEFAULT 0;#年度积分三
	DECLARE impDate VARCHAR(10) DEFAULT '';#客户电话
	DECLARE custPhone VARCHAR(60) DEFAULT '';#客户姓名
	DECLARE yearClearStatus VARCHAR(10) DEFAULT '';#结转状态
	SET status = -1;
	START TRANSACTION;
	BEGIN
		#临时表CUST_INFO_TEMP有可能有重复数据，查询的时候需要去重（按证件号码去重 生成去重表CUST_INFO_TEMP_1）
		CREATE TABLE CUST_INFO_TEMP_1 AS (SELECT CERT_NO,CUST_NAME,YEAR_PLUS,YEAR_PLUS_ONE,YEAR_PLUS_TWO,YEAR_PLUS_THR,IMP_DATE,CUST_PHONE,YEAR_CLEAR_STATUS FROM CUST_INFO_TEMP GROUP BY CERT_NO);
		#去重表添加主键
		ALTER TABLE CUST_INFO_TEMP_1 ADD PRIMARY KEY(CERT_NO);
		#去年与前年积分未结转
		IF (nowYear-clearYear = 2) THEN
			#客户存在就更新客户积分
			UPDATE CUST_INFO A,CUST_INFO_TEMP_1 B SET A.CUST_NAME=B.CUST_NAME,A.YEAR_PLUS_THR = B.YEAR_PLUS_THR,A.YEAR_PLUS=(A.YEAR_PLUS_ONE+A.YEAR_PLUS_TWO+A.YEAR_PLUS_THR+A.YEAR_PLUS_EX1+A.YEAR_PLUS_EX2+A.YEAR_PLUS_EX3+A.YEAR_PLUS_EX4+A.YEAR_PLUS_EX5+A.YEAR_PLUS_EX6+A.YEAR_PLUS_EX7+A.YEAR_PLUS_EX8+A.YEAR_PLUS_EX9+A.YEAR_PLUS_EX10-A.YEAR_PLUS_EXCHG),A.IMP_DATE=B.IMP_DATE,A.CUST_PHONE=B.CUST_PHONE,A.YEAR_CLEAR_STATUS=B.YEAR_CLEAR_STATUS WHERE A.CERT_NO=B.CERT_NO ;
			#客户不存在就插入
			BEGIN				
				declare stop int default 0; 
				declare cur cursor for SELECT CERT_NO certNo,CUST_NAME custName,YEAR_PLUS yearPlus,YEAR_PLUS_THR yearPlusThr,IMP_DATE impDate,CUST_PHONE custPhone,YEAR_CLEAR_STATUS yearClearStatus FROM CUST_INFO_TEMP_1 WHERE CERT_NO NOT IN (SELECT CERT_NO FROM CUST_INFO);
				declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = 1;
				open cur;   
				fetch cur into certNo,custName,yearPlus,yearPlusThr,impDate,custPhone,yearClearStatus; 
				while stop <> 1 do  
						INSERT INTO CUST_INFO(CERT_NO,CUST_NAME,YEAR_PLUS,YEAR_PLUS_THR,IMP_DATE,CUST_PHONE,YEAR_CLEAR_STATUS) VALUES(certNo,custName,yearPlus,yearPlusThr,impDate,custPhone,yearClearStatus);
						fetch cur into certNo,custName,yearPlus,yearPlusThr,impDate,custPhone,yearClearStatus; 
				END while;
				close cur;
			END;
		#去年积分未结转
		ELSEIF (nowYear-clearYear = 1) THEN
			#客户存在就更新客户积分
			UPDATE CUST_INFO A,CUST_INFO_TEMP_1 B SET A.CUST_NAME=B.CUST_NAME,A.YEAR_PLUS_TWO = B.YEAR_PLUS_TWO,A.YEAR_PLUS=(A.YEAR_PLUS_ONE+A.YEAR_PLUS_TWO+A.YEAR_PLUS_THR+A.YEAR_PLUS_EX1++A.YEAR_PLUS_EX2+A.YEAR_PLUS_EX3+A.YEAR_PLUS_EX4+A.YEAR_PLUS_EX5+A.YEAR_PLUS_EX6+A.YEAR_PLUS_EX7+A.YEAR_PLUS_EX8+A.YEAR_PLUS_EX9+A.YEAR_PLUS_EX10-A.YEAR_PLUS_EXCHG),A.IMP_DATE=B.IMP_DATE,A.CUST_PHONE=B.CUST_PHONE,A.YEAR_CLEAR_STATUS=B.YEAR_CLEAR_STATUS WHERE A.CERT_NO=B.CERT_NO ;
			#客户不存在就插入
			BEGIN				
				declare stop int default 0; 
				declare cur cursor for SELECT CERT_NO certNo,CUST_NAME custName,YEAR_PLUS yearPlus,YEAR_PLUS_TWO yearPlusTwo,IMP_DATE impDate,CUST_PHONE custPhone,YEAR_CLEAR_STATUS yearClearStatus FROM CUST_INFO_TEMP_1 WHERE CERT_NO NOT IN (SELECT CERT_NO FROM CUST_INFO);
				declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = 1;
				open cur;   
				fetch cur into certNo,custName,yearPlus,yearPlusTwo,impDate,custPhone,yearClearStatus; 
				while stop <> 1 do  
					INSERT INTO CUST_INFO(CERT_NO,CUST_NAME,YEAR_PLUS,YEAR_PLUS_TWO,IMP_DATE,CUST_PHONE,YEAR_CLEAR_STATUS) VALUES(certNo,custName,yearPlus,yearPlusTwo,impDate,custPhone,yearClearStatus);
					fetch cur into certNo,custName,yearPlus,yearPlusTwo,impDate,custPhone,yearClearStatus; 
				END while;
				close cur;
			END;
	 #去年积分以结转
		ELSEIF(nowYear-clearYear = 0) THEN
			#客户存在就更新客户积分
			UPDATE CUST_INFO A,CUST_INFO_TEMP_1 B SET A.CUST_NAME=B.CUST_NAME,A.YEAR_PLUS_ONE = B.YEAR_PLUS_ONE,A.YEAR_PLUS=(A.YEAR_PLUS_ONE+A.YEAR_PLUS_TWO+A.YEAR_PLUS_THR+A.YEAR_PLUS_EX1++A.YEAR_PLUS_EX2+A.YEAR_PLUS_EX3+A.YEAR_PLUS_EX4+A.YEAR_PLUS_EX5+A.YEAR_PLUS_EX6+A.YEAR_PLUS_EX7+A.YEAR_PLUS_EX8+A.YEAR_PLUS_EX9+A.YEAR_PLUS_EX10-A.YEAR_PLUS_EXCHG),A.IMP_DATE=B.IMP_DATE,A.CUST_PHONE=B.CUST_PHONE,A.YEAR_CLEAR_STATUS=B.YEAR_CLEAR_STATUS WHERE A.CERT_NO=B.CERT_NO ;
			#客户不存在就插入
			BEGIN				
				declare stop int default 0; 
				declare cur cursor for SELECT CERT_NO certNo,CUST_NAME custName,YEAR_PLUS yearPlus,YEAR_PLUS_ONE yearPlusOne,IMP_DATE impDate,CUST_PHONE custPhone,YEAR_CLEAR_STATUS yearClearStatus FROM CUST_INFO_TEMP_1 WHERE CERT_NO NOT IN (SELECT CERT_NO FROM CUST_INFO);
				declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = 1;
				open cur;   
				fetch cur into certNo,custName,yearPlus,yearPlusOne,impDate,custPhone,yearClearStatus; 
				while stop <> 1 do  
					INSERT INTO CUST_INFO(CERT_NO,CUST_NAME,YEAR_PLUS,YEAR_PLUS_ONE,IMP_DATE,CUST_PHONE,YEAR_CLEAR_STATUS) VALUES(certNo,custName,yearPlus,yearPlusOne,impDate,custPhone,yearClearStatus);
					fetch cur into certNo,custName,yearPlus,yearPlusOne,impDate,custPhone,yearClearStatus; 
				END while;
				close cur;
				END;
		END IF;
		#删除去重表
		DROP TABLE IF EXISTS  CUST_INFO_TEMP_1;
		#清空临时表
		TRUNCATE TABLE CUST_INFO_TEMP;
		COMMIT;
		SET status = 0;
	END;
END

JDBC调用实例

                        Connection con = null;
			CallableStatement cstmt = null;
			int flag = -1;//执行状态  0：成功
			try {
				startTime = System.currentTimeMillis();
				con = JDBCUtil.getConection();
				//三个参数：当前年份(2014)，结转年份(2013)，返回标志(flag)
				cstmt = con.prepareCall("{call importPlusCustInfo(?,?,?)}"); 
				cstmt.setInt(1, curYear);
				cstmt.setInt(2, clearYear);
				cstmt.registerOutParameter(3, Types.INTEGER);
				cstmt.execute();
				flag = cstmt.getInt(3);
				if(0==flag){
					logger.info("从临时表更新到客户信息表执行成功！");
					success = true;
				}else{
					logger.info("从临时表更新到客户信息表执行失败");
					success = false;
				}
				endTime = System.currentTimeMillis();
				logger.info("从临时表更新到客户信息表用时：【"+(endTime-startTime)+"】ms");
				JawaGlobals.setProperty("JIFEN", "0");
				JawaGlobals.setProperty("system.status", "0");
			} catch (Exception e) {
				logger.error("执行存储过程出错",e);
				success = false;
			} finally{
				JDBCUtil.close(cstmt, con);
			}