Oracle 随机数 记录时间 for循环

create or replace procedure Proc_startPunchWork
as

declare
 hh         integer := 7;
  mm         integer;
  ss         integer;
  todayDate  varchar2(20);
  systemDate varchar2(30);
  wendu      varchar(10);
  state      integer := 0;
  startDate  Date;
begin
  for person in (select t.id from t_users t where t.id in(1,2,3,4,5,6,7,8,9,10,12,13,455)) loop
    select t.mmval
      into mm
      from (select trunc(dbms_random.value(0, 60)) as mmval from dual) t;
    select t.mmval
      into ss
      from (select trunc(dbms_random.value(0, 60)) as mmval from dual) t;
    select t.mmval
      into ss
      from (select trunc(dbms_random.value(0, 60)) as mmval from dual) t;
    select s.systime, s.today
      into systemDate, todayDate
      from (select to_char(sysdate, 'yyyy-MM-dd ') || hh || ':' || mm || ':' || ss as systime,
                   to_char(sysdate, 'yyyy-MM-dd ') as today
              from dual) s;
    select p.wendu
      into wendu
      from (SELECT trunc(dbms_random.value(36.1, 36.7), 1) wendu FROM dual) p;
    /*dbms_output.put_line(todayDate);
    dbms_output.put_line(systemDate);*/
    select count(*)
      into state
      from T_PUNCHRECORDS t
     where t.userid = person.id
       and t.today = to_date(todayDate, 'yyyy-MM-dd');
    dbms_output.put_line(state);
    if state = 1 then
      select t.startworktime
        into startDate
        from T_PUNCHRECORDS t
       where t.userid = person.id
         and t.today = to_date(todayDate, 'yyyy-MM-dd');
      if startDate > to_date(todayDate||' 8:0:0', 'yyyy-MM-dd hh:mi:ss') then
        UPDATE T_PUNCHRECORDS a
           SET a.startworktime = to_date(systemDate, 'yyyy-MM-dd hh:mi:ss'),
               a.STARTFLAG     = 1,
               a.inserttime    = to_date(systemDate, 'yyyy-MM-dd hh:mi:ss')
         where a.userid = person.id
           and a.today = to_date(todayDate, 'yyyy-MM-dd');
      end if;
    else
      INSERT into T_PUNCHRECORDS a
        (A.ID,
         a.userid,
         a.today,
         a.startworktime,
         a.STARTFLAG,
        a.MTEMP,
         a.inserttime)
      VALUES
        ((select nvl(max(ID), 0) + 1 from T_PUNCHRECORDS),
         person.id,
         to_date(todayDate, 'yyyy-MM-dd hh:mi:ss'),
         to_date(systemDate, 'yyyy-MM-dd hh:mi:ss'),
         1,
         wendu,
         to_date(systemDate, 'yyyy-MM-dd hh:mi:ss'));
    end if;
    end loop;
  commit;
end;

 

--下午打卡
create or replace procedure Proc_endPunchWork as

  declare
  hh         integer := 17;
  mm         integer;
  ss         integer;
  todayDate  varchar2(20);
  systemDate varchar2(30);
  wendu      varchar(10);
  state      integer := 0;
  startDate  Date;
  endFlag    integer := 0;
begin
  for person in (select t.id from t_users t where t.id in(1,2,3,4,5,6,7,8,9,10,12,13,455)) loop
    select t.mmval
      into mm
      from (select trunc(dbms_random.value(0, 60)) as mmval from dual) t;
    select t.mmval
      into ss
      from (select trunc(dbms_random.value(0, 60)) as mmval from dual) t;
    select t.mmval
      into ss
      from (select trunc(dbms_random.value(0, 60)) as mmval from dual) t;
    select s.systime, s.today
      into systemDate, todayDate
      from (select to_char(sysdate, 'yyyy-MM-dd ') || hh || ':' || mm || ':' || ss as systime,
                   to_char(sysdate, 'yyyy-MM-dd ') as today
              from dual) s;
    select p.wendu
      into wendu
      from (SELECT trunc(dbms_random.value(36.1, 36.7), 1) wendu FROM dual) p;
 
    select count(*)
      into state
      from T_PUNCHRECORDS t
     where t.userid = person.id
       and t.today = to_date(todayDate, 'yyyy-MM-dd');
    dbms_output.put_line(state);
    if state = 1 then
      select t.endworktime, t.endflag
        into startDate, endFlag
        from T_PUNCHRECORDS t
       where t.userid = person.id
         and t.today = to_date(todayDate, 'yyyy-MM-dd');
      if endFlag = 0 then
        UPDATE T_PUNCHRECORDS a
           SET a.endworktime = to_date(systemDate, 'yyyy-MM-dd hh24:mi:ss'),
               a.endflag     = 1,
               a.atemp =wendu
         where a.userid = person.id
           and a.today = to_date(todayDate, 'yyyy-MM-dd');
      end if;
    end if;
  end loop;
  commit;
end;