动态导出为csv文件 （2）不带引号！

PROCEDURE PRC_WRITE_FILE (P_QUERY IN  VARCHAR2, --SQL QUERY STATEMENT
P_TABLE_NAME IN VARCHAR2,                       --TABLE NAME
P_MAX_LINESIZE IN NUMBER DEFAULT 32000 )IS      --MAX LINESIZE,MUST LESS THAN 32787

l_OUTPUT UTL_FILE.FILE_TYPE;
l_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
l_COLUMNVALUE VARCHAR2(4000);
l_STATUS INTEGER;
l_COLCNT NUMBER := 0;
l_SEPARATOR VARCHAR2(1);
l_DESCTBL DBMS_SQL.DESC_TAB;
BEGIN

  --OPEN FILE
  l_OUTPUT := UTL_FILE.FOPEN( 'HSM_BUCKUP_CSV_DIR',P_TABLE_NAME || '_' || SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDD'),0,6) || '.csv', 'W', P_MAX_LINESIZE);

  --DEFINE DATE FORMAT
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY/MM/DD HH24:MI:SS''';

  --OPEN CURSOR
  DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );
  DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL );

  --DUMP TABLE COLUMN NAME
  FOR I IN 1 .. L_COLCNT LOOP
     DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000 );
    l_SEPARATOR := ',';
  END LOOP;

  --EXECUTE THE QUERY STATEMENT
  l_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);

  --DUMP TABLE COLUMN VALUE
  WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP
    l_SEPARATOR := '';
    FOR I IN 1 .. L_COLCNT LOOP
      DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE );

      --SHUZHI
      IF FNC_ISNUMBER(L_COLUMNVALUE) = 1 THEN
        UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR ||
                     TRIM(LEADING '9' FROM TRIM(TRIM(TRAILING '.' FROM
                     TRIM(TRAILING '0' FROM TO_CHAR(L_COLUMNVALUE,'9999999990.00000'))))));
      ELSE
        UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR || L_COLUMNVALUE);
      END IF;

       l_SEPARATOR := ',';

    END LOOP;
    UTL_FILE.NEW_LINE(l_OUTPUT);
  END LOOP;

  --CLOSE CURSOR
  DBMS_SQL.CLOSE_CURSOR(l_THECURSOR);

  --CLOSE FILE
  UTL_FILE.FCLOSE(l_OUTPUT);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END; 

 

还要判断字段的值是否是数值类型的！

FUNCTION FNC_ISNUMBER(pVAlUE IN VARCHAR2 ) RETURN NUMBER IS
lVAlUE VARCHAR2(200);
BEGIN

   lVAlUE := TO_NUMBER(pVAlUE);
   IF INSTRB(pVAlUE,'.') = 0 THEN
     RETURN 2;
   ELSE
     RETURN 1;
   END IF;

EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;