Oracle动态SQL

  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;