oracle将当前用户下所有的表的create语句导出.sql文件

很久以前写的一段代码了。

还记得那个时候是我刚从学校出来的那段时光，整天呆在公司，还没有具体事物交给我做。当时，由于比较闲，一个同事就跑过来，问我。说可不可以把oracle当前
用户下所有表的建表语句都导出来。

当时由于比较闲，就花了一个下午的时间，研究这个问题。我发现很多数据库GUI的程序，都不能做到这一点。后来发现可以在cmd里用数据库的一些命令，把所有的建
表语句都打印出来，然后再利用spool off截屏之后把文本放到一个文本文件里。这样做显然是可以的，可是我那个同事却说，一定要一个表导出一个文件。

听完这句话，我当时就想到了两个解决办法。

1，就是把所有表的建表语句导出到一个文本文件里，然后再用正则匹配，对整个文件进行切割，按表名切割成多个.sql的文件。
2,从源头上解决整个问题，那就是自己写plsql代码块，然后自己去拼接整个建表语句，然后将拼接的字符写到硬盘上。

当时的我对于正则匹配还不是很了解，所以就很倾向于第二种方式。

下面就是我的所有代码：

--获取表的注释
--tabname表名
create or replace function get_tab_comment(tabname varchar2)  
     return varchar2  
   as
      Result varchar2(4000);
      v_query_sql varchar2(400);
      v_acc_nbr varchar2(400);
      type ref_cursor is ref cursor;
      v_cursor ref_cursor;
    
     begin  

        Result:='';
        
        v_query_sql:= 'select COMMENTS from user_tab_comments where TABLE_NAME ='''||tabname||'''';
        
        open v_cursor for  v_query_sql;
 
  loop
        fetch v_cursor into v_acc_nbr;
        exit when v_cursor%notfound;
        if(v_acc_nbr is null) then
             Result:=Result||''; 
        else Result:=Result||'comment on table '||tabname||CHR(10)||' is '''||v_acc_nbr||''''||';'; 
        end if;
  end loop;
  
  close v_cursor;
 
 return Result;

end get_tab_comment; 
/
--获取列的注释
--tabname表名
create or replace function get_col_comments(tabname varchar2)  
     return varchar2  
   as
      Result varchar2(4000);
      v_query_sql varchar2(400);
      type ref_cursor is ref cursor;
      v_cursor ref_cursor;
      v_col_name varchar2(4000);
      v_comments varchar2(4000);
    
     begin  

        Result:='';
        
        v_query_sql:= 'select column_name,comments from user_col_comments where TABLE_NAME ='''||tabname||'''';
        
        open v_cursor for  v_query_sql;
 
  loop
        fetch v_cursor into v_col_name,v_comments;
        exit when v_cursor%notfound;
        
        if(v_comments is null) then
             Result:=Result||'';      
        else Result:=Result||'comment on '||tabname||'.'||lower(v_col_name)||CHR(10)||' is '''||v_comments||''''||';'||CHR(10);
        end if;          
  end loop;
  
  close v_cursor;
 
 return Result;

end get_col_comments; 
/
--将查出的所有创建表语句的结果集放在一个视图里
CREATE OR REPLACE VIEW STUDENT AS
SELECT t.TABLE_NAME as tabname,DBMS_METADATA.GET_DDL('TABLE',table_name) as temp FROM USER_TABLES t;
--创建一个目录盛放写出的文件
create or replace directory TESTFILE as 'D:zzkuploadservice';
/
 declare

  CURSOR C_EVENT is select t.tabname,t.temp from student t;
  temp Student.TEMP%TYPE;
  filehandle utl_file.file_type;
  mystring  VARCHAR2(4000):='';
begin
  for temp in C_EVENT
  Loop
     filehandle := utl_file.fopen('TESTFILE',lower(temp.TABNAME)||'.sql','w');
     if(get_tab_comment(temp.TABNAME) is null) then
       utl_file.put_line(filehandle,temp.TEMP||get_col_comments(temp.TABNAME));
     else utl_file.put_line(filehandle,temp.TEMP||get_tab_comment(temp.TABNAME)||CHR(10)||get_col_comments(temp.TABNAME));
     end if;
     utl_file.fclose(filehandle);
     
  End Loop;
 end; 
/
--删除为执行任务而创建的资源
 drop directory testfile;
 drop view student;
 drop function get_tab_comment;
 drop function get_col_comments;
/
--select get_tab_comment('HOUSEHOLD') from dual;

由于本机没有oracle，所以这里就不往上贴测试图片了。

（编辑：李大同）

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