Oracle 通过存储过程来生成CSV文件

太过爱你忘了你带给我的痛 2024-04-18 02:19 60阅读 0赞

创建directory

create directory REPORT\_DIR as '/u01/app/oracle/report';

grant all on directory REPORT\_DIR to TEST ;

创建存储过程  
CREATE OR REPLACE PROCEDURE SQL\_TO\_CSV(  
                                       P\_DIR      IN VARCHAR2, -- output directory  
                                       P\_FILENAME IN VARCHAR2 -- csv file name  
                                       ) IS  
  L\_OUTPUT       UTL\_FILE.FILE\_TYPE;  
  L\_SEPARATOR    VARCHAR2(1);  
  L\_DESCTBL      DBMS\_SQL.DESC\_TAB;  
  P\_MAX\_LINESIZE NUMBER := 32000;  
    \--DEFINE A CURSOR  
  CURSOR c1 IS SELECT D\_START\_DATE FROM OSMSR\_VIEW\_OPERATION\_HISTORY WHERE ROWNUM <= 10;  
  v\_start\_date  OSMSR\_VIEW\_OPERATION\_HISTORY.D\_START\_DATE%TYPE;  
BEGIN  
  --OPEN FILE  
  L\_OUTPUT := UTL\_FILE.FOPEN(P\_DIR, P\_FILENAME, 'W', P\_MAX\_LINESIZE);  
  --DEFINE DATE FORMAT  
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS\_DATE\_FORMAT=''YYYY-MM-DD HH24:MI:SS''';  
  --OPEN CURSOR  
    OPEN c1;  
  LOOP  -- Fetches columns into variables  
    FETCH c1 INTO v\_start\_date;  
    EXIT WHEN c1%NOTFOUND;  
  --DEFINE THE SEPARATOR ,  
    L\_SEPARATOR := '';  
    UTL\_FILE.PUT(L\_OUTPUT, L\_SEPARATOR || v\_start\_date);  
    L\_SEPARATOR := ',';  
   --CHANGE THE LINE  
    UTL\_FILE.NEW\_LINE(L\_OUTPUT);  
  END LOOP;  
  --CLOSE CURSOR  
  CLOSE c1;  
  --CLOSE FILE  
  UTL\_FILE.FCLOSE(L\_OUTPUT);  
EXCEPTION  
  WHEN OTHERS THEN  
    RAISE;  
END;  
  
 调用存储过程，REPORT\_DIR是directory的名称，02.csv是生成的CSV文件名  
BEGIN  
  SQL\_TO\_CSV('REPORT\_DIR', '02.csv');  
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2125200/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2125200/