Question

oracle生成insert语句的存储过程怎么写

oracle表创建存储过程生成insert语句保存到文件中，求教这个存储过程要怎么写？
就是对一张表创建一个存储过程 ，能生成出insert插入语句的，并且执行这个存储就可以保存结果到一个文件中。以便我将数据插入到一个sql server中。
表的数据量还是比较大的，有几百万条。所以想到有存储过程先把数据拿出来。
表字段有二三十个，表名：JZ31.TCL_CUSTBASEINFO
简单点就好，没有复杂的判断。。
由于个人对存储过程不太了解，看了许多例子都比较复杂，写不出来自己想要的，求教各位达人了

Answer 1

你可以用oracle的UTL_FILE包,直接将文件生成到服务器上。

下面给你个例子。测试环境windows2008 + oracle11g。

Blob，Clob类型不能导出，导出的文本适用于sqlserver.

create table Test_Gsh(
   aChar     varchar2(20),
   bNum    number,
   cDate     date);

insert into Test_Gsh(aChar, bNum, cDate)
select '这', 1, sysdate from dual
union all
select '是', 2, sysdate from dual
union all
select '测', 3, sysdate from dual
union all
select '试', 4, sysdate from dual
union all
select 'UTL_FILE', 5, sysdate from dual
union all
select '的', to_Number(null), Null from dual
union all
select '例子', to_Number(null), Null from dual;

--  建立目录
create directory UTL_FILE_GAO as 'c:\';
-- 建立过程
create or replace procedure out_File_Test as
  vFileName      varchar2(50);
  vFilePath         varchar2(30);
  out_file            utl_file.file_type;
  vBackupTime varchar2(30);
begin
  vBackupTime := to_Char(sysdate, 'yyyymmddhh24miss');
  dbms_output.put_line(vBackupTime);
  vFilePath := 'UTL_FILE_GAO';
  vFileName := 'Test_Gsh' || vBackupTime || '.sql';
  dbms_output.put_line(vFileName);
  out_file := utl_file.fopen(vFilePath,  vFileName,  'w');
  utl_file.put_line(out_file, '-- 备份表:Test_Gsh');
  utl_file.put_line(out_file, '-- 备份时间:' || vBackupTime);
  for r in (select 'insert into Test_Gsh(aChar, bNum, cDate) values (' || 
                            case when aChar is null then 'Null' else Chr(39) || aChar || Chr(39) end || ', ' ||
                            case when bNum is null then 'Null' else to_Char(bNum) end || ', ' ||
                            case when cDate is null then 'Null' else 'cast(' || Chr(39) || to_Char(cDate, 'yyyymmddhh24miss') || Chr(39) || ' as dateTime)'  end ||
                            ');' insData
                  from Test_Gsh) loop
     utl_file.put_line(out_file, r.InsData); 
  end loop;
  utl_file.FCLOSE(out_file);
exception
  when others then
     dbms_output.put_line(sqlerrm);
     utl_file.FCLOSE(out_file);
end;
/

Answer 2

其实SQL Developer、Toad、PL/SQL Developer都有导出为insert语句的SQL文件形式。
如果你非要自己写、其实就是select语句用||拼接字符串:
select 'insert into JZ31.TCL_CUSTBASEINFO(colStr,colNum)values('''||colStr||''','||colNum||')'

from JZ31.TCL_CUSTBASEINFO;
碰到日期字段还要拼好to_date函数。

追问

项目是用定时任务做取数据，不是直接用工具取。
主要是我的数据量比较大，一查询就内存溢出了。。。
上面的是存储过程里的吗，其它部分要怎么写？

追答

只是思路啊、用什么方式最终都要查询拼凑Insert语句，
数据多的时候可以分页循环取，再spool到文件里。

追问

请问你上面的语句，我如果全部字段都需要能否使用 * 号代替？

追答

不能。你这个像是在做数据同步方案、Oracle是在Linux下还是Windows下？
可以考虑用ODBC通过DBLink来同步SQL Server库。

追问

也算是数据同步吧。。Oracle不清楚什么环境 ，是远程数据库。
之前是用sql server 2000的DTS包来处理的，现在系统升级成2005，没有DTS包，只有SSIS,
因为是前人做的，迁移SSIS包出了问题。。只好用程序来取数据，就遇到了这个问题。

就是说现在方案已经定好了，用程序来跑。。。但是现在大数据量的问题，不知道要怎么解决。

追答

按这么说、只能通过循环按表主键逐段取、逐段插入了，
程序控制一次取多少行、下次在此增量上继续取，没有更巧妙的了