能不能把存储过程跑出的结果表zmz_bc_call_count_1，自动形成excel或文本文件 直接FTP到远程计算机上。过程和JOB如下，如过能的话程序该怎么改造？CREATE OR REPLACE PROCEDURE lac_ci AUTHID CURRENT_USER AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table zmz_call_tj';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table zmz_call_tj as
select b.lac, b.cell_id, b.user_label,a.msisdn,b.area_code,count(*) count
from ucr_bc1.tg_cdr'||to_char(add_months(sysdate, -1), 'mm')||'@ngbil a, bc_lac_cell b
where a.lac1 = b.lac
and a.cell_id1 = b.cell_id and a.visit_area_code=0436
and a.call_type in(01,02)
group by b.lac, b.cell_id, b.user_label,a.msisdn,b.area_code'; ----上月 BEGIN
EXECUTE IMMEDIATE 'drop table zmz_bc_call_count';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table zmz_bc_call_count as
select *
from (select user_label,
lac,
cell_id,
msisdn,area_code,
count,
row_number() over(partition by msisdn order by count desc) rn
from zmz_call_tj)
where rn = 1'; BEGIN
EXECUTE IMMEDIATE 'drop table zmz_bc_call_count_1';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table zmz_bc_call_count_1 as
select a.*,d.area_name
from zmz_bc_call_count a,ucr_crm3.tf_f_user@ngcrm b,ucr_cen1.td_m_depart@ngcrm c,
ucr_cen1.td_m_area@ngcrm d
where a.msisdn=b.serial_number
and b.remove_tag=0
and b.open_depart_id=c.depart_id
and c.area_code=d.area_code'; END;
---------------------------------------------------------
建立了每月5日执行的JOB
DECLARE
joblac NUMBER;
BEGIN
dbms_job.submit(joblac,
'lac_ci;',
trunc(add_months(SYSDATE, 1), 'mm') + 4 + 6 / 144,
'trunc(add_months(sysdate,1),''mm'')+4+6/144');
COMMIT;
END;-----------job
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table zmz_call_tj';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table zmz_call_tj as
select b.lac, b.cell_id, b.user_label,a.msisdn,b.area_code,count(*) count
from ucr_bc1.tg_cdr'||to_char(add_months(sysdate, -1), 'mm')||'@ngbil a, bc_lac_cell b
where a.lac1 = b.lac
and a.cell_id1 = b.cell_id and a.visit_area_code=0436
and a.call_type in(01,02)
group by b.lac, b.cell_id, b.user_label,a.msisdn,b.area_code'; ----上月 BEGIN
EXECUTE IMMEDIATE 'drop table zmz_bc_call_count';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table zmz_bc_call_count as
select *
from (select user_label,
lac,
cell_id,
msisdn,area_code,
count,
row_number() over(partition by msisdn order by count desc) rn
from zmz_call_tj)
where rn = 1'; BEGIN
EXECUTE IMMEDIATE 'drop table zmz_bc_call_count_1';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table zmz_bc_call_count_1 as
select a.*,d.area_name
from zmz_bc_call_count a,ucr_crm3.tf_f_user@ngcrm b,ucr_cen1.td_m_depart@ngcrm c,
ucr_cen1.td_m_area@ngcrm d
where a.msisdn=b.serial_number
and b.remove_tag=0
and b.open_depart_id=c.depart_id
and c.area_code=d.area_code'; END;
---------------------------------------------------------
建立了每月5日执行的JOB
DECLARE
joblac NUMBER;
BEGIN
dbms_job.submit(joblac,
'lac_ci;',
trunc(add_months(SYSDATE, 1), 'mm') + 4 + 6 / 144,
'trunc(add_months(sysdate,1),''mm'')+4+6/144');
COMMIT;
END;-----------job
2、写一个sql脚本，里面使用spool命令将数据输出。下面是一个样例(test.sql)：
set heading off
set termout off
set feedback off
set pagesize 0
set trimsout on
set trimspool on
spool test.csv
select col1||','||col2 from test;--换成你的语句
spool off
exit
3、写一个ftp命令脚本test.ftp
open ftp机器IP
用户名
密码
bin
put test.csv
bye
3、写一个批处理，调用此脚本，并使用ftp命令上传导出文件。test.bat批处理内容：
sqlplus test/test@mytab @test.sql
ftp -s:test.ftp
4、建立一个计划任务，调用批处理test.bat。