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

最新推荐文章于 2024-04-26 12:04:39 发布

liyanmingkong

最新推荐文章于 2024-04-26 12:04:39 发布

阅读量1.9k

点赞数

文章标签： csv output exception integer query table

本文链接：https://blog.youkuaiyun.com/liyanmingkong/article/details/1585548

版权

本文介绍了一个Oracle PL/SQL过程，用于将数据库查询结果导出为CSV文件。该过程包括设置文件路径和名称、定义日期格式、执行SQL查询、获取列名和值并写入文件等功能。此外，还特别考虑了数值类型字段的处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;