create or replace function fun_getadded(buildId nvarchar2,
gatewayId nvarchar2,
hour_start date,
hour_end date,
itemId nvarchar2)
return nvarchar2 is
maxtime date;
currentvalue nvarchar2(50);
current_real_value number;
current_virtual_value number;
lastvalue nvarchar2(50);
rslt number;
begin
lastvalue := '0.00';
currentvalue := '0.00'; begin
execute immediate
'select max(fv.gettime)
from t_d_functionvalue fv
join t_d_function f on f.buildid || ''-'' || f.gatewayid || ''-'' ||
f.meterid || ''-'' || f.functionId = fv.functionid
left join t_d_functionsplit fs on fs.functionid = f.id
where fv.gettime >=' || hour_start ||
' and fv.gettime < ' || hour_end ||
' and fv.iserror = ''0''
and (fv.functiontype =''' || itemId || ''' or fs.type =''' || itemId || ''')
and fv.functionId like ''' || buildId || '-' || gatewayId || '%''' into maxtime;
end;
end fun_getadded;
请问execute immediate使用上有什么错误吗 执行的时候总是报:"无效的SQL语句"
'select max(fv.gettime)
from t_d_functionvalue fv
join t_d_function f on f.buildid || ''-'' || f.gatewayid || ''-'' ||
f.meterid || ''-'' || f.functionId = fv.functionid
left join t_d_functionsplit fs on fs.functionid = f.id
where fv.gettime >=' || hour_start ||
' and fv.gettime < ' || hour_end ||
' and fv.iserror = ''0''
and (fv.functiontype =''' || itemId || ''' or fs.type =''' || itemId || ''')
and fv.functionId like ''' || buildId || '-' || gatewayId || '%''' into maxtime'--这里少了个单引号;
on f.buildid || '||'-'||' || f.gatewayid || '||'-'||' ||
f.meterid || '||'-'||' || f.functionId
拼接的 以及没有return 的错误最好是把你的三个表的结构以及代表性的数据弄出来 别人好帮个你解决
and fv.gettime < ' || hour_end ||' 改成 where fv.gettime >=''' || hour_start ||'''
and fv.gettime < ''' || hour_end ||''' 最好to_char成指定格式
EXECUTE immediate v_sql;然后在看打印出来的sql有啥问题,就知道了也可以把sql 写入到一个日志表调试一下的嘛
1、拼接的时候需要将DATE型转为VARCHAR2(用TO_CHAR)
2、执行的时候需要将VARCHAR2转回DATE(用TO_DATE)
这句:
fv.gettime >=' || hour_start || ' and fv.gettime < ' || hour_end
改为:
fv.gettime >=TO_DATE(''' || TO_CHAR(hour_start,'YYYYMMDD')
|| ''',''YYYYMMDD'') and fv.gettime < TO_DATE(''' || TO_CHAR(hour_end,'YYYYMMDD')||''')'
v_SQL varchar2(2000);
v_SQL :='select max(fv.gettime)
from t_d_functionvalue fv
join t_d_function f on f.buildid || ''-'' || f.gatewayid || ''-'' ||
f.meterid || ''-'' || f.functionId = fv.functionid
left join t_d_functionsplit fs on fs.functionid = f.id
where fv.gettime >=' || hour_start ||
' and fv.gettime < ' || hour_end ||
' and fv.iserror = ''0''
and (fv.functiontype =''' || itemId || ''' or fs.type =''' || itemId || ''')
and fv.functionId like ''' || buildId || '-' || gatewayId || '%'''
execute immediate v_SQL into maxtime;
这样发现错误方便DEbug
sqlStr := 'select max(fv.gettime)
from t_d_functionvalue fv
join t_d_function f on f.buildid || ''-'' || f.gatewayid || ''-'' ||
f.meterid || ''-'' || f.functionId =
fv.functionid
left join t_d_functionsplit fs on fs.functionid = f.id
where fv.gettime >=to_date(''' ||
to_char(hour_start, 'yyyy-mm-dd hh24:mi:ss') || ''',''yyyy-mm-dd hh24:mi:ss'')
and fv.gettime <to_date(''' ||
to_char(hour_end, 'yyyy-mm-dd hh24:mi:ss') ||''',''yyyy-mm-dd hh24:mi:ss'')
and fv.iserror = ''0''
and (fv.functiontype =''' || itemId ||
''' or fs.type =''' || itemId || ''')
and fun_GetBuildIdByUniqueId(fv.functionId) = ''' ||
buildId || '''
and fun_GetGateWayIdByUniqueId(fv.functionId)=''' ||
gatewayId || '''';
execute immediate sqlStr into maxtimeOK 好久没上CSDN了 没想到这么多人回答