Oracle：动态SQL实例

 

create or replace procedure FSPVIQA7_qty
as
  var_sql   varchar2(2000);
  i         number;
  j         number;
begin
  delete from tpt_app_qty;
  i := 1;
  while i<=5 loop
    j := 1;
    while j <= 3 loop
      var_sql := '';
      var_sql := ' insert into tpt_app_qty(tpt_flow,tpt_nbr,tpt_crt_dept,tpt_date,tpt_imp_dept,tpt_cm,tpt_cm_sort,tpt_need,tpt_opinion,tpt_n_opinion,
                                          tpt_engineer,tpt_eng_name,tpt_crt_by,tpt_crt_name)
                   select a.app_flow,a.app_nbr,a.app_dept,a.app_date,a.app_'||to_char(i)||'_dept'||',a.app_cm,cm_sort,a.app_need,'||to_char(i)||to_char(j)||',a.app_'||to_char(i)||'_summary,
                   app_'||to_char(i)||'_engineer'||to_char(j)||',u1.usr_name,app_crt_by,u.usr_name from app1_mstr a
                   left join v_erp_cust on cm_addr=app_cm
                   join usr_mstr u on u.usr_user=app_crt_by
                   join usr_mstr u1 on u1.usr_user=app_'||to_char(i)||'_engineer'||to_char(j)||
                   ' where app_'||to_char(i)||'_pst=''1''
                   and app_'||to_char(i)||'_sub'||to_char(j)||'=''0''
                   and app_'||to_char(i)||'_engineer'||to_char(j)||' is not null
                   and app_product_fee = 0
                   and not exists(select 1 from app1_mstr b where a.app_nbr = b.app_nbr and b.app_version > a.app_version)
                   union all
                   select app_flow,app_nbr,app_dept,app_date,app_'||to_char(i)||'_dept'||',app_cm,cm_sort,app_need,'||to_char(i)||to_char(j)||',app_'||to_char(i)||'_summary,
                   app_'||to_char(i)||'_engineer'||to_char(j)||',u1.usr_name,app_crt_by,u.usr_name from app1_mstr a
                   left join v_erp_cust on cm_addr=app_cm
                   join usr_mstr u on u.usr_user=app_crt_by
                   join usr_mstr u1 on u1.usr_user=app_'||to_char(i)||'_engineer'||to_char(j)||
                   ' where app_'||to_char(i)||'_pst=''1''
                   and app_work  = 1
                   and app_prog_code = ''FSPUMTA4''
                   and not exists(select 1 from app1_mstr b where a.app_nbr = b.app_nbr and b.app_version > a.app_version)';
     -- dbms_output.put_line(var_sql);
      execute immediate var_sql;
      j := j+1;
    end loop;
    i:= i+1;
  end loop;
end FSPVIQA7_qty;