如何将SQL结果存入变量，拼接FLAG并检测指定模式返回状态值？

我来帮你一步步搞定这个需求，咱们拆解成几个环节来实现：

先理清楚基础数据和目标 #

首先看咱们的基础数据和原始查询：

原始查询语句 #

INSERT INTO temp 
SELECT esd, 'E' FROM test_data_sovlp WHERE esd IS NOT NULL 
UNION ALL 
SELECT td, CASE is_db WHEN 0 THEN 'S' WHEN 1 THEN 'H' END AS FLAG FROM test_data_sovlp WHERE td IS NOT NULL

源数据表test_data_sovlp #

原始查询返回的结果集 #

咱们的目标是：

1. 把这些FLAG按DT的升序拼接成字符串q_result（示例里就是EHEEESEHH）
2. 检查这个字符串里是否存在HH、EE、HS、SE任意一组连续双字符序列，存在则返回0，不存在返回1

第一步：生成拼接后的q_result #

在Oracle里，用LISTAGG函数就能直接完成按序拼接的工作，不用依赖临时表也能搞定：

SELECT LISTAGG(FLAG, '') WITHIN GROUP (ORDER BY DT) AS q_result
FROM (
    -- 这里就是你原来的查询逻辑，先把所有DT和对应的FLAG查出来
    SELECT esd AS DT, 'E' AS FLAG FROM test_data_sovlp WHERE esd IS NOT NULL 
    UNION ALL 
    SELECT td AS DT, CASE is_db WHEN 0 THEN 'S' WHEN 1 THEN 'H' END AS FLAG FROM test_data_sovlp WHERE td IS NOT NULL
) t;

这个语句会直接返回按DT排序拼接好的FLAG字符串，完全符合咱们的需求。

第二步：检测目标序列 #

接下来要检查这个字符串里有没有指定的连续双字符。Oracle的REGEXP_LIKE函数很适合做这个正则匹配，咱们用(HH|EE|HS|SE)作为匹配模式，只要字符串里有任意一个匹配上，就说明要返回0，否则返回1。

第三步：封装成存储过程 #

把上面的逻辑打包成存储过程，方便调用。这里用OUT参数来返回结果：

CREATE OR REPLACE PROCEDURE check_flag_sequence(p_result OUT NUMBER)
IS
    v_q_result VARCHAR2(1000); -- 可以根据你的实际数据长度调整这个值，如果字符串很长就改成CLOB类型
BEGIN
    -- 先获取拼接好的FLAG字符串
    SELECT LISTAGG(FLAG, '') WITHIN GROUP (ORDER BY DT)
    INTO v_q_result
    FROM (
        SELECT esd AS DT, 'E' AS FLAG FROM test_data_sovlp WHERE esd IS NOT NULL 
        UNION ALL 
        SELECT td AS DT, CASE is_db WHEN 0 THEN 'S' WHEN 1 THEN 'H' END AS FLAG FROM test_data_sovlp WHERE td IS NOT NULL
    ) t;

    -- 检查是否存在目标序列
    IF REGEXP_LIKE(v_q_result, '(HH|EE|HS|SE)') THEN
        p_result := 0;
    ELSE
        p_result := 1;
    END IF;

    -- 异常处理，方便排查问题
EXCEPTION
    WHEN OTHERS THEN
        p_result := -1; -- 异常情况返回-1，你可以根据需求调整
        DBMS_OUTPUT.PUT_LINE('出错啦：' || SQLERRM);
END;
/

怎么调用这个存储过程？ #

可以用下面的代码测试：

DECLARE
    v_result NUMBER;
BEGIN
    check_flag_sequence(v_result);
    DBMS_OUTPUT.PUT_LINE('最终结果：' || v_result);
END;
/

在你的示例里，q_result是EHEEESEHH，里面有EE和HH，所以调用后会返回0。

小补充：如果字符串超长怎么办？ #

如果你的数据量很大，LISTAGG可能会因为字符串长度限制报错，这时候可以换成XMLAGG来拼接更长的字符串，代码调整成这样：

SELECT RTRIM(XMLAGG(XMLELEMENT(e, FLAG) ORDER BY DT).EXTRACT('//text()').GETCLOBVAL(), ',') AS q_result
FROM (...) t;

记得把存储过程里的v_q_result定义成CLOB类型就行啦。

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