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 < deprice  THEN
      # '您的账户余额不足！'
      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;
}