PL/SQL脚本输出无法添加CIDGEN与NATION表头问题求助

问题根源 #

你把表头打印逻辑嵌套在了内层查询结果循环里，只有当第一个p_nations元素对应的查询返回数据时，才会触发表头打印。如果第一个nation的查询无结果，后续nation的查询即使有数据，表头会在该nation的数据前打印；若所有nation的查询都无数据，表头自然不会显示。另外，若你期望表头固定在所有数据最顶部，当前逻辑也可能不符合预期。

修复方案 #

方案1：提前检查数据并打印表头 #

先判断是否存在符合条件的数据，若存在则先打印表头，再遍历输出所有数据，确保表头在最顶部：

DECLARE
    v_nation VARCHAR2(2000);
    err_msg  VARCHAR2(300);
    v_has_data BOOLEAN := FALSE;
BEGIN
    -- 检查是否有符合条件的数据
    SELECT CASE WHEN EXISTS (
        SELECT 1
        FROM COMPANY B
        JOIN NATION_LOOKUP C ON B.NATION = C.CODE
        WHERE B.CIDGEN IN (
            SELECT CIDGEN
            FROM RAN.OA_REQUEST_RESPONSE_STATUS
            WHERE RESPONSE_STATUS = 'FailedRequest'
            AND RESPONSE_UPDATE_STAMP >= TO_DATE(p_lastrundate, 'DD-MON-YYYY HH24:MI:SS')
        )
        AND B.NATION MEMBER OF p_nations
    ) THEN TRUE ELSE FALSE INTO v_has_data FROM DUAL;

    -- 有数据则先打印表头
    IF v_has_data THEN
        DBMS_OUTPUT.PUT_LINE('CIDGEN||NATION');
    END IF;

    -- 遍历所有nation输出数据
    FOR ID IN 1..p_nations.count LOOP
        v_nation := p_nations(ID);

        FOR rec IN (
            SELECT B.CIDGEN, B.NATION
            FROM COMPANY B
            JOIN NATION_LOOKUP C ON B.NATION = C.CODE
            WHERE B.CIDGEN IN (
                SELECT CIDGEN
                FROM RAN.OA_REQUEST_RESPONSE_STATUS
                WHERE RESPONSE_STATUS = 'FailedRequest'
                AND RESPONSE_UPDATE_STAMP >= TO_DATE(p_lastrundate, 'DD-MON-YYYY HH24:MI:SS')
            )
            AND B.NATION = v_nation
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(rec.CIDGEN || '||' || rec.NATION);
        END LOOP;
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        err_msg := SUBSTR(SQLERRM, 1, 200);
        DBMS_OUTPUT.PUT_LINE('Exception caught: ' || err_msg);
END;
/

方案2：保留原有循环逻辑，确保表头仅打印一次 #

如果不想提前查询数据，可保留内层循环的表头判断，确保只要有第一条数据输出就打印表头，后续不再重复：

DECLARE
    v_nation VARCHAR2(2000);
    err_msg  VARCHAR2(300);
    v_header_printed BOOLEAN := FALSE;
BEGIN
    FOR ID IN 1..p_nations.count LOOP
        v_nation := p_nations(ID);

        FOR rec IN (
            SELECT B.CIDGEN, B.NATION
            FROM COMPANY B
            JOIN NATION_LOOKUP C ON B.NATION = C.CODE
            WHERE B.CIDGEN IN (
                SELECT CIDGEN
                FROM RAN.OA_REQUEST_RESPONSE_STATUS
                WHERE RESPONSE_STATUS = 'FailedRequest'
                AND RESPONSE_UPDATE_STAMP >= TO_DATE(p_lastrundate, 'DD-MON-YYYY HH24:MI:SS')
            )
            AND B.NATION = v_nation
        ) LOOP
            IF NOT v_header_printed THEN
                DBMS_OUTPUT.PUT_LINE('CIDGEN||NATION');
                v_header_printed := TRUE;
            END IF;
            DBMS_OUTPUT.PUT_LINE(rec.CIDGEN || '||' || rec.NATION);
        END LOOP;
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        err_msg := SUBSTR(SQLERRM, 1, 200);
        DBMS_OUTPUT.PUT_LINE('Exception caught: ' || err_msg);
END;
/

额外检查项 #

• 执行脚本前务必开启DBMS_OUTPUT：在SQL*Plus或PL/SQL Developer中执行SET SERVEROUTPUT ON;
• 若p_nations是空集合，脚本不会输出任何内容
• 确认p_lastrundate参数格式正确，避免查询无结果

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