Java调用Oracle中的存储过程
1、首先你得在数据库中写一个存储过程
例如:
P_DZK_NETRES(V_DZID IN NUMBER,V_DZTYPE IN NUMBER ,V_ZYBM IN VARCHAR2,V_DZ IN VARCHAR2,V_RENUM OUT NUMBER)
调用代码如下:
/**
* 描述: 执行存过和函数,paramValues与paramClass的长度必须是一致的,对java的几种基本数据类型通用
* @param func 存过调用字符串:func(?,?,?,?)
* @param paramValues 要传的参数值,要保证类型正确,以字符串数组的形式
* @param paramClass 要传的参数值的类型的一个示例,根据这个判断这个参数是什么类型的,例如:Object[] paramClass = {1L,1,"1","1"};
* @param returnIndex 输出参的位置
* @return 存过输出参的值
* @throws JCtnException
*/
public String executeCallable(String func, String[] paramValues,
Object[] paramClass, int returnIndex) throws JCtnException{
if(paramValues.length != paramClass.length) {
throw new JCtnException("存过参数值与参数类型的个数不一致!");
}
String res = "";
CallableStatement cstmt = null;
TransactionUtil transaction = tranLocal.get();
Connection conn = null;
try {
String sql = "{call " + func + "}";
if(transaction == null){//未使用事务
conn = poolHashMap.get(this.poolUrl).getConnection();
}else{//使用事务
conn = transaction.getConn();
}
cstmt = conn.prepareCall(sql);
int index = 1;
boolean flag = false;
for (int i = 1; i <= paramValues.length; i++) {
index = i;
if(i == returnIndex) {//第returnIndex个位置为输出参数,出参为中间的某一个
flag = true;
}
if(flag == true) {
index ++;
}
try {
if(paramClass[i-1] instanceof Integer) {
cstmt.setInt(index, Integer.valueOf(paramValues[i-1]));
} else if(paramClass[i-1] instanceof String) {
cstmt.setString(index, paramValues[i-1]);
} else if(paramClass[i-1] instanceof Boolean) {
cstmt.setBoolean(index, Boolean.valueOf(paramValues[i-1]));
} else if(paramClass[i-1] instanceof Double) {
cstmt.setDouble(index, Double.valueOf(paramValues[i-1]));
} else if(paramClass[i-1] instanceof Float) {
cstmt.setFloat(index, Float.valueOf(paramValues[i-1]));
} else if(paramClass[i-1] instanceof Long) {
cstmt.setLong(index, Long.valueOf(paramValues[i-1]));
} else if(paramClass[i-1] instanceof Byte) {
cstmt.setByte(index, Byte.valueOf(paramValues[i-1]));
}
} catch (Exception e) {
throw new JCtnException(e);
}
}
//设置出参
cstmt.registerOutParameter(returnIndex, Types.VARCHAR);
cstmt.execute();
res = cstmt.getString(returnIndex);
} catch (HibernateException e) {
throw new JCtnException(e);
} catch (SQLException e) {
throw new JCtnException(e);
} finally {
try {
if (cstmt != null) {
cstmt.close();
}
} catch (SQLException e) {
throw new JCtnException(e);
}
}
return res;
}