写了个简单的job

2019独角兽企业重金招聘Python工程师标准>>>

首先建表，还有序列：

-- Create table
create table MYDAILYDK
(
  dkproint NUMBER not null,
  week     VARCHAR2(100) not null,
  dktime   DATE not null,
  name     VARCHAR2(255) not null,
  id       NUMBER not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column MYDAILYDK.dkproint
  is '打卡得分';
comment on column MYDAILYDK.week
  is '打卡日期（星期）';
comment on column MYDAILYDK.dktime
  is '打卡时间';
comment on column MYDAILYDK.name
  is '打卡人姓名';
comment on column MYDAILYDK.id
  is '主键id';
-- Create/Recreate primary, unique and foreign key constraints 
alter table MYDAILYDK
  add constraint ID primary key (ID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create sequence 
create sequence S_MYDAILYDK
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache
order;

然后，获取当前是星期几的函数：

create or replace function getcurrentday return varchar2

/**
description 获取当前日期的函数
return 当前的星期  是周几
the day 2016-12-12
*/
is
currentday varchar2(50);
begin

select decode(to_char(sysdate-1, 'd'),1,'星期一',2,'星期二',3,'星期三',4,'星期四',5,'星期五',6,'星期六',7,'星期天') into currentday
from dual;
return currentday;

end;

判断是不是周末的函数，如果是周末，则不进行打卡。

create or replace function isweekend return Boolean

/**
description 获取当前日期的函数
return true or false
如果是工作日则 return true ,else return false
*/
is
currentday varchar2(50);
isweekend Boolean;
begin

select to_char(sysdate-1, 'd') into currentday
from dual;
 if currentday = 6 or currentday = 7 then
 isweekend := true;
 else
 isweekend := false;
 end if;
return isweekend;

end;

打卡job要跑的存储过程：

create or replace procedure mbproc_mydailydk

as
-- description to record the daily work automitic
begin
if isweekend = false then
insert into mydailydk(id,name,dktime,currentday,Dkponit)
values(s_madailydk.nextval,'wendy',sysdate,getcurrentday,trunc(dbms_random.value(1,101)));
commit;
end if;
end;

手动调用执行job:

  begin
  dbms_job.run(1);
  end;

 

转载于:https://my.oschina.net/xiaomingnevermind/blog/894246