Oracle存储过程定时导出txt文件

1.创建一个表，为了能清楚看到定时器的运行情况我们创建一个带有日期字段的表

create table job_table(run_time date); 

2.创建存储过程函数

创建输出路径

create or replace directory MY_DIR as 'D:\MY_DIR\';

生成的文件赋予用户读写权限

grant read,write on directory MY_DIR to adm;

查询是否成功

select * from dba_directories;

创建查询集导出txt文件函数

CREATE OR REPLACE PROCEDURE pro_exportTxt_SIEM  IS export_handle UTL_FILE.file_type;
BEGIN
		export_handle := UTL_FILE.FOPEN ( 'TXT_DIR', to_char(SYSDATE,'YYYYMMDDHH24miss')||'.txt', 'w' );
	UTL_FILE.PUT_LINE ( export_handle,'ID_NAME,BR_NO,ID_NO' );
	FOR x IN ( SELECT * FROM BUSI_CARD_INFO_PER )
	LOOP
	UTL_FILE.PUT_LINE (
		export_handle,
		x.id_name || ',' || x.br_no || ',' || x.id_no 
	);
	
END LOOP;
UTL_FILE.FCLOSE ( export_handle );
EXCEPTION 
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE ( SUBSTR( SQLERRM, 1, 2000 ) );
END;

创建查询集导出txt文件函数(带参数)

CREATE OR REPLACE PROCEDURE pro_exportTxt_SIEM (id_no_out IN NUMBER)  IS export_handle UTL_FILE.file_type;
BEGIN
		export_handle := UTL_FILE.FOPEN ( 'TXT_DIR', to_char(SYSDATE,'YYYYMMDDHH24miss')||'.txt', 'w' );
	UTL_FILE.PUT_LINE ( export_handle,'ID_NAME,BR_NO,ID_NO' );
	FOR x IN ( SELECT * FROM BUSI_CARD_INFO_PER WHERE ID_NO=id_no_out)
	LOOP
	UTL_FILE.PUT_LINE (
		export_handle,
		x.id_name || ',' || x.br_no || ',' || x.id_no 
	);
	
END LOOP;
UTL_FILE.FCLOSE ( export_handle );
EXCEPTION 
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE ( SUBSTR( SQLERRM, 1, 2000 ) );
END;

3.创建job，并且指定为一分钟执行一次

DECLARE job NUMBER;
BEGIN
		dbms_job.submit ( job, 'pro_exportTxt_SIEM;', SYSDATE, 'TRUNC(sysdate,''mi'') + 1/(24*60)' );
commit;
END;

每天固定时间运行，比如早上8:10分钟

trunc(sysdate+1) + (8*60+10)/24*60

每天6点

trunc(sysdate+1)+6/24 

每天午夜12点

TRUNC(SYSDATE + 1)

4.创建之后自动处于运行状态，我们查询job表，看看我们创建的job

select job,broken,what,interval,t.* from user_jobs t;   

5.停止job,停止成功之后查看user_jobs表的broken是否变为Y值了

begin  
  dbms_job.broken(2,true);  
	commit; 
end;  
 

6.启动job,启动成功之后查看user_jobs表的broken是否变为N值了

 BEGIN
	dbms_job.run(2);
	commit;
END;

7.删除job、存储过程、表

delete user_jobs where job=2;  有可能遇到权限不足
drop procedure pro_exportTxt_SIEM
drop table job_table 

BEGIN
dbms_job.remove(2); 这里的1 是查询出来的 删除job
END;