最近,在写一个代码工具,从数据读取表生成实体Bean、SQLMap,需要获取到数据库的元信息,为了适应不同的数据库,需要针对每一种数据库实现一种获取元信息的方式,这里是 Oracle10g元信息的获取方式:
 
spool get_tab_ddl. log 

prompt 
prompt Creating function GET_TAB_DDL 
prompt ============================= 
prompt 
create  or  replace function get_tab_ddl(p_tab_name varchar2)  return clob  is 

         --返回表结构 
        v_result clob; 

         --表所在表空间 
        v_tablespace_name varchar2(200); 

         --表模式 
        v_logging varchar2(100); 

         --是否分区表 
        v_partitioned varchar2(100); 

         --前导空格 
        v_block varchar2(100) :=  '     '

         --区域ddl 
        v_ddl varchar2(32767); 

         --主键索引 
        v_pk_index varchar2(200); 

begin 

         --表信息 
         select t.tablespace_name, 
                     decode(t.logging,  'YES''LOGGING''NO LOGGING') logging, 
                     t.partitioned 
             into v_tablespace_name, 
                     v_logging, 
                     v_partitioned 
             from user_tables t 
          where t.table_name =  upper(p_tab_name); 

        v_result :=  '-- Create table' || chr(13) || 'create table ' || 
                                upper(p_tab_name) || '(' || chr(13); 

        --列信息 
        for col in (select '"' || c.COLUMN_NAME || '"' || ' ' || c.DATA_TYPE || 
                                             decode(c.DATA_TYPE, 
                                                            'VARCHAR2'
                                                            '(' || c.DATA_LENGTH || ') '
                                                            'CHAR'
                                                            '(' || c.DATA_LENGTH || ') '
                                                            decode(c.DATA_PRECISION, 
                                                                         null
                                                                         ' '
                                                                         decode(c.DATA_SCALE, 
                                                                                        0, 
                                                                                        '(' || c.DATA_PRECISION || ') '
                                                                                        '(' || c.DATA_PRECISION || ',' || 
                                                                                        c.DATA_SCALE || ') '))) || 
                                             decode(c.NULLABLE, 'Y''NULL ''NOT NULL ') tab_col, 
                                             c.data_default 
                                    from user_tab_columns c 
                                 where c.table_name = upper(p_tab_name) 
                                 order by c.column_id) 
        loop 
         
                if col.data_default is null 
                then 
                 
                        v_result := v_result || v_block || col.tab_col || ',' || chr(13); 
                 
                else 
                 
                        v_result := v_result || v_block || col.tab_col || 'DEFAULT ' || 
                                                rtrim(col.data_default, chr(10)) || ',' || chr(13); 
                 
                end if
         
        end loop; 

        v_result := rtrim(rtrim(v_result, chr(13)), ',') || chr(13) || ') ' || 
                                v_logging || chr(13) || 'tablespace ' || v_tablespace_name || ';' || 
                                chr(13); 

        -- Add comments to the table 
        select decode(m.comments, 
                                    null
                                    v_result, 
                                    v_result || '-- Add comments to the table' || chr(13) || 
                                    'comment on table ' || m.table_name || ' is ''' || m.comments || 
                                    ''';' || chr(13)) 
            into v_result 
            from user_tab_comments m 
         where m.table_name = upper(p_tab_name); 

        -- Add comments to the columns 
        v_ddl := ''

        for com in (select 'comment on column ' || c.table_name || '.' || 
                                             c.column_name || ' is ''' || c.comments || ''';' || 
                                             chr(13) col_com 
                                    from user_col_comments c 
                                 where c.table_name = upper(p_tab_name) 
                                     and c.comments is not null
        loop 
         
                v_ddl := v_ddl || com.col_com; 
         
        end loop; 

        if v_ddl is not null 
        then 
         
                v_result := v_result || '-- Add comments to the columns ' || chr(13) || 
                                        v_ddl; 
         
        end if

        -- Create/Recreate primary, unique and foreign key constraints 
        v_ddl            := ''
        v_pk_index := ''

        for con in (select c.constraint_name, 
                                             c.constraint_type, 
                                             c.search_condition, 
                                             c.r_constraint_name, 
                                             c.index_name, 
                                             decode(c.delete_rule, 'CASCADE'' on delete cascade''') delete_rule, 
                                             i.tablespace_name 
                                    from user_constraints c 
                                    left join user_indexes i on (c.index_name = i.index_name) 
                                 where c.table_name = upper(p_tab_name) 
                                 order by c.constraint_type) 
        loop 
         
                --pk 
                if con.constraint_type = 'P' 
                then 
                 
                        v_pk_index := con.index_name; 
                 
                        v_ddl := v_ddl || 'alter table ' || upper(p_tab_name) || 
                                         ' add constraint ' || con.constraint_name || 
                                         ' primary key('
                 
                        for pk in (select c.column_name 
                                                 from user_cons_columns c 
                                                where c.constraint_name = con.constraint_name 
                                                order by c.position) 
                        loop 
                         
                                v_ddl := v_ddl || pk.column_name || ','
                         
                        end loop; 
                 
                        v_ddl := rtrim(v_ddl, ',') || ')' || chr(13) || 'using index' || 
                                         chr(13) || 'tablespace ' || con.tablespace_name || ';' || 
                                         chr(13); 
                 
                end if
         
                --fk 
                if con.constraint_type = 'R' 
                then 
                 
                        v_ddl := v_ddl || 'alter table ' || upper(p_tab_name) || 
                                         ' add constraint ' || con.constraint_name || 
                                         ' foreign key('
                 
                        for spk in (select c.column_name 
                                                    from user_cons_columns c 
                                                 where c.constraint_name = con.constraint_name 
                                                 order by c.position) 
                        loop 
                         
                                v_ddl := v_ddl || spk.column_name || ','
                         
                        end loop; 
                 
                        v_ddl := rtrim(v_ddl, ',') || ')'
                 
                        select distinct (v_ddl || ' references ' || c.table_name || '('
                            into v_ddl 
                            from user_cons_columns c 
                         where c.constraint_name = con.r_constraint_name; 
                 
                        for tfk in (select c.column_name 
                                                    from user_cons_columns c 
                                                 where c.constraint_name = con.r_constraint_name 
                                                 order by c.position) 
                        loop 
                         
                                v_ddl := v_ddl || tfk.column_name || ','
                         
                        end loop; 
                 
                        v_ddl := rtrim(v_ddl, ',') || ')' || con.delete_rule || ';' || 
                                         chr(13); 
                 
                end if
         
                --check 
                if (con.constraint_type = 'C' and 
                     instr(con.search_condition, 'NOT NULL') = 0) 
                then 
                 
                        v_ddl := v_ddl || 'alter table ' || upper(p_tab_name) || 
                                         ' add check (' || rtrim(con.search_condition, chr(10)) || ');' || 
                                         chr(13); 
                 
                end if
         
        end loop; 

        if v_ddl is null 
        then 
         
                v_result := v_result || chr(13); 
         
        else 
         
                v_result := v_result || 
                                        '-- Create/Recreate primary, unique and foreign key constraints ' || 
                                        chr(13) || v_ddl; 
         
        end if

        -- Create/Recreate indexes 
        v_ddl := ''

        for idx in (select t.index_name, 
                                             t.table_name, 
                                             decode(t.uniqueness, 
                                                            'NONUNIQUE'
                                                            ' '
                                                            ' ' || t.uniqueness || ' ') uniqueness, 
                                             t.tablespace_name 
                                    from user_indexes t 
                                 where t.table_name = upper(p_tab_name) 
                                     and t.index_type <> 'LOB' 
                                     and t.index_name <> v_pk_index 
                                     and instr(t.index_name, 'SYS_C00') <> 1) 
        loop 
         
                v_ddl := v_ddl || 'create' || idx.uniqueness || 'index ' || 
                                 idx.index_name || ' on ' || idx.table_name || '('
         
                for i_col in (select c.column_name || ' ' || c.descend column_name 
                                                from user_ind_columns c 
                                             where c.index_name = idx.index_name 
                                             order by c.column_position) 
                loop 
                 
                        v_ddl := v_ddl || i_col.column_name || ','
                 
                end loop; 
         
                v_ddl := rtrim(v_ddl, ',') || ')' || ' tablespace ' || 
                                 idx.tablespace_name || ';' || chr(13); 
         
        end loop; 

        if v_ddl is null 
        then 
         
                v_result := v_result || chr(13); 
         
        else 
         
                v_result := v_result || '-- Create/Recreate indexes' || chr(13) || 
                                        v_ddl; 
         
        end if

        return(v_result); 

end get_tab_ddl; 



spool off 
 
select s.column_name, 
             s.data_type column_type, 
             decode(s.data_type, 
                             'VARCHAR2'
                            s.data_length, 
                             'CHAR'
                            s.data_length, 
                            s.data_precision) column_length, 
             c.comments column_comments, 
             decode(s.nullable,  'Y''N''N''Y') isnotnull, 
             decode(l.column_name,  null'N''Y') ispk 
     from user_tab_columns s 
     left  join user_col_comments c 
         on (s.column_name = c.column_name  and s.table_name = c.table_name) 
     left  join user_constraints t 
         on (s.table_name = t.table_name  and t.constraint_type =  'P'
     left  join user_cons_columns l 
         on (s.table_name = l.table_name  and 
             t.constraint_name = l.constraint_name  and 
             s.column_name = l.column_name) 
where s.table_name = 表名称 
order  by s.column_id