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程序调用该存储过程时会报错,需要改成: