OracleストアドプロシージャがCURSORパラメータを返す場合、

  1. JDBC経由で登録

`CallableStatement.registerOutParameter(index、OracleTypes.CURSOR)`です。

  1. `callableStatement.getObject(index)`でそれを取得します.

コードスニペットを見る

…​.//getDBUSERCursor is a stored procedure
String getDBUSERCursorSql = “{call getDBUSERCursor(?,?)}”;
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, “mkyong”);
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
callableStatement.executeUpdate();
rs = (ResultSet) callableStatement.getObject(2);
while (rs.next()) {
String userid = rs.getString(“USER__ID”);
String userName = rs.getString(“USERNAME”);
}

===  JDBC CallableStatement CURSORの例

OUT **  CURSOR ** パラメータの完全なJDBCの `CallableStatement`の例を参照してください。

===  1.ストアドプロシージャ

1つのINパラメータと1つのOUT CURSORパラメータを持つOracleストアド・プロシージャ。

その後、JDBC経由で呼び出します。

CREATE OR REPLACE PROCEDURE getDBUSERCursor(
p

username IN DBUSER.USERNAME%TYPE,
c

dbuser OUT SYS__REFCURSOR)
IS
BEGIN

OPEN c__dbuser FOR
SELECT **  FROM DBUSER WHERE USERNAME LIKE p__username || '%';

END;/…​.

2. CallableStatement経由でストアドプロシージャを呼び出します.

JDBCの例では、上記のストアドプロシージャを呼び出し、返された

CURSOR



ResultSet

にキャストし、レコードを順番にループします。


File:JDBCCallableStatementCURSORExample.java

package com.mkyong.jdbc;

import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

public class JDBCCallableStatementCURSORExample {

    private static final String DB__DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DB__CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
    private static final String DB__USER = "user";
    private static final String DB__PASSWORD = "password";

    public static void main(String[]argv) {

        try {

            callOracleStoredProcCURSORParameter();

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

    }

    private static void callOracleStoredProcCURSORParameter()
            throws SQLException {

        Connection dbConnection = null;
        CallableStatement callableStatement = null;
        ResultSet rs = null;

        String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";

        try {
            dbConnection = getDBConnection();
            callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);

            callableStatement.setString(1, "mkyong");
            callableStatement.registerOutParameter(2, OracleTypes.CURSOR);

           //execute getDBUSERCursor store procedure
            callableStatement.executeUpdate();

           //get cursor and cast it to ResultSet
            rs = (ResultSet) callableStatement.getObject(2);

            while (rs.next()) {
                String userid = rs.getString("USER__ID");
                String userName = rs.getString("USERNAME");
                String createdBy = rs.getString("CREATED__BY");
                String createdDate = rs.getString("CREATED__DATE");

                System.out.println("UserName : " + userid);
                System.out.println("UserName : " + userName);
                System.out.println("CreatedBy : " + createdBy);
                System.out.println("CreatedDate : " + createdDate);
            }

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            if (rs != null) {
                rs.close();
            }

            if (callableStatement != null) {
                callableStatement.close();
            }

            if (dbConnection != null) {
                dbConnection.close();
            }

        }

    }

    private static Connection getDBConnection() {

        Connection dbConnection = null;

        try {

            Class.forName(DB__DRIVER);

        } catch (ClassNotFoundException e) {

            System.out.println(e.getMessage());

        }

        try {

            dbConnection = DriverManager.getConnection(
                DB__CONNECTION, DB__USER,DB__PASSWORD);
            return dbConnection;

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

        return dbConnection;

    }

}

完了しました。