我有一个PL / SQL函数,该函数返回ROWTYPE。我需要从Java调用此函数并检索数据(无论是数组,映射,结构,类,无论如何,我真的不在乎,我只需要它,最好不必拧PL / SQL代码,例如更改函数的返回类型)。我研究了多个“解决方案”和论坛,但没有找到答案。
我已经尝试将out参数注册为struct和class,但都没有用。
这是我的PL / SQL函数:

  FUNCTION DAR_CLIENTE(cedula VARCHAR2) RETURN CLIENTE%ROWTYPE AS
  RET CLIENTE%ROWTYPE;
  BEGIN
    -- TAREA: Se necesita implantación para FUNCTION P_CLIENTE.DAR_CLIENTE
    SELECT * INTO RET FROM CLIENTE WHERE Persona_cedula=cedula;
    RETURN RET;
  END DAR_CLIENTE;


这是我的Java代码:

public static void main(String args[]) throws SQLException {
    Properties info = new Properties();
    info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
    info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
    info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");

    OracleDataSource ods = new OracleDataSource();
    ods.setURL(DB_URL);
    ods.setConnectionProperties(info);

    // With AutoCloseable, the connection is closed automatically.
    try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
        // Get the JDBC driver name and version
        DatabaseMetaData dbmd = connection.getMetaData();
        System.out.println("Driver Name: " + dbmd.getDriverName());
        System.out.println("Driver Version: " + dbmd.getDriverVersion());
        // Print some connection properties
        System.out.println("Default Row Prefetch Value is: " + connection.getDefaultRowPrefetch());
        System.out.println("Database Username is: " + connection.getUserName());
        System.out.println();
        System.out.println(connection.getSchema());
        // Perform a database operation

        Map<String, Class<?>> myMap = new HashMap<String, Class<?>>();
        myMap.put("P09551_1_5.CLIENTE", Cliente.class);

        connection.setTypeMap(myMap);
        CallableStatement storedProc = connection
                .prepareCall("{? = call P09551_1_5.p_cliente.dar_cliente('1144102435')}");

        storedProc.registerOutParameter(1, oracle.jdbc.OracleTypes.JAVA_STRUCT);
        storedProc.execute();

    }
}


我正在使用ojdbc8.jar。

该信息在程序开始时打印到控制台:

Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0

最佳答案

我已通过将返回类型更改为SYS_REFCURSOR来解决了该问题,如下所示:

FUNCTION DAR_CLIENTE(cedula VARCHAR2) RETURN SYS_REFCURSOR AS
  RET SYS_REFCURSOR;
  BEGIN
    -- TAREA: Se necesita implantación para FUNCTION P_CLIENTE.DAR_CLIENTE
    OPEN RET FOR
    SELECT * FROM CLIENTE WHERE Persona_cedula=cedula;
    RETURN RET;
  END DAR_CLIENTE;


Java代码如下:

public static void main(String args[]) throws SQLException {
    Properties info = new Properties();
    info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
    info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
    info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");

    OracleDataSource ods = new OracleDataSource();
    ods.setURL(DB_URL);
    ods.setConnectionProperties(info);

    // With AutoCloseable, the connection is closed automatically.
    try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
        // Get the JDBC driver name and version
        DatabaseMetaData dbmd = connection.getMetaData();
        System.out.println("Driver Name: " + dbmd.getDriverName());
        System.out.println("Driver Version: " + dbmd.getDriverVersion());
        // Print some connection properties
        System.out.println("Default Row Prefetch Value is: " + connection.getDefaultRowPrefetch());
        System.out.println("Database Username is: " + connection.getUserName());
        System.out.println("Schema: "+connection.getSchema());
        System.out.println();
        // Perform a database operation

        Map<String, Class<?>> myMap = new HashMap<String, Class<?>>();
        myMap.put("P09551_1_5.CLIENTE", Cliente.class);

        connection.setTypeMap(myMap);
        CallableStatement storedProc = connection
                .prepareCall("{? = call P09551_1_5.p_cliente.dar_cliente('1144102435')}");

        storedProc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
        storedProc.execute();
        ResultSet resultSet = (ResultSet) storedProc.getObject(1);
        ResultSetMetaData meta = resultSet.getMetaData();
        int columnCount = meta.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                System.out.println(meta.getColumnLabel(i)+":"+resultSet.getObject(i).toString());
            }
            //System.out.println(resultSet.getString(1));

        }

    }
}


在控制台上获得此响应(我这样做是为了使所有字段都打印有其列标签,尽管此特定表只有一列):

Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
Default Row Prefetch Value is: 20
Database Username is: P09551_1_5
Schema: P09551_1_5

PERSONA_CEDULA:1144102435


这是解决方案的来源:
https://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/

09-10 07:53
查看更多