此功能可以用Oracle管道函数解决,代码及思路如下1、创建type用于存储函数返回值
create or replace type type_split as table of varchar2(4000);
2、Function代码(管道函数)
create or replace function test_split(p_list VARCHAR2, p_sep VARCHAR2) return type_split
PIPELINED IS
l_idx PLS_INTEGER;
v_list VARCHAR2(32767) := p_list;
begin
LOOP
l_idx := instr(v_list, p_sep);
IF l_idx > 0 THEN
PIPE ROW(substr(v_list, 1, l_idx - 1));
v_list := substr(v_list, l_idx + length(p_sep));
ELSE
PIPE ROW(v_list);
EXIT;
END IF;
END LOOP;
RETURN;
end test_split;
3、函数调用及结果查看
SELECT * FROM table(test_split('aaa,bbb,ccc',',')) ;
create or replace type type_split as table of varchar2(4000);
2、Function代码(管道函数)
create or replace function test_split(p_list VARCHAR2, p_sep VARCHAR2) return type_split
PIPELINED IS
l_idx PLS_INTEGER;
v_list VARCHAR2(32767) := p_list;
begin
LOOP
l_idx := instr(v_list, p_sep);
IF l_idx > 0 THEN
PIPE ROW(substr(v_list, 1, l_idx - 1));
v_list := substr(v_list, l_idx + length(p_sep));
ELSE
PIPE ROW(v_list);
EXIT;
END IF;
END LOOP;
RETURN;
end test_split;
3、函数调用及结果查看
SELECT * FROM table(test_split('aaa,bbb,ccc',',')) ;
寫一個函數(用Oracle完成)
傳遞的值是: {name}test{worker_id}S0888{EMAIL}[email protected] 等 以{}+value形式的一串有規則字符
要求根據{}中的內容得到value
如果 輸入{name},則得到tes
輸入{worker_id},則得到S0888