MySQL执行存储过程

程序执行存储过程首先需要在控制台书写存储过程代码，并且执行存储过程，我是用idea的数据库模块书写代码并且在上面执行存储过程，我的存储过程代码为：

create procedure PRO_usedGoods_Purchase(IN inuid varchar(11), IN ingid int)
label:BEGIN
-- 声明变量 账户余额、商品价格、商品状态
DECLARE decoin int;
DECLARE deprice int;
DECLARE destate int;
-- 获取变量值
SELECT coin into decoin FROM user WHERE uid = inuid;
SELECT price into deprice FROM usedgoods WHERE gid = ingid;
SELECT gstatus into destate FROM usedgoods WHERE gid = ingid;
-- 账户余额小于商品价格
IF decoin # '您的账户余额不足！'
ROLLBACK;
LEAVE label;
END IF;
-- 商品已被出售
IF destate != 0 THEN
# '该商品已出售'
ROLLBACK;
LEAVE label;
END IF;
IF decoin > deprice THEN
-- 账户上扣除商品价格
UPDATE user SET coin = decoin-deprice WHERE uid = inuid;
-- 置商品为已售
UPDATE usedGoods SET gstatus = 1 WHERE gid = ingid;
-- 添加纪录到goodsOrder商品订单表
# INSERT INTO goodsOrder(gid,otime,uid) VALUES(ingid,DATE(CURDATE()),inuid);
-- 成功执行存储过程
END IF;
END;
其中SELECT coin into decoin FROM user WHERE uid = inuid;语句为将查询到的金币值赋给一个变量decoin,我先前是写的SELECT coin = decoin FROM user WHERE uid = inuid;最终查询到的结果全为空，mysql不能这样赋值，所以应该用into语句。

第二，在JDBC工具类上面书写调用存储过程的函数，在JDBC工具类中书写存储过程的好处是该工具类已经获取了MySQL连接数据库的驱动、用户名、密码、路径等，所以在该类中书写代码然后在其他类中直接调用该方法，增加了程序的可读性，代码如下：

//执行存储过程代码
//当用户点击购买后若用户金币大于商品价钱则执行购买操作，减去用户账户上面相对应的金币，且相应的
//商品状态置为已售状态，若小于商品价格则执行回滚操作，并提示账户余额不足
public static void executePRO(String uid, int gid) throws ClassNotFoundException, SQLException {
Class.forName(driver);
Connection cOnnection= DriverManager.getConnection(url, username, password);
CallableStatement cstm = connection.prepareCall("{call PRO_usedGoods_Purchase(?,?)}");
cstm.setString(1, uid);
cstm.setInt(2, gid);
cstm.execute();
cstm.close();
connection.close();
}

最后在DAO层直接调用该类中的方法

public static int insert(GoodsOrder go) throws SQLException, ClassNotFoundException {
String sql = "select gstatus from usedGoods where gid = ?";
ResultSet rs = JDBCHelper.query(sql,go.getGid());
int gstatus=0;
if(rs.next()){
gstatus = rs.getInt(1);
}
if (gstatus==0) {
String SQL = " insert into " +tableName + " ( gid , uid ,name , address , phone , oimage , price) "
+" values ( "+go.getGid()+" , '"+go.getUid()+"' , '"+go.getName()+"' , '"+go.getAddress()+"' , '"+go.getPhone()+"' , '"+go.getOimage()+"' , "+go.getPrice()+" )";
JDBCHelper.executePRO(go.getUid(),go.getGid());//调用存储过程
return JDBCHelper.executeInsert( SQL) ;
}
return 0;
}