create or replace type mytabletype as table of varchar2;
/
create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/
SQL> select * from table(cast(strtab('11,12,13') as mytabletype));
COLUMN_VALUE
------------
11
12
13
create function get_times(p_name in varchar2)
return number
as
type t_rc is ref cursor;
v_rc t_rc;
str varchar2(50);
v_name varchar2(50);
v_num number:=0;
cursor t_sor is
select name from work;
begin
for v_sor in t_sor loop
str:='select * from table(cast(strtab('||v_sor.name||') as mytabletype))';
open v_rc for str;
loop
fetch v_rc into v_name;
exit when v_rc%notfound;
if v_name=p_name then
v_num:=v_num+1;
end if;
end loop;
colse v_rc;
end loop;
return v_num;
end;
/
select a.name,get_times(a.name) from (select * from table(cast(strtab(select name from (select * from work order by length(name) desc) where rownum<2) as mytabletype))) a;
select a,count(1) times from(
select substr(name,instr(name,',',1,1)+1,instr(name,',',1,2)-instr(name,',',1,1)-1) a from work
union all
select substr(name,instr(name,',',1,2)+1,instr(name,',',1,3)-instr(name,',',1,2)-1) a from work
union all
select substr(name,instr(name,',',1,3)+1,instr(name,',',1,4)-instr(name,',',1,3)-1) a from work)
group by a;
select a,count(1) times from(
select substr(name,1,2) a from work
union all
select substr(name,4,2) a from work
union all
select substr(name,7,2) a from work)
group by a;
create table dali."work" ("id" int,"name" varchar2(4000));
insert into dali."work" values(1,'张三,李四,王五');
insert into dali."work" values(2,'张三,王五');
insert into dali."work" values(3,'王五');
insert into dali."work" values(4,'王五,李四');
go
select "name",sum(1) 合计 from (select substr(','||aa."name",num+1,instr(','||aa."name"||',',',',num+1)-num-1) "name" from dali."work" aa,(select rownum num from all_objects where rownum<=4000) bb where substr(','||aa."name",num,8000) like ',_%') group by "name";