oracle Trigger

Peerless_
浏览: 4366 次
性别:
最近访客更多访客>>

fengtaibin
iam_3k
Cobain_LI
THU天马行空
博主相关

博客
微博
相册
留言
关于我
文章分类

社区版块

存档分类

博客分类：
oracle
oracle
create or replace trigger trigger_job_log
  after insert or update or delete on portal_job_log  
  for each row
declare
  pragma autonomous_transaction;
  v_sql             varchar2(1000);
  v_sql_ye          varchar2(1000);
  v_day             varchar2(2);
  r_bwc             PORTAL_BASE_WORKCLASS%rowtype;
  r_bwc_1           PORTAL_BASE_WORKCLASS%rowtype;
  r_bwc_2           PORTAL_BASE_WORKCLASS%rowtype;
  v_total_half_hour number default 0;
  v_total_log       number default 0;
  v_rest            number default 0;
  v_timefrom1       varchar2(20);
  v_timefrom2       varchar2(20);
  v_timeend1        varchar2(20);
  v_timeend2        varchar2(20);
  v_log_date        varchar2(20);
  v_log_date_2      varchar2(20);
  TYPE i_cursor_type IS REF CURSOR;
  job_log_cur i_cursor_type;
  v_is_work   char(1);
  row_data    portal_job_log%rowtype;
  /*  type r_record is record(
       start_time varchar2(20),
       end_time   varchar2(20),
       log_date   date
  );
  type t_table is table of r_record index by binary_integer;
  t_job_log t_table;*/
begin
  if inserting or updating then
    row_data.account    := :new.account;
    row_data.log_date   := :new.log_date;
    row_data.start_time := :new.start_time;
    row_data.end_time   := :new.end_time;
  else
    row_data.account    := :old.account;
    row_data.log_date   := :old.log_date;
    row_data.start_time := :old.start_time;
    row_data.end_time   := :old.end_time;
  end if;
  v_day        := to_number(to_char(row_data.log_date, 'dd'));
  v_log_date   := to_char(row_data.log_date, 'yyyymmdd');
  v_log_date_2 := to_char(row_data.log_date + 1, 'yyyymmdd');
  v_sql        := '
  select bwc.* from PORTAL_EMP_WORKCLASS wc join PORTAL_BASE_WORKCLASS bwc on wc.d' ||
                  v_day || ' = bwc.workid
  where wc.emp_no = ''' || row_data.account || ''' 
  and wc.year_tmp = ''' ||
                  to_char(row_data.log_date, 'yyyy') || '''
  and wc.month_tmp =''' ||
                  to_char(row_data.log_date, 'mm') || '''';
  v_sql_ye     := '
  select bwc.* from PORTAL_EMP_WORKCLASS wc join PORTAL_BASE_WORKCLASS bwc on wc.d' ||
                  (to_number(to_char(row_data.log_date - 1, 'dd'))) ||
                  ' = bwc.workid
  where wc.emp_no = ''' || row_data.account || ''' 
  and wc.year_tmp = ''' ||
                  to_char(row_data.log_date - 1, 'yyyy') || '''
  and wc.month_tmp =''' ||
                  to_char(row_data.log_date - 1, 'mm') || '''';
  begin
    execute immediate v_sql
      into r_bwc_1;
    execute immediate v_sql_ye
      into r_bwc_2;
  exception
    when others then
      goto do_now;
  end;
  -- 
  -- 
  if (r_bwc_2.timeend2 < r_bwc_2.timefrom1) and row_data.end_time < '12:00' then
    r_bwc        := r_bwc_2;
    v_log_date   := to_char(row_data.log_date - 1, 'yyyymmdd');
    v_log_date_2 := to_char(row_data.log_date, 'yyyymmdd');
    begin
      select pc.iswork
        into v_is_work
        from portal_calendar pc
       where trunc(pc.workdate) = trunc(row_data.log_date - 1)
         and pc.factoryid =
             (select decode(pu.area, 'HZGL', 'HZSZ', 'HZLH', 'HZSZ', pu.area)
                from portal_user pu
               where pu.account = row_data.account);
    exception
      when others then
        v_is_work := '0';
    end;
  else
    r_bwc := r_bwc_1;
    begin
      select pc.iswork
        into v_is_work
        from portal_calendar pc
       where trunc(pc.workdate) = trunc(row_data.log_date)
         and pc.factoryid =
             (select decode(pu.area, 'HZGL', 'HZSZ', 'HZLH', 'HZSZ', pu.area)
                from portal_user pu
               where pu.account = row_data.account);
    exception
      when others then
        v_is_work := '0';
    end;
  end if;

  if v_is_work is not null and v_is_work = '1' then
  
    if 'OT' = r_bwc.workid then
      goto do_now;
    end if;
    v_timefrom1 := r_bwc.timefrom1;
    v_timeend1  := r_bwc.timeend1;
    v_timefrom2 := r_bwc.timefrom2;
    v_timeend2  := r_bwc.timeend2;
    /* v_timeend1 := replace(v_timeend1, '00:00:00', '24:00:00');
    v_timeend2 := replace(v_timeend2, '00:00:00', '24:00:00');*/
    -- 
    if v_timeend1 < v_timefrom1 then
      v_timefrom1 := v_log_date || v_timefrom1;
      v_timeend1  := v_log_date_2 || v_timeend1;
      v_timefrom2 := v_log_date_2 || v_timefrom2;
      v_timeend2  := v_log_date_2 || v_timeend2;
      -- 
    elsif v_timefrom2 < v_timeend1 then
      v_timefrom1 := v_log_date || v_timefrom1;
      v_timeend1  := v_log_date || v_timeend1;
      v_timefrom2 := v_log_date_2 || v_timefrom2;
      v_timeend2  := v_log_date_2 || v_timeend2;
      -- 
    elsif v_timeend2 < v_timefrom2 then
      v_timefrom1 := v_log_date || v_timefrom1;
      v_timeend1  := v_log_date || v_timeend1;
      v_timefrom2 := v_log_date || v_timefrom2;
      v_timeend2  := v_log_date_2 || v_timeend2;
      -- 
    else
      v_timefrom1 := v_log_date || v_timefrom1;
      v_timeend1  := v_log_date || v_timeend1;
      v_timefrom2 := v_log_date || v_timefrom2;
      v_timeend2  := v_log_date || v_timeend2;
    end if;
  
    v_total_half_hour := (to_date(v_timeend1, 'yyyymmddhh24:mi:ss') -
                         to_date(v_timefrom1, 'yyyymmddhh24:mi:ss')) * 24 * 2 +
                         (to_date(v_timeend2, 'yyyymmddhh24:mi:ss') -
                         to_date(v_timefrom2, 'yyyymmddhh24:mi:ss')) * 24 * 2;
    v_rest            := (to_date(v_timefrom2, 'yyyymmddhh24:mi:ss') -
                         to_date(v_timeend1, 'yyyymmddhh24:mi:ss')) * 24 * 2;
    if r_bwc.timeend2 > r_bwc.timefrom1 then
      v_sql := 'select id, start_time, end_time, log_date from portal_job_log where account = ''' ||
               row_data.account ||
               ''' and to_char(log_date,''yyyymmdd'') = ''' ||
               to_char(row_data.log_date, 'yyyymmdd') || '''';
    elsif row_data.end_time < '12:00' then
      v_sql := 'select id, start_time, end_time, log_date from portal_job_log where account = ''' ||
               row_data.account ||
               ''' and ( to_char(log_date,''yyyymmdd'') = ''' ||
               to_char(row_data.log_date, 'yyyymmdd') ||
               ''' or  to_char(log_date,''yyyymmdd'') = ''' ||
               to_char(row_data.log_date - 1, 'yyyymmdd') || ''')';
    else
      v_sql := 'select id, start_time, end_time, log_date from portal_job_log where account = ''' ||
               row_data.account ||
               ''' and ( to_char(log_date,''yyyymmdd'') = ''' ||
               to_char(row_data.log_date, 'yyyymmdd') ||
               ''' or  to_char(log_date,''yyyymmdd'') = ''' ||
               to_char(row_data.log_date + 1, 'yyyymmdd') || ''')';
    end if;
  
    open job_log_cur for v_sql;
    loop
      declare
        v_start_time varchar2(20);
        v_end_time   varchar2(20);
        v_date       date;
        v_id         portal_job_log.id%type;
      begin
        fetch job_log_cur
          into v_id, v_start_time, v_end_time, v_date;
      
        if job_log_cur%NOTFOUND and (inserting or updating) then
          v_start_time := to_char(:new.log_date, 'yyyymmdd') ||
                          :new.start_time || ':00';
          if :new.end_time = '24:00' then
            v_end_time := to_char(:new.log_date + 1, 'yyyymmdd') ||
                          '00:00:00';
          else
            v_end_time := to_char(:new.log_date, 'yyyymmdd') ||
                          :new.end_time || ':00';
          end if;
        
        else
          v_start_time := to_char(v_date, 'yyyymmdd') || v_start_time ||
                          ':00';
          if v_end_time = '24:00' then
            v_end_time := to_char(v_date + 1, 'yyyymmdd') || '00:00:00';
          else
            v_end_time := to_char(v_date, 'yyyymmdd') || v_end_time ||
                          ':00';
          end if;
        
        end if;
        if deleting and :old.id = v_id then
          goto my_continue;
        end if;
        if (inserting or updating) and :new.id = v_id then
          goto my_continue;
        end if;
        if v_start_time <= v_timefrom1 then
          v_start_time := v_timefrom1;
        end if;
        if v_end_time >= v_timeend2 then
          v_end_time := v_timeend2;
        end if;
        if v_start_time >= v_timeend1 and v_start_time <= v_timefrom2 then
          v_start_time := v_timefrom2;
        end if;
        if v_end_time >= v_timeend1 and v_end_time <= v_timefrom2 then
          v_end_time := v_timeend1;
        end if;
        if v_start_time >= v_end_time then
          v_total_log := v_total_log + 0;
        else
          v_total_log := v_total_log +
                         (to_date(v_end_time, 'yyyymmddhh24:mi:ss') -
                         to_date(v_start_time, 'yyyymmddhh24:mi:ss')) * 24 * 2;
        end if;
        if v_start_time <= v_timeend1 and v_end_time >= v_timefrom2 then
          v_total_log := v_total_log - v_rest;
        end if;
        <<my_continue>>
        exit when job_log_cur%NOTFOUND;
      
      end;
    end loop;
    close job_log_cur;
  end if;
  <<do_now>>
  if v_total_log = v_total_half_hour and (inserting or updating) then
    declare
      count_ number default 0;
    begin
      select count(1)
        into count_
        from portal_day_job dj
       where dj.account = row_data.account
         and to_char(dj.log_date, 'yyyymmdd') = v_log_date;
    
      if count_ = 0 then
        insert into portal_day_job
          (id, account, log_date, is_full)
        values
          (v_log_date || row_data.account,
           row_data.account,
           to_date(v_log_date, 'yyyymmdd'),
           1);
        commit;
      end if;
    end;
  else
    delete from portal_day_job dj
     where dj.account = row_data.account
       and to_char(dj.log_date, 'yyyymmdd') = v_log_date;
    commit;
  end if;

exception
  when others then
    rollback;
    dbms_output.put_line(sqlerrm);
end trigger_job_log;
分享到：