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'') =:2:3';
||' and to_char(h.salesdate,''yyyy'') =:2';
--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;

如果大家想了解更多相关知识，可以来关注一下星辉的Oracle教程，里面的内容详细，通俗易懂，适合小白学习。