记一下，存储过程加了commit结果用mysql客户端执行不报错，用jdbcTemplate执行就执行了一步，因为加了commit后面的程序都不跑了。另外
存储过程里面如果用java调的话select赋值也会中断存储过程
#SELECT @v_bill_cycle:=bill_cycle_id FROM T_BILL_CYCLE WHERE DATE_FORMAT(from_time,'%Y%m') = v_year_month;
set @v_bill_cycle = v_year_month;
set @month_states = (SELECT month_state FROM T_PRODUCT_BILLSTATE_SPEC WHERE bill_state=v_bill_state AND product_spec_id = v_product_spec_id);
要注意赋值的时候select加上括号
最后决定还是用jdbc去调用
public void callPBillAttrSync(String yearMonth) {
jdbcTemplate.execute("CALL P_BILL_SYNC_ALL_ATTR("+yearMonth+")");
}
public void callPBillSyncJDBC(String yearMonth) {
String sql="{CALL P_BILL_SYNC(?)}";
Connection con = DataSourceUtils.getConnection(dataSource);
try {
CallableStatement c=con.prepareCall(sql);
c.setString(1, yearMonth);
c.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
还是报错了，
好吧，给存储过程加个返回值试下，
public int callMethod(String sql,String yearMonth){
Connection con = DataSourceUtils.getConnection(dataSource);
//String flag="@flag";
try {
System.out.println(sql+","+yearMonth);
CallableStatement c=con.prepareCall(sql);
//if(!yearMonth.equals("0")){
System.out.println(yearMonth);
c.setString(1, yearMonth);
//c.setString(2, flag);
c.registerOutParameter(2,Types.VARCHAR);
//}
c.setQueryTimeout(5);
try{
c.execute();
String rec_cnt=c.getString(2);
System.out.println(rec_cnt);
}catch(SQLTimeoutException e){
c.close();
con.close();
return 0;
}
c.close();
con.close();
return 0;
} catch (SQLException e) {
e.printStackTrace();
try {
con.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
return 0;
}
}
结果调用存储过程的时候报Parameter number 2 is not an OUT parameter ，
一查报错源码发现noAccessToProcedureBodies=false就抛异常了，为什么会抛出这个异常呢？
好吧，百度了下，找到了这么一条有用的信息https://www.aliyun.com/jiaocheng/1117218.html
解释：这是因为jdbc调用存储过程时需要有show create procudure权限或是有表mysql.proc的select的权限
解决方法:
1.通过在jdbc连接属性中设置noAccessToProcedureBodies=true(默认是false)。但是加该参数会有影响
- 调用存储过程时,将没有类型检查,设为字符串类型,并且所有的参数设为in类型,但是在调用registerOutParameter时,不抛出异常
- 存储过程的查询结果无法使用getXXX(String parameterName)的形式获取,只能通过getXXX(int parameterIndex)的方式获取
想想我们的数据库经过了mycat代理，就选择直连的方式，不用mycat代理（因为直连是用的root账号，这个要改下，并且存储过程也要设置为该用户的所有者）
好的，终于不报错了。坑爹啊，，，，，
存储过程如下：
BEGIN
DECLARE v_first_time DATETIME;
DECLARE v_start_time DATETIME;
SELECT @v_bill_cycle:=bill_cycle_id FROM T_BILL_CYCLE WHERE from_time=CONCAT(v_year_month,'01');
SET @v_first_day = CONCAT(v_year_month,'01');
SET v_first_time = NOW();
SET v_start_time = NOW();
#初始化清空表
SET @v_sql=CONCAT('TRUNCATE TABLE T_BILL_INFO');
PREPARE sqltext FROM @v_sql;
EXECUTE sqltext;
#日志
INSERT INTO T_BILL_LOG(bill_cycle,procs_name,sub_procs_name,flow_path,start_time,end_time,use_time,state)
VALUES (v_year_month,'P_BILL_SYNC','P_BILL_SYNC','初始化表',v_start_time,NOW(),UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(v_start_time),0);
#COMMIT;
#日志
INSERT INTO T_BILL_LOG(bill_cycle,procs_name,sub_procs_name,flow_path,start_time,end_time,use_time,state)
VALUES (v_year_month,'P_BILL_SYNC','P_BILL_SYNC','初始化表2',v_start_time,NOW(),UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(v_start_time),0);
#COMMIT;
#同步固网用户
SET v_start_time = NOW();
CALL P_BILL_INFO_SYNC(v_year_month);
#日志
INSERT INTO T_BILL_LOG(bill_cycle,procs_name,sub_procs_name,flow_path,start_time,end_time,use_time,state)
VALUES (v_year_month,'P_BILL_SYNC','P_BILL_INFO_SYNC_ADB','同步业务数据',v_start_time,NOW(),UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(v_start_time),0);
#COMMIT;
SET v_start_time = NOW();
##################特殊处理######################
#
UPDATE T_BILL_INFO SET OFFERING_SPEC_ID = PRODUCT_SPEC_ID
WHERE
PRODUCT_SPEC_ID IN ('GD9900546','GD9900550','GD9900551','GD9900552','GD9900553','GD9900554',
'GD9900131','GD9900414','GD9900418','GD9900477','GD9900073','GD2200001','GD9900068','GD9900007');
UPDATE T_BILL_INFO
SET OFFERING_SPEC_ID = 'GD9999000'
WHERE product_spec_id = 'GD9900517';
UPDATE T_BILL_INFO
SET OFFERING_SPEC_ID = 'GD9900100'
WHERE product_spec_id IN ('GD9900562','GD9900572','GD9900358','GD9900511');
##################特殊处理######################
INSERT INTO T_BILL_LOG(bill_cycle,procs_name,sub_procs_name,flow_path,start_time,end_time,use_time,state)
VALUES (v_year_month,'P_BILL_SYNC','P_BILL_SYNC','业务特殊处理',v_start_time,NOW(),UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(v_start_time),0);
#COMMIT;
#同步固网用户属性
/****/
SET v_start_time = NOW();
CALL P_BILL_SYNC_ALL_ATTR(v_year_month);
#日志
INSERT INTO T_BILL_LOG(bill_cycle,procs_name,sub_procs_name,flow_path,start_time,end_time,use_time,state)
VALUES (v_year_month,'P_BILL_SYNC_ALL_ATTR','P_BILL_SYNC_ALL_ATTR','业务同步属性结束',v_start_time,NOW(),UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(v_first_time),0);
#COMMIT;
INSERT INTO T_BILL_LOG(bill_cycle,procs_name,sub_procs_name,flow_path,start_time,end_time,use_time,state)
VALUES (v_year_month,'P_BILL_SYNC','P_BILL_SYNC','同步业务数据完毕',v_first_time,NOW(),UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(v_first_time),0);
#COMMIT;
END