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 ;#客户不存在就插入BEGINdeclare 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 ;#客户不存在就插入BEGINdeclare 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 ;#客户不存在就插入BEGINdeclare 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);}