从 oracle 获取函数列表和过程签名

素材狗 2021-01-01 16:18:18 文章分类：数据库问题点击数：2次

文章标签

Getting a list of functions and procedure signature from oracle(从 oracle 获取函数列表和过程签名)

本文介绍了从 oracle 获取函数列表和过程签名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否有任何查询可以为我提供函数/过程的签名详细信息.我在看返回类型，函数名，参数类型，是否为IN/OUT/INOUT.

Is there any query which can provide me signature details of a Function/Procedure. I am looking at return type, function name, argument types, whether IN/OUT/INOUT.

我知道这个线程，但它只提供名称

I am aware of this thread, but it provides only names

推荐答案

这是一个生成 PL/SQL 函数原型的小脚本:

Here's a little script that produces PL/SQL function prototypes:

DECLARE 
  -- Local variables here
  strPrev_object          VARCHAR2(30);
  strReturn_type          VARCHAR2(30);
  strProcedure_definition VARCHAR2(32767);
BEGIN
  -- This dumps out subprogram definitions.  It doesn't try to build
  -- package scripts; instead, it dumps the package procedures as though they were defined
  -- individually, with package name preceding subprogram name.

  FOR aRow IN (SELECT *
                 FROM USER_ARGUMENTS a
                 INNER JOIN (SELECT PACKAGE_NAME, OBJECT_NAME, MAX(SEQUENCE) AS MAX_SEQUENCE
                               FROM USER_ARGUMENTS
                               GROUP BY PACKAGE_NAME, OBJECT_NAME)
                   USING (PACKAGE_NAME, OBJECT_NAME)
                 WHERE PACKAGE_NAME IS NULL AND
                       OBJECT_NAME = '<your package, procedure, or function>'
                 ORDER BY PACKAGE_NAME, OBJECT_NAME, SEQUENCE)
  LOOP
    strProcedure_definition := NULL;

    IF strPrev_object IS NOT NULL AND
       strPrev_object <> aRow.OBJECT_NAME 
    THEN
      IF strReturn_type IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('     );');
      ELSE
        DBMS_OUTPUT.PUT_LINE('     ) RETURN ' || strReturn_type || ';');
      END IF;

      DBMS_OUTPUT.PUT_LINE('');
    END IF;

    IF aRow.SEQUENCE = 1 THEN
      IF aRow.ARGUMENT_NAME IS NULL THEN
        strProcedure_definition := 'FUNCTION ';
        strReturn_type := aRow.DATA_TYPE;
      ELSE
        strProcedure_definition := 'PROCEDURE ';
        strReturn_type := NULL;
      END IF;

      IF aRow.PACKAGE_NAME IS NOT NULL THEN
        strProcedure_definition := strProcedure_definition || aRow.PACKAGE_NAME || '.' || aRow.OBJECT_NAME || '(';
      ELSE
        strProcedure_definition := strProcedure_definition || aRow.OBJECT_NAME || '(';
      END IF;

      DBMS_OUTPUT.PUT_LINE(strProcedure_definition);
    END IF;

    IF aRow.ARGUMENT_NAME IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('     ' || aRow.ARGUMENT_NAME || '  ' ||
                           CASE aRow.IN_OUT WHEN 'IN/OUT' THEN 'IN OUT' ELSE aRow.IN_OUT END || '  ' ||
                           aRow.DATA_TYPE || CASE WHEN aRow.SEQUENCE <> aRow.MAX_SEQUENCE THEN ',' ELSE '' END
                           );
    END IF;

    strPrev_object := aRow.OBJECT_NAME;
  END LOOP;  -- aRow

  IF strReturn_type IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('     );');  
  ELSE
    DBMS_OUTPUT.PUT_LINE(') RETURN ' || strReturn_type || ';');
  END IF;
END;