Posted on 2009-06-27 00:35
Gavin.lee 閱讀(711)
評論(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();