我有一个plsql函数的问题，它没有返回数据中的信息。该函数必须从NOTIFICATIONS表中获取所有通知，但在USER_NOTIFICATIONS表中没有任何插入。
USER_NOTIFICATIONS表中的每一行都有一个user_id，表示“阅读”了具有特定NOTIFICATION_ID的通知的用户。我希望它能为我带来用户尚未阅读的通知。
这是一个函数

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_result

字符串
这些功能

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_result

型
看起来问题就在这里：

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

型
换句话说，此条件只负责从NOTIFICATIONS表中选择在USER_NOTIFICATIONS表中与特定用户（user_id）不匹配的行。这意味着通知尚未被该用户读取，因为USER_NOTIFICATIONS中没有与USER_ID和NOTIFICATION_ID的组合匹配的条目。
使用Oracle DB Version 11