CREATE OR REPLACE PROCEDURE p_test(
file_name VARCHAR2 --导出的文件名 例aaa.txt
)AS
CURSOR v_cursor IS
SELECT
a.empno||chr(9),
a.ename||chr(9),
TO_CHAR(a.hiredate,'YYYY-MM-DD')
FROM emp a;
vempno VARCHAR2(50);
vename VARCHAR2(50);
vhiredate VARCHAR2(50);
vfile utl_file.file_type;
BEGIN
vfile := utl_file.fopen('TEST',file_name,'w'); --'TEST'是自定义的Directorie
--utl_file.put_line(vfile,'导出emp表数据'); --导出说明,可以不要
OPEN v_cursor;
LOOP
FETCH v_cursor INTO vempno,vename,vhiredate;
EXIT WHEN v_cursor%NOTFOUND;
utl_file.put(vfile,vempno);
utl_file.put(vfile,vename);
utl_file.put(vfile,vhiredate);
utl_file.put_line(vfile,'');
END LOOP;
CLOSE v_cursor;
utl_file.fflush(vfile);
utl_file.fclose(vfile);
EXCEPTION
WHEN OTHERS THEN
IF utl_file.is_open(vfile) THEN
utl_file.fclose(vfile);
END IF;
END;
file_name VARCHAR2 --导出的文件名 例aaa.txt
)AS
CURSOR v_cursor IS
SELECT
a.empno||chr(9),
a.ename||chr(9),
TO_CHAR(a.hiredate,'YYYY-MM-DD')
FROM emp a;
vempno VARCHAR2(50);
vename VARCHAR2(50);
vhiredate VARCHAR2(50);
vfile utl_file.file_type;
BEGIN
vfile := utl_file.fopen('TEST',file_name,'w'); --'TEST'是自定义的Directorie
--utl_file.put_line(vfile,'导出emp表数据'); --导出说明,可以不要
OPEN v_cursor;
LOOP
FETCH v_cursor INTO vempno,vename,vhiredate;
EXIT WHEN v_cursor%NOTFOUND;
utl_file.put(vfile,vempno);
utl_file.put(vfile,vename);
utl_file.put(vfile,vhiredate);
utl_file.put_line(vfile,'');
END LOOP;
CLOSE v_cursor;
utl_file.fflush(vfile);
utl_file.fclose(vfile);
EXCEPTION
WHEN OTHERS THEN
IF utl_file.is_open(vfile) THEN
utl_file.fclose(vfile);
END IF;
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24805255/viewspace-721337/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24805255/viewspace-721337/