Oracle中动态SQL拼接



1. 直接用单引号，单引号的使用是就近配对，即就近原则。从第二个单引号开始被视为转义符
v_sql := ' insert into  BJTONGRENTANGTEMPTB  select distinct h.sellerid,h.sellercode,h.sellername,h.prodcode,h.prodname from historyofsales_day h '
||' where h.sellerid in (select distinct ovalorgid from bjtongrentangpc ) '
||' and h.prodcode in (select prodcode from buproduct where bucode= '''||v_bucode||''')'
||' and to_char(h.salesdate,''yyyyMM'') =''' || v_year||v_month||'''';

if v_productcode is not null then
  v_sql := v_sql || ' and h.prodcode = '''||v_productcode||'''';
end if;

if v_seller is not null then
  v_sql := v_sql || ' and h.sellername like ''%'||v_seller||'%''';
end if;

if v_provincecode is not null then
  v_sql := v_sql || ' and h.buyerprovincecode = '''||v_provincecode||'''';
end if;

if v_productspec is not null then
  v_sql := v_sql || ' and h.prodspec like ''%'||v_productspec||'%''';
end if;

execute immediate v_sql;
commit;

2. 利用chr(39)转义单引号
v_sql := ' insert into  BJTONGRENTANGTEMPTB  select distinct h.sellerid,h.sellercode,h.sellername,h.prodcode,h.prodname from historyofsales_day h '
||' where h.sellerid in (select distinct ovalorgid from bjtongrentangpc ) '
||' and h.prodcode in (select prodcode from buproduct where bucode= '||chr(39)||v_bucode||chr(39)||')'
||' and to_char(h.salesdate,''yyyyMM'') =' ||chr(39)|| v_year||v_month||chr(39);

if v_productcode is not null then
  v_sql := v_sql || ' and h.prodcode = '||chr(39)||v_productcode||chr(39);
end if;

if v_seller is not null then
  v_sql := v_sql || ' and h.sellername like '||chr(39)||'%'||v_seller||'%'||chr(39);
end if;

if p_provincename is not null then
  v_sql := v_sql || ' and h.buyerprovincename = '||chr(39)||p_provincename||chr(39);
end if;

if v_productspec is not null then
  v_sql := v_sql || ' and h.prodspec like '||chr(39)||'%'||v_productspec||'%'||chr(39);
end if;

3. 利用execute immediate using占位符语法处理
v_sql := ' insert into  BJTONGRENTANGTEMPTB  select distinct h.sellerid,h.sellercode,h.sellername,h.prodcode,h.prodname from historyofsales_day h '
||' where h.sellerid in (select distinct ovalorgid from bjtongrentangpc ) '
||' and h.prodcode in (select prodcode from buproduct where bucode= :1)'
--||' and to_char(h.salesdate,''yyyyMM'') =:v2:v3';
||' and to_char(h.salesdate,''yyyy'') =:v2';

--execute immediate v_sql using v_bucode,v_year,v_month; --error  ORA-01006：绑定变量不存在
execute immediate v_sql using v_bucode,v_year;
commit;

4. 其他的
select q'[it's a cat]' from dual;