oracle存储过程实现导出表结构

嫔然4344

要将数据库中的表结构全部倒出来，有分区表和非分区表，涉及到的字段有number、data、timestamp、varchar2、char。所以只针对了这几个字段的表的导出，如果表有其类型字段，则需要添加代码。分区表都是以时间做分区的，所以导出来的分区表结构都是以时间分区的。只是根据了自己实际情况写的，根据不同的情况要改写！

存储过程带一个参数，默认为Y，导出分区表的分区，如果指定其他值，如
execu table_frame('N')，则只导出表结构。

使用方法：
1、要导出哪个用户的所有表结构，就在该用户下执行最下面的存储过程。
2、如下建立一个directory，同样要在数据库服务器D盘下建立一个名为‘结构’的文件夹。
create or replace directory DIR_DUMP as 'd:/结构';
3、执行存储过程，生成的表结构代码就在路径d:/结构下的txt文件中。

create or replace procedure table_frame(v_partition_status varchar2 default 'Y')
is
   type column_type is table of  user_tab_columns.column_name%type;
   v_column column_type;
   type data_type is table of  user_tab_columns.data_type%type;
   v_type data_type;
   type length_type is table of  user_tab_columns.data_length%type;
   v_length length_type;
   type datapre_type is table of  user_tab_columns.DATA_PRECISION%type;
   v_ldatapre datapre_type;
   type datasca_type is table of  user_tab_columns.DATA_SCALE%type;
   v_dayasca datasca_type;
   v_str clob;

   file_name UTL_FILE.file_type;
   v_tables varchar2(50);

   partition_status varchar2(3);
   partition_keywords varchar2(30);
   
   TYPE part_cursor is ref CURSOR;
   part_name part_cursor;
   
   partition_name user_tab_partitions.partition_name%type;
   high_value user_tab_partitions.high_value%type;
begin
  file_name := UTL_FILE.FOPEN('DIR_DUMP','table.txt','w');

  --判断是否需要分区
  partition_status := v_partition_status;

  --按表循环
  for j in (select table_name  from user_tables  group by table_name ) loop
   v_tables :=upper(j.table_name);
   v_str := 'create table '||v_tables||'(';

   UTL_FILE.PUT_LINE(file_name,v_str);

   --提取表的字段信息
   select column_name,data_type,data_length,DATA_PRECISION,DATA_SCALE
    bulk collect into v_column,v_type,v_length,v_ldatapre,v_dayasca
      from user_tab_columns where table_name=v_tables;

    --按字段循环
    for i in 1..v_column.count loop
      if v_type(i)= 'DATE' or v_type(i) like 'TIMESTAMP%'  then
        v_str :=v_column(i)||' '||v_type(i)||',';
      elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is not null  then
        v_str :=v_column(i)||' '||v_type(i)||'('||v_ldatapre(i)||','||v_dayasca(i)||'),';
      elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is  null  then
        v_str :=v_column(i)||' '||v_type(i)||',';
      elsif v_type(i)= 'CLOB' or  v_type(i)= 'BLOB' or v_type(i)= 'LONG'   then
        v_str :=v_column(i)||' '||v_type(i)||',';
      elsif v_type(i)= 'VARCHAR2' or  v_type(i)= 'NVARCHAR2' or v_type(i)= 'CHAR' then
        v_str :=v_column(i)||' '||v_type(i)||'('||v_length(i)||'),';
      else v_str := '该字段类型没有在代码中整理';
      end if;
      
      if i=v_column.count then
        v_str :=substr(v_str,1,length(v_str)-1);
      end if;

      UTL_FILE.PUT_LINE(file_name,v_str);
      
    end loop;

    --判断是否添加分区
    if partition_status = 'Y' then      
     
     SELECT nvl(max(column_name),'0') into partition_keywords  FROM USER_PART_KEY_COLUMNS
      where object_type = 'TABLE'  and name=v_tables;
      if partition_keywords != '0' then
         UTL_FILE.PUT_LINE(file_name,')partition by range ('||partition_keywords||')(');
         
         open part_name for select partition_name,high_value  from user_tab_partitions
         where table_name = v_tables;
         v_str := null;
         loop
           fetch part_name into partition_name,high_value;            
           if part_name%notfound then
             --去掉最后逗号
             v_str :=substr(v_str,1,length(v_str)-1);
             UTL_FILE.PUT_LINE(file_name,v_str);
             exit;
           end if;         
           UTL_FILE.PUT_LINE(file_name,v_str);
           v_str :='partition '||partition_name||' values less than ('||high_value||'),';
         end loop;
      end if;
    end if;

    UTL_FILE.PUT_LINE(file_name,');');
    UTL_FILE.PUT_LINE(file_name,'-------------------------------------------------------------');

    end loop;
   
    UTL_FILE.fclose_all;
end;