Oracle中使用INSERT...SELECT插入后获取记录ID的问题求助

针对你的问题，核心错误在于直接在普通SQL中使用RETURNING ... INTO（这是PL/SQL语法），以及Java代码中错误调用executeQuery()执行INSERT语句。以下是两种可行的解决方法：

方案一：插入单条记录时获取ID #

将INSERT语句包装为PL/SQL块，通过CallableStatement注册输出参数来获取单个ID：

String plsql = "BEGIN " +
               "INSERT INTO amalgam_care (id, name, activestatus, incorpcountry, sourcetable, sourcesystem) " +
               "SELECT fund_id, fund_name, active, domicile, 'EMIS_FUNDS', 'EMIS' FROM EMIS_FUNDS " +
               "WHERE status IN (2, 3, 4) AND active = 1 ORDER BY fund_id " +
               "RETURNING ID INTO ?; " +
               "END;";

try (CallableStatement cs = conn.prepareCall(plsql)) {
    // 根据ID列实际类型调整，比如Types.INTEGER如果是数字类型
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.execute();
    String insertedId = cs.getString(1);
    System.out.println("插入的ID：" + insertedId);
} catch (SQLException e) {
    e.printStackTrace();
}

方案二：插入多条记录时获取所有ID #

如果SELECT返回多条记录，需要用BULK COLLECT批量收集ID，再通过游标返回结果：

String plsql = "DECLARE " +
               "    TYPE id_table_type IS TABLE OF amalgam_care.id%TYPE; " +
               "    v_ids id_table_type; " +
               "BEGIN " +
               "    INSERT INTO amalgam_care (id, name, activestatus, incorpcountry, sourcetable, sourcesystem) " +
               "    SELECT fund_id, fund_name, active, domicile, 'EMIS_FUNDS', 'EMIS' FROM EMIS_FUNDS " +
               "    WHERE status IN (2, 3, 4) AND active = 1 ORDER BY fund_id " +
               "    RETURNING ID BULK COLLECT INTO v_ids; " +
               "    OPEN ? FOR SELECT * FROM TABLE(v_ids); " +
               "END;";

try (CallableStatement cs = conn.prepareCall(plsql)) {
    // 注册游标类型参数，Oracle驱动可使用OracleTypes.CURSOR
    cs.registerOutParameter(1, OracleTypes.CURSOR);
    cs.execute();
    ResultSet rs = (ResultSet) cs.getObject(1);
    // 遍历输出所有插入的ID
    while (rs.next()) {
        System.out.println("插入的ID：" + rs.getString(1));
    }
} catch (SQLException e) {
    e.printStackTrace();
}

关键说明 #

1. 必须将RETURNING逻辑放在PL/SQL块中执行，普通SQL不支持INTO子句。
2. 根据amalgam_care.id的实际数据类型调整registerOutParameter的参数类型（如数字类型用Types.INTEGER）。
3. 若无需保证插入顺序，可去掉ORDER BY子句，它不会影响最终表的数据存储顺序。

内容的提问来源于stack exchange，提问作者Sithija Piyuman Thewa Hettige