当前位置:  数据库>oracle

获取Oracle10g表元数据

    来源: 互联网  发布时间:2017-04-17

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

最近,在写一个代码工具,从数据读取表生成实体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

























































































































































































































































































    
 
 

您可能感兴趣的文章:

  • 关系型数据库元数据获取工具 db-meta
  • sql语句获取数据库服务器上所有数据库的名字
  • 如何从gstreamer中获取一帧数据
  • 如何获取数据包?如何读取包内的信息(如:ip 地址)?
  • socket 通讯问题!客户端获取服务端数据问题,死活都是0!
  • sql server 使用ntile获取数据的例子
  • 获取 CSV 数据工具 csonv.js
  • 怎样在JAVA里实现获取指定服务器上所有可用数据库的列表??
  • 菜鸟问题:如何让jsp来获取与之关联的java 数据(赚分了)
  • 某些IE客户端无法通过WCF获取数据的解决方法
  • 哥们! 知不知道有什么方法能获取数据库中的表名!
  • 数据库中随机获取10条数据的方法
  • 如果获取数据库中的表名!
  • java从输入流中获取数据并返回字节数组示例
  • 用函数怎样获取IP数据包?
  • TCP连接如何获取当前Sender buffer中已经缓存的数据量
  • MSSQL数据库获取TEXT字段的内容长度
  • 怎样获取数据库中的字段值?
  • 获取数据库中两个时间字段的相差天数及ABS/DATEDIFF函数应用
  • sql语句获取分组排序后数据
  • 如何获取Linux下的Oracle
  • shell如何获取oracle错误码 在线等
  • Oracle存储过程如何返回一个结果集&如何获取
  • 求教:shell 脚本怎么获取ORACLE存储过程的返回值?
  • 如何获取Linux及Oracle版本信息
  • 如何获取java连接oracle的驱动?
  • Oracle最大日期获取方法
  • Oracle parameter可能值获取方法
  • oracle获取当前用户表、字段等详细信息SQL
  • Oracle 查询表信息获取表字段及字段注释
  • Oracle 获取上周一到周末日期的查询sql语句
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Python获取网页编码的方法及示例代码
  • linux不用命令方式读文件获取网络流量,如何使用C函数调用获取网络流量信息?
  • java Servlet获取和设置cookie实例代码
  • 关于获取在jsp上获取客户端时间的问题
  • Python通过正则表达式获取,去除(过滤)或者替换HTML标签的几种方法
  • 关于Ganglia中的gmond组件收集到的资源信息如何获取(如何获取telnet后返回的信息)
  • php获取访客ip地址原理及提供七段代码供参考
  • java获取系统路径字体、得到某个目录下的所有文件名、获取当前路径
  • 利用sender的Parent获取GridView中的当前行(获取gridview的值)
  • php获取本机ip地址 php获取远程IP地址
  • linux获取主机名后用gethostbyname() 不能获取主机ip
  • javascript 获取url参数的正则表达式(用来获取某个参数值)
  • 在gtk下,怎样获取系统时间??怎样获取当前的目录路径???
  • C++获取文件哈希值(hash)和获取torrent(bt种子)磁力链接哈希值
  • linux根据pid获取进程名和获取进程pid(c语言获取pid)
  • Shell获取系统时间问题
  • android 如何获取MCC/MNC控制小区广播的开启
  • 新手问题:Linux下如何获取进程占用的系统资源
  • c++如何获取当前进程名
  • 怎么通过snmp获取远程主机指定进程信息?
  • C#获取路径的多种方法


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3