Posted on 2009-06-27 00:35
Gavin.lee 阅读(737)
评论(0) 编辑 收藏 所属分类:
JDBC
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
执行方法：一：
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();