create or replace function GETDIAOXIANXINXItest(xdate in NVARCHAR2,tabledate in NVARCHAR2,linename in NVARCHAR2,busname in NVARCHAR2,TIMELABEL in number) return varchar2 is
re nvarchar2(70);
xsql varchar2(800);
type my_curtype is ref cursor;
cur my_curtype;
type rtype is record (O_GPSDATETIME date,
JW varchar2(30));
r rtype;
begin
xsql := xsql|| ' SELECT O_GPSDATETIME,O_LONGITUDE||'',''||O_LATITUDE AS JW';
xsql := xsql|| ' FROM T_JK_FULLGPSDATA'||tabledate;
xsql := xsql|| ' WHERE O_LINENAME='''||linename||''' AND O_DATE='''||xdate||''' AND O_BUSNAME='''||busname||'''';
xsql := xsql|| ' order by O_GPSDATETIME';
--dbms_output.put_line(xsql);
open cur for xsql;
loop
fetch cur into r;
exit when cur%notfound;
--if r.SJC>TIMELABEL-40 then
if getTimeStamp(r.O_GPSDATETIME)<TIMELABEL then
re := r.JW||'';
end if;
-- end if;
end loop;
close cur;
return re;
end GETDIAOXIANXINXItest;
我创建了一个function 用sql调用.应该相当于子查询
" SELECT O_TIMELABEL_CLOSE," +
" GETDIAOXIANXINXItest('2012-02-01','20120201','850','5951',O_TIMELABEL_CLOSE) AS CLOSEJW " +
" FROM T_BUSLOGIN201202 A" +
" WHERE O_CLOSEREASON='网络连接被断开' AND O_LINENAME='123'AND O_BUSNAME='23456'" +
" AND O_DATE='2012-02-01'" +
" ORDER BY O_CLOSETIME" +可是在function 中如果用getTimeStamp(r.O_GPSDATETIME)去和传进来的参数比较时 java jdbc查询结果就返回空.但是再数据库中查询却有结果
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货