ORACLE导出存储过程SQL至文件

   ORACLE导出存储过程SQL至文件 

      项目部署时，需要迁移存储过程，利用工具导出过程SQL，只是按照顺序导出，不能体现依赖关系，不能一次执行成功，比较麻烦。通过USER_SOURCE、DBA_SOURCE、ALL_SOURCE查询Oralce数据库对象SQL语句，并导出至文件。自己动手，当然知道那些是依赖关系，一次麻烦，以后方便。

1、ALL_SOURCE

      可以看到数据库对象的SQL，方便我们分析问题，因为有些对象属于系统对象。

SELECT NAME, LINE, TEXT
FROM USER_SOURCE
WHERE NAME = '&name'
   AND LINE >= 10 AND LINE < 30
ORDER BY LINE;

2、使用记录表  

      ORACLE 数据库将管理内存区分为系统全局区 SGA、程序全局区 PGA、用户全局区 UGA；记录表之类的集合类型在处理时以管道的形式从程序全局区 PGA 中读取数据；

      游标是以集合的方式从全局区 SGA 中读取数据；这样在数据处理时,游标就比记录表多了一些中间环节,使用记录表要比游标在速度上提高2~4倍,但由于记录表对系统的内存要求较高,当单列数据长度过大时,建议不使用记录表。 本例使用记录表来实现。

3、过程代码

CREATE OR REPLACE PROCEDURE PX_SYS_EXPORT_SQL( V_OWNER VARCHAR2 )
AS
type PROC_INFO_TYPE IS
  TABLE OF USER_SOURCE.TEXT%TYPE INDEX BY BINARY_INTEGER;
  
  PROC_INFOS PROC_INFO_TYPE;
  file_handle utl_file.file_type;
  
  V_STR VARCHAR2(4000);
  V_SQL1  VARCHAR2(1000);
  V_SQL2 VARCHAR2(1000);
  V_SQL3 VARCHAR2(1000);
  
  V_CNT    NUMBER;
  V_INDEX  NUMBER;
BEGIN
  V_SQL1:='SELECT DISTINCT(NAME) FROM ALL_SOURCE WHERE OWNER = ''' || UPPER(V_OWNER) || '''';
  EXECUTE immediate V_SQL1 bulk collect INTO PROC_INFOS;
  
  file_handle := utl_file.fopen('DATA_PUMP_DIR','PROC.sql','W');
  
  FOR i IN 1..PROC_INFOS.count
  LOOP
    V_INDEX        :=1;
    V_SQL2:='SELECT MAX(LINE) FROM ALL_SOURCE WHERE OWNER=''' || UPPER(V_OWNER) || ''' AND NAME=''' || PROC_INFOS(i) || '''';
    
    EXECUTE immediate V_SQL2 INTO V_CNT;
    WHILE V_INDEX<=V_CNT
    LOOP
      V_SQL3:='SELECT TEXT FROM ALL_SOURCE WHERE OWNER=''' || UPPER(V_OWNER) || ''' AND NAME=''' || PROC_INFOS(i) || ''' AND LINE = ' || V_INDEX;
      
      EXECUTE immediate V_SQL3 INTO V_STR;
      V_INDEX:=V_INDEX+1;
      utl_file.put(file_handle,V_STR);
    END LOOP;
  END LOOP;
  utl_file.fclose(file_handle);
  COMMIT;
  
END PX_SYS_EXPORT_SQL;