函数如下:比较简单create or replace function get_trkname_from_serviceid(pni_service_id number,pni_office_id number)
return VARCHAR is
RESULT VARCHAR2(200);
begin
for cur1 in (select v.trk1_name from vsp_ci_trk_src v
where v.service_id = pni_service_id
and v.office1_id = pni_office_id)
LOOP
RESULT := result||chr(39)||TRIM(cur1.trk1_name)||chr(39)||',';
END LOOP;
result := '('||substr(result,1,length(result)-1)||')';
return(RESULT);
end get_trkname_from_serviceid;
test之后如下:result String ('4','42','2')
pni_service_id Float 2002
pni_office_id Float 1001在sql里调用这个函数怎么就是不行呢?
select sum(t.seiz)
from tsppi_trfs_tkgp_i t,tsp_ci_trk k
where t.office_id = 1001
and t.mstr_day_id = 20060711
and t.s_hour = 19
and t.trk_id = k.trk_id
and k.trk_name in get_trkname_from_serviceid(2002,1001)
但是~~~,把sql最后的那个函数直接改为test之后的结果却有数据,正常的,如下:select sum(t.seiz)
from tsppi_trfs_tkgp_i t,tsp_ci_trk k
where t.office_id = 1001
and t.mstr_day_id = 20060711
and t.s_hour = 19
and t.trk_id = k.trk_id
and k.trk_name in ('4','42','2')哪位帮忙看看,谢了,问题解决就给分~~~~~~~~~~~~~~~~~
return VARCHAR is
RESULT VARCHAR2(200);
begin
for cur1 in (select v.trk1_name from vsp_ci_trk_src v
where v.service_id = pni_service_id
and v.office1_id = pni_office_id)
LOOP
RESULT := result||chr(39)||TRIM(cur1.trk1_name)||chr(39)||',';
END LOOP;
result := '('||substr(result,1,length(result)-1)||')';
return(RESULT);
end get_trkname_from_serviceid;
test之后如下:result String ('4','42','2')
pni_service_id Float 2002
pni_office_id Float 1001在sql里调用这个函数怎么就是不行呢?
select sum(t.seiz)
from tsppi_trfs_tkgp_i t,tsp_ci_trk k
where t.office_id = 1001
and t.mstr_day_id = 20060711
and t.s_hour = 19
and t.trk_id = k.trk_id
and k.trk_name in get_trkname_from_serviceid(2002,1001)
但是~~~,把sql最后的那个函数直接改为test之后的结果却有数据,正常的,如下:select sum(t.seiz)
from tsppi_trfs_tkgp_i t,tsp_ci_trk k
where t.office_id = 1001
and t.mstr_day_id = 20060711
and t.s_hour = 19
and t.trk_id = k.trk_id
and k.trk_name in ('4','42','2')哪位帮忙看看,谢了,问题解决就给分~~~~~~~~~~~~~~~~~
不知道你有什么目的?
如果在jsp里调用
可以这样调:
CallableStatement cstmt = conn.prepareCall("{ call get_trkname_from_serviceid(?,?)}"); //‘?’是什么意思,问号代表参数,你有几个参数就有几个问号。
cstmt.setFloat(1,param);// 第一个是设置输入参数
cstmt.setFloat(2,param);// 第2个是设置输入参数
cstmt.execute();怎样获得它的结果集,用ResultSet?然后,把结果赋给变量,
变量再用到sql语句
select t.mstr_day_id,
sum(decode(t.trk_id,get_trk_id2(1006,0),t.seiz,0))
from tsppi_trfs_tkgp_o t
where t.s_hour = 19
and t.mstr_day_id = 20060710
group by t.mstr_day_id这个就可以在PLSQL里显示结果,最后被我的程序调用
select 1 from dual where ('4','42','2') = get_trkname_from_serviceid(2002,1001)
select 1 from dual where '(''4'',''42'',''2'')' = get_trkname_from_serviceid(2002,1001)
应该就知道为什么了
1) get_trkname_from_serviceid(2002,1001)返回的结果并不是楼主认为的 ('4','42','2')
而是'(''4'',''42'',''2'')'
2) trk_name in ('4','42','2') 是指trk_name 是 '4','42','4'中的一个,并不是 trk_name 和返回的 '(''4'',''42'',''2'')' 字符串进行比较
from tsppi_trfs_tkgp_i t,tsp_ci_trk k
where t.office_id = 1001
and t.mstr_day_id = 20060711
and t.s_hour = 19
and t.trk_id = k.trk_id
and instr(get_trkname_from_serviceid(2002,1001),''''||k.trk_name'''')>0;
可是,为什么会出现这样的情况呢??是函数写的有问题??