PL/SQL存储过程遍历查询结果并返回可Java调用的用户集合方案咨询

Hey there! Let's tackle this problem step by step. First off, I want to point out that you don't necessarily need to manually loop and build a collection unless you have specific business logic to run on each user. Oracle's REF CURSOR is the most straightforward and efficient way to return this kind of data to Java's CallableStatement. But I'll cover both approaches—one for the simple case, and another if you really need that loop-based collection building.

方案1：使用REF CURSOR直接返回结果（推荐） #

This is the go-to approach because it skips the unnecessary step of building an in-memory collection. You can directly return the query result as a cursor, which Java can handle with a familiar ResultSet. Even if you need to process each user in the loop (like logging or minor transformations), you can do that before returning the cursor (though for efficiency, it's better to handle those transformations in the query itself if possible).

PL/SQL存储过程代码 #

CREATE OR REPLACE PROCEDURE get_all_users(p_users OUT SYS_REFCURSOR)
IS
BEGIN
  -- 如果你需要先循环处理每个用户，在这里加逻辑
  -- FOR foundU IN (SELECT id, uName FROM USERS) LOOP
  --   DBMS_OUTPUT.PUT_LINE('Processing user: ' || foundU.uName);
  --   -- 这里可以加任何自定义处理，比如数据校验、转换
  -- END LOOP;

  -- 直接返回查询结果的游标
  OPEN p_users FOR
    SELECT id, uName FROM USERS;
END get_all_users;
/

Java调用代码 #

Java's CallableStatement works seamlessly with REF CURSORS—here's how you'd handle it:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import oracle.jdbc.OracleTypes; // 如果你用的是Oracle驱动

// 假设conn是已经建立好的数据库连接
CallableStatement cs = conn.prepareCall("{call get_all_users(?)}");
// 注册OUT参数：ojdbc6+可以用Types.REF_CURSOR，旧版本用OracleTypes.CURSOR
cs.registerOutParameter(1, Types.REF_CURSOR);
cs.execute();

// 获取游标并遍历结果
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next()) {
    Integer userId = rs.getInt("id");
    String userName = rs.getString("uName");
    // 将数据添加到Java的List或其他集合中
}

// 别忘了关闭资源
rs.close();
cs.close();

方案2：手动循环收集到自定义集合返回 #

If you absolutely need to build a collection by looping through each user (for complex business logic that can't be done in a query), you'll need to define custom PL/SQL types first, then populate the collection in your loop.

第一步：定义自定义类型（在包中） #

We'll create a record type for a single user, then a collection type of that record. Defining these in a package makes them accessible to both the stored procedure and Java.

CREATE OR REPLACE PACKAGE user_types AS
  -- 单个用户的记录类型
  TYPE user_rec IS RECORD(
    id NUMBER,
    uName VARCHAR2(100) -- 匹配USERS表中uName的字段类型和长度
  );
  -- 用户集合类型
  TYPE user_list IS TABLE OF user_rec;
END user_types;
/

第二步：编写存储过程 #

Now we'll initialize the collection, loop through the users, process each one, and add it to the collection.

CREATE OR REPLACE PROCEDURE get_all_users(p_users OUT user_types.user_list)
IS
  v_current_user user_types.user_rec;
BEGIN
  -- 初始化集合（必须先做这个，否则无法添加元素）
  p_users := user_types.user_list();

  FOR foundU IN (SELECT id, uName FROM USERS) LOOP
    -- 这里添加你的自定义处理逻辑
    v_current_user.id := foundU.id;
    v_current_user.uName := UPPER(foundU.uName); -- 示例：将用户名转为大写

    -- 扩展集合容量并添加当前用户
    p_users.EXTEND;
    p_users(p_users.LAST) := v_current_user;
  END LOOP;
END get_all_users;
/

Java调用代码 #

Handling custom PL/SQL collections in Java requires using Oracle's ARRAY and STRUCT classes. Here's how to map the collection to Java objects:

import java.sql.CallableStatement;
import java.sql.Connection;
import oracle.sql.ARRAY;
import oracle.sql.STRUCT;

// 假设conn是已建立的连接
CallableStatement cs = conn.prepareCall("{call get_all_users(?)}");
// 注册OUT参数：指定集合类型的完整名称（包名.类型名）
cs.registerOutParameter(1, OracleTypes.ARRAY, "USER_TYPES.USER_LIST");
cs.execute();

// 获取集合并转换为Java对象
ARRAY userArray = (ARRAY) cs.getObject(1);
Object[] userRecords = (Object[]) userArray.getArray();

for (Object recordObj : userRecords) {
    STRUCT userStruct = (STRUCT) recordObj;
    Object[] attributes = userStruct.getAttributes();
    
    Integer userId = (Integer) attributes[0];
    String userName = (String) attributes[1];
    // 添加到Java集合中
}

cs.close();

最后提醒 #

• 优先用方案1：It's simpler, more efficient, and Java handles it like a regular query result. Only use方案2 if you have unavoidable per-user business logic.
• 确保Java驱动版本匹配：Older ojdbc versions might require OracleTypes.CURSOR instead of Types.REF_CURSOR.
• 自定义类型要在包中：This ensures the type is visible to both the stored procedure and your Java code.

内容的提问来源于stack exchange，提问作者LifeequalsPain