EXECUTE IMMEDIATE 动态使用一

来源：互联网发布：淘宝代销怎么刷单编辑：程序博客网时间：2024/06/18 16:38

declare

t_sql varchar2(1000);

t_strcd varchar2(10);

t_nowfirst varchar2(30);

incomcd varchar2(30);

t_jxflag varchar2(30);

indate varchar2(30);

t_table varchar2(30);

begin

t_sql := t_sql || ' insert into ' || t_table;

t_sql := t_sql || '(gmspsid0p_comcd,gmspsid0p_strcd,gmspsid0p_div,gmspsid0p_dep,';

t_sql := t_sql || 'gmspsid0p_dpt,gmspsid0p_class,gmspsid0p_area,gmspsid0p_intcd,';

t_sql := t_sql || 'gmspsid0p_saldate,gmspsid0p_mdprice,gmspsid0p_mdnetprice,gmspsid0p_grsprice,';

t_sql := t_sql || 'gmspsid0p_grscost,gmspsid0p_netprice,gmspsid0p_netcost,gmspsid0p_freshflg,';

t_sql := t_sql || 'gmspsid0p_supcd,gmspsid0p_conno)';

t_sql := t_sql || ' select gmsmmitm_comcd,:t_strcd,gmsmmitm_divcd,gmsmmitm_depcd,';

t_sql := t_sql || 'gmsmmitm_dptcd,gmsmmitm_clscd,gmsmmitm_areacd,gmsmmitm_intcd,';

t_sql := t_sql || 'to_date(:t_nowfirst,:t_format2),gmsmmitm_grscost,gmsmmitm_netcost,gmsmmitm_grsprice,';

t_sql := t_sql || 'gmsmmitm_grscost,gmsmmitm_netprice,gmsmmitm_netcost,gmsmmitm_freshflg,';

t_sql := t_sql || 'gmsmmitm_vendor,gmsmmitm_contract';

t_sql := t_sql || ' from gmsmm_itemb ';

t_sql := t_sql || ' where gmsmmitm_comcd =:t_comcd1 ';

t_sql := t_sql || ' and gmsmmitm_manager =:t_jxflag1 ';

t_sql := t_sql || ' andto_char(gmsmmitm_createdate,''yyyymmdd'') = :t_date1 ';

t_sql := t_sql || ' AND EXISTS (SELECT 1 FROMgmspsi_item ';

t_sql := t_sql || ' WHERE gmspsiitm_comcd =gmsmmitm_comcd ';

t_sql := t_sql || ' AND gmspsiitm_strcd =:t_strcd3 ';

t_sql := t_sql || ' AND gmspsiitm_intcd =gmsmmitm_intcd)';

EXECUTE IMMEDIATE t_sql

--引用动态，并且还可以对数据进行再次判断后执行

USING t_strcd, t_nowfirst, 'yyyymmdd', incomcd, t_jxflag,RTRIM(indate), case when t_loop = t_From then t_strcd else 0 end

end;