--by zhuyj 显示&

select ‘\&s’ from dual; --注意必须要加引号

--by zhuyj

create or replace function fun_export_data(

v_tablename in varchar2,

v_condition in varchar2

)

return varchar2 is

/*

by zhuyj 20171130

*/

cursor cur(v_tablename varchar2) is

select column_name,data_type from user_tab_columns where table_name=upper(v_tablename) order by COLUMN_ID;

tmp varchar2(4000);

msg varchar2(4000);

len number;

v_condition1 varchar2(4000);

begin

v_condition1:=' '||v_condition;

tmp:='select ''insert into '||v_tablename||' values(''';

for re in cur(v_tablename) loop

if re.data_type='CHAR' or re.data_type='DATE' or re.data_type='VARCHAR2' or re.data_type='RAW' then

tmp:=tmp||''''''||'||'||re.column_name||'||'||''''''',''';

else

tmp:=tmp||'||'||nvl(re.column_name,0)||'||'||''',''';

end if;

end loop;

len:=length(tmp);

msg:=substr(tmp,1,len-3);

if v_condition is not null then

msg:=msg||''');'' from '||v_tablename||v_condition1||';';

else

msg:=msg||''');'' from '||v_tablename||';';

end if;

return msg;

end;

--执行函数例子：

select BuildSelSql('test') from dual; --test为表名

sql脚本如下 test.sql

--应获取到sql 导出表的insert语句 by zhuyj

select 'insert into test values('''||ID||''','''||SEQ||''','||FLAG||');' from test;

set trimout on

set trimspool on

set heading off

set feedback off

set termout off

set ver off

set linesize 4000

set pagesize 0

whenever sqlerror exit

set serveroutput on

--生成拼接insert的sql

spool e:\sql\test1.sql;

select fun_export_data('test','where id in (''a'',''a2'')') from dual;

spool off

set pagesize 6 --设置页大小的行数

btitle left 'commit;' --分批提交

--生成insert的sql

spool e:\sql\test.sql;

start e:\sql\test1.sql

spool off

/

--执行insert语句：

@test.sql

执行后查看test.sql文件的内容