错误：第 2 行或接近"WITH"语法错误：递归 CTE 为

Question

请让我知道我在哪里犯了错误，因为我无法创建过程：

CREATE OR REPLACE PROCEDURE dpk_mc_tr.dpr_mc_tr_cmo_med_form_arc_inf ( in_usercode text, in_usersess text, in_compcode text, in_stardate text, in_stopdate text, in_sericate text, in_matccate text, in_seritycd text, in_matycode text, in_venucode text, in_teamcode text, in_drsmflag text, in_daynight text, in_telecast text, in_progname text, out_code INOUT numeric ) AS $body$
WITH RECURSIVE cte AS (
DECLARE
c01 CURSOR FOR
SELECT DISTINCT REPLACE(SUBSTR((SELECT array_to_string(a, '') FROM regexp_matches(in_sericate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL
- 1)),
2,
6
),
']',
''
) sericate
(SELECT array_to_string(a, '') FROM regexp_matches(in_sericate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL - 1)) IS NOT NULL  UNION ALL
DECLARE
c01 CURSOR FOR
SELECT DISTINCT REPLACE(SUBSTR((SELECT array_to_string(a, '') FROM regexp_matches(in_sericate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL
- 1)),
2,
6
),
']',
''
) sericate

(SELECT array_to_string(a, '') FROM regexp_matches(in_sericate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL - 1)) IS NOT NULL JOIN cte c ON ()
) SELECT * FROM cte;
;
m01 CURSOR FOR
SELECT sysofcde sericate
FROM sycodmas
WHERE compcode = '001' AND modlcode = 'MM' AND syhrdcde = 'SRC'
UNION ALL
SELECT 'X' sericate
;WITH RECURSIVE cte AS (

c02 CURSOR FOR
SELECT DISTINCT REPLACE(SUBSTR((SELECT array_to_string(a, '') FROM regexp_matches(in_matccate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL
- 1)),
2,
6
),
']',
''
) matccate
(SELECT array_to_string(a, '') FROM regexp_matches(in_matccate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL - 1)) IS NOT NULL  UNION ALL

c02 CURSOR FOR
SELECT DISTINCT REPLACE(SUBSTR((SELECT array_to_string(a, '') FROM regexp_matches(in_matccate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL
- 1)),
2,
6
),
']',
''
) matccate

(SELECT array_to_string(a, '') FROM regexp_matches(in_matccate, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL - 1)) IS NOT NULL JOIN cte c ON ()
) SELECT * FROM cte;
;
m02 CURSOR FOR
SELECT sysofcde matccate
FROM sycodmas
WHERE compcode = '001' AND modlcode = 'MM' AND syhrdcde = 'MAC'
UNION ALL
SELECT 'X' matccate
;WITH RECURSIVE cte AS (

c03 CURSOR FOR
SELECT DISTINCT REPLACE(SUBSTR((SELECT array_to_string(a, '') FROM regexp_matches(in_seritycd, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL
- 1)),
2,
6
),
']',
''
) seritycd
(SELECT array_to_string(a, '') FROM regexp_matches(in_seritycd, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL - 1)) IS NOT NULL  UNION ALL

c03 CURSOR FOR
SELECT DISTINCT REPLACE(SUBSTR((SELECT array_to_string(a, '') FROM regexp_matches(in_seritycd, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL
- 1)),
2,
6
),
']',
''
) seritycd

(SELECT array_to_string(a, '') FROM regexp_matches(in_seritycd, '[^,]+', 'g') AS foo(a) LIMIT 1 OFFSET (LEVEL - 1)) IS NOT NULL JOIN cte c ON ()
) SELECT * FROM cte;
;

错误：在"处或附近出现语法错误；WITH">
第2行：具有递归cte AS(**

感谢

Answer 1

您的查询实际上有几个错误。

1. 中的分号：SELECT'X'服务；与。。。

2. 不能在递归CTE中使用游标递归CTE实际上是非常有限的。不能在递归部分内部使用cte进行外部联接，不能使用分组子句，不能在子查询中使用cte的名称。。。

我不确定你想要实现什么，但与递归一起使用不是一个好方法。。。另外，请记住regexp匹配非常缓慢。。。