VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

调用存储过程

调用存储过程,可以使用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();