要求:每月24日定时抽取表mlk_flag_2012最新月份的数据作为当月的数据插入到表mlk_flag_2012中,例如,7月24号前,表mlk_flag_2012中只有1-6月份的数据,7月24号抽取6月份的数据作为7月份的初始值。
步骤:
一:创建存储过程MLK_FLAG_PROC 作用:抽取表mlk_flag最新月份的数据作为当月的数据插入。对于表不存在及无历史数据的情况都做了处理,也可以作为oracle初始化之用。
create or replace procedure MLK_FLAG_PRO_NEW as
year_ varchar2(4);
create_table_ varchar2(3000);
insert_data_ varchar2(3000);
is_latest_year varchar2(100);
delete_sql_ varchar2(3000);
is_exists_sql_ varchar2(3000);
is_exists_ number(10);
username varchar(200);
columns_name_sql varchar(3000);
columns_name varchar(3000);
begin year_ := to_char(sysdate,'yyyy');
--处理mlk flag
DBMS_OUTPUT.PUT_LINE('处理mlk flag');
select substr(max(table_name),length(max(table_name))-3,4) into is_latest_year from user_tables where table_name like 'MLK_FLAG_%';
--判断当年表是否存在
--1.当年表存在
if is_latest_year=year_ then
--判断当前月份是否存在数据,若存在,删除当前月份数据,然后倒入最新数据
is_exists_sql_ :=' select count(*) from MLK_FLAG_'||year_||' where time_ = to_char(sysdate,''yyyymm'') ';
execute immediate is_exists_sql_ into is_exists_;
if is_exists_>0 then
delete_sql_ :='delete from MLK_FLAG_'||year_||' where time_ = to_char(sysdate,''yyyymm'') ';
execute immediate delete_sql_;
commit;
end if;
columns_name_sql :='select substr(max(sys_connect_by_path(NAME, '','')), 2, 10000) columns_name
from (
SELECT COLUMN_NAME as NAME, rownum ro FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = ''MLK_FLAG_'||year_||''' AND COLUMN_NAME!=''TIME_''
ORDER BY COLUMN_ID
) newtab
start with newtab.ro = 1
connect by prior newtab.ro = newtab.ro-1';
DBMS_OUTPUT.PUT_LINE(columns_name_sql);
execute immediate columns_name_sql into columns_name;
DBMS_OUTPUT.PUT_LINE(columns_name);
insert_data_ := ' insert into MLK_FLAG_'||year_||'
('||columns_name||',time_)
select '||columns_name||' ,to_char(sysdate,''yyyymm'') as TIME_
from MLK_FLAG_'||year_||'
where time_=(select max(time_) from MLK_FLAG_'||year_||')' ;
DBMS_OUTPUT.PUT_LINE(insert_data_); execute immediate insert_data_;
commit;
end if;
--2.当年表不存在,创建当年表,并将上年的最后一个月份的数据倒入当当年表中
select user into username from dual;
execute immediate 'grant create table to '||username;
if is_latest_year<year_ then
create_table_ := ' create table MLK_FLAG_'||year_|| ' as
select * from MLK_FLAG_'||is_latest_year||' where rownum<1' ;
execute immediate create_table_;
columns_name_sql :='select substr(max(sys_connect_by_path(NAME, '','')), 2, 10000) columns_name
from (
SELECT COLUMN_NAME as NAME, rownum ro FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = ''MLK_FLAG_'||is_latest_year||''' AND COLUMN_NAME!=''TIME_''
ORDER BY COLUMN_ID
) newtab
start with newtab.ro = 1
connect by prior newtab.ro = newtab.ro-1';
--DBMS_OUTPUT.PUT_LINE(columns_name_sql);
execute immediate columns_name_sql into columns_name;
--DBMS_OUTPUT.PUT_LINE(columns_name);
insert_data_ := ' insert into MLK_FLAG_'||year_||'
('||columns_name||',time_)
select '||columns_name||',to_char(sysdate,''yyyymm'') as TIME_ from MLK_FLAG_'||is_latest_year||'
where time_=(select max(time_) from MLK_FLAG_'||is_latest_year||')';
Dbms_output.Put_line(insert_data_);
execute immediate insert_data_;
commit;
end if;
end MLK_FLAG_PRO_NEW;
二:执行定时任务
declare
jobno number;
begin
dbms_job.submit(
jobno,
'MLK_FLAG_PROC;', --what
to_date('24-07-2012 02:00:00', 'dd-mm-yyyy hh24:mi:ss'), --next_date
' TRUNC(LAST_DAY(SYSDATE))+24+2/24' --interval,关键设置 每月24号凌晨2点执行
);
commit;
end;
三: 查看定时任务的详细情况
select * from user_jobs;
四:关闭定时任务
begin dbms_job.remove(n); end;
n为任务号,通过三察看任务号。
五:Interval的相关设置
1、 每分钟执行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)
Interval =>'sysdate+1/1440'
2、 每天定时执行
例如:每天的凌晨2点执行
Interval => TRUNC(sysdate) + 1 +2 / (24)
3、 每周定时执行
例如:每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天
4、 每月定时执行
例如:每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5、 每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
6、 每半年定时执行
例如:每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7、 每年定时执行
例如:每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24
8、 每3小时执行一次
Interval =>sysdate+180/1440'
分享到:
相关推荐
Oracle 定时执行存储过程 Oracle 定时执行存储过程是一种高效的方式来执行存储过程,通过使用 Oracle 提供的 job 机制来实现。Job 机制允许开发者创建、计划和执行存储过程,实现自动化和批量处理。 Broken() ...
oracle定时器调用存储过程
5. **文件名称列表**:`oracle定时任务`和`存储过程`这两个文件名可能是指包含有关如何在实际环境中设置和使用这些功能的文档或脚本。在学习和实践中,可以参考这些文件以获取更具体的步骤和示例。 综上所述,...
本文将介绍如何在Windows平台上利用VFP调用Oracle存储过程,以及实现Oracle数据库内容向VFP数据表的自动转换。 首先,我们来了解Oracle数据库。Oracle数据库是一种基于SQL的大型关系型数据库系统,它以支持多用户、...
1. **创建存储过程**:首先,你需要编写一个存储过程,包含你希望定时执行的逻辑,比如更新数据库中的某些记录。这个过程可能涉及复杂的查询、数据处理或者与业务逻辑相关的操作。 2. **创建调度作业**:使用DBMS_...
在 Oracle 中,可以使用 `DBMS_SCHEDULER` 包来创建定时任务,以自动调用存储过程。定时任务的创建主要包括以下几个步骤: 1. **定义程序**: - 使用 `DBMS_SCHEDULER.create_program` 创建程序对象。 - 需要指定...
结合Oracle数据库,我们可以利用任务计划程序调用Oracle存储过程,实现定时的数据处理、备份或其他业务逻辑。本文将深入探讨如何实现这一功能。 首先,理解Oracle存储过程是关键。存储过程是预编译的SQL和PL/SQL...
总结,Oracle中的Job调度存储过程和触发器是强大的数据库管理工具,它们可以帮助你实现自动化和定时执行数据库任务。通过合理地组合使用这两者,可以提高效率,减少人为错误,并确保数据的准确性和一致性。务必根据...
本文将围绕“Oracle定时导出”这一主题,深入探讨如何设置与执行Oracle定时导出任务,以及这一过程中的关键参数和注意事项。 ### Oracle定时导出的意义 Oracle定时导出,指的是在预设的时间点自动进行数据库或表...
1. **定义存储过程**:首先,我们需要编写一个存储过程来封装要定时执行的操作。例如,如果要每天创建一个新的表来记录更新的节目信息,我们可以创建一个名为`sp_createtab_tbl_programme`的存储过程。在这个存储...
### Oracle定时备份脚本知识点详解 #### 一、Oracle数据库定时备份原理与脚本实现 在Oracle数据库管理中,为了确保数据的安全性和可用性,定期进行数据备份是必不可少的操作之一。通过编写定时备份脚本,可以自动...
通过以上的介绍,我们可以了解到Oracle存储过程的定义、使用方法、变量的定义与声明、游标的使用、循环控制语句、过程控制语句、存储过程的创建和异常处理、触发器的概念与使用、JOB的管理以及SQL优化策略等多个方面...
文件"Oracle触发器与存储过程高级编程.pdf"很可能是这本书或教程的电子版,它会详细讲解触发器和存储过程的各种高级特性,包括但不限于:触发器的时间线(Before/After)、级联触发、触发器中的事务控制、存储过程的...
Oracle存储过程是一组为了完成特定功能的SQL语句集,它被编译并存储在数据库中,可以被多次调用执行,提高代码的复用性并减少网络流量。本手册将详细介绍Oracle存储过程的创建、变量使用、控制结构、游标等高级特性...
### ORACLE定时更新数据及告警 #### 一、背景介绍 在企业级应用中,为了确保业务数据的准确性和时效性,往往需要对数据库中的数据进行定期更新,并且当某些关键指标超出预设范围时能够及时触发警告通知相关人员。...
在Oracle数据库中,面对随着时间增长的数据表,特别是那些与时间紧密相关的日志表,定期分割表是一种有效的管理策略。...通过创建并调用适当的存储过程,这一过程可以自动化,确保了数据库系统的稳定性和效率。
2. Oracle的DBMS_SCHEDULER:在数据库级别设置定时任务,直接调用PL/SQL存储过程执行导出导入。 五、最佳实践 - 定期备份:根据业务需求,设定合理的备份频率,如每天、每周等。 - 安全存储:导出文件应存储在安全...
2. **执行PL/SQL块**:定时任务通常执行自定义的PL/SQL代码,例如更新、插入或删除记录,或者调用存储过程。 3. **设置调度**:你可以设定任务何时开始、何时结束,以及执行频率。例如,可以设定每天的特定时间执行...