Oracle动态SQL

来源：互联网发布：mac 打字反应很慢编辑：程序博客网时间：2024/06/07 00:33
  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;
0 0