Oracle Apex多选择LOV历史表ID转显示名称查询方法咨询

方法一：使用内联子查询+LISTAGG（无需创建函数） #

直接在查询中拆分ID字符串、关联字典表并拼接名称，无需额外创建数据库对象：

SELECT 
  COLUMN_NAME,
  CASE WHEN COLUMN_NAME = 'BONUS_TYPE_ID' THEN
    (SELECT LISTAGG(b.bonus_type_name, ':') WITHIN GROUP (ORDER BY b.bonus_id)
     FROM BONUS_DATA b
     WHERE b.bonus_id IN (
       SELECT REGEXP_SUBSTR(old, '[^:]+', 1, LEVEL)
       FROM DUAL
       CONNECT BY REGEXP_SUBSTR(old, '[^:]+', 1, LEVEL) IS NOT NULL
     ))
  ELSE old END AS old_value,
  CASE WHEN COLUMN_NAME = 'BONUS_TYPE_ID' THEN
    (SELECT LISTAGG(b.bonus_type_name, ':') WITHIN GROUP (ORDER BY b.bonus_id)
     FROM BONUS_DATA b
     WHERE b.bonus_id IN (
       SELECT REGEXP_SUBSTR(new, '[^:]+', 1, LEVEL)
       FROM DUAL
       CONNECT BY REGEXP_SUBSTR(new, '[^:]+', 1, LEVEL) IS NOT NULL
     ))
  ELSE new END AS new_value
FROM BONUS_HISTORY;

逻辑说明： #

• REGEXP_SUBSTR(old, '[^:]+', 1, LEVEL)：将冒号分隔的ID字符串拆分为单个ID值
• CONNECT BY 循环拆分所有ID，直到没有匹配项
• LISTAGG 将匹配到的奖金类型名称按ID顺序拼接为冒号分隔的字符串

方法二：创建自定义函数（复用性更强） #

如果需要在多个查询中复用ID转名称的逻辑，可以创建一个数据库函数：

CREATE OR REPLACE FUNCTION GET_BONUS_NAMES(p_id_str VARCHAR2) RETURN VARCHAR2 IS
  v_names VARCHAR2(4000);
BEGIN
  IF p_id_str IS NULL THEN
    RETURN NULL;
  END IF;

  SELECT LISTAGG(b.bonus_type_name, ':') WITHIN GROUP (ORDER BY b.bonus_id)
  INTO v_names
  FROM BONUS_DATA b
  WHERE b.bonus_id IN (
    SELECT REGEXP_SUBSTR(p_id_str, '[^:]+', 1, LEVEL)
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(p_id_str, '[^:]+', 1, LEVEL) IS NOT NULL
  );

  RETURN v_names;
END;
/

使用函数的查询语句： #

SELECT 
  COLUMN_NAME,
  CASE WHEN COLUMN_NAME = 'BONUS_TYPE_ID' THEN GET_BONUS_NAMES(old) ELSE old END AS old_value,
  CASE WHEN COLUMN_NAME = 'BONUS_TYPE_ID' THEN GET_BONUS_NAMES(new) ELSE new END AS new_value
FROM BONUS_HISTORY;

注意事项： #

• 确保BONUS_DATA表中的BONUS_ID与历史表中存储的ID格式一致（无多余空格）
• 如果历史表中存在无效ID（在BONUS_DATA中无匹配），该逻辑会自动忽略这些ID，仅返回有匹配的名称

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