Oracle中表数据导出到excel中

2010-12-01 20:30:25

[code=SQL] SQL> create table tutlfile(job_id varchar(20),job_title varchar(20),min_salary number(10,0)); 表已创建。 SQL> insert into tutlfile select 'JOB_ID'||level,'JOB_TITLE'||(level*100),level*1000 from dual connect by level<=20; 已创建20行。 SQL> set pages 9999 SQL> select * from tutlfile; JOB_ID JOB_TITLE MIN_SALARY -------------------- -------------------- ---------- JOB_ID1 JOB_TITLE100 1000 JOB_ID2 JOB_TITLE200 2000 JOB_ID3 JOB_TITLE300 3000 JOB_ID4 JOB_TITLE400 4000 JOB_ID5 JOB_TITLE500 5000 JOB_ID6 JOB_TITLE600 6000 JOB_ID7 JOB_TITLE700 7000 JOB_ID8 JOB_TITLE800 8000 JOB_ID9 JOB_TITLE900 9000 JOB_ID10 JOB_TITLE1000 10000 JOB_ID11 JOB_TITLE1100 11000 JOB_ID12 JOB_TITLE1200 12000 JOB_ID13 JOB_TITLE1300 13000 JOB_ID14 JOB_TITLE1400 14000 JOB_ID15 JOB_TITLE1500 15000 JOB_ID16 JOB_TITLE1600 16000 JOB_ID17 JOB_TITLE1700 17000 JOB_ID18 JOB_TITLE1800 18000 JOB_ID19 JOB_TITLE1900 19000 JOB_ID20 JOB_TITLE2000 20000 已选择20行。 SQL> CREATE OR REPLACE PROCEDURE SP_JOBS_DATA_OUT( p_file_name IN VARCHAR2 ) 2 as 3 --***定义并声明存储信息的纪录***-- 4 --***record_define start***-- 5 TYPE job_record_type is RECORD( job_id tutlfile.job_id%TYPE, job_title tutlfile.job_title%TYPE, 6 min_salary varchar2(30) 7 ); 8 job_rec job_record_type; 9 --***record_define end***-- 10 --***定义获取job信息的游标***-- 11 --***cursor_define start***-- 12 CURSOR c_jobs IS 13 select job_id||chr(9), --***chr(9)是TAB字符，保证数据输出到EXCEL时能自动换到下一列***-- 14 job_title||chr(9), 15 min_salary||chr(9) 16 FROM 17 tutlfile; 18 --***cursor_define end***-- 19 l_file utl_file.file_type; --***处理文件操作的句柄***-- 20 proc_lines number;　　　　　　　 21 22 BEGIN 23 l_file :=utl_file.fopen('FILEPATH',p_file_name,'w'); --FILEPATH是先于导出前用户建立的存储导出文件的路径 24 utl_file.put_line(l_file,'jobs表导出数据'); 25 utl_file.put_line(l_file,'工作标号'||chr(9)||'工作内容'||chr(9)||'最低薪水'); 26 OPEN c_jobs; 27 proc_lines :=0; 28 LOOP 29 FETCH c_jobs INTO 30 job_rec.job_id , 31 job_rec.job_title , 32 job_rec.min_salary ; 33 proc_lines := proc_lines+1; 34 EXIT WHEN c_jobs%NOTFOUND; 35 utl_file.put(l_file,job_rec.job_id ); --***数据写入excle文件中***-- 36 utl_file.put(l_file,job_rec.job_title); 37 utl_file.put_line(l_file,job_rec.min_salary); 38 39 END LOOP; 40 CLOSE c_jobs; 41 utl_file.fflush(l_file); 42 utl_file.fclose(l_file); 43 EXCEPTION 44 WHEN others THEN 45 IF utl_file.is_open(l_file) THEN 46 utl_file.fclose(l_file); 47 END IF; 48 dbms_output.put_line(proc_lines);　　　 49 END; 50 / 过程已创建。 SQL> create or replace directory FILEPATH as 'c:/vrv'; 目录已创建。 SQL> set serverout on SQL> exec SP_JOBS_DATA_OUT('A.xls'); PL/SQL 过程已成功完成。 SQL> create or replace directory FILEPATH as 'c:/vrv'; 目录已创建。 SQL> exec SP_JOBS_DATA_OUT('A.xls'); PL/SQL 过程已成功完成。 SQL> ho type c:/vrv/a.xls jobs表导出数据 工作标号 工作内容 最低薪水 JOB_ID1 JOB_TITLE100 1000 JOB_ID2 JOB_TITLE200 2000 JOB_ID3 JOB_TITLE300 3000 JOB_ID4 JOB_TITLE400 4000 JOB_ID5 JOB_TITLE500 5000 JOB_ID6 JOB_TITLE600 6000 JOB_ID7 JOB_TITLE700 7000 JOB_ID8 JOB_TITLE800 8000 JOB_ID9 JOB_TITLE900 9000 JOB_ID10 JOB_TITLE1000 10000 JOB_ID11 JOB_TITLE1100 11000 JOB_ID12 JOB_TITLE1200 12000 JOB_ID13 JOB_TITLE1300 13000 JOB_ID14 JOB_TITLE1400 14000 JOB_ID15 JOB_TITLE1500 15000 JOB_ID16 JOB_TITLE1600 16000 JOB_ID17 JOB_TITLE1700 17000 JOB_ID18 JOB_TITLE1800 18000 JOB_ID19 JOB_TITLE1900 19000 JOB_ID20 JOB_TITLE2000 20000 [/code]