先创建表
1,create table UUMS_USER
(
id VARCHAR2(32) not null,
login_name NVARCHAR2(200) not null,
real_name NVARCHAR2(200) not null,
password VARCHAR2(200) not null,
salt VARCHAR2(100) not null,
status NUMBER(1) not null,
ctime DATE,
utime DATE,
orders NUMBER(5) not null,
last_login DATE,
psdupdatetime DATE default SYSDATE,
pid VARCHAR2(32),
pids VARCHAR2(2000),
start_date DATE,
end_date DATE
)
;
comment on table UUMS_USER
is '用户';
comment on column UUMS_USER.id
is 'id';
comment on column UUMS_USER.login_name
is '登录名';
comment on column UUMS_USER.real_name
is '姓名';
comment on column UUMS_USER.password
is '密码';
comment on column UUMS_USER.salt
is '密码盐';
comment on column UUMS_USER.status
is '状态 ,0 是无效,1是有效';
comment on column UUMS_USER.ctime
is '创建时间';
comment on column UUMS_USER.utime
is '更新时间';
comment on column UUMS_USER.orders
is '排序';
comment on column UUMS_USER.last_login
is '最后登录时间';
comment on column UUMS_USER.psdupdatetime
is '密码更新时间';
comment on column UUMS_USER.pid
is '父用户id';
comment on column UUMS_USER.pids
is '所有父用户id';
comment on column UUMS_USER.start_date
is '起止时间';
comment on column UUMS_USER.end_date
is '结束时间';
alter table UUMS_USER
add constraint PK_UUMS_USER primary key (ID);
alter table UUMS_USER
add constraint U_LOGIN_NAME unique (LOGIN_NAME);
2,创建存储过程
create or replace procedure pro_user_status is
p_status number(1);
begin
p_status:=0;
update uums_user t set t.status=p_status where t.end_date<sysdate;
commit;
end pro_user_status;
3,创建定时任务
declare job_user_status number;
begin
dbms_job.submit(job=>job_user_status,what =>'pro_user_status;',next_date =>sysdate ,interval =>'sysdate+1' -- 如果时间是设置分钟(‘sysdate+1/24/60’)
);
end;
这里面有个坑,如果直接复制过去,不能执行,肯定是符合或者空格问题导致,照着敲一遍
存储过程是否创建成功,执行以下就知道
call pro_user_status ();
-- 查询定时任务id
SELECT * FROM USER_JOBS;
-- 执行定时任务
begin
dbms_job.run(24);
commit;
end;