1. 直接用單引號，單引號的使用是就近配對，即就近原則。從第二個單引號開始被視為轉(zhuǎn)義符

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)轉(zhuǎn)義單引號

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;

如果大家想了解更多相關(guān)知識，可以來關(guān)注一下動力節(jié)點的Oracle教程，里面的內(nèi)容詳細，通俗易懂，適合小白學習。