DECLARE
strstartdate varchar2(50);
strenddate varchar2(50);
slbcode varchar2(50);
sql1 varchar2(1000);
year1 varchar2(4);
month1 varchar2(2);
day1 varchar2(2);
areacode varchar2(30);
begin
year1:='2016';
month1:='4';
areacode:='632700';
if month1='2' then day1:='28';
elsif month1='4' then day1:='30';
elsif month1='6' then day1:='30';
elsif month1='9' then day1:='30';
elsif month1='11' then day1:='30';
else day1:=31;
end if;
slbcode:='0203';
strstartdate:=year1||'-'||month1||'-'||'1 00:00:00';
strenddate:=year1||'-'||month1||'-'||day1||' 23:59:59';
dbms_output.put_line(strstartdate);
dbms_output.put_line(strenddate);
sql1:='select
chhis.h_in_people XZRS,chhis.h_de_people TCRS from
db_changehistory chhis where chhis.dcreatedate>=to_date('''||strstartdate||''','||'''yyyy-MM-dd hh24:mi:ss'') and
chhis.dcreatedate<=to_date('''||strenddate||''','||'''yyyy-MM-dd hh24:mi:ss'') and
chhis.slbcode='''||slbcode||''' and chhis.ideletemark=0 and instr(chhis.sareacode,'||areacode||')>0';
dbms_output.put_line(sql1);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('[PL/SQL 基本写法]中出现异常,错误代码:ORA'||slbcode);
END ;
编译后:
select
chhis.h_in_people XZRS,chhis.h_de_people TCRS from
db_changehistory chhis where chhis.dcreatedate>=to_date('2016-4-1 00:00:00','yyyy-MM-dd hh24:mi:ss') and
chhis.dcreatedate<=to_date('2016-4-30 23:59:59','yyyy-MM-dd hh24:mi:ss') and
chhis.slbcode='0203' and chhis.ideletemark=0 and instr(chhis.sareacode,632700)>0
如果有连接字符串,要以连接字符串来作为分割标记,连接字符串左边的为一个字符串引用(‘’),右边为另外一个字符串引用,
然后逻辑首先是:找到连接字符串符 进行切割字符串,再从外面找到字符串引用的 字符,去掉字符串引用字符对应的一对'',再把字符串引用符里面''解析成'。