oracle plsql写excel,oracle用PLSQL把表数据写到excel的例子（UTL_FILE）

oracle用PLSQL把表数据写到excel的例子(UTL_FILE)

这里有个例子﹐不过不是结果﹐而是指定的table.你可以参考一下。

CREATE OR REPLACE PROCEDURE pro_Datatoexcel(

/*

写出前需建立文件目录

CREATE OR REPLACE DIRECTORY

my_dir AS

'D:/aa'

赋权限给执行的用户

GRANT WRITE,READ ON DIRECTORY my_dir TO exam0701b

*/

p_table IN VARCHAR2,

p_pathIN VARCHAR2,

p_fileIN VARCHAR2)

AS

TYPE t_refcurIS REF CURSOR ;--定义游标变量

v_fetchrowt_refcur ;--实例化

v_cursorINTEGER;

v_sqlstatement VARCHAR2(100);

v_colnameVARCHAR2(50);

v_colsVARCHAR2(500);

v_dataVARCHAR2(500);

v_describeinfo DBMS_SQL.DESC_TAB;

v_drecDBMS_SQL.DESC_REC;

v_returncode INTEGER;

v_numcolumns INTEGER;

kINTEGER:=0;

v_filehandle UTL_FILE.FILE_TYPE;

BEGIN

--写标宠则息

v_cursor:=DBMS_SQL.OPEN_CURSOR;

v_sqlstatement:='select * from ' || p_table;

DBMS_SQL.PARSE(v_cursor, v_sqlstatement, dbms_sql.v7);

DBMS_SQL.DESCRIBE_COLUMNS(v_cursor,v_numcolumns, v_describeinfo);

v_filehandle:=UTL_FILE.FOPEN(p_path,p_file,'w');

FOR v_col IN 1..v_numcolumns LOOP

v_drec:=v_describeinfo(v_col);

v_cols:=v_cols||RPAD(v_drec.col_name, 16)||CHR(9);

END LOOP;

UTL_FILE.PUT(v_filehandle,v_cols) ;

--获取总行数

EXECUTE IMMEDIATE 'select count(*) from '|| p_table

INTO k;

--读取数据

--先从行开始﹐再从列开始

FOR i IN 1..k LOOP

FOR v_col IN 1..v_numcolumns LOOP

v_drec:=v_describeinfo(v_col);

v_colname:=v_drec.col_name;

OPEN v_fetchrow FOR 'select '||v_colname||' from (select '||p_table||'.*,rownum rk from '||p_table||' ) where rk ='||i;

FETCH v_fetchrow INTO v_cols;

v_data:=v_data||v_cols;

v_data:=v_data||CHR(9);

END LOOP;

UTL_FILE.NEW_LINE(v_filehandle,1);

UTL_FILE.PUT(v_filehandle,v_data) ;

v_data :='';

UTL_FILE.FFLUSH(v_filehandle) ;

END LOOP;

UTL_FILE.FCLOSE(v_filehandle);

EXCEPTION

WHEN UTL_FILE.INVALID_PATH THEN

RAISE_APPLICATION_ERROR(-20002,'ORA-错误﹐无效的文件路径!') ;

WHEN UTL_FILE.INVALID_MODE THEN

RAISE_APPLICATION_ERROR(-20002,'ORA-错误﹐指定的文件模式无效!') ;

WHEN UTL_FILE.WRITE_ERROR THEN

RAISE_APPLICATION_ERROR(-20002,'ORA-错误﹐在写操作时发生操作系统错误!') ;

WHEN OTHERS THEN

IF UTL_FILE.IS_OPEN(v_filehandle) THEN

UTL_FILE.FCLOSE(v_filehandle);

END IF;

RAISE_APPLICATION_ERROR(-20002,'产生转档文件失败﹐请联系程序开发人员!') ;

END;