Oracle中SP與表之間的關系

SP-TABLE

select t1.owner as powner,t1.name as pname

,case when t1.referenced_owner in ('SAPCRDB','SAPIRDB') then t1.referenced_owner else t2.owner end as towner

,t1.referenced_name as tname

from all_dependencies t1

inner join all_tables t2 

on  t1.referenced_name=t2.table_name 

where t1.type = 'PROCEDURE'

and t1.owner in ('SAPCRDB','SAPIRDB')

and case when t1.referenced_owner in ('SAPCRDB','SAPIRDB') then t1.referenced_owner else t2.owner end = t2.owner

and t1.referenced_type in ('TABLE','VIEW','SYNONYM')

and t1.name='SAP_AG_ATC'

and t2.temporary='N'

SP-SP

select t1.owner as powner,t1.name as pname

,case when t1.referenced_owner in ('SAPCRDB','SAPIRDB') then t1.referenced_owner else t2.owner end as towner

,t1.referenced_name as tname

from all_dependencies t1

inner join all_objects t2 

on  t1.referenced_name=t2.object_name 

where t1.type = 'PROCEDURE'

and t1.owner in ('SAPCRDB','SAPIRDB')

and case when t1.referenced_owner in ('SAPCRDB','SAPIRDB') then t1.referenced_owner else t2.owner end = t2.owner

and t1.referenced_type in ('PROCEDURE','SYNONYM')

and t2.object_type='PROCEDURE'

and t1.name='SAP_AG_ETL_01';