将Oracle表里的数据导出生成insert into 脚本

1. create or replace procedure print_insert(v_tname varchar2,v_cbatch number default 0)     
2. /*     
3. v_tname 要输出sql的表名 作者zh2000@hotmail.com     
4. v_cbatch 输出commit间隔 博客:http://blog.itpub.net/xzh2000     
5. */     
6. as     
7. /* 声明动态游标变量 */     
8. type cur_alldata is ref cursor;     
9. l_alldata cur_alldata;     
10. /* 将单行数据写入v_row*/     
11. v_sql varchar2(3999);     
12. v_row varchar2(3999);     
13. /* 函数的前向声明 */     
14. function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2;     
15. /* 格式化数据输出 */     
16. function formatdata(v_tname varchar2,v_row varchar2) return varchar2     
17. as     
18. v_ldata varchar2(32765);     
19. v_rdata varchar2(32765);     
20. v_cname varchar2(3999);     
21. v_instr number(10);     
22. v_count number(6);     
23. begin     
24. v_instr := instr(v_row,'(');     
25. v_ldata := substr(v_row,1,v_instr);     
26. v_rdata := substr(v_row,v_instr+1);     
27. v_instr := instr(v_rdata,')');     
28. v_rdata := substr(v_rdata,1,v_instr-1);     
29.      
30. v_count := 0;     
31. loop     
32. v_instr := instr(v_rdata,',');     
33. exit when v_instr = 0;     
34.      
35. v_cname := substr(v_rdata,1,v_instr-1);     
36. v_rdata := substr(v_rdata,v_instr+1);     
37. v_count := v_count + 1;     
38. /* 格式化不同的数据类型 */     
39. v_cname := formatfield(v_tname,v_cname,v_count);     
40.      
41. /* 将处理后的字段值加入v_ldata */     
42. if v_count = 1 then     
43. v_ldata := v_ldata||v_cname;     
44. else     
45. v_ldata := v_ldata||','||v_cname;     
46. end if;     
47.      
48. end loop;     
49.      
50. /* 添加最后一个字段的值 */     
51. if v_count = 1 then     
52. v_ldata := v_ldata||formatfield(v_tname,v_rdata,v_count+1)||');';     
53. else     
54. v_ldata := v_ldata||','||formatfield(v_tname,v_rdata,v_count+1)||');';     
55. end if;     
56. return v_ldata;     
57. end;     
58. /* 针对不同的数据类型进行处理 */     
59. function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2     
60. as     
61. v_name varchar2(3999);     
62. v_type varchar2(99);     
63. begin     
64. select coltype into v_type from col where tname = upper(v_tname) and colno = v_colno;     
65. if v_type = 'DATE' then     
66. v_name := 'to_date('||''''||v_cname||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';     
67. elsif v_type = 'VARCHAR2' then     
68. v_name := ''''||v_cname||'''';     
69. else     
70. v_name := v_cname;     
71. end if;     
72. return v_name;     
73. end;     
74. /* 求输入表的字段列表 */     
75. function getfields(v_tname varchar2) return varchar2     
76. as     
77. v_fields varchar2(3999);     
78. begin     
79. for cur_fname in (select cname,coltype from col where tname = upper(v_tname) order by colno) loop     
80. if v_fields is null then     
81. v_fields := 'nvl('||cur_fname.cname||','||''''||'0'||''''||')';     
82. else     
83. v_fields := v_fields||'||'',''||'||'nvl('||cur_fname.cname||','||''''||'0'||''''||')';     
84. end if;     
85. end loop;     
86. v_fields := 'select '||''''||'insert into '||v_tname||' values('||''''||'||'||v_fields||'||'||''''||')'||''''||' from '||v_tname;     
87. return v_fields;     
88. end;     
89. begin     
90. execute immediate 'alter session set nls_date_format='||''''||'yyyy-mm-dd hh24:mi:ss'||'''';     
91. dbms_output.put_line(' *** XX向你问好！ *** ');     
92.      
93. v_sql := getfields(v_tname);     
94. --dbms_output.put_line(v_sql);     
95. open l_alldata for v_sql;     
96. loop     
97. fetch l_alldata into v_row;     
98. exit when l_alldata%notfound;     
99. --dbms_output.put_line(v_row);     
100.      
101. dbms_output.put_line(formatdata(v_tname,v_row));     
102. if mod(l_alldata%rowcount,v_cbatch) = 0 then     
103. dbms_output.put_line('commit;');     
104. end if;     
105. end loop;     
106.      
107. close l_alldata;     
108. end;  

set   pagesize   0;
set   linesize   100;
spool   c:/data.sql
select   'insert   into   tblinfo   values
( ' ' '||FID|| ' ' ', ' ' '||FNAME|| ' ' ', '||nvl(FAGE,0) '); '
from   tblinfo;    
spool   off;

提供一Perl程序   record2insert.pl
实施步骤
1、先到www.activeperl.com去下载一个activeperl，免费的
2、安装perl
3、到/Perl/bin下面执行   ppm
4、出现PPM提示符如下
      D:/Perl/bin> PPM
      PPM   interactive   shell   (2.1.6)   -   type   'help '   for   available   commands.
      PPM>
5、在PPM提示符下执行如下命令，安装数据库连接模块DBI,DBD
      PPM> install   dbi
      PPM> install   dbd-oracle
6、执行下面的perl程序即可，其中用户名和密码，以及连接字符串自己改改
      再命令窗口直接键入如下命令格式，该程序无需编译，祝你好运
      record2insert.pl   > 输出文件名.sql
7、该程序不支持RAW,LONG和LOB类型的字段


#!/usr/bin/perl   -w
#author atgc
use   DBI;
$dbh     =   DBI-> connect( "DBI:Oracle:连接名 ", '用户名 ', '密码 ');
$sql     =   "select   table_name   from   user_tables ";
$sth1   =   $dbh-> prepare($sql);
$sth1-> execute();
while($table_name   =   $sth1-> fetchrow)
{
$sql     =   "select   *   from   $table_name ";
$sth2   =   $dbh-> prepare($sql);
$sth2-> execute();
while(@field   =   $sth2-> fetchrow)
{
$value= " ";
foreach   $ele(@field){$ele.= " ";$ele=~s/ '/ ' '/;$value.=$ele. " ', ' ";}
$value=~s//,/ '$//;
print   "insert   into   $table_name   values   ( ' ". "$value/);/n ";
}
print   "/n ";
$sth2-> finish();
}
$sth1-> finish();
$dbh-> disconnect();

 

下面介绍Oracle导出SQL脚本的实现方法。本方法使用spool。

a. 获取单个的建表和建索引的语法

1.set heading off;

2.set echo off;

3.Set pages 999;

4.set long 90000;

5.

6.spool DEPT.sql

7.select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

8.select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

9.spool off;

b.获取一个SCHEMA下的所有建表和建索引的语法，以scott为例：

1.set pagesize 0

2.set long 90000

3.

4.set feedback off

5.

6.set echo off

7.spool scott_schema.sql

8.connect scott/tiger;

9.SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)

10.         FROM USER_TABLES u;

11.SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)

12.         FROM USER_INDEXES u;

13.spool off;

c.       获取某个SCHEMA的建全部存储过程的语法

1.connect brucelau /brucelau;

2.

3.spool procedures.sql

4.

5.select

6.       DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name)

7.from

8.       user_objects u

9.

10.where

11.       object_type = 'PROCEDURE';

12.

13.spool off;

另：

1.dbms_metadata.get_ddl('TABLE','TAB1','USER1')

三个参数中，第一个指定导出DDL定义的对象类型（此例中为表类型），第二个是对象名（此例中即表名），第三个是对象所在的用户名。

要自己写程序进行Oracle导出SQL脚本，执行一段SQL，让SQL查询结果就是我们想要的SQL脚本：

如下：

1.select

2.            'INSERT INTO B_STATTEMPLATE ( N_ID,C_NAME, C_KBH, N_PRINT, N_TYPE, N_APP, N_VALID ) '

3.            || 'Values (' || To_Char(N_ID) ||',''' || C_NAME || ''', ''' || C_KBH || ''', '

4.            || To_Char( N_PRINT ) || ', ' || To_Char( N_TYPE ) || ', ' || '0, 0 );'

5. from b_stattemplate

6.

将查询结果保存起来就可以了！

Oracle导出SQL脚本这样就实现了