导出数据库表结构对象

程序员文章站 2022-03-15 15:17:07

...

导出数据库表结构对象第一步，便是要导出对应的数据表内容，首先给出一段导出表字段描述的SQL：

DECLARE
  CURSOR cols(p_table_name VARCHAR2, p_owner VARCHAR2) IS
    SELECT tc.column_id,
           tc.column_name,
           cc.comments,
           tc.data_type,
           tc.data_length,
           decode(tc.nullable, 'N', '', '√') AS nullable,
           tc.data_default
      FROM dba_tab_columns /*sys.all_tab_columns*/ tc, dba_col_comments /*sys.all_col_comments*/ cc
     WHERE tc.table_name = upper(p_table_name)
       AND tc.owner = upper(p_owner)
       AND tc.owner = cc.owner
       AND tc.table_name = cc.table_name
       AND tc.column_name = cc.column_name
     ORDER BY column_id;

  lv_table_name VARCHAR2(30) := 'table_name';
  lv_owner      VARCHAR2(10) := 'user';
BEGIN
  dbms_output.put_line('<html><table><thead><th>序号</th><th>字段名称</th><th>字段描述</th><th>字段类型</th><th>长度</th><th>允许空</th><th>缺省值</th></thead>');

  FOR c IN cols(lv_table_name, lv_owner) LOOP
    dbms_output.put_line('<tr><td>' || c.column_id || '</td><td>' || c.column_name || '</td><td>' ||
                         c.comments || '</td><td>' || c.data_type || '</td><td>' || c.data_length ||
                         '</td><td>' || c.nullable || '</td><td>' || c.data_default ||
                         '</td></tr>');
  END LOOP;
  dbms_output.put_line('</table></br></html>');
END;

接下来再对此SQL进行一下优化，使其导出的内容更加具体一点，包含表设计具体内容：

DECLARE
  CURSOR cols(p_table_name VARCHAR2, p_owner VARCHAR2) IS
    SELECT tc.column_id,
           tc.column_name,
           cc.comments,
           tc.data_type,
           tc.data_length,
           decode(tc.nullable, 'N', '', '√') AS nullable,
           tc.data_default
      FROM dba_tab_columns /*sys.all_tab_columns*/ tc, dba_col_comments /*sys.all_col_comments*/ cc
     WHERE tc.table_name = upper(p_table_name)
       AND tc.owner = upper(p_owner)
       AND tc.owner = cc.owner
       AND tc.table_name = cc.table_name
       AND tc.column_name = cc.column_name
     ORDER BY column_id;

  lv_table_name VARCHAR2(30) := 'table_name';
  lv_owner      VARCHAR2(10) := 'user';
BEGIN
  dbms_output.put_line('<html><table><thead><th>序号</th><th>字段名称</th><th>字段描述</th><th>字段类型</th><th>长度</th><th>允许空</th><th>缺省值</th></thead>');

  FOR c IN cols(lv_table_name, lv_owner) LOOP
    dbms_output.put_line('<tr><td>' || c.column_id || '</td><td>' || c.column_name || '</td><td>' ||
                         c.comments || '</td><td>' || c.data_type || '</td><td>' || c.data_length ||
                         '</td><td>' || c.nullable || '</td><td>' || c.data_default ||
                         '</td></tr>');
  END LOOP;
  dbms_output.put_line('</table></br></html>');
END;

好了，这样一张表的导出，基本上已经符合我们的需求了，那么，接下来就是需要给他加点样式，让后导出整个数据库用户对应的所有表结构了：

/*导出 TABLE 数据库对象的描述*/
/*下面是html版本,把DBMS输出存为doc或htm或xls文件即可.*/
/*注意对不同Schema需要修改此owner*/
DECLARE
  CURSOR tabs IS
    SELECT tab.table_name,
           com.comments,
           tab.owner,
           tab.tablespace_name AS space_name,
           tab.status AS status,
           decode(tab.logging, 'NO', '', '√') AS logged,
           tab.num_rows AS num_rows,
           round(((tab.blocks * 8192) / 1024 / 1024 / 1024), 8) AS val,
           to_char(tab.last_analyzed, 'yyyy/mm/dd hh24:mi:ss') AS last_analyzed,
           decode(tab.partitioned, 'NO', '', '√') AS partitioned
      FROM dba_tables /*sys.ALL_ALL_TABLES*/ tab, dba_tab_comments /*sys.all_tab_comments*/ com
     WHERE tab.table_name = com.table_name
       AND tab.owner = com.owner
       AND tab.table_name NOT LIKE 'TOAD_%'
       AND tab.table_name NOT LIKE 'TEMP_%'
       AND tab.table_name NOT LIKE 'TEST_%'
       AND tab.table_name NOT LIKE 'TMP_%'
       AND tab.table_name NOT LIKE '%MLOG_%'
       AND tab.table_name NOT LIKE '%SQLN_%'
       AND tab.table_name NOT LIKE '%$%'
       AND tab.table_name NOT LIKE '%PLSQL_%'
       AND tab.table_name NOT LIKE '%_BK%'
       AND tab.table_name NOT LIKE '%BIG_DEL%'
       AND tab.table_name NOT LIKE '%_091021%'
       AND tab.table_name NOT LIKE '%BAK%'
       AND tab.table_name NOT LIKE '%MY_%'
       AND tab.table_name NOT LIKE '%WKZ%'
       AND tab.table_name NOT LIKE 'BIG%'
       AND tab.table_name NOT IN ('TBLMITEMCCLASS2MITEM_BF080920',
                                  'TBLPARTDEVICE_091024',
                                  'TBLUGU20111229BACK',
                                  'TBLIQCCHECKCRITERIONTMP1028',
                                  'QUEST_SL_TEMP_EXPLAIN1',
                                  'TEST',
                                  'MIS2012TMP',
                                  'WKZTEMP0504')
       AND (tab.owner = upper('user'))
     ORDER BY tab.table_name;

  CURSOR cols(p_table_name VARCHAR2, p_owner VARCHAR2) IS
    SELECT tc.column_id,
           tc.column_name,
           cc.comments,
           tc.data_type,
           tc.data_length,
           decode(tc.nullable, 'N', '', '√') AS nullable,
           tc.data_default
      FROM dba_tab_columns /*sys.all_tab_columns*/ tc, dba_col_comments /*sys.all_col_comments*/ cc
     WHERE tc.table_name = p_table_name
       AND tc.owner = p_owner
       AND tc.owner = cc.owner
       AND tc.table_name = cc.table_name
       AND tc.column_name = cc.column_name
     ORDER BY column_id;

  v_css        VARCHAR2(4000);
  v_tab_header VARCHAR2(2000);
  v_tab_footer VARCHAR2(20);
  v_number     NUMBER DEFAULT 1;
BEGIN

  v_css := '<html>
   <style type="text/css">
        h2
        {
            font-size: 15pt;
            color: #4F81BD;
            font-family: "微软雅黑";
        }
        h5
        {
            font-size: 13pt;
            color: #4F81BD;
            font-family: "微软雅黑";
        }
        .tn
        {
            font-size: 13pt;
            color: white;
            background-color:#4F81BD;
            font-family: "微软雅黑";
        }
        table
        {
            font-size: 12pt;
            font-family: "微软雅黑";
            width: 100%;
            border-collapse: collapse;
            border: none;
            padding-left: 5pt;
            mso-border-alt: solid #4F81BD 1.0pt;
            mso-padding-alt: 0cm 5.4pt 0cm 5.4pt;
        }
        thead
        {
            mso-yfti-irow: 0;
            font-family: "微软雅黑";
            mso-yfti-firstrow: yes;
        }
        th
        {
            font-family: "微软雅黑";
            font-size: 12pt;
            padding: 6pt 6pt 6pt 6pt;
            border-top: solid #4F81BD 1.0pt;
            border-left: solid #4F81BD 1.0pt;
            border-bottom: solid #4F81BD 3.0pt;
            border-right: solid #4F81BD 1.0pt;
        }
        tr
        {
            mso-yfti-irow: 1;
        }
        td
        {
            text-align: justify;
            text-justify: inter-ideograph;
            font-size: 10pt;
            font-family: "微软雅黑";
            margin: 0pt;
            padding: 4pt 4pt 4pt 4pt;
            border-top: none;
            border-left: solid #4F81BD 1.0pt;
            border-bottom: solid #4F81BD 1.0pt;
            border-right: solid #4F81BD 1.0pt;
        }
    </style>';
  dbms_output.put_line(v_css);
  dbms_output.put_line('<table><thead><th>序号</th><th>表名</th><th>描述</th><th>表空间</th><th>状态</th><th>日志</th><th>表行数</th><th>表容量(G)</th><th>上次分析时间</th><th>是否分区</th></thead>');

  FOR t IN tabs LOOP
    dbms_output.put_line('<tr><td align=center>' || lpad(v_number, 3, 0) ||
                         '</td><td><a class="tn" href="#' || t.table_name || '">' || t.table_name ||
                         '</a></td><td>' || t.comments || '</td><td>' || t.space_name ||
                         '</td><td>' || t.status || '</td><td>' || t.logged || '</td><td>' ||
                         t.num_rows || '</td><td>' || t.val || '</td><td>' || t.last_analyzed ||
                         '</td><td>' || t.partitioned || '</td></tr>');
    v_number := v_number + 1;
  END LOOP;

  v_tab_footer := '</table></br>';
  dbms_output.put_line(v_tab_footer);
  v_tab_header := '<table><thead><th>序号</th><th>字段名称</th><th>字段描述</th><th>字段类型</th><th>长度</th><th>允许空</th><th>缺省值</th></thead>';

  FOR t IN tabs LOOP
    dbms_output.put_line('<h2><a name="' || t.table_name || '">' || t.table_name || '</a></h2>');
    dbms_output.put_line('<h5>表描述:<font color="black">' || t.comments || '</font></h5>');
    dbms_output.put_line(v_tab_header);
  
    FOR c IN cols(t.table_name, t.owner) LOOP
      dbms_output.put_line('<tr><td>' || c.column_id || '</td><td>' || c.column_name ||
                           '</td><td>' || c.comments || '</td><td>' || c.data_type || '</td><td>' ||
                           c.data_length || '</td><td>' || c.nullable || '</td><td>' ||
                           c.data_default || '</td></tr>');
    END LOOP;
  
    dbms_output.put_line(v_tab_footer);
  END LOOP;
  dbms_output.put_line('</html>');
END;

这样便能输出带样式的数据库表结构对象了。

注意：

一般来说，一个用户的拥有表有时会很多的，而Oracle数据库的输出限制是1000000 bytes，所以常会有超出限制的情况发生，当超出限制时，只需要再输出内容前执行此语句即可：

dbms_output.enable(buffer_size => NULL);

此语句的作用就是，使缓存大小限制失效。

导出数据库表结构对象

sql脚本查询数据库表,数据,结构,约束等操作的方法

mysqldump只导出数据或者只导出表结构_MySQL

关于Oracle数据库迁移（跨版本）以及空表不导出

wordpress-4.4.1 数据库表结构解析，wordpress数据库结构

php mysql数据的导入导出,数据表结构的导入导出_PHP教程

mysql mysqldump只导出表结构或只导出数据的实现方法

mysql mysqldump只导出表结构或只导出数据的实现方法

Navicat for MySQL导出表结构脚本的简单方法

Navicat for MySQL导出表结构脚本的简单方法

sql脚本查询数据库表,数据,结构,约束等操作的方法