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]