oracle 存储过程，存储函数以及定时器的综合使用

CREATE TABLE test_1(--实时数据表
ID NUMBER(9) PRIMARY KEY,
cytime VARCHAR2(30) NOT NULL,
v1 NUMBER(6,2),
v2 NUMBER(6,2),
v3 NUMBER(6,2)
);

--创建索引
create index INDEX_CYTIME_1 on test_1 (cytime);

--创建随机生成指定位数小数的函数
--params1随机数的位数
--params2随机数的小数位数
CREATE OR REPLACE FUNCTION random_num(params1 NUMBER,params2 NUMBER)
return number as--返回值类型
BEGIN
DECLARE
r_num NUMBER;--声明变量
par1 NUMBER;
par2 NUMBER;
BEGIN
par1 := 1;
par2 := 1;
FOR i IN 1..params1 LOOP
par1 := par1 * 10;
END LOOP;
FOR i IN 1..params2 LOOP
par2 := par2 * 10;
END LOOP;
SELECT ABS(MOD(DBMS_RANDOM.RANDOM,par1))INTO r_num FROM DUAL;
r_num := r_num/par2;
return r_num;
END;
end random_num;

SELECT random_num(6,2) FROM dual;

--获取当前系统时间格式化
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--创建test1模拟数据添加的存储过程
CREATE OR REPLACE PROCEDURE insert_test1_pro AS
BEGIN
INSERT INTO test_1 VALUES(SEQ_NO.nextVal,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
,random_num(6,2),random_num(6,2),random_num(6,2));
commit;
END;

--test1存储过程添加
CALL insert_test1_pro();
SELECT * FROM test_1 ORDER BY cytime DESC;

--定时任务创建后，哪怕数据库关闭重启后依旧会执行
--查看数据库定时任务
select job,next_date,next_sec,failures,broken from user_jobs;
--删除定时任务
--5是查询job的编号
begin
dbms_job.remove(14);
commit;
end;

--运行一个定时器
begin
dbms_job.run(13);
COMMIT;
END;

--停止一个定时器
BEGIN
dbms_job.broken(13,sys.diutil.int_to_bool(1));
commit;
end;

--改变一个定时器的执行频率城每隔30s执行一次
BEGIN
dbms_job.interval(13,'sysdate + 1 / (24*60*2)');
commit;
end;

--创建定时器
declare
job_test1 NUMBER;
begin
sys.dbms_job.submit(
job_test1, --标示定时器名称
what => 'insert_test1_pro;',--执行的存储过程名字，注意名字后面的分号
next_date => sysdate,--指定标示何时运行这个工作(这里创建后立即执行，也可指定时间)
interval => 'sysdate+1/2880'); --每天1440分钟，即30s执行insert_test1_pro一次
COMMIT;
end;

CREATE TABLE test_2(--日报表
ID NUMBER(9) PRIMARY KEY,
cytime VARCHAR2(30) NOT NULL,
v1 NUMBER(6,2),
v2 NUMBER(6,2),
v3 NUMBER(6,2)
);
--创建索引
create index INDEX_CYTIME_2 on test_2 (cytime);

--创建序列-test1
CREATE SEQUENCE seq_no
MINVALUE 1 --最小值
NOMAXVALUE --没有最大值
INCREMENT BY 1 --每次递增1
NOCACHE --没有缓存
NOCYCLE;--不循环

--创建序列-test2
CREATE SEQUENCE seq_no_2
MINVALUE 1 --最小值
NOMAXVALUE --没有最大值
INCREMENT BY 1 --每次递增1
NOCACHE --没有缓存
NOCYCLE;--不循环

--创建实时数据备份到日报表的存储过程
--该存储过程每30s执行，系统秒数在30秒以内为00s
--30--59s是30s
CREATE OR REPLACE PROCEDURE insert_test2_pro AS
actual_s NUMBER;--实时秒数
actual_date VARCHAR2(30);--实际时间
BEGIN
SELECT to_char(sysdate,'ss') INTO actual_s FROM dual;--获取当前时间的秒数
SELECT concat(to_char(trunc(SYSDATE,'mi'),'yyyy-mm-dd hh24:mi:'),'00') INTO actual_date FROM dual;
IF actual_s >= 0 AND actual_s < 30 THEN
SELECT concat(to_char(trunc(SYSDATE,'mi'),'yyyy-mm-dd hh24:mi:'),'00') INTO actual_date FROM dual;
ELSE
SELECT concat(to_char(trunc(SYSDATE,'mi'),'yyyy-mm-dd hh24:mi:'),'30') INTO actual_date FROM dual;
END IF;
BEGIN
INSERT INTO test_2
SELECT seq_no_2.nextVal AS ID,actual_date AS cytime,v1,v2,v3 FROM test_1
WHERE cytime = (SELECT MAX(cytime) FROM test_1);
commit;
END;
END;

CALL insert_test2_pro();

SELECT * FROM test_2 ORDER BY cytime DESC;

--创建定时器
declare
job_test2 NUMBER;
begin
sys.dbms_job.submit(
job_test2, --标示定时器名称
'insert_test2_pro;',--执行的存储过程名字，注意名字后面的分号
sysdate,--指定标示何时运行这个工作(这里创建后立即执行，也可指定时间)
'sysdate + 1 / (24*60*2)'); --即30s执行insert_test1_pro一次
COMMIT;
end;