mysql存储过程在程序中调用不了_mysql 存储过程用程序调的问题

记一下，存储过程加了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就抛异常了，为什么会抛出这个异常呢？

解释：这是因为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