oracle 在 Java 程序中调用 PL/SQL 包代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26511314/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling PL/SQL package code in a Java Program
提问by Chiseled
I am trying to call a procedure defined with a PL/SQLpackage in a Javaprogram.
我试图在程序中调用一个用PL/SQL包定义的过程Java。
I am aware one can call stored procedures using connection.prepareCallin Jdbc. But there is very little information out there on how to call a procedure within a package.
我知道可以connection.prepareCall在 Jdbc 中使用调用存储过程。但是关于如何在包中调用过程的信息很少。
I am at a stage in development where i am still considering what db framework to use. Just wondering what are the pros and cons of using JDBC for PLSQL ? For this usecase are there better alternatives to JDBC ?
我正处于开发阶段，我仍在考虑使用什么数据库框架。只是想知道将 JDBC 用于 PLSQL 的优缺点是什么？对于这个用例，是否有更好的 JDBC 替代品？
回答by Lokesh
Follow the simple steps below:
请按照以下简单步骤操作：
public static final String SOME_NAME = "{call schema_name.org_name_pkg.return_something(?,?)}"; // Change the schema name,packagename,and procedure name.
// Simple JDBC Connection Pooling
// Here I am passing param companyId which is IN param to stored procedure which will return me some value.
Connection conn = null;
CallableStatement stmt = null;
ResultSet rset = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "password");
stmt = conn.prepareCall(SOME_NAME);//We have declared this at the very top
stmt.setString(1, companyid);//Passing CompanyID here
stmt.registerOutParameter(2, OracleTypes.CURSOR);//Refcursor selects the row based upon query results provided in Package.
stmt.execute();
rset = (ResultSet) stmt.getObject(2);
while (rset.next()) {
String orgId=rset.getString("RPT_ORG_ID");
// When using refcursor easy to get the value just by using Column name
String orgName=rset.getString("RPT_ORG_NAME");
// Some Logic based what do you want to do with the data returned back from query
} catch (Exception e) {
LOGGER.error("Error extracting ", e);
} finally {
DBUtils.cleanUp(conn, stmt, rset);
}
// Clean and close you connection
