Oracle 提取包体以获取每个过程/函数源代码

汤姆，

出于文档目的，我们尝试编写一些SQL来从Oracle软件包主体中逐个提取函数和过程的源代码。

到目前为止，我们所做的工作如下:

创建一个函数，该函数将包的包主体作为CLOB返回，方法是将包的各个行从ALL_SOURCE组合到CLOB。将包名作为参数，返回CLOB

FUNCTION GetPackageBody(PackageName IN VARCHAR2)
RETURN CLOB
IS
C CLOB;
      l_in_comment boolean default FALSE;
      l_comment_pos     int;
      l_text            varchar2(4000);

    BEGIN
      DBMS_LOB.CREATETEMPORARY(C,TRUE);
      FOR I IN ( SELECT TEXT
                   FROM  ALL_SOURCE
                  WHERE  NAME  = PackageName
                    AND  TYPE  = 'PACKAGE BODY'
              ORDER BY LINE
              )

     LOOP
       L_TEXT := (I.TEXT);
        IF L_TEXT IS NOT NULL THEN
          DBMS_LOB.WRITEAPPEND(C,LENGTH(L_TEXT),L_TEXT);
       END IF;
     END LOOP;
     RETURN C;
   END;

为了简化事情，我们创建了一个视图，该视图具有包名称和CLOB字段，并且仅限于特定架构的包

create or replace view package_script as
select object_name AS PKG_NAME,
         GetPackageBody(object_name)   PKG_BODY
from all_objects
where owner = 'SchemaOwnerName>'
and object_type = 'PACKAGE';

以上所有工作都很好。

然后，最后一位是查找并读取包中每个功能/过程的源代码。
下面的SQL将从package_script视图中读取包中有多少个函数/过程，即级别，并报告它们开始的位置。

SELECT Q.* 
   FROM 
(SELECT X.*, BODY_LINE AS "START"  
--- LEAD(BODY_LINE, 1, 0) OVER (ORDER BY PKG_NAME, PKG_NO)   AS "STOP" 
-- LEAD(BODY_LINE, 1, 0) OVER (ORDER BY PKG_NAME, PKG_NO) - BODY_LINE AS "CHARS"
FROM
(
WITH BODY_COUNT AS (
  SELECT PKG_NAME, GREATEST( REGEXP_COUNT(PKG_BODY , 'FUNCTION'), REGEXP_COUNT(PKG_BODY , 'PROCEDURE')) CNT
   FROM PACKAGE_SCRIPT
   )
SELECT T."LEVEL",T."LEVEL" AS PKG_NO, T."PKG_NAME",T."PKG_BODY",T."BODY_FUNCTION",T."BODY_PROCEDURE", GREATEST(BODY_FUNCTION, BODY_PROCEDURE) BODY_LINE
FROM
( SELECT LEVEL, AT.PKG_NAME , AT.PKG_BODY, INSTR(AT.PKG_BODY,'FUNCTION', 1, LEVEL)  AS BODY_FUNCTION,
                           INSTR(AT.PKG_BODY,'PROCEDURE', 1, LEVEL)  AS BODY_PROCEDURE
   FROM PACKAGE_SCRIPT AT, BODY_COUNT BC
    WHERE AT.PKG_NAME = BC.PKG_NAME
  CONNECT BY LEVEL <=   CNT
  ) T
  ) X
  ) Q

这个SQL也执行ok，它不是最快的，但仍然可以接受。

然后我们将数学整合到查询中，以使用analytics函数LEAD获得每个函数/过程的开始和结束位置，并杀死了性能。这些在上面的SQL中以粗体显示，当前已注释掉。

我们的问题是:

1) 是否有更好的方法从软件包主体中提取各个函数/过程的源代码？
2) 为什么性能如此急剧下降，并且有没有一种方法可以 “修复” SQL以使其性能更好？

谢谢，
卡里

哦，天啊。我不知道从哪里开始。

你这样做的事实让我担心你的PL/SQL不在源代码管理中。您已将PL/SQL保存在源代码管理中的文件中，对吗？

接下来，如果您尝试按照Javadoc的方式生成文档... 这不是这样做的方法!

在您的源代码中添加适当的注释。然后你可以在SQL Developer中使用DBDoc提取它:

https://www.thatjeffsmith.com/archive/2012/03/javadoc-for-the-database-a-la-dbdoc-via-sql-developer/

最后，SQL中存在许多缺陷来查找过程的开始/结束。首先，它假定大写关键字。可能不是这样!

您还将遇到嵌套单元的问题-在另一个proc/fn的声明中定义的函数/过程。

相反，请查看PL/Scope。这为您提供了有关PL/SQL如何组合在一起的许多详细信息。根据您要尝试做的事情，这可能会给您想要的东西。

这是一个快速示例，显示了如何提取包的详细信息:

create or replace package pkg as 
  procedure p ( p int );
end;
/

create or replace package body pkg as 
  procedure p ( p int ) as
    l int;
    function f2 ( p int ) 
      return int as
      retval int;
    begin
      return retval;
    end f2;
  begin
    l := f2 ( p );
    dbms_output.put_line ( l );
  end;
  
  function f1 ( p int ) 
    return int as
    retval int;
  begin
    return retval;
  end f1;
  
end;
/

select lpad(' ', level*2, ' ') || name as name,
       type,
       usage,
       line,
       col
from   user_identifiers
start with usage_context_id = 0
and    object_name = 'PKG'
and    object_type = 'PACKAGE BODY'
connect by prior usage_id = usage_context_id
and    object_name = 'PKG'
and    object_type = 'PACKAGE BODY';

NAME               TYPE        USAGE         LINE   COL   
  PKG               PACKAGE      DEFINITION           1     14 
    P               PROCEDURE    DEFINITION           2     13 
      P             FORMAL IN    DECLARATION          2     17 
        INT         SUBTYPE      REFERENCE            2     19 
      L             VARIABLE     DECLARATION          3      5 
        INT         SUBTYPE      REFERENCE            3      7 
      F2            FUNCTION     DECLARATION          4     14 
        F2          FUNCTION     DEFINITION           4     14 
          P         FORMAL IN    DECLARATION          4     19 
            INT     SUBTYPE      REFERENCE            4     21 
          INT       SUBTYPE      REFERENCE            5     14 
          RETVAL    VARIABLE     DECLARATION          6      7 
            INT     SUBTYPE      REFERENCE            6     14 
          RETVAL    VARIABLE     REFERENCE            8     14 
      L             VARIABLE     ASSIGNMENT          11      5 
        F2          FUNCTION     CALL                11     10 
          P         FORMAL IN    REFERENCE           11     15 
    F1              FUNCTION     DECLARATION         15     12 
      F1            FUNCTION     DEFINITION          15     12 
        P           FORMAL IN    DECLARATION         15     17 
          INT       SUBTYPE      REFERENCE           15     19 
        INT         SUBTYPE      REFERENCE           16     12 
        RETVAL      VARIABLE     DECLARATION         17      5 
          INT       SUBTYPE      REFERENCE           17     12 
        RETVAL      VARIABLE     REFERENCE           19     12