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

本文提供了一个使用Oracle PL/SQL将数据库表数据导出到Excel文件的示例。通过创建存储过程,实现数据的读取与文件写入。首先打开文件并写入列名,然后逐行写入数据。

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

oraclePLSQL把表数据写到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_path   IN VARCHAR2,

   p_file   IN VARCHAR2)

AS

   TYPE t_refcur   IS REF CURSOR ;       --定义游标变量

   v_fetchrow     t_refcur ;              --实例化

   v_cursor    INTEGER;

   v_sqlstatement VARCHAR2(100);

   v_colname    VARCHAR2(50);

   v_cols       VARCHAR2(500);

   v_data       VARCHAR2(500);

   v_describeinfo DBMS_SQL.DESC_TAB;

   v_drec       DBMS_SQL.DESC_REC;

   v_returncode INTEGER;

   v_numcolumns INTEGER;

   k              INTEGER:=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;

 

 

### UTL_FILE包的使用方法及示例 UTL_FILEOracle 提供的一个 PL/SQL 包,用于在数据库服务器上读取和写入文本文件。该包主要用于将数据库中的数据导出到外部文件中,或者从外部文件导入数据数据库中。 #### 创建DIRECTORY对象 在使用 UTL_FILE 之前,需要先创建一个 DIRECTORY 对象,该对象指向服务器上的一个物理目录。这样做的好处是可以避免直接在代码中硬编码路径,从而提高安全性和可维护性。 ```sql CREATE OR REPLACE DIRECTORY CZW_DIR AS '/path/to/directory'; ``` 确保数据库用户具有对该目录的读写权限[^1]。 #### 打开文件 使用 `FOPEN` 函数打开文件,需要提供 DIRECTORY 对象名称、文件名以及打开模式('R' 为只读,'W' 为写入,'A' 为追加)。 ```plsql DECLARE HANDLE UTL_FILE.FILE_TYPE; BEGIN HANDLE := UTL_FILE.FOPEN('CZW_DIR', 'DEPT.TXT', 'W', 1000); -- 文件操作 UTL_FILE.FCLOSE(HANDLE); END; ``` #### 写入文件 可以使用 `PUT_LINE` 过程将一行文本写入文件。此过程会自动添加换行符。 ```plsql DECLARE HANDLE UTL_FILE.FILE_TYPE; BEGIN HANDLE := UTL_FILE.FOPEN('CZW_DIR', 'DEPT.TXT', 'W', 1000); FOR I IN (SELECT T.DEPTNO || ',' || T.DNAME || ',' || T.LOC AS MSG FROM SCOTT.DEPT T) LOOP UTL_FILE.PUT_LINE(HANDLE, I.MSG); END LOOP; UTL_FILE.FFLUSH(HANDLE); -- 刷新缓冲区 UTL_FILE.FCLOSE(HANDLE); -- 关闭文件 END; ``` #### 读取文件 使用 `GET_LINE` 过程可以从文件中读取一行文本。 ```plsql DECLARE HANDLE UTL_FILE.FILE_TYPE; BUFFER VARCHAR2(32767); BEGIN HANDLE := UTL_FILE.FOPEN('CZW_DIR', 'DEPT.TXT', 'R'); LOOP BEGIN UTL_FILE.GET_LINE(HANDLE, BUFFER); DBMS_OUTPUT.PUT_LINE(BUFFER); -- 输出到控制台 EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; UTL_FILE.FCLOSE(HANDLE); END; ``` #### 关闭文件 完成文件操作后,务必使用 `FCLOSE` 函数关闭文件以释放资源。 ```plsql UTL_FILE.FCLOSE(HANDLE); ``` #### 注意事项 - 确保 DIRECTORY 对象所指向的目录存在并且数据库实例有权限访问。 - 在生产环境中,应考虑异常处理机制,以确保程序的健壮性。 - 使用 `FFLUSH` 可以强制刷新缓冲区,保证数据及时写入磁盘[^2]。 ###
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值