如何区分Oracle元数据中的过程和函数? [英] How do I differentiate between Procedures and Functions in Oracle's Metadata?

问题描述

我想列出所有在给定模式中使用重载的存储过程.所有过程都在软件包中.我可以使用下面的SQL差不多到达那里(任何proc_count> 1的东西).

I want to list all the Stored Procedures which use overloading in a given schema. All the procedures are within packages. I can use the SQL below to nearly get there (anything with proc_count > 1).

select 
    object_name, procedure_name, count(procedure_name) as proc_count 
from 
    all_procedures                
where 
    owner = 'SCHEMA_NAME' 
group by
    object_name, procedure_name
order by proc_count desc   

但是，似乎没有办法在我的情况下区分名为"ask_version"的函数和名为"ask_version"的过程.情况是我们的中间件在使用重载的地方调用proc时遇到麻烦.我需要对发生在多少地方进行影响分析.我们从不直接调用函数，因此需要将其隔离

However there seems to be no way to differentiate between a function named 'ask_version' and a procedure named 'ask_version' which I need to do in my case. The case being that our middleware has trouble calling procs where overloading is used. I need to do an impact analysis on how many places this occurs. We never call functions directly, hence the need to isolate them

有什么我想念的吗?

推荐答案

all_arguments似乎有帮助.对于函数，有一个带有position=0的参数(这是返回值)，对于过程而言，此参数不存在.

all_arguments seems to help. For a function, there is an argument with position=0 (which is the return value), for procedures this argument does not exist.

SELECT object_name, procedure_name, t, COUNT(1) AS proc_count
FROM
(
  SELECT p.object_name, p.procedure_name,
         CASE WHEN a.object_id IS NULL THEN 'PROCEDURE' ELSE 'FUNCTION' END AS t
  FROM all_procedures p
  LEFT JOIN all_arguments a ON ( a.object_id = p.object_id
                             AND a.subprogram_id = p.subprogram_id AND a.position = 0 )
  WHERE p.owner = 'SCHEMA_NAME'
)
GROUP BY object_name, procedure_name, t
ORDER BY proc_count DESC;

这篇关于如何区分Oracle元数据中的过程和函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！