现在做的项目中有一项功能是这么传递数据的:前台传递到后台一个以逗号分隔的字符串,如:1,2,3 ,然后服务器端通过参数进行相应的查询。
查询语句:
select * from tableName where id in (1,2,3)
在jdbc中可以这样调用:
PreparedStatement pstmt =
conn.prepareStatement ("select * from tableName where id in(?,?,?)" );
pstmt.setInt( 1,1 );
pstmt.setInt( 2,2 );
pstmt.setInt( 3,4 );
pstmt.executeQuery();
设想一下,如果参数不是三个呢?传递100个参数岂不要写一百个占位符?setInt100次?
我是这么解决的:
--创建一个可变数组
create type inarray is table of varchar2(20);
--创建pipeline函数
create or replace function inarguments(str varchar2) return inarray pipelined is
endp int:=0;
temp varchar2(20):=str;
begin
loop
endp:=instr(temp,',',1,1);
exit when endp=0;
pipe row(substr(temp,1,endp-1));
temp:=substr(temp,endp+1);
endp:=0;
end loop;
pipe row(temp);
return;
end;
--测试
with temp as (
select 1 id,'a' name from dual
union all
select 2,'b' from dual
union all
select 3,'c' from dual
union all
select 4,'e' from dual
)
select * from temp where id in (select * from table(inarguments('1,3')))
在jdbc中可以这样调用:
PreparedStatement pstmt =
conn.prepareStatement ("select * from tab where id in(select * from table(inarguments(?)))" );
pstmt.setString( 1,参数字符串 );
pstmt.executeQuery();
![](https://img-my.csdn.net/uploads/201208/01/1343794096_9330.png)