欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

抓取oracle建表语句的代码实例教程

程序员文章站 2022-06-17 19:58:41
抓取oracle建表语句的代码实例教程 set echo off; set feedback off; set pagesize 0; set trimspool on; set lin...

抓取oracle建表语句的代码实例教程

set echo off;
set feedback off;
set pagesize 0;
set trimspool on;
set linesize 10000;
set heading off;
set term off;
set showmode off;
set verify off;

drop table tmp_tabsql;
 
create table tmp_tabsql (
       seq        number,
       table_name varchar2(50), 
       table_sql varchar2(4000)
);

declare
    v_notparttable varchar2(1000):= '&2';
    --v_sql clob;
    v_parttype varchar2(20);
    v_partcolumn varchar2(50);
    v_subparttype varchar2(50);
    v_subpartsql varchar2(4000);
    v_seq number := 10;
begin
  for v_cur in (
    select table_name,partitioned,t.tablespace_name,t.pct_free, t.logging, t.cache from user_tables t 
    where partitioned = 'yes' 
  ) loop
        v_seq := v_seq + 1;
        insert into tmp_tabsql(seq,table_name,table_sql) values(v_seq, v_cur.table_name, 'create table ' || v_cur.table_name || '(' );
        --columns
        for v_column in (
            select case when regexp_like (column_name,'^\d+$') then '"'||column_name||'"' else column_name end column_name, data_type,data_length, data_precision,data_scale,column_id from user_tab_columns 
            where table_name = v_cur.table_name 
            order by column_id asc
       ) loop
            v_seq := v_seq + 1;
            insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name, 
                    case when v_column.column_id <> 1 then '    ,' end
                      || v_column.column_name || ' ' || v_column.data_type
                         || case v_column.data_type when 'timestamp(6)' then '' when 'date' then ''
                         		 when 'clob' then '' when 'blob' then ''
                             when 'number' then 
                                  case 
                                  		 when v_column.data_scale is not null and v_column.data_precision is not null then '('||v_column.data_precision||','||v_column.data_scale||')'
                                       when v_column.data_precision is not null then '(' || v_column.data_precision|| ')'
                                  end    
                             else 
                                  case when v_column.data_length  is not null then '(' || v_column.data_length || ')' end
                             end
                   );
       end loop;
       
       v_seq := v_seq + 1;
       insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name, ')');
       
       if v_cur.partitioned = 'yes' then
           select t.partitioning_type, t.subpartitioning_type , tk.column_name
           into v_parttype, v_subparttype, v_partcolumn
                   from user_part_tables t, user_part_key_columns tk
                   where t.table_name = tk.name
                   and t.table_name = v_cur.table_name
           ;  
           v_seq := v_seq + 1;
           insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name,
                  ' pctfree 0 nologging partition by ' || v_parttype ||'('||v_partcolumn||')' );
           -- subpartition
           v_subpartsql := '';
           if v_subparttype <> 'none' then
                select t.column_name into v_partcolumn from user_subpart_key_columns t
                where t.name = v_cur.table_name
                ;
                v_subpartsql := 'subpartition by '|| v_subparttype ||'('|| v_partcolumn ||') subpartition template (' ||chr(10);
                for v_tmp in (select t.subpartition_name, t.high_bound, t.subpartition_position 
                    from user_subpartition_templates t 
                    where table_name =  v_cur.table_name
                    order by t.subpartition_position asc
                )  loop
                    v_subpartsql := v_subpartsql || case when v_tmp.subpartition_position > 1 then  chr(10) ||'   ,' end
                                 || 'subpartition '|| v_tmp.subpartition_name || ' values (' || v_tmp.high_bound || ')'
                                 ;
                end loop;  
                v_subpartsql := v_subpartsql || chr(10) || ')' ||chr(10);
           end if;
           
           v_seq := v_seq + 1;
           insert into tmp_tabsql(seq, table_name, table_sql) values(v_seq, v_cur.table_name,
              v_subpartsql || '(partition p2011010100 values less than (to_date(''2011-01-01'',''yyyy-mm-dd'')));' 
           );
       else
           v_seq := v_seq + 1;
           insert into tmp_tabsql(seq,table_name,table_sql) values(v_seq, v_cur.table_name, 
                          ' tablespace ' || v_cur.tablespace_name 
                          || ' pctfree ' || v_cur.pct_free 
                          || case trim(v_cur.logging) when 'no' then ' nologging' end
                          || case trim(v_cur.cache) when 'y' then ' cache' end
                          ||';'
                         )
                     ;
       end if;
       commit;
  end loop;
end;
/

spool &1
select table_sql from (
	select 'set echo off;' table_sql,0 seq from dual
	union all
	select 'set feedback off;', 1 seq from dual
	union all
	select table_sql,seq from tmp_tabsql
	union all
	select 'exit;',9999999999 seq from dual
) order by seq asc;
spool off;
drop table tmp_tabsql purge;
exit;

建存放sql的表

-- create table
create table tmp_tabsql
(
  seq        integer,
  table_name varchar2(100),
  table_sql  varchar2(3000)
)
 

提取sql:select table_sql from tmp_tabsql order by seq

抓取oracle建表语句的代码实例教程