要点
chr(10)–表示换行
set heading off–不显示表格头部
set pagesize 0–不分页
set linesize 800–行宽
spool filename–写入到文件中
sql语句

select 'declare'||chr(10)||' i   integer:=0;'||chr(10)||'cursor cur is select rowid as rid from '||t1 ||' WHERE DATASOURCE=''WN'' OR MODIFYSOURCE=''WN'';'||chr(10)||'begin for c in cur loop '||chr(10)||'i:=i+1;'||chr(10)|| 'update '||t1||chr(10)||'SET DATASOURCE=F_BYF_UPDATE_WN(datasource),MODIFYSOURCE=F_BYF_UPDATE_WN(MODIFYSOURCE),REMARK=F_BYF_UPDATE_WN(REMARK) WHERE rowid=c.rid;'||chr(10)|| ' if mod(i,200)=0 then'||chr(10)|| '  commit;'||chr(10)||'end if;'||chr(10)||' if mod(i,20000)=0 then'||chr(10)||'  dbms_lock.sleep(40);'||chr(10)||' if i>30000 then'||chr(10)|| ' dbms_lock.sleep(40);'||chr(10)||' end if;'||chr(10)||' end if;'||chr(10)|| ' if i>500000 then'||chr(10)||'  exit;'||chr(10)||' end if;'||chr(10)||' end loop;'||chr(10)||'  commit;'||chr(10)||'end;'||chr(10)||'/'||chr(10)||'--------'||t1||'-------------' from t1;

效果

declare
 i      integer:=0;
cursor cur is select rowid as rid from FUND294
 WHERE DATASOURCE='WN' OR MODIFYSOURCE='WN';
begin for c in cur loop
i:=i+1;
update FUND294
SET DATASOURCE=F_BYF_UPDATE_WN(datasource),MODIFYSOURCE=F_BYF_UPDATE_WN(MODIFYSOURCE),REMARK=F_BYF_UPDATE_WN(REMARK) WHERE rowid=c.rid;
 if mod(i,200)=0 then
  commit;
end if;
 if mod(i,20000)=0 then
  dbms_lock.sleep(40);
 if i>30000 then
 dbms_lock.sleep(40);
 end if;
 end if;
 if i>500000 then
  exit;
 end if;
 end loop;
  commit;
end;
/
--------FUND294-------------