查询存储过程用到的表,并进行分组、排序、联结:
1 SELECT 2 REFERENCED_OWNER, 3 REFERENCED_NAME, 4 LISTAGG(XH||'>'||NAME,',') WITHIN GROUP(ORDER BY XH ) NAME 5 FROM 6 ( 7 SELECT 8 A.REFERENCED_OWNER, 9 A.REFERENCED_NAME, 10 A.NAME , 11 ROW_NUMBER() OVER(PARTITION BY A.REFERENCED_OWNER, A.REFERENCED_NAME ORDER BY A.NAME DESC ) XH 12 FROM USER_DEPENDENCIES A --系统表 13 WHERE A.REFERENCED_OWNER IN ('IRS_MODEL', 'IRS_ODS','ADMDATA') --根据实际用户 14 AND TYPE IN ('PROCEDURE'/*, 'VIEW'*/) --实际类别 15 AND A.NAME LIKE 'PMODEL_DS%' --实际名称 16 ORDER BY A.REFERENCED_OWNER,A.REFERENCED_NAME 17 ) 18 GROUP BY REFERENCED_OWNER,REFERENCED_NAME;