学步园

procedure proc_tmp_flowlog is

    cursor cur01 is
      select flow_id, gtin, security_code, flow_log, flag, priinfo_id, encrypt_date, create_time
        from t_csc_temp_flowlog
       where flag = '1';
       
    --v_gtin varchar2(100); --gtin码
    v_conn_afters varchar2(4000); --后继
    --var01 number;
    
   begin
  
    --一次更新本次job取到未处理的流通记录
    update t_csc_temp_flowlog set flag = '1' where flag = '0';
  
    for r01 in cur01 loop
    
      begin
        merge into t_csc_flowlog a
        using (select pro_id,
                      gtin,
                      security_code,
                      priinfo_id,
                      encrypt_date,
                      back_nodes
                 from t_csc_product
                where security_code = r01.security_code
                  and gtin = r01.gtin
                  and priinfo_id = r01.priinfo_id
                  and encrypt_date = r01.encrypt_date) b
        on (a.gtin = b.gtin and a.security_code = b.security_code and a.priinfo_id = b.priinfo_id and a.encrypt_date = b.encrypt_date)
        when matched then
          update set a.flow_log = r01.flow_log
        when not matched then
          insert
            (flow_id,
             pro_id,
             gtin,
             security_code,
             flow_log,
             priinfo_id,
             encrypt_date,
             create_time)
          values
            (func_get_seq('t_csc_flowlog'),
             b.pro_id,
             b.gtin,
             b.security_code,
             r01.flow_log,
             b.priinfo_id,
             b.encrypt_date,
             sysdate);
      
        --正常
        --dbms_output.put_line('正常处理的：' || r01.security_code);
      
        --出错
        --select 1 / 0 into var01 from dual;
      
      exception
        when others then
          --如果插入t_csc_flowlog异常,记录出错信息到t_csc_flowlog_err表
          insert into t_csc_flowlog_err
            (flow_id,
             gtin,
             security_code,
             flow_log,
             flag,
             create_time,
             priinfo_id,
             encrypt_date,
             log_user,
             log_date)
          values
            (r01.flow_id,
             r01.gtin,
             r01.security_code,
             r01.flow_log,
             '0',
             r01.create_time,
             r01.priinfo_id,
             r01.encrypt_date,
             user,
             sysdate);
      end;
    
      --取出后驱
      select back_nodes
        into v_conn_afters
        from t_csc_product
       where security_code = r01.security_code
         and gtin = r01.gtin
         and priinfo_id = r01.priinfo_id
         and encrypt_date = r01.encrypt_date;
    
      --后驱不为 -1 即存在后继 后继保存的为防伪码 e.g. v_conn_afters=110,130
      if (v_conn_afters <> '-1') then
      
        declare
          cursor cur02 is
            select column_value
              from table(func_split(v_conn_afters, ','));
          v_proc_seccode cur02%rowtype;
        
        begin
          open cur02;
        
          loop
            fetch cur02
              into v_proc_seccode;
            exit when cur02%notfound;
          
            begin
              merge into t_csc_flowlog a
              -- 查询时 根据主键来查询  priinfo_id 和 encrypt_date 用于分区查询
              using (select pro_id,
                            gtin,
                            security_code,
                            priinfo_id,
                            encrypt_date
                       from t_csc_product b
                      where priinfo_id = r01.priinfo_id
                        and encrypt_date = r01.encrypt_date
                        --and gtin = v_gtin
                        and security_code = v_proc_seccode.column_value) b
              on (a.gtin = b.gtin and a.security_code = b.security_code and a.priinfo_id = b.priinfo_id and a.encrypt_date = b.encrypt_date)
              when matched then
                update set a.flow_log = r01.flow_log
              when not matched then
                insert
                  (flow_id,
                   pro_id,
                   gtin,
                   security_code,
                   flow_log,
                   priinfo_id,
                   encrypt_date,
                   create_time)
                values
                  (func_get_seq('t_csc_flowlog'),
                   b.pro_id,
                   b.gtin,
                   b.security_code,
                   r01.flow_log,
                   b.priinfo_id,
                   b.encrypt_date,
                   sysdate);
            
            exception
              when others then
                --如果插入t_csc_flowlog异常,记录出错信息到t_csc_flowlog_err表
                insert into t_csc_flowlog_err
                  (flow_id,
                   gtin,
                   security_code,
                   flow_log,
                   flag,
                   create_time,
                   priinfo_id,
                   encrypt_date,
                   log_user,
                   log_date)
                values
                  (r01.flow_id,
                   r01.gtin,
                   r01.security_code,
                   r01.flow_log,
                   '0',
                   r01.create_time,
                   r01.priinfo_id,
                   r01.encrypt_date,
                   user,
                   sysdate);
            end;
          
          end loop;
        
          close cur02;
          --dbms_output.put_line(v_proc_id.column_value);
        end;
      end if;
    
    end loop;
  
    --删除t_csc_temp_flowlog中已经处理过的数据
    delete t_csc_temp_flowlog where flag = '1';
  
    commit;
  
  exception
    when others then
      raise_application_error('-20001',
                              'pkg_flowlog.proc_tmp_flowlog处理流通记录表出错');
  end proc_tmp_flowlog;

【当执行事务操作（DML语句 insert/update/delete/select…for update）时，Oracle会在被作用表上加表锁，以防止其他用户改变表结构；同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。】