如何在PostgreSQL中转换Oracle的CONNECT BY及相关存储过程代码

一、Oracle CONNECT BY语法的PostgreSQL替代方案 #

Oracle的CONNECT BY主要用于递归层级查询，PostgreSQL中使用WITH RECURSIVE语法实现递归逻辑。针对字符串拆分这类常见场景，PostgreSQL还提供了更简洁的原生函数组合（如string_to_array + unnest），无需复杂递归即可完成需求。

二、目标SQL语句的转换 #

原Oracle SQL的作用是：若变量v_month为'0'则返回'0'，否则将v_month按逗号拆分，每行返回一个子串。

转换后的PostgreSQL写法 #

方式1：原生函数组合（高效简洁）

SELECT 
  CASE 
    WHEN v_month = '0' THEN '0'
    ELSE unnest(string_to_array(v_month, ','))
  END AS month_part;

方式2：递归CTE模拟CONNECT BY逻辑

如果需要严格对齐原Oracle的递归遍历逻辑，可使用递归CTE实现：

WITH RECURSIVE split_cte AS (
  SELECT
    CASE 
      WHEN v_month = '0' THEN '0'
      ELSE regexp_substr(v_month, '[^,]+', 1, 1)
    END AS month_part,
    1 AS level_num,
    v_month AS original_str
  WHERE v_month IS NOT NULL
  UNION ALL
  SELECT
    regexp_substr(original_str, '[^,]+', 1, level_num + 1),
    level_num + 1,
    original_str
  FROM split_cte
  WHERE regexp_substr(original_str, '[^,]+', 1, level_num + 1) IS NOT NULL
    AND original_str != '0'
)
SELECT month_part FROM split_cte;

三、存储过程代码的转换 #

以下是对应Oracle存储过程的PostgreSQL PL/pgSQL实现：

CREATE OR REPLACE PROCEDURE process_month(v_month TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
  -- 示例：遍历拆分结果并输出，可根据业务需求调整逻辑
  FOR rec IN
    SELECT 
      CASE 
        WHEN v_month = '0' THEN '0'
        ELSE unnest(string_to_array(v_month, ','))
      END AS month_part
  LOOP
    RAISE NOTICE '拆分后的月份：%', rec.month_part;
  END LOOP;
END;
$$;

调用存储过程 #

-- 测试单值场景
CALL process_month('0');
-- 测试多值拆分场景
CALL process_month('202401,202402,202403');

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