--create type create or replace type t_ret_table is table of varchar2(20); / --create function create or replace function f_test(var_str in string,delimiter in string) return t_ret_table is var_out t_ret_table; var_tmp varchar2(2000); var_element varchar2(2000); begin var_tmp := var_str; var_out := t_ret_table(); while instr(var_tmp,delimiter)>0 loop var_element := substr(var_tmp,1,instr(var_str,delimiter)-1); var_tmp := substr(var_tmp,instr(var_str,delimiter)+1,length(var_tmp)); var_out.extend(1); var_out(var_out.count) := var_element; end loop; var_out.extend(1); var_out(var_out.count) := var_tmp; return var_out; end f_test; / --测试 declare aa t_ret_table; begin aa := f_test('a,b,c',','); for i in 1..aa.count loop dbms_output.put_line(aa(i)); end loop; end; --result a b c
我用VB写了一个小程序,使用该函数打开一个纪录集SELECT f_test(TEST,',') FROM TABLE1, 其中TABLE1为表名,TEST为表TABLE1的一个字段, Dim tmpRst As New ADODB.Recordset tmpRst.Open "SELECT f_test(TEST,',') f FROM TABLE1", g_ADOConn, adOpenKeyset, adLockReadOnly 执行到这里就出现异常,VB关闭。
create or replace type t_ret_table is table of varchar2(20);
/
--create function
create or replace function f_test(var_str in string,delimiter in string) return t_ret_table is
var_out t_ret_table;
var_tmp varchar2(2000);
var_element varchar2(2000);
begin
var_tmp := var_str;
var_out := t_ret_table();
while instr(var_tmp,delimiter)>0 loop
var_element := substr(var_tmp,1,instr(var_str,delimiter)-1);
var_tmp := substr(var_tmp,instr(var_str,delimiter)+1,length(var_tmp));
var_out.extend(1);
var_out(var_out.count) := var_element;
end loop;
var_out.extend(1);
var_out(var_out.count) := var_tmp;
return var_out;
end f_test;
/
--测试
declare
aa t_ret_table;
begin
aa := f_test('a,b,c',',');
for i in 1..aa.count loop
dbms_output.put_line(aa(i));
end loop;
end;
--result
a
b
c
其中TABLE1为表名,TEST为表TABLE1的一个字段,
Dim tmpRst As New ADODB.Recordset
tmpRst.Open "SELECT f_test(TEST,',') f FROM TABLE1", g_ADOConn, adOpenKeyset, adLockReadOnly
执行到这里就出现异常,VB关闭。