下面是我递归查询给定对象的所有依赖目录的尝试：
Create or Replace Procedure DEPCAT( Out OBJECT_SCHEMA char(20),
Out OBJECT_NAME Char(20),
OBJECT_TYPE char(20),
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,
'' 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,
'' 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,
'' SYSTEM_VIEW_NAME,
'' 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)
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
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
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 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 ' '
SELECT d.REQ_OBJECT_SCHEMA into OBJECT_SCHEMA ,
REQ_OBJECT_NAME into OBJECT_NAME
REQ_OBJECT_TYPE into OBJECT_TYPE,
DuplicateError into DupX
FROM DEPENDENCY_CHAIN d
ORDER BY SortOrder;
End;
我无法算出编译错误。第145行出现编译错误：位置1关键字SEARCH不应为。有效令牌：(获取除偏移量以外的限制订单并集。
有什么想法可以解决或更好地构建它吗？
是的，您缺少一个)来关闭DEPENDENCY_CHAINCTE子层
我建议在尝试将select语句封装到过程中之前，先让它工作起来。
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)
) -- <-- THIS ONE
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 ' '
select d.req_object_schema
,req_object_name
,req_object_type
,duplicateerror
from dependency_chain d
order by sortorder;
这里还有一个拼写错误：
D.SYSTEM_TABLE_NAME AS DEP_OBJECT_SYSTEM_NAME,
AS REQ_OBJECT_CATALOG,T.TABLE_DEFINER AS DEP_OBJECT_DEFINER,
这对我来说是有效的(或者至少返回数据(…
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
,t.base_table_catalog 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
,'' 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
,'' 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
,'' system_view_name
,'' 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 mylevel
from dependency_chain_base
where req_object_schema in ('MYLIB')
and req_object_name = 'MYFILE'
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, mylevel) 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 mylevel
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.mylevel + 1 as mylevel
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 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 ' '
select *
from dependency_chain d
order by sortorder;
注意不要将level用作列名，它是一个关键字。。
最后，要将上述内容制作成一个存储过程，只需
declare depGraph cursor for <statement>;
open depGraph;
return;