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

2016-05-27 iugclub
oracle存储过程实现导出表结构
 
   要将数据库中的表结构全部倒出来,有分区表和非分区表,涉及到的字段有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)||','; 
      else 
        v_str :=v_column(i)||' '||v_type(i)||'('||v_length(i)||'),'; 
      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;