使用PL/SQL可以读写文件,我们可以将查询的数据以一定格式写入文件中,当我们注意分割字段的方式,就可以将它转换成我们需要的格式!
首先我了解到将字段存入txt文件中,如果使用制表符分隔字段,将文件名后缀改为xls,我们就可以以Excel打开这个文本文件,并且是按照表格形式展现的;同样道理,如果我们使用逗号进行分隔,然后将后缀改为cvs,也会以Excel的CVS格式打开这个文件!
下面写一个例子:
1 首先写一个用于写文件的公共方法
CREATE
OR
REPLACE
PROCEDURE
PROC_FILE_WRITE(
i_title VARCHAR2 ,
i_fileMsg VARCHAR2 ,
i_fileType VARCHAR2
) IS
v_file UTL_FILE.FILE_TYPE; -- 写入文件
PATH VARCHAR2 ( 1024 );
v_fileName VARCHAR2 ( 2048 );
BEGIN
PATH: = ' TEST ' ;
v_fileName: = i_title || ' . ' || i_fileType;
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF ;
v_file : = UTL_FILE.FOPEN(PATH, v_fileName, ' w ' );
UTL_FILE.PUT_LINE(v_file, i_fileMsg);
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF ;
END PROC_FILE_WRITE;
/
i_title VARCHAR2 ,
i_fileMsg VARCHAR2 ,
i_fileType VARCHAR2
) IS
v_file UTL_FILE.FILE_TYPE; -- 写入文件
PATH VARCHAR2 ( 1024 );
v_fileName VARCHAR2 ( 2048 );
BEGIN
PATH: = ' TEST ' ;
v_fileName: = i_title || ' . ' || i_fileType;
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF ;
v_file : = UTL_FILE.FOPEN(PATH, v_fileName, ' w ' );
UTL_FILE.PUT_LINE(v_file, i_fileMsg);
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF ;
END PROC_FILE_WRITE;
/
2 创建文件生成路径
CREATE
OR
REPLACE
DIRECTORY TEST
AS
'
C:TEST
'
3 创建一个表并添加数据
CREATE
TABLE
TEST_FILE_T(
ID NUMBER NOT NULL ,
NAME VARCHAR ( 32 ),
AGE NUMBER ,
SEX VARCHAR ( 2 )
);
-- 数据自行填写
ID NUMBER NOT NULL ,
NAME VARCHAR ( 32 ),
AGE NUMBER ,
SEX VARCHAR ( 2 )
);
-- 数据自行填写
4 写文件
CREATE
OR
REPLACE
PROCEDURE
PROC_OUT_FILE
AS
v_fileMsg VARCHAR2 ( 30000 );
CURSOR get_file_cur IS
SELECT id, name,age,sex
FROM test_file_t;
get_file_rec get_file_cur % ROWTYPE;
vs_time VARCHAR2 ( 16 );
BEGIN
v_fileMsg : = v_fileMsg || ' ID ' || chr( 44 ) || ' NAME ' || chr( 44 ) || ' AGE ' || chr( 44 ) || ' SEX ' ;
OPEN get_file_cur;
SELECT TO_CHAR(sysdate, ' yyyymmddhhmiss ' ) INTO vs_time FROM DUAL;
LOOP
FETCH get_file_cur
INTO get_file_rec;
EXIT WHEN get_file_cur % NOTFOUND;
-- 转换成cvs,需要用逗号(chr(44))分隔字段,转换成xls,需要用制表符(chr(9))分隔字段,换行(chr(10))
IF get_file_cur % ROWCOUNT = 1 THEN
v_fileMsg : = v_fileMsg || ' ID ' || chr( 44 ) || ' NAME ' || chr( 44 ) || ' AGE ' || chr( 44 ) || ' SEX ' ;
END IF ;
v_fileMsg : = v_fileMsg || chr( 10 ) || to_char( get_file_rec.id) || chr( 44 ) ||
get_file_rec.name || chr( 44 ) || to_char(get_file_rec.age) || chr( 44 ) || get_file_rec.sex;
PROC_FILE_WRITE( ' file ' || vs_time,v_fileMsg, ' csv ' );
END LOOP;
CLOSE get_file_cur;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END ;
/
AS
v_fileMsg VARCHAR2 ( 30000 );
CURSOR get_file_cur IS
SELECT id, name,age,sex
FROM test_file_t;
get_file_rec get_file_cur % ROWTYPE;
vs_time VARCHAR2 ( 16 );
BEGIN
v_fileMsg : = v_fileMsg || ' ID ' || chr( 44 ) || ' NAME ' || chr( 44 ) || ' AGE ' || chr( 44 ) || ' SEX ' ;
OPEN get_file_cur;
SELECT TO_CHAR(sysdate, ' yyyymmddhhmiss ' ) INTO vs_time FROM DUAL;
LOOP
FETCH get_file_cur
INTO get_file_rec;
EXIT WHEN get_file_cur % NOTFOUND;
-- 转换成cvs,需要用逗号(chr(44))分隔字段,转换成xls,需要用制表符(chr(9))分隔字段,换行(chr(10))
IF get_file_cur % ROWCOUNT = 1 THEN
v_fileMsg : = v_fileMsg || ' ID ' || chr( 44 ) || ' NAME ' || chr( 44 ) || ' AGE ' || chr( 44 ) || ' SEX ' ;
END IF ;
v_fileMsg : = v_fileMsg || chr( 10 ) || to_char( get_file_rec.id) || chr( 44 ) ||
get_file_rec.name || chr( 44 ) || to_char(get_file_rec.age) || chr( 44 ) || get_file_rec.sex;
PROC_FILE_WRITE( ' file ' || vs_time,v_fileMsg, ' csv ' );
END LOOP;
CLOSE get_file_cur;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END ;
/
按照上面所述,就可以将数据转换成想要的格式,需要注意的是:
1 上面只有xls和cvs的,如果有其他格式的规则,也可以转换;
2 需要对特殊字符进行转换,如分隔cvs的逗号,需要过滤查询结果里的逗号;
3 当我以ID起头拼的串,会报一个小问题,有兴趣的可以试一下,无意中发现的,有明白的朋友请指点一二,谢谢!