存储过程拼接sql

CREATE OR REPLACE PROCEDURE test1(v_istotalquery IN VARCHAR2, v_areastr IN VARCHAR2, v_areastrs IN VARCHAR2, v_landlevelno IN VARCHAR2, v_fenddate IN VARCHAR2, v_zonecodes IN VARCHAR2, v_fstartdate IN VARCHAR2, v_usedtype IN VARCHAR2, v_fprjname IN VARCHAR2)
AS
str_sql varchar2(4000);
str_sql1 varchar2(4000);
str_sql11 varchar2(4000);
ttsl number(1); 
state VARCHAR2(2) := '1';
state00 VARCHAR2(111) :='00';
state02 VARCHAR2(111) :='02';
state03 VARCHAR2(111) :='03';
state04 VARCHAR2(111) :='04';
state06 VARCHAR2(111) :='06'; 
state15703 VARCHAR2(5) :='15100'; 
state15704 VARCHAR2(5) :='15101'; 
begin 
 select count(*) into ttsl from user_tables where table_name = 'LSXXBXS';
if TTSL != 0 THEN
      str_sql := 'DROP TABLE LSXXBXS';
      execute immediate str_sql;
      END if;
  str_sql := 'create table lsxxbxs as SELECT
project.FPRJNAME, 
td.usedtype,
DZ.ZONENAME,
dz.zonecode,
project.FID,
nvl( sum(HouseArea), 0) as FAREA,
round(nvl( sum(RealPrice) / sum(HouseArea), 0),2) as avgnumber,
COUNT(1) as FCOUNT
FROM test1 saleCont
LEFT JOIN test2 houseSale
ON saleCont.SaleID = houseSale.Fid
LEFT JOIN test3 houseInfo
ON houseSale.Fhid = houseInfo.Fid
INNER JOIN  test4 td on  td.usedtypeno = houseInfo.Usedtypeno
LEFT JOIN test5 salecert
ON houseInfo.Fmid = salecert.fbid
LEFT JOIN test6 detail
ON salecert.fbid = detail.fbid
LEFT JOIN test7 buildinfo
ON buildinfo.hfmpguid = houseInfo.Fmid
LEFT JOIN test8 project
ON buildinfo.fpid = project.fid
LEFT JOIN test9 DZ ON DZ.ZONECODE = project.FREGIONID
WHERE houseSale.State = '''||state||'''
AND saleCont.Status in ('''||state00||''', '''||state02||''', '''||state03||''', '''||state04||''', '''||state06||''')
AND houseInfo.frentstateno in ('''||state15703||''', '''||state15704||''') ';
BEGIN
IF v_istotalquery is not NULL then
 str_sql:= str_sql ||' AND project.istotalquery = '''||state||''' ';
END IF; 
IF v_areastr is not NULL then
 str_sql:= str_sql ||' AND detail.status ='''||v_areastr||''' ';
END IF;
IF v_landlevelno is not NULL then 
 str_sql:= str_sql||' AND buildinfo.landlevelno='''||v_landlevelno||''' ';
END IF;
IF v_fenddate is not NULL then
 str_sql:= str_sql||' AND substr(saleCont.contracttime, 0, 10) <= '''||v_fenddate||''' ';
END IF;
IF v_fstartdate is not NULL then
str_sql:= str_sql||' AND substr(saleCont.contracttime, 0, 10) >= '''||v_fstartdate||''' ';
END IF;
IF v_usedtype is not NULL then
str_sql:= str_sql||' AND  td.usedtype='''||v_usedtype||''' '; 
END IF;
IF v_fprjname is not NULL then
str_sql:= str_sql||' AND  project.FPRJNAME like '''||v_fprjname||''' '; 
END IF;
IF v_areastrs is not NULL then
str_sql:= str_sql||' AND project.fregionid in ( select t.zonecode from sys_area t where  '''||v_areastrs|| ''' like ''%''||t.zonecode||''%'') '; 
END IF;
IF v_zonecodes is not NULL then
str_sql:= str_sql||' AND project.fregionid in ( '''||v_zonecodes||''') ';  
END IF;
str_sql:= str_sql||'GROUP BY  project.FPRJNAME, td.usedtype, DZ.ZONENAME, dz.zonecode, project.FID  ORDER BY project.FPRJNAME,DZ.ZONECODE,TD.USEDTYPE';
dbms_output.put_line(str_sql);
execute immediate str_sql;
end;
END;

主要分享给大家的是：

select t.zonecode from sys_area t where  '''||v_areastrs|| ''' like ''%''||t.zonecode||''%''

转义

select t.zonecode from sys_area t where '370100，370101' like '%'||t.zonecode||'%'

根据字符串参数反查表中是否有数据。