首先建表，还有序列：
代码语言：javascript复制-- 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;然后，获取当前是星期几的函数：
代码语言：javascript复制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;判断是不是周末的函数，如果是周末，则不进行打卡。
代码语言：javascript复制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要跑的存储过程：
代码语言：javascript复制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:
代码语言：javascript复制 begin
dbms_job.run(1);
end;
