ÓÃoracle utl_file°ü¶ÁÈ¡Êý¾ÝдÈëÎļþ

create or replace directory MY_DIR as '/usr/test/';
create or replace function f_exportTxt(
  --´«Èë²ÎÊý
  i_query in varchar2,
  i_separator in varchar2,
  i_dir in varchar2,
  i_filename in varchar2
) return number
is
  /**
  ** º¯ÊýÃû:f_exportTxt
  ** ²ÎÊý:1.i_query ²éѯsqlÓï¾ä; 2.i_separator ·Ö¸ô·û,ĬÈÏΪ',' ;
  **      3.i_dir´æ·ÅĿ¼; 4.i_filenameÎļþÃû,ĬÈÏÔÚÎļþÃûÇ°¼ÓÉÏyyyymmddhh24mi
  ** ·µ»Ø: ´óÓÚµÈÓÚ0¼´ÎªÐ´ÈëÎļþ¼Ç¼Êý,¸ºÎªÒì³£
  ** ¹¦ÄÜ: Óû§Êý¾Ý³éÈ¡,²¢Éú³ÉÎļþµ½Ö¸¶¨Ä¿Â¼ÏÂ
  ** ±¸×¢:
  ** ×÷Õß:lingo
  ** ÐÞ¸ÄÈÕÆÚ:2010-04-15
  **/
  --¶¨Òå²ÎÊý
  v_file utl_file.file_type;
  v_theCursor integer default dbms_sql.open_cursor;
  v_columnValue varchar2(2000); --ÁÙʱ(ÁÐÖµ)
  v_colCnt number default 0; --ÁÐ×ÜÊý
  v_separator varchar2(10) default ',';--·Ö¸ô·û,ĬÈÏΪ#@
  v_cnt number default 0;  --¼Ç¼×ÜÊý
  v_filename varchar2(100);--ʱ¼ä
  v_status integer;--Ö´ÐÐSQLºó·µ»Ø״ֵ̬
  v_count number default 10000 ; --ÿ´Î²éѯµÄÊýÁ¿£¬´óÓÚ¸ÃÊýÁ¿Ôò¶à´Î¶ÁÈ¡Êý¾Ýµ½Óαê
  v_tmp number ;--ÁÙʱ(×ܼǼÊý,ͨ¹ýSQLͳ¼ÆËã³ö,¼ÙÈçv_cnt²»µÈÓÚv_tmp,Ôòµ¼³öÓÐÎó)
  v_sql varchar2(2000) ; --×éºÏsqlÓï¾ä
  v_loops number ; --Ñ­»·´ÎÊý
begin
  --select to_char(sysdate,'yyyymmddhh24mi') into v_filename from dual; --ȡʱ¼äÄêÔÂÈÕʱ·Ö×öÎļþÃûǰ׺
  v_filename :='';
  v_filename := v_filename||i_filename ; --×齨ÎļþÃû
  v_sql := 'select count(''x'') from (' ||i_query||')' ;--ͳ¼Æ×ÜÊý
  execute immediate v_sql into v_tmp;
  select trunc(v_tmp/v_count) into v_loops from dual ; --Ñ­»·´ÎÊý
  if mod(v_tmp,v_count) > 0 then
    v_loops := v_loops+1;
  end if;
  v_file := utl_file.fopen(i_dir,v_filename,'W'); --´ò¿ªÎļþ
  for i in 1 .. v_loops loop
      v_sql := 'select * from ( select m.*,rownum r fr