Java中执行存储过程

来源：互联网发布：高端机箱知乎编辑：程序博客网时间：2024/05/23 01:59

存储过程示例：

CREATE proc p_upAppPda(
    @upNo varchar(50),
    @whCode varchar(50),
    @instockNo varchar(50),
    @ReturnVal int output
)
as
Begin

set @gettime=convert(varchar(10),getdate(),120)

Begin tran t_test
if exists (select a.upNo from v_sumUpQty a left join v_factNum b on a.upNo=b.upNo where convert(decimal(19,0),a.upQty)-convert(decimal(19,0),b.factNum)< 0 and a.upNo=@upNo)
Begin
   Rollback tran t_test
   set @ReturnVal=1
   return -1
end
else
Begin
    if exists (select autoId from t_inventory where whCode=@whCode and packageBarcode=@boxCode and inventCode=@inventCode and placeCode=@placeCode )
    Begin
        if(@@error<>0)
        Begin
            Raiserror('修改数据出错',16,-1)
            Rollback tran t_test
            set @ReturnVal=-1
            Return -1
        end
    end
    Commit tran t_test
    set @ReturnVal=0
    return 0
end

相应的Java代码：

执行方法：一：

Connection conn = db.createConnection();

CallableStatement call = null;

call=conn.prepareCall("{call p_upAppPda(?,?,?,?)}");

call.setString(1, upNo);

call.setString(2, whCode);

call.setString(3, instockNo);

call.registerOutParameter(4,java.sql.Types.INTEGER);

call.executeUpdate();

System.out.println(call.getInt(4)); // 获取存储过程的返回值

方法二（看过没用过，不知道怎么样）：

Connection conn = db.createConnection();

Statement statement = conn.createStatement();
statement = conn.prepareStatement("exec p_upAppPda'" + sql + "'," + 1+ "," + 2);

rs = statement.executeQuery();