plsql学习范例--使用utl_file包将查询结果输出到文件中

最近在开发过程中需要根据查询结果生成一个HTML文件，用到了utl_file包，对于该包的使用，简单做了一下测试：

--先在数据中创建directory并授权:

SQL> create or replace directory FILEPATH as '/home/oracle/utlfile' ;

 

Directory created.

 

SQL> GRANT READ, WRITE ON DIRECTORY FILEPATH to xjny;

 

Grant succeeded.

--使用UTL_FILE.PUT_LINE生成CSV格式的文件

CREATE OR REPLACE PROCEDURE P_WRITE_ALERTS AS

V_FILE UTL_FILE.FILE_TYPE;

V_BUFFER VARCHAR2(32767);

BEGIN

V_FILE := UTL_FILE.FOPEN('FILEPATH', 'ALERTS' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.csv', 'w', 32767);

V_BUFFER := 'ID,SID,ALERT_CONTENT,ALERT_DATE,ALERT_TYPE,RECORD_DATE';

UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);

FOR I IN 

(

 SELECT '"' || ID || '","' || 

 SID || '","' || 

 ALERT_CONTENT || '","' || 

 ALERT_DATE || '","' || 

 ALERT_TYPE || '","' || 

 RECORD_DATE || '" ' RESULT

 FROM ALERTS

 ) LOOP

 UTL_FILE.PUT_LINE(V_FILE, I.RESULT);

 END LOOP;

 UTL_FILE.FCLOSE(V_FILE);

 END;

 /

 

 --使用UTL_FILE.PUT生成XLS格式的文件

CREATE OR REPLACE PROCEDURE P_WRITE_ALERTS AS

V_FILE UTL_FILE.FILE_TYPE;

V_BUFFER VARCHAR2(32767);

V_FILENAME VARCHAR2(50);

TYPE type_alerts is RECORD

(

id                VARCHAR2(50),

sid               VARCHAR2(50),

alert_content     VARCHAR2(32767),

alert_date        VARCHAR2(50),

alert_type        VARCHAR2(50),

record_date       VARCHAR2(50)

);

rec_alerts type_alerts;

CURSOR C_ALERTS IS 

select id||chr(9),sid||chr(9),alert_content||chr(9),

       alert_date||chr(9),alert_type||chr(9),record_date||chr(9) 

from alerts where rownum<=10;

BEGIN

--V_FILE := UTL_FILE.FOPEN('FILEPATH', 'ALERTS' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.xls', 'w', 32767);

--V_BUFFER := 'ID,SID,ALERT_CONTENT,ALERT_DATE,ALERT_TYPE,RECORD_DATE';

--UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);

OPEN c_alerts;

  loop 

  fetch c_alerts into rec_alerts;

  exit when c_alerts%notfound;

      V_FILENAME :=substr(rec_alerts.id,1,4);

      V_FILE := UTL_FILE.FOPEN('FILEPATH', V_FILENAME, 'w', 32767);

      utl_file.put(v_file,rec_alerts.id);

      utl_file.put(v_file,rec_alerts.sid);

      utl_file.put(v_file,rec_alerts.alert_content);

      utl_file.put(v_file,rec_alerts.alert_date);

      utl_file.put(v_file,rec_alerts.alert_type);

      utl_file.put(v_file,rec_alerts.record_date);

-- utl_file.new_line(v_file,1);

  utl_file.fflush(v_file);

  utl_file.fclose(v_file);

  end loop;

  close c_alerts;

 END;

 /