17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE FUNCTION SPLIT (p_list VARCHAR2, p_sep VARCHAR2 := ',')
RETURN type_split PIPELINED
IS
l_idx PLS_INTEGER;
v_list VARCHAR2 (50) := 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 SPLIT;
select * from table(split('bb','aa'));
返回的是一个表的2条数据 bb aa
/*
SQL> drop table a;
Table dropped
SQL> create table A (id int,userid varchar2(20), je int);
Table created
SQL> insert into A values(1,'aa,22,bb',99);
1 row inserted
SQL> insert into A values(2,'dd2,3a',2);
1 row inserted
SQL> commit;
Commit complete */
SQL> select * from A;
ID USERID JE
------------ --------------- ----------
1 aa,22,bb 99
2 dd2,3a 2
SQL> select id,userid,je/(count(1) over(partition by id)) as je
2 from (
3 select id,substr(',' || userid || ',',
4 instr(',' || userid || ',', ',', 1, rn) + 1,
5 instr(',' || userid || ',', ',', 1, rn + 1) -
6 instr(',' || userid || ',', ',', 1, rn) - 1) as userid,
7 je
8 from A,
9 (select rownum rn
10 from all_objects
11 where rownum <= (select max(length(userid) -
12 length(replace(userid, ',')) + 1)
13 from A)))
14 where userid is not null
15 /
ID USERID JE
-------------- ------------ ----------
1 aa 33
1 22 33
1 bb 33
2 dd2 1
2 3a 1
SQL>
create or replace procedure pro_split as
cu sys_refcursor;
arow a%rowtype;
v_userid varchar2(20);
n number;
v_user_idout varchar2(20);
begin
open cu for
select * from a;
loop
fetch cu
into arow;
exit when cu%notfound;
n := 1;
v_userid := arow.userid;
while instr(v_userid, ',') > 0 loop
v_userid := substr(v_userid, instr(v_userid, ',') + 1);
n := n + 1;
end loop;
v_userid := arow.userid;
while instr(v_userid, ',') > 0 loop
v_user_idout := substr(v_userid, 1, instr(v_userid, ',') - 1);
v_userid := substr(v_userid, instr(v_userid, ',') + 1);
dbms_output.put_line(arow.id || ' ' || v_user_idout || ' ' ||
(arow.je / n));
end loop;
dbms_output.put_line(arow.id || ' ' || v_userid || ' ' ||
(arow.je / n));
end loop;
end;
SQL> exec pro_split;
1 aa 33
1 22 33
1 bb 33
2 dd2 1
2 3a 1
PL/SQL procedure successfully completed