基于基类创建存储过程
create or replace procedure createtable2
authid current_user is
v_createsql varchar2(400);
v_dropsql varchar2(100);
v_count number(9);
v_trigger clob;
begin
v_createsql:='create table '||'READING_DATAS_' || to_char(sysdate,'yyyyMMddhh24mi')||'(
"ID" NUMBER(19,0) primary key NOT NULL ENABLE,
"NAME" VARCHAR2(20 BYTE),
"CREATE_TIME" DATE,
"VALUE" VARCHAR2(20 BYTE))';
v_dropsql:='drop table '||'READING_DATAS_' || to_char(sysdate,'yyyyMMddhh24mi')||' cascade constraints';
select count(*) into v_count from user_tables where table_name=upper('READING_DATAS_' || to_char(sysdate,'yyyyMMddhh24mi'));
if v_count>0 then
execute immediate v_dropsql;
commit;
end if;
execute immediate v_createsql;
v_trigger:='create or replace TRIGGER '||'READING_DATAS_' || to_char(sysdate,'yyyyMMddhh24mi')||'_TRI
before insert on '||'READING_DATAS_' || to_char(sysdate,'yyyyMMddhh24mi')||' for each row
begin
select READING_DATAS_ID_SEQ.nextval into :new.id from dual;
end;';
execute immediate v_trigger;
commit;
end;
字段解释:
- authid current_user is --使得当前执行存储过程的用户同步原用户的权限(dba)
- v_createsql varchar2(400); – 声明变量,用于创建表;
- v_dropsql varchar2(100); – 声明变量,用于删除表;
- v_count number(9); – 声明变量,用于统计当前表名在数据库的数量;
- v_trigger clob; --声明变量,用于存储创建自增序列触发器语句,可根据语句长度选择长度,本次选择clob。
- v_createsql:=’ … ’;
execute immediate v_createsql; --第一句为编辑执行的sql,等于后是两个单引号。
第二句是执行sql。 - 'create table ‘||‘READING_DATAS_’ || to_char(sysdate,‘yyyyMMddhh24mi’)||’(…)’; --创建动态变化的表名 => create table READING_DATAS_2023120722(…);
- select count(*) into v_count from user_tables 查询当前表在数据库中的数量
- 创建序列自增的触发器
- 'create or replace TRIGGER ‘||‘READING_DATAS_’ || to_char(sysdate,‘yyyyMMddhh24mi’)||’_TRI
before insert on '||'READING_DATAS_' || to_char(sysdate,'yyyyMMddhh24mi')||' for each row
begin
select READING_DATAS_ID_SEQ.nextval into :new.id from dual;
end;'
create or replace trigger READING_DATAS_2023120722_TRI
before insert on READING_DATAS_2023120722
for each row
begin
select READING_DATAS_ID_SEQ.nextval into : new.id from dual;
–此处直接使用了我基表中的序列,如果需要每次生成的表使用新的序列,可以声明变量,创建sql,执行sql。
创建作业PL/SQL定期执行存储过程
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'test_JO',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN createtable2(); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; interval=2',
enabled => TRUE,
comments => 'Test jo');
END;
select * from user_scheduler_jobs ;
select * from user_scheduler_job_run_details order by log_date desc;
call dbms_scheduler.drop_job('test_JO');
字段解释:
1.job_name: 任务名称
2.job_type:定时器类型,PL/SQL Block、Stored procedure、Executable
3.job_action:
若job_type为存储过程,job_action为存储过程的名字;
若job_type为PL/SQL块,job_action为完整的PL/SQL代码;
若job_type为指定的外部程序,job_action为输入的script的名称或者操作系统的指令名
4.start_date:开始时间
5.repeat_interval:运行的时间间隔
6.end_date:到期时间
7.enabled:创建后自动激活
8.auto_drop:默认true,即当job执行完毕都到期是否直接删除job
9.comments:备注
repeat_interval参数实例:
FREQ:YEARLY(年) | MONTHLY(月) | WEEKLY(周) | DAILY(日) | HOURLY(时) | MINUTELY(分) | SECONDLY(秒)
BYDAY=:“MON” | “TUE” | “WED” | “THU” | “FRI” | “SAT” | “SUN”
BYMONTH: “JAN” | “FEB” | “MAR” | “APR” | “MAY” | “JUN” | “JUL” | “AUG” | “SEP” | “OCT” | “NOV” | “DEC”
下面是一些例子:
-- REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=1', 每小时执行一次
-- REPEAT_INTERVAL => 'FREQ=MINUTELY; INTERVAL=5', 每5分钟执行一次
-- REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=1; BYDAY=MON', 每周一执行一次
-- REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=1; BYDAY=MON; BYHOUR=8; BYMINUTE=00', 每周一早上8点执行一次
-- REPEAT_INTERVAL => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1; BYHOUR=8; BYMINUTE=00', 每月第一天早上8点执行一次
定时器实例相关操作
相关操作
- call dbms_scheduler.run(‘jobName’) – 运行job
- call dbms_scheduler.stop_job(‘jobName’,force) 停止job,force默认为false
- call dbms_scheduler.drop_job(‘jobName’); 删除job
- call dbms_scheduler.enable(‘jobName’);打开job
- call dbms_scheduler.disable(‘jobName’,force); 禁用job,force参数用于dependencies
相关视图
- select * from user_scheduler_jobs 查看所有job信息
- select * from user_scheduler_running_jobs 查看所有正在运行的job
- select * from user_scheduler_job_run_details job运行日志
- select * from User_Scheduler_Job_Log job job日志
参考教程:
https://blog.csdn.net/qq_38425803/article/details/108128652
https://www.pianshen.com/question/45331241404/