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

我知道this线程,但它只提供名称

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,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;

分享和享受.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!