Oracle 11g PL/SQL未读通知函数无数据返回问题排查

我使用Oracle 11g数据库编写了PL/SQL函数NEW_NOTIFICATION，需求是从NOTIFICATIONS表中获取指定用户的未读通知——即USER_NOTIFICATIONS表中不存在对应USER_ID与NOTIFICATION_ID记录的通知。但添加以下NOT EXISTS筛选条件后，函数无法返回数据：

AND NOT EXISTS (
          SELECT 1 
          FROM USER_NOTIFICATIONS UN
          WHERE UN.USER_ID = user_id AND UN.NOTIFICATION_ID = N.ID
      )

移除该条件后函数可正常运行，以下是两个版本的函数代码：

带NOT EXISTS的异常函数 #

FUNCTION NEW_NOTIFICATION(
    user_id IN NUMBER,
    fecha_val IN VARCHAR2,
    start_position IN NUMBER,
    end_position IN NUMBER
)
RETURN CLOB
AS
    l_result1 CLOB;
    l_result2 CLOB := ''; 
    l_result3 CLOB;
    l_result_size NUMBER;
    l_count NUMBER;
    l_row_check NUMBER := start_position;
    l_row_number NUMBER := 0;
    l_json_record VARCHAR2(4000);

    CURSOR c_notifications_new IS
        SELECT 
            '{ "ID": "' || ID || '", "RECIPIENTS": "' || RECIPIENTS || '", "MESSAGE_TITLE": "' || MESSAGE_TITLE || '", "MESSAGE_BODY": "' || MESSAGE_BODY || '", "MULTIMEDIA_ID": "' || MULTIMEDIA_ID || '", "NOTIFICATION_DATE_FROM": "' || TO_CHAR(NOTIFICATION_DATE_FROM, 'DD/MM/YYYY HH24:MI:SS') || '", "NOTIFICATION_DATE_TO": "' || TO_CHAR(NOTIFICATION_DATE_TO, 'DD/MM/YYYY HH24:MI:SS') || '", "SEND_BY_EMAIL": "' || SEND_BY_EMAIL || '", "CREATED_AT": "' || TO_CHAR(CREATED_AT, 'DD/MM/YYYY HH24:MI:SS') || '}' AS json_record
        FROM NOTIFICATIONS N
        WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
          AND NOT EXISTS (
              SELECT 1 
              FROM USER_NOTIFICATIONS UN
              WHERE UN.USER_ID = user_id AND UN.NOTIFICATION_ID = N.ID
          )
          AND N.DELETED_AT IS NULL
        ORDER BY N.CREATED_AT DESC;

BEGIN
    SELECT COUNT(*) INTO l_count
    FROM NOTIFICATIONS N
    WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
      AND NOT EXISTS (
          SELECT 1 
          FROM USER_NOTIFICATIONS UN
          WHERE UN.USER_ID = user_id AND UN.NOTIFICATION_ID = N.ID
      )
      AND N.DELETED_AT IS NULL;

    FOR r in c_notifications_new LOOP
        IF l_row_check + 1 >= start_position AND l_row_check + 1 <= end_position THEN
            l_json_record := r.json_record;

            l_result_size := DBMS_LOB.GETLENGTH(l_result1 || l_result2 || l_json_record || '}');

            IF l_result_size > 10000 THEN
                EXIT;
            ELSE
                
                IF l_row_check != 0 THEN
                    l_result2 := l_result2 || ', ';
                END IF;
                l_result2 := l_result2 || l_json_record;
                l_row_check := l_row_check + 1;
                l_row_number := l_row_number + 1;
            END IF;
        END IF;
    END LOOP;
    
    l_result1 := '{ "count": ' || l_count ||  ', "rows": ' || l_row_number || ', "data": [';
    l_result3 := l_result1 || l_result2 || ']}';
    RETURN l_result3;
END;

（注：原代码中RETURN l_result存在变量名错误，已修正为l_result3）

移除NOT EXISTS后的正常函数 #

FUNCTION NEW_NOTIFICATION(
    user_id IN NUMBER,
    fecha_val IN VARCHAR2,
    start_position IN NUMBER,
    end_position IN NUMBER
)
RETURN CLOB
AS
    l_result1 CLOB;
    l_result2 CLOB := '';
    l_result3 CLOB;
    l_result_size NUMBER;
    l_count NUMBER;
    l_row_check NUMBER := start_position;
    l_row_number NUMBER := 0;
    l_json_record VARCHAR2(4000); 

    CURSOR c_notifications_new IS
        SELECT 
            '{ "ID": "' || ID || '", "RECIPIENTS": "' || RECIPIENTS || '", "MESSAGE_TITLE": "' || MESSAGE_TITLE || '", "MESSAGE_BODY": "' || MESSAGE_BODY || '", "MULTIMEDIA_ID": "' || MULTIMEDIA_ID || '", "NOTIFICATION_DATE_FROM": "' || TO_CHAR(NOTIFICATION_DATE_FROM, 'DD/MM/YYYY HH24:MI:SS') || '", "NOTIFICATION_DATE_TO": "' || TO_CHAR(NOTIFICATION_DATE_TO, 'DD/MM/YYYY HH24:MI:SS') || '", "SEND_BY_EMAIL": "' || SEND_BY_EMAIL || '", "CREATED_AT": "' || TO_CHAR(CREATED_AT, 'DD/MM/YYYY HH24:MI:SS') || '}' AS json_record
        FROM NOTIFICATIONS N
        WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
        
          AND N.DELETED_AT IS NULL
        ORDER BY N.CREATED_AT DESC;

BEGIN
    SELECT COUNT(*) INTO l_count
    FROM NOTIFICATIONS N
    WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
     
      AND N.DELETED_AT IS NULL;

    FOR r in c_notifications_new LOOP
        IF l_row_check + 1 >= start_position AND l_row_check + 1 <= end_position THEN
            l_json_record := r.json_record;

            l_result_size := DBMS_LOB.GETLENGTH(l_result1 || l_result2 || l_json_record || '}');

            IF l_result_size > 10000 THEN
                EXIT;
            ELSE
                
                IF l_row_check != 0 THEN
                    l_result2 := l_result2 || ', ';
                END IF;
                l_result2 := l_result2 || l_json_record;
                l_row_check := l_row_check + 1;
                l_row_number := l_row_number + 1;
            END IF;
        END IF;
    END LOOP;
    
    l_result1 := '{ "count": ' || l_count ||  ', "rows": ' || l_row_number || ', "data": [';
    l_result3 := l_result1 || l_result2 || ']}';
    RETURN l_result3;
END;

（注：原代码中RETURN l_result存在变量名错误，已修正为l_result3）

排查及修复方案 #

1. 变量名冲突问题
   函数参数名为user_id，NOT EXISTS子句中UN.USER_ID = user_id的写法会被Oracle优先解析为USER_NOTIFICATIONS表的列（若该表存在同名列），而非函数参数。
   修复：将参数名改为p_user_id，或明确指定参数所属函数，比如UN.USER_ID = NEW_NOTIFICATION.user_id。

2. 分页逻辑错误
   原代码中l_row_check初始值设为start_position，导致循环中判断l_row_check + 1 >= start_position时，第一行数据会被跳过（比如start_position=1时，初始l_row_check=1，l_row_check+1=2，第一行位置1不会被选中）。
   修复：将l_row_check NUMBER := start_position;改为l_row_check NUMBER := 0;，确保第一行循环时l_row_check+1=1，符合起始位置要求。

3. 数据类型与匹配验证

   • 检查USER_NOTIFICATIONS.USER_ID、USER_NOTIFICATIONS.NOTIFICATION_ID与NOTIFICATIONS.ID、函数参数user_id的数据类型是否完全一致，避免隐式转换导致匹配失败。
   • 直接执行核心查询语句（替换为实际参数值），验证是否存在符合条件的未读通知：

     SELECT *
     FROM NOTIFICATIONS N
     WHERE TO_DATE('2024/05/20', 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
       AND NOT EXISTS (
           SELECT 1 
           FROM USER_NOTIFICATIONS UN
           WHERE UN.USER_ID = 123 AND UN.NOTIFICATION_ID = N.ID
       )
       AND N.DELETED_AT IS NULL;
     

     如果该查询无结果，说明确实没有符合条件的未读通知；如果有结果，再排查函数分页或拼接逻辑的问题。

内容的提问来源于stack exchange，提问作者Julián Oviedo