在oracle 10g中 我写了一个function函数.以后是其中的一段SQLselect max(fv.gettime) into thistime 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 fun_getbuildidbyuniqueid(fv.functionId)=buildId
and fun_getgatewayidbyuniqueid(fv.functionId)=gatewayId;
return to_char(thistime,'yyyy-mm-dd hh24:mi:ss');我把上面这段SQL复制出来 把对应的参数带入到SQL中 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>=to_date('2010-08-01 07:00:00','yyyy-mm-dd hh24:mi:ss') and fv.gettime<to_date('2010-08-01 08:00:00','yyyy-mm-dd hh24:mi:ss')
and fv.iserror = '0'
and (fv.functiontype='01A30' or fs.type='01A30')
and fun_getbuildidbyuniqueid(fv.functionId)='320106A002'
and fun_getgatewayidbyuniqueid(fv.functionId)='01';
按理来说输出来的结果应该是一样的 但在function中返回的时间经常出现错误 和实际时间不同 请问有高手知道里面的原因吗
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 fun_getbuildidbyuniqueid(fv.functionId)=buildId
and fun_getgatewayidbyuniqueid(fv.functionId)=gatewayId;
return to_char(thistime,'yyyy-mm-dd hh24:mi:ss');我把上面这段SQL复制出来 把对应的参数带入到SQL中 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>=to_date('2010-08-01 07:00:00','yyyy-mm-dd hh24:mi:ss') and fv.gettime<to_date('2010-08-01 08:00:00','yyyy-mm-dd hh24:mi:ss')
and fv.iserror = '0'
and (fv.functiontype='01A30' or fs.type='01A30')
and fun_getbuildidbyuniqueid(fv.functionId)='320106A002'
and fun_getgatewayidbyuniqueid(fv.functionId)='01';
按理来说输出来的结果应该是一样的 但在function中返回的时间经常出现错误 和实际时间不同 请问有高手知道里面的原因吗
在function中加入一个脚本,将你传入的参数写入一个临时表,返回的结果也写进去。
当你出现所谓的错误的时候,可以检查出来当时你实际传入的参数是什么。
and fun_getgatewayidbyuniqueid(fv.functionId)=gatewayId;不管传入参数据为任何值都会转化成: and fun_getbuildidbyuniqueid(fv.functionId)=f.buildId
and fun_getgatewayidbyuniqueid(fv.functionId)=f.gatewayId;
只要把函数参数据调整一下就没问题了!