java存储过程 函数_Java中调用存储过程或存储函数的方法

1.调用存储过程：

CallableStatement clstmt = null;

try {

clstmt = conn.prepareCall("{call package_name.procedure_name(?,?,?,?,?)}");

clstmt.setString(1, bill.getBillType());

clstmt.setString(2, bill.getId());

clstmt.setInt(3, bill.getNum());

clstmt.registerOutParameter(4, Types.CHAR);

clstmt.registerOutParameter(5, Types.VARCHAR);

clstmt.execute();

out_opresult = clstmt.getString(4).trim();

out_operror = clstmt.getString(5).trim();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

2.调用存储函数：

CallableStatement clstmt = null;

try {

clstmt = conn.prepareCall("{? = call package_name.function_name(?,?,?,?)}");

clstmt.registerOutParameter(1, Types.VARCHAR);

clstmt.setString(2, bill.getBillType());

clstmt.setString(3, bill.getId());

clstmt.registerOutParameter(4, Types.CHAR);

clstmt.registerOutParameter(5, Types.VARCHAR);

clstmt.execute();

out_opresult = clstmt.getString(4).trim();

out_operror = clstmt.getString(5).trim();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

* 存储过程编写时应注意：

1.输出参数不能作为查询条件；

eg：假设，out_id已赋值，

SELECT name

INTO out_name

FROM tb_test

WHERE id = out_id;

这样写PL/SQL中会正常通过，而在Java程序调用该存储过程时会报错，需要改成：