获取Oracle10g表元数据

最近，在写一个代码工具，从数据读取表生成实体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_idMYSQL C API 学习汇总获取MySQL 5.5表元信息相关资讯 Oracle基础教程

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）
Oracle 管理之 Linux 网络基础（02/16/2013 18:37:35）

本文评论查看全部评论（0）