递归查询依赖目录的SQL存储过程编译错误排查求助

问题概述 #

编写了名为DEPCAT的SQL存储过程，用于递归查询指定对象的所有依赖目录，但编译时出现错误：

第145行，Position 1 Keyword SEARCH not expected. Valid tokens: ) FETCH LIMIT ORDER UNION EXCEPT OFFSET

错误原因解析 #

1. 递归CTE语法位置错误
   SEARCH和CYCLE是递归CTE的可选子句，必须紧跟在递归部分的查询语句之后，且要放在整个CTE定义的闭合括号)之前。原代码里直接把这两个子句写在递归JOIN查询之后，没有闭合DEPENDENCY_CHAIN的CTE定义，导致数据库解析时识别不了SEARCH关键字。

2. SELECT INTO语法错误
   原代码中SELECT INTO的字段间缺少逗号：

REQ_OBJECT_NAME into OBJECT_NAME
REQ_OBJECT_TYPE into OBJECT_TYPE,

这里OBJECT_NAME后面必须加逗号，否则会触发语法错误。

3. 子查询列缺失
   第二个UNION ALL的SELECT语句里，AS REQ_OBJECT_CATALOG前面没有列值，应该补上'' AS REQ_OBJECT_CATALOG，否则会导致列数不匹配。

修正后的完整代码 #

Create or Replace Procedure DEPCAT( 
    Out OBJECT_SCHEMA char(20),
    Out OBJECT_NAME Char(20),
    Out OBJECT_TYPE char(20),
    Out DupX char(1)
)

/* Environment */
Specific DEPCAT
Language SQL
Modifies SQL Data

Begin

Declare DuplicateError CHAR (1);

WITH DEPENDENCY_CHAIN_BASE                                          
(REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,                                         
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,                                                  
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,
DEP_OBJECT_PARM_SIGNATURE,REQ_OBJECT_PARM_SIGNATURE)                                            
AS (
    -- 变量依赖
    SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS                                            
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,                                           
D.VARIABLE_SCHEMA AS DEP_OBJECT_SCHEMA,D.VARIABLE_NAME AS
DEP_OBJECT_NAME, 'VARIABLE' AS DEP_OBJECT_TYPE,                                                 
 SYSTEM_VAR_SCHEMA AS DEP_OBJECT_SYSTEM_SCHEMA, SYSTEM_VAR_NAME AS                                          
  DEP_OBJECT_SYSTEM_NAME, '' AS REQ_OBJECT_CATALOG,
  V.VARIABLE_DEFINER AS DEP_OBJECT_DEFINER, D.PARM_SIGNATURE AS
  DEP_OBJECT_PARM_SIGNATURE,CAST(NULL AS VARCHAR(10000)                                               
           FOR BIT DATA) AS REQ_OBJECT_PARM_SIGNATURE                                           
  FROM SYSVARIABLEDEP D                                         
  JOIN SYSVARIABLES V ON V.VARIABLE_NAME=D.VARIABLE_NAME                                            
                     AND V.VARIABLE_SCHEMA=D.VARIABLE_SCHEMA                                            
UNION ALL                                           
    -- 物化查询表依赖
    SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS                                            
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,                                           
 D.TABLE_SCHEMA AS DEP_OBJECT_SCHEMA,D.TABLE_NAME AS
 DEP_OBJECT_NAME, 'MATERIALIZED QUERY TABLE' AS DEP_OBJECT_TYPE,
 D.SYSTEM_TABLE_SCHEMA AS DEP_OBJECT_SYSTEM_SCHEMA,
D.SYSTEM_TABLE_NAME AS DEP_OBJECT_SYSTEM_NAME,
 '' AS REQ_OBJECT_CATALOG,T.TABLE_DEFINER AS DEP_OBJECT_DEFINER,                                           
 D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,
 CAST(NULL AS VARCHAR(10000) FOR BIT DATA) AS
 REQ_OBJECT_PARM_SIGNATURE FROM SYSTABLEDEP D                                           
 JOIN SYSTABLES T ON T.TABLE_NAME=D.TABLE_NAME AND T.TABLE_SCHEMA
   =D.TABLE_SCHEMA                                          
UNION ALL                                           
    -- 触发器依赖（表关联触发器）
    SELECT D.EVENT_OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,
D.EVENT_OBJECT_TABLE AS REQ_OBJECT_NAME,                                            
       CASE T.TABLE_TYPE WHEN 'A' THEN 'ALIAS'                                          
            WHEN 'L' THEN 'LF'                                          
            WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'                                            
            WHEN 'P' THEN 'PF'                                          
            WHEN 'T' THEN 'TABLE'                                           
            WHEN 'V' THEN 'VIEW'                                            
            ELSE 'OTHER' END AS REQ_OBJECT_TYPE,                                            
       D.TRIGGER_SCHEMA AS DEP_OBJECT_SCHEMA,D.TRIGGER_NAME
       AS DEP_OBJECT_NAME, 'TRIGGER' AS DEP_OBJECT_TYPE,                                            
       SYSTEM_TRIGGER_SCHEMA AS DEP_OBJECT_SYSTEM_SCHEMA,
       TRIGGER_PROGRAM_NAME AS DEP_OBJECT_SYSTEM_NAME,                                          
    BASE_TABLE_CATALOG AS REQ_OBJECT_CATALOG,D.TRIGGER_DEFINER AS
           DEP_OBJECT_DEFINER,                                          
       CAST(NULL AS VARCHAR(10000) FOR BIT DATA) AS                                               
           DEP_OBJECT_PARM_SIGNATURE,CAST(NULL AS VARCHAR(10000)
 FOR BIT DATA) AS REQ_OBJECT_PARM_SIGNATURE                                         
  FROM SYSTRIGGERS D                                            
  JOIN SYSTABLES T ON T.TABLE_NAME=D.EVENT_OBJECT_TABLE                                         
                  AND T.TABLE_SCHEMA=D.EVENT_OBJECT_SCHEMA                                          
UNION ALL                                           
    -- 触发器依赖（对象关联触发器）
    SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS                                            
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,                                           
       D.TRIGGER_SCHEMA AS DEP_OBJECT_SCHEMA,D.TRIGGER_NAME AS                                          
           DEP_OBJECT_NAME,'TRIGGER' AS DEP_OBJECT_TYPE,                                                
       D.SYSTEM_TRIGGER_SCHEMA,T.TRIGGER_PROGRAM_NAME,                                          
       '' AS OBJECT_CATALOG,T.TRIGGER_DEFINER,                                         
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,
        CAST(NULL AS VARCHAR(10000) FOR BIT DATA) AS
       REQ_OBJECT_PARM_SIGNATURE FROM SYSTRIGDEP D                                          
  JOIN SYSTRIGGERS T ON T.TRIGGER_SCHEMA=D.TRIGGER_SCHEMA AND
  T.TRIGGER_NAME=D.TRIGGER_NAME                                         
UNION ALL                                           
    -- 视图依赖
    SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS                                            
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,                                           
       D.VIEW_SCHEMA AS DEP_OBJECT_SCHEMA,VIEW_NAME AS
      DEP_OBJECT_NAME,'VIEW' AS DEP_OBJECT_TYPE,                                            
       D.SYSTEM_VIEW_SCHEMA,D.SYSTEM_VIEW_NAME,                                         
       '' AS OBJECT_CATALOG,V.VIEW_DEFINER,                                            
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,
      CAST(NULL AS VARCHAR(10000)
           FOR BIT DATA) AS REQ_OBJECT_PARM_SIGNATURE                                           
  FROM SYSVIEWDEP D                                         
  JOIN SYSVIEWS V ON V.TABLE_SCHEMA=D.VIEW_SCHEMA                                           
                 AND V.TABLE_NAME=D.VIEW_NAME                                           
UNION ALL                                           
    -- 存储过程/函数依赖
    SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS                                            
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,                                           
       R.ROUTINE_SCHEMA AS DEP_OBJECT_SCHEMA,R.ROUTINE_NAME AS                                          
           DEP_OBJECT_NAME,R.ROUTINE_TYPE AS DEP_OBJECT_TYPE,                                           
       '' AS SYSTEM_VIEW_NAME,                                         
       '' AS SYSTEM_VIEW_SCHEMA,                                           
       OBJECT_CATALOG AS REQ_OBJECT_CATALOG,R.ROUTINE_DEFINER,                                          
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,
       R.PARM_SIGNATURE AS REQ_OBJECT_PARM_SIGNATURE                                            
  FROM SYSROUTINEDEP D                                          
  JOIN SYSROUTINES R ON R.SPECIFIC_NAME=D.SPECIFIC_NAME                                               
                    AND R.SPECIFIC_SCHEMA=D.SPECIFIC_SCHEMA                                         
),                                          
DEPENDENCY_CHAIN_TOP AS (                                           
SELECT REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,                                           
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,                                                  
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,                                            
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,                                          
DEP_OBJECT_PARM_SIGNATURE,REQ_OBJECT_PARM_SIGNATURE,                                            
1 AS LEVEL                                          
  FROM DEPENDENCY_CHAIN_BASE                                            
 WHERE REQ_OBJECT_SCHEMA IN ('*LIBL','KAL1D')                                           
   AND REQ_OBJECT_NAME='"PARTS"'                                            
   AND REQ_OBJECT_TYPE='TABLE'  -- Optional                                         
),                                          
DEPENDENCY_CHAIN (REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,                                            
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,                                                  
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,DEP_OBJECT_PARM_SIGNATURE,                                            
REQ_OBJECT_PARM_SIGNATURE,LEVEL, SortOrder)                                            
AS (                                            
SELECT REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,                                           
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,                                                  
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,                                            
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,DEP_OBJECT_PARM_SIGNATURE,                                            
REQ_OBJECT_PARM_SIGNATURE,LEVEL,
CAST(NULL AS VARCHAR(1000)) AS SortOrder                                         
  FROM DEPENDENCY_CHAIN_TOP                                         
UNION ALL                                           
SELECT d.REQ_OBJECT_SCHEMA,d.REQ_OBJECT_NAME,d.REQ_OBJECT_TYPE,                                         
d.DEP_OBJECT_SCHEMA,d.DEP_OBJECT_NAME,d.DEP_OBJECT_TYPE,                                                
d.DEP_OBJECT_SYSTEM_SCHEMA,d.DEP_OBJECT_SYSTEM_NAME,                                            
d.REQ_OBJECT_CATALOG,d.DEP_OBJECT_DEFINER,
d.DEP_OBJECT_PARM_SIGNATURE,                                            
d.REQ_OBJECT_PARM_SIGNATURE,b.LEVEL+1 AS LEVEL,
CAST(NULL AS VARCHAR(1000)) AS SortOrder                                          
  FROM DEPENDENCY_CHAIN b                                           
  JOIN DEPENDENCY_CHAIN_BASE d ON d.REQ_OBJECT_SCHEMA
                              IN (b.DEP_OBJECT_SCHEMA,'*LIBL')
                              AND d.REQ_OBJECT_NAME=b.DEP_OBJECT_NAME                                           
                              AND (d.DEP_OBJECT_PARM_SIGNATURE=                                         
        b.REQ_OBJECT_PARM_SIGNATURE OR b.REQ_OBJECT_PARM_SIGNATURE IS NULL)
-- 修正：SEARCH和CYCLE放在递归查询之后，CTE闭合括号之前
SEARCH DEPTH FIRST BY DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME SET SortOrder
CYCLE DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE                                      
SET DuplicateError To '*' Default ' '
) -- 补上CTE的闭合括号

-- 修正SELECT INTO的逗号问题
SELECT d.REQ_OBJECT_SCHEMA into OBJECT_SCHEMA,
       d.REQ_OBJECT_NAME into OBJECT_NAME,
       d.REQ_OBJECT_TYPE into OBJECT_TYPE,
       DuplicateError into DupX
  FROM DEPENDENCY_CHAIN d                                           
 ORDER BY SortOrder;

End;

结构优化建议 #

• 参数化查询条件：把硬编码的'*LIBL','KAL1D'和'"PARTS"'改成存储过程的输入参数，比如添加IN p_schema char(20), IN p_object char(20), IN p_type char(20)，让存储过程可以查询任意对象的依赖，提升复用性。
• 调整输出方式：原存储过程用OUT参数只能返回单条结果，但递归查询会生成多条依赖链数据，建议改用游标返回所有结果，或者定义表类型的输出参数，避免丢失数据。
• 添加递归深度限制：在递归CTE的WHERE条件里添加b.LEVEL < 10（可自定义深度），防止因循环依赖导致的性能问题，即使有CYCLE子句，限制深度也更稳妥。
• 优化系统视图关联：对SYSVARIABLEDEP、SYSTABLEDEP等系统视图的关联字段（比如OBJECT_SCHEMA、OBJECT_NAME）创建索引，提升递归查询的性能。
• 代码模块化：把每个依赖类型的查询块封装成单独的CTE，或者添加清晰的注释，方便后续维护和扩展。

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