从 PL/SQL 函数返回大字符串

如果你想在 Oracle 中创建 JSON，那么从 Oracle 12 开始，使用 JSON 函数，不要尝试手动制作 JSON 字符串（对于初学者来说，当字符串太长时它会失败，但你也没有处理字符串中可能出现的任何转义字符）。

像这样的东西：

CREATE FUNCTION TODAS_NOTIFICACIONES_ACTIVAS(
  fecha_val IN VARCHAR2,
  start_position IN NUMBER,
  end_position IN NUMBER
) RETURN CLOB
AS
    l_json CLOB;
BEGIN
    SELECT JSON_OBJECT(
             KEY 'count' VALUE COUNT(*),
             KEY 'data' VALUE JSON_ARRAYAGG(
               JSON_OBJECT(
                 KEY 'ID' VALUE id,
                 KEY 'RECIPIENTS' VALUE recipients,
                 KEY 'AGE_FROM' VALUE age_from,
                 KEY 'AGE_TO' VALUE age_to,
                 KEY 'DEPARTMENT' VALUE department_id,
                 KEY 'LOCALITY' VALUE locality_id,
                 KEY 'MESSAGE_TITLE' VALUE message_title,
                 KEY 'MESSAGE_BODY' VALUE message_body,
                 KEY 'MULTIMEDIA_ID' VALUE multimedia_id,
                 KEY 'NOTIFICATION_DATE_FROM' VALUE TO_CHAR(notification_date_from, 'DD/MM/YYYY HH24:MI:SS'),
                 KEY 'NOTIFICATION_DATE_TO' VALUE TO_CHAR(notification_date_to, 'DD/MM/YYYY HH24:MI:SS'),
                 KEY 'SEND_BY_EMAIL' VALUE send_by_email,
                 KEY 'CREATED_AT' VALUE TO_CHAR(created_at, 'DD/MM/YYYY HH24:MI:SS'),
                 KEY 'DELETED_AT' VALUE TO_CHAR(deleted_at, 'DD/MM/YYYY HH24:MI:SS')
                 RETURNING CLOB
               )
               RETURNING CLOB
             )
             RETURNING CLOB
           )
    INTO   l_json
    FROM   NOTIFICATIONS
    WHERE  TO_DATE(fecha_val, 'DD/MM/YYYY') >= NOTIFICATION_DATE_FROM
    AND    TO_DATE(fecha_val, 'DD/MM/YYYY') <= NOTIFICATION_DATE_TO;

    RETURN l_json;
END;
/

（如果需要，请重新应用分页。

其中，对于示例数据：

CREATE TABLE notifications (
  id VARCHAR2(20),
  recipients VARCHAR2(20),
  age_from NUMBER,
  age_to NUMBER,
  department_id VARCHAR2(20),
  locality_id VARCHAR2(20),
  message_title VARCHAR2(20),
  message_body VARCHAR2(4000),
  multimedia_id VARCHAR2(20),
  notification_date_from DATE,
  notification_date_to DATE,
  send_by_email VARCHAR2(20),
  created_at DATE,
  deleted_at DATE
);

INSERT INTO notifications (
  id,
  recipients,
  age_from,
  age_to,
  department_id,
  locality_id,
  message_title,
  message_body,
  multimedia_id,
  notification_date_from,
  notification_date_to,
  send_by_email,
  created_at,
  deleted_at
) VALUES (
  'A',
  'B',
  100,
  200,
  'C',
  'D',
  'Title',
  LPAD('E', 4000, 'E'),
  'F',
  TRUNC(SYSDATE),
  TRUNC(SYSDATE) + 1,
  'G',
  TRUNC(SYSDATE),
  NULL
)

然后：

SELECT TODAS_NOTIFICACIONES_ACTIVAS(TO_CHAR(SYSDATE, 'DD/MM/YYYY'), 1, 1) FROM DUAL;

输出：

小提琴

这就是我修复它的方式。我有 oracle 版本 11，所以我无法实现JSON_OBJECT

FUNCTION TODAS_NOTIFICACIONES_ACTIVAS(fecha_val IN VARCHAR2, start_position IN NUMBER, end_position IN NUMBER)
RETURN CLOB
AS
    l_result1 CLOB;
    l_result2 CLOB := '';  -- Inicializa l_result2 para almacenar registros individuales
    l_result3 CLOB;
    l_result_size NUMBER;
    l_count NUMBER;
    l_row_check NUMBER := 0;
    l_json_record VARCHAR2(4000); -- Para almacenar el registro individual como JSON

    CURSOR c_notifications IS
        SELECT 
            '{ "ID": "' || ID || '", "RECIPIENTS": "' || RECIPIENTS || '", "AGE_FROM": "' || AGE_FROM || '", "AGE_TO": "' || AGE_TO || '", "DEPARTMENT": "' || DEPARTMENT_ID || '", "LOCALITY": "' || LOCALITY_ID || '", "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') || '", "DELETED_AT": "' || TO_CHAR(DELETED_AT, 'DD/MM/YYYY HH24:MI:SS') || '"}' as json_record
        FROM NOTIFICATIONS
        WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') >= NOTIFICATION_DATE_FROM
          AND TO_DATE(fecha_val, 'DD/MM/YYYY') <= NOTIFICATION_DATE_TO
        ORDER BY CREATED_AT DESC;

BEGIN
    SELECT COUNT(*) INTO l_count
    FROM NOTIFICATIONS
    WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') >= NOTIFICATION_DATE_FROM
      AND TO_DATE(fecha_val, 'DD/MM/YYYY') <= NOTIFICATION_DATE_TO;

    FOR r in c_notifications 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
                -- Agregar coma si no es el primer registro
                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;
            END IF;
        END IF;
    END LOOP;
    
    l_result1 := '{ "count": ' || TO_CHAR(l_count) ||  ',"rows": ' || TO_CHAR(l_row_check) || ', "data": [';  -- Cambiado para que la data esté entre corchetes

    l_result3 := l_result1 || l_result2 || ']}';

    RETURN l_result3;
END;