Oracle动态SQL

你的名字 2022-08-06 12:12 199阅读 0赞

procedure stp_synchronize_schedule
      is
      
          l_call_no number;
    
          depot_position_type varchar2(10) := '2';
    
          schedule_count number := 0;
    
          day_column_name varchar2(30);
    
          update_sql varchar2(300);
    
      begin
           --获取序列号
          select seq_tcas_running_log.nextval into l_call_no from dual;
          --记录开始时间
          pkg_tcas_comm.stp_running_log('pkg_tcas_schedule',
                                        'stp_synchronize_schedule',
                                        sysdate,
                                        null,
                                        null,
                                        'start',
                                        0,
                                        l_call_no);
    
         for schedule in
         (
             select s.*, s.rowid rid
             from ti_tcas_spms_schedule s
             where s.position_type = depot_position_type
             order by s.creat_time asc
         )
         loop
             begin
    
                delete from tt_tcas_depotpbs_emp_info d
                where d.emp_no = schedule.emp_code
                and d.dept_code = schedule.dept_code
                and trunc(d.work_date) = trunc(schedule.work_date);
    
                insert into tt_tcas_depotpbs_emp_info
                (
                  depot_pbs_emp_info_id,
                  emp_no,
                  emp_name,
                  area_code,
                  dept_code,
                  work_date,
                  work_time,
                  created_emp_code,
                  created_tm,
                  modified_emp_code,
                  modified_tm
                )
                values
                (
                  seq_tcas_depot.nextval,
                  schedule.emp_code,
                  schedule.emp_name,
                  schedule.area_code,
                  schedule.dept_code,
                  schedule.work_date,
                  schedule.work_time,
                  schedule.creat_emp_code,
                  schedule.creat_time,
                  schedule.modify_emp_code,
                  schedule.modify_time
                );
    
               day_column_name := 'DAY_' || extract(day from schedule.work_date);
    
               select count(1)
               into schedule_count
               from tt_tcas_depot_schedule ds
               where trunc(ds.commission_month, 'month') = trunc(schedule.work_date, 'month')
               and ds.emp_code = schedule.emp_code
               and ds.dept_code = schedule.dept_code;
    
               if schedule_count > 0 then
                  update_sql := 'update tt_tcas_depot_schedule tt set tt.' || day_column_name || '=:1 '
                             || ',tt.created_emp_code = :2 '
                             || ',tt.created_tm = :3 '
                             || ',tt.modified_emp_code = :4 '
                             || ',tt.modified_tm = :5 '
                             || ' where trunc(tt.commission_month, ''month'')=trunc(:6, ''month'') '
                             || ' and tt.emp_code = :7 '
                             || ' and tt.dept_code = :8 ';
    
                  execute immediate update_sql
                  using schedule.work_time,
                        schedule.creat_emp_code,
                        schedule.creat_time,
                        schedule.modify_emp_code,
                        schedule.modify_time,
                        schedule.work_date,
                        schedule.emp_code,
                        schedule.dept_code;
               else
                  update_sql := 'insert into tt_tcas_depot_schedule(depot_schedule_id, commission_month, area_code, emp_code, emp_name, dept_code, created_emp_code, created_tm, modified_emp_code, modified_tm, '
                             || day_column_name || ') values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)';
    
                  execute immediate update_sql
                  using seq_tcas_depot.nextval,
                        trunc(schedule.work_date, 'month'),
                        schedule.area_code,
                        schedule.emp_code,
                        schedule.emp_name,
                        schedule.dept_code,
                        schedule.creat_emp_code,
                        schedule.creat_time,
                        schedule.modify_emp_code,
                        schedule.modify_time,
                        schedule.work_time;
               end if;
    
               insert into ti_tcas_spms_schedule_bak
               (
                  schedule_id,
                  emp_code,
                  emp_name,
                  area_code,
                  dept_code,
                  group_code,
                  work_date,
                  work_time,
                  job_seq_code,
                  job_seq,
                  position_type,
                  person_type,
                  creat_emp_code,
                  creat_time,
                  modify_emp_code,
                  modify_time,
                  bak_time
               )
               values
               (
                  schedule.schedule_id,
                  schedule.emp_code,
                  schedule.emp_name,
                  schedule.area_code,
                  schedule.dept_code,
                  schedule.group_code,
                  schedule.work_date,
                  schedule.work_time,
                  schedule.job_seq_code,
                  schedule.job_seq,
                  schedule.position_type,
                  schedule.person_type,
                  schedule.creat_emp_code,
                  schedule.creat_time,
                  schedule.modify_emp_code,
                  schedule.modify_time,
                  sysdate
               );
    
               delete from ti_tcas_spms_schedule t
               where t.rowid = schedule.rid;
    
               commit;
    
               exception
               when others then
                 dbms_output.put_line('sqlcode: ' || sqlcode);
                 dbms_output.put_line('sqlerrm: ' || sqlerrm);
                 rollback;
                 begin
                   pkg_tcas_comm.stp_running_log('pkg_tcas_schedule',
                                        'stp_main_syn_schedule',
                                        sysdate,
                                        sqlcode,
                                        sqlerrm || schedule.schedule_id,
                                        'error',
                                        0,
                                        l_call_no);
                   commit;
                 end;
             end;
         end loop;
    
        --记录结束时间
        pkg_tcas_comm.stp_running_log('pkg_tcas_schedule',
                                      'stp_synchronize_schedule',
                                      sysdate,
                                      null,
                                      null,
                                      'end',
                                      0,
                                      l_call_no);
    
      end stp_synchronize_schedule;