17,140
社区成员




CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
--使用方法--
select * from table(splitstr('16021-16022','-'));
--执行出的结果
COLUMN_VALUE
16021
16022
CREATE OR REPLACE FUNCTION fn_GetDead (v_deadId varchar2)
return varchar2
is
v_deadString varchar2(100);
cursor c_deadIdSpilt --设置一个分割字符串游标--
is
select * from table(splitstr(''||v_deadId||'','-'));
c_row c_deadIdSpilt%rowtype;
begin
if instr(v_deadId,'-') > 0 then ---判读变量中是否含有"-"字符,如果有,则循环遍历这个字符,取出亡人姓名
open c_deadIdSpilt; --打开游标--
loop
fetch c_deadIdSpilt into c_row;
SELECT wrxm into v_deadString FROM wrxxb WHERE id=C_ROW.COLUMN_VALUE;
v_deadString:=v_deadString||'-';
return v_deadString;
end loop;
v_deadString := substr(v_deadString, 1, LENGTH(v_deadString) - 1);
close c_deadIdSpilt;
else
SELECT wrxm INTO v_deadString FROM wrxxb WHERE id=''||v_deadId||'';
return v_deadString;
end if;
--异常处理--
exception
when others then dbms_output.put_line('error');
return -1;
end fn_GetDead;
/
--调用--
SELECT fn_GetDead('16021-16022') FROM dual;
我执行出的结果
FN_GETDEAD('16021-16022')
齐同元-
--对多个“-”可以用 regexp_count() 函数
with table1 as
(
select 123 col1, '张三' col2 from dual union all
select 456 col1, '李四' col2 from dual union all
select 789 col1, '王五' col2 from dual
)
select replace(WMSYS.WM_CONCAT(col2), ',', '-') from (
SELECT REGEXP_SUBSTR ('123-456-789', '[^-]+', 1,rownum) col3
FROM DUAL
CONNECT BY ROWNUM <=regexp_count(('123-456-789', '[^-]+')
) aa left join table1 bb on aa.col3=bb.col1
CREATE OR REPLACE FUNCTION fn_GetDead (v_deadId varchar2)
return varchar2
is
v_deadString varchar2(100);
v_deadString_all VARCHAR2(4000);
cursor c_deadIdSpilt --设置一个分割字符串游标--
is
select * from table(splitstr(''||v_deadId||'','-'));
c_row c_deadIdSpilt%rowtype;
begin
if instr(v_deadId,'-') > 0 then ---判读变量中是否含有"-"字符,如果有,则循环遍历这个字符,取出亡人姓名
open c_deadIdSpilt; --打开游标--
loop
fetch c_deadIdSpilt into c_row;
EXIT WHEN c_deadIdSpilt%NOTFOUND;
SELECT wrxm into v_deadString FROM wrxxb WHERE id=C_ROW.COLUMN_VALUE;
v_deadString_all:=v_deadString_all||'-'||v_deadString;
end loop;
close c_deadIdSpilt;
return ltrim(v_deadString_all,'-');
else
SELECT wrxm INTO v_deadString FROM wrxxb WHERE id=''||v_deadId||'';
return v_deadString;
end if;
--异常处理--
exception
when others then dbms_output.put_line('error');
return -1;
end fn_GetDead;
/
with table1 as
(
select 123 col1, '张三' col2 from dual union all
select 456 col1, '李四' col2 from dual union all
select 789 col1, '王五' col2 from dual
)
select replace(WMSYS.WM_CONCAT(col2), ',', '-') from (
SELECT REGEXP_SUBSTR ('123-456', '[^-]+', 1,rownum) col3
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH ('123-456') - LENGTH (REPLACE ('123-456', '-', ''))+1
) aa left join table1 bb on aa.col3=bb.col1
v_deadId := v_deadId || '-';
-- 16021-16022-