通过java执行pl sql块的问题
问题描述:
我已经在java中编写了下面的代码。通过java执行pl sql块的问题
package demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class Sample {
private Connection c = null;
public Sample()
{
try {
c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "jboss");
String plsql = ""+
"declare"+
"x number;"+
"begin "+
"select HEADCOUNT FROM PROJECT where deptid=1;"+
"end;";
CallableStatement cs = c.prepareCall(plsql);
//cs.registerOutParameter(0, Types.INTEGER);
cs.execute();
System.out.println("Result is "+cs.getInt(1));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args)
{
Sample z = new Sample();
}
}
我只想探索一下我可以通过java来执行plsql块的机制。上面的 只是简单的查询。 上面的Java代码执行,我得到错误的
java.sql.SQLException: Invalid SQL type: sqlKind = UNINITIALIZED
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:75)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1043)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
at demo.Sample.<init>(Sample.java:26)
at demo.Sample.main(Sample.java:36)
让我知道如果有执行这样的PL/SQL块之前所需的任何以前的设置。
答
1)第一个问题是在其他答案中提到的declarex...
。
2)Secound问题是select HEADCOUNT FROM PROJECT where deptid=1;
在plsql中,此查询无效。应该select HEADCOUNT into x ...
答
几个错误:
- 的字符串将级联,
declarex
,你需要它们之间的空间。 - 您未从表
INTO
中选择PL/SQL变量的值。 - PL/SQL块没有将该值放入绑定变量以将其返回给Java(如果直接使用绑定变量,则不需要在PL/SQL中声明单独的变量)。
- 您已注释掉注册输出参数的行,并且参数是1索引的(不是0索引的)。
事情是这样的:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class Sample {
public static void main(final String[] args)
{
try {
Class.forName("oracle.jdbc.OracleDriver"); // If you are using the Oracle driver.
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE",
"username",
"password"
);
CallableStatement cs = con.prepareCall(
"BEGIN SELECT HEADCOUNT INTO :1 FROM PROJECT WHERE deptid=1; END;"
);
cs.registerOutParameter(1, Types.NUMERIC);
cs.execute();
System.out.println(cs.getInt(1));
}
catch (ClassNotFoundException | SQLException ex)
{
System.err.println(ex.getMessage());
}
}
}
答
提供字符串中的适当空间,以形成SQL
查询。这是改变了必要空间后的样子!
String plsql = "declare x number; "+
"begin "+
"select HEADCOUNT FROM PROJECT where deptid=1; "+
"end;";
你需要用正确的空格,否则要通过自身希望通过您的Java
/JDBC
代码出错的SQL
纠正你的代码。
你可以改变你PL/SQL
代码如下:
CallableStatement cs = con.prepareCall(
"BEGIN SELECT HEADCOUNT INTO :1 FROM PROJECT WHERE deptid=1; END;"
);
希望这回答你的问题很好!
你认为怎么样' “申报” + “x'给你? –
又见https://*.com/questions/36447240/invalid-sql-type-sqlkind-uninitialized-error-is-shown –
为什么要用mysql标记,如果你连接到Oracle? –