VastbaseG100

调用存储过程

调用存储过程，可以使用prepareCall方法，问号(?)是prepareCall的占位符，对于存储过程的输出，需要使用CallableStatement的registerOutparameter方法先注册，执行存储过程使用execute()方法。

下面的例子展示了如何调用存储过程：

CallableStatement upperProc = conn.prepareCall("{? = call upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
String upperCased = upperProc.getString(1);
upperProc.close();

获取结果集

函数可以通过两种方式返回结果集，根据函数被调用的方式决定结果集的返回方式。

• 方式一：

  Statement stmt = conn.createStatement();
  stmt.execute("CREATE OR REPLACE FUNCTION setoffunc() RETURNS SETOF int AS "
  + "' SELECT 1 UNION SELECT 2;' LANGUAGE sql");
  ResultSet rs = stmt.executeQuery("SELECT * FROM setoffunc()");
  while (rs.next())
  {
  // do something
  }
  rs.close();
  stmt.close();
  

• 方式二

  // Setup function to call.
  Statement stmt = conn.createStatement();
  stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS "
  + " DECLARE "
  + "    mycurs refcursor; "
  + " BEGIN "
  + "    OPEN mycurs FOR SELECT 1 UNION SELECT 2; "
  + "    RETURN mycurs; "
  + " END; ");
  stmt.close();
  
  // We must be inside a transaction for cursors to work.
  conn.setAutoCommit(false);
  
  // Procedure call.
  CallableStatement proc = conn.prepareCall("{? = call refcursorfunc() }");
  proc.registerOutParameter(1, Types.OTHER);
  proc.execute();
  ResultSet results = (ResultSet) proc.getObject(1);
  while (results.next())
  {
  // do something with the results.
  }
  results.close();
  proc.close();