写了一个函数测试一下:create or replace function getStudentCount return number as
v_cnt number;
begin
select count(*) into v_cnt from student;
return v_cnt;
end getStudentCount;
调用:package xdg;import java.sql.*;public class Test {
public static void main(String[] args) throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url="jdbc:oracle:thin:@169.254.105.177:1521:student11g";
//orcl为数据库的SID
String user="student";
String password="learn";
Connection conn= DriverManager.getConnection(url,user,password); CallableStatement cs=conn.prepareCall("?={call getStudentCount()}");
cs.executeUpdate();
// ResultSet rs=(ResultSet) cs.getObject(1);
// while (rs.next()){
// System.out.printf("%d,%s\n",rs.getInt("student_id"),rs.getString("first_name"));
// }// System.out.println(cs.getInt(1));
conn.close();
}
}
结果抛异常:
Exception in thread "main" java.sql.SQLException: 索引中丢失 IN 或 OUT 参数:: 1
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1737)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3376)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3462)
at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:3877)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1349)
at xdg.Test.main(Test.java:15)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)但我压根就没使用参数,何来参数索引不对?函数在oracle中测试ok
v_cnt number;
begin
select count(*) into v_cnt from student;
return v_cnt;
end getStudentCount;
调用:package xdg;import java.sql.*;public class Test {
public static void main(String[] args) throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url="jdbc:oracle:thin:@169.254.105.177:1521:student11g";
//orcl为数据库的SID
String user="student";
String password="learn";
Connection conn= DriverManager.getConnection(url,user,password); CallableStatement cs=conn.prepareCall("?={call getStudentCount()}");
cs.executeUpdate();
// ResultSet rs=(ResultSet) cs.getObject(1);
// while (rs.next()){
// System.out.printf("%d,%s\n",rs.getInt("student_id"),rs.getString("first_name"));
// }// System.out.println(cs.getInt(1));
conn.close();
}
}
结果抛异常:
Exception in thread "main" java.sql.SQLException: 索引中丢失 IN 或 OUT 参数:: 1
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1737)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3376)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3462)
at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:3877)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1349)
at xdg.Test.main(Test.java:15)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)但我压根就没使用参数,何来参数索引不对?函数在oracle中测试ok
不是有
OUT 参数
么?
CallableStatement cs=conn.prepareCall("?={call getStudentCount()}");
这里好像有问题啊,是这样调用的吗?