动态SQL的应用案例

select 的 语句就用 open p_rc for

insert   update  delete 的语句就用  execute immediate 

下面给出具体实例：

动态SQL在select 语句上的使用:

create or replace procedure hq_cin(v_locCd_i     in varchar2,
                                   v_beginDate_i in varchar2,
                                   v_endDate_i   in varchar2,
                                   p_rc out sys_refcursor)
as
    v_beginDate varchar2(100);
    v_endDate   varchar2(100);
    v_sql_str   varchar2(30000);
    v_loc_i      varchar2(20);


begin
  v_loc_i:='@LOC'||v_locCd_i;
    v_beginDate := 'to_date(''' || v_beginDate_i || ' 0600'',' ||
                     '''YYYY-MM-DD HH24MI''' || ')';
    v_endDate   := 'to_date(''' || v_endDate_i || ' 0600'',' ||
                     '''YYYY-MM-DD HH24MI''' || ') +1 ';
                     
v_sql_str :='select ''HQ_POS_PROFIT'', ''HQ'', count(*), sum(hpf_sell_amt)
  from HQ_POS_PROFIT
  where hpf_location_cd =' || v_locCd_i || ' and hpf_rcre_date between '||
       v_beginDate || ' and ' || v_endDate ||
' union
  select ''HQ_POS_PROFIT'', ''CIN'', count(*), sum(hpf_sell_amt)
  from '|| 'HQ_POS_PROFIT'|| v_loc_i ||
 ' where hpf_location_cd ='|| v_locCd_i || ' and hpf_rcre_date between '||
       v_beginDate || ' and ' || v_endDate ||

      
' union
  select ''HQ_POS_STOCK_MOVE'', ''HQ'', count(*), sum(0)
  from HQ_POS_STOCK_MOVE
  where HQM_LOC_CD = '|| v_locCd_i ||' and HQM_RCRE_DATE between '||
       v_beginDate || ' and ' || v_endDate ||
' union
  select ''HQ_POS_STOCK_MOVE'', ''CIN'', count(*), sum(0)
  from '|| 'HQ_POS_STOCK_MOVE'|| v_loc_i ||
 ' where HQM_RCRE_DATE between '||
       v_beginDate || ' and ' || v_endDate;

dbms_output.put_line(sqlerrm);
open p_rc for  v_sql_str;
commit;
end hq_cin;

动态SQL在select 语句上的使用:

function MovePOS_STOCK(v_beginDate_i in varchar2,
                         v_endDate_i   in varchar2,
                         v_locCode_i   in varchar2,
                         v_exception_o OUT VARCHAR2) return string is
    v_beginDate varchar2(100);
    v_endDate   varchar2(100);
    v_sql_str   varchar2(8000);
    v_sql_str1  varchar2(800);
  
  begin
    if (v_beginDate_i is null or v_endDate_i is null) then
      v_beginDate := 'to_date(''' || to_char(sysdate - 2, 'yyyy-mm-dd') ||
                     ' 0600'',' || '''YYYY-MM-DD HH24MI''' || ')';
      v_endDate   := 'to_date(''' || to_char(sysdate, 'yyyy-mm-dd') ||
                     ' 0559'',' || '''YYYY-MM-DD HH24MI''' || ') + 1';
    else
      v_beginDate := 'to_date(''' || v_beginDate_i || ' 0600'',' ||
                     '''YYYY-MM-DD HH24MI''' || ')';
      v_endDate   := 'to_date(''' || v_endDate_i || ' 0559'',' ||
                     '''YYYY-MM-DD HH24MI''' || ') + 1';
    end if;
  
    update HQ_POS_STOCK_TEMP
       set HPC_REPEAT_FLG = 'Y'
     where (HPC_LOC_CD,HPC_DATA_RCRE_DATE,HPC_GOOD_ID,HPC_PLACE_ID,HPC_PLACE_TYPE) in (select HPC_LOC_CD,HPC_DATA_RCRE_DATE,HPC_GOOD_ID,HPC_PLACE_ID,HPC_PLACE_TYPE
                            from HQ_POS_STOCK_TEMP
                           where HPC_MEGRE_FLG = 'N'
                             AND HPC_REPEAT_FLG = 'N'
                           group by HPC_LOC_CD,HPC_DATA_RCRE_DATE,HPC_GOOD_ID,HPC_PLACE_ID,HPC_PLACE_TYPE
                          having count(*) > 1)
       and HPC_TIMESTAMP not in (select max(HPC_TIMESTAMP)
                           from HQ_POS_STOCK_TEMP
                           where HPC_MEGRE_FLG = 'N'
                             AND HPC_REPEAT_FLG = 'N'
                          group by HPC_LOC_CD,HPC_DATA_RCRE_DATE,HPC_GOOD_ID,HPC_PLACE_ID,HPC_PLACE_TYPE
                         having count(*) > 1);
    commit;
  
    v_sql_str := 'merge into HQ_POS_STOCK P
       using (select *
               from HQ_POS_STOCK_TEMP
              where HPC_DATA_RCRE_DATE between ' ||
                 v_beginDate || ' and ' || v_endDate;
    if v_locCode_i is not null then
      v_sql_str := v_sql_str || ' and HPC_LOC_CD = ' || v_locCode_i;
    end if;
    v_sql_str := v_sql_str || ' and HPC_MEGRE_FLG =''N'' and HPC_REPEAT_FLG=''N'' order by HPC_TIMESTAMP) T
      ON (P.HPC_LOC_CD = T.HPC_LOC_CD and P.HPC_GOOD_ID=T.HPC_GOOD_ID 
          and P.HPC_PLACE_ID=T.HPC_PLACE_ID and P.HPC_PLACE_TYPE=T.HPC_PLACE_TYPE and P.HPC_DATA_RCRE_DATE=T.HPC_DATA_RCRE_DATE)
      when matched then
        update
           set 
               P.HPC_COUNT          = T.HPC_COUNT
      when not matched then
        insert
          (P.HPC_DATA_RCRE_DATE,
           P.HPC_LOC_CD,
           P.HPC_PLACE_TYPE,
           P.HPC_PLACE_ID,
           P.HPC_GOOD_ID,
           P.HPC_COUNT)
        values
              (T.HPC_DATA_RCRE_DATE,
               T.HPC_LOC_CD,
               T.HPC_PLACE_TYPE,
               T.HPC_PLACE_ID,
               T.HPC_GOOD_ID,
               T.HPC_COUNT)';
  
    v_sql_str1 := 'update HQ_POS_STOCK_TEMP
         set HPC_MEGRE_FLG = ''Y'', HPC_MEGRE_TIME = sysdate
       where HPC_REPEAT_FLG=''N'' and HPC_DATA_RCRE_DATE between ' ||
                  v_beginDate || ' and ' || v_endDate;
    if v_locCode_i is not null then
      v_sql_str1 := v_sql_str1 || ' and HPC_LOC_CD = ' || v_locCode_i;
    end if;
    execute immediate v_sql_str;
    execute immediate v_sql_str1;
    commit;
    return 0;
  exception
    when others then
      v_exception_o := sqlerrm;
      return 4;
  end MovePOS_STOCK;