-- 1.提取此类数据中的1的索引位置,从1开始 例: 0001100001100 --> 4,5,10,11
create or replace function hazq_instr_contains
(
v_str in varchar2
)
return varchar2
is
v_count number(10):=0;
v_char varchar2(50):=null;
strTmp varchar2(4000):=null;
v_tm varchar2(50):=null;
begin
v_count:=length(v_str);
for i in 1..v_count loop
v_char:=substr(v_str,i,1);
if (v_char = "1") then
strTmp:=strTmp || to_char(i) || ",";
end if;
if (i = v_count) then
v_tm:= length(strTmp);
strTmp:=substr(strTmp,1,v_tm-1);
end if;
end loop;
return (strTmp);
end hazq_instr_contains;
-- 2.获取指定分隔符位置的字符串
-- 将 3,5,11,88数据转换成行数据 字符串分割函数,oracle拆分字符串
create or replace function hazq_rjy_StrArrayOfIndex
(
av_str varchar2, --要分割的字符串
av_split varchar2, --分隔符号
av_index number --取第几个元素
)
return varchar2
is
lv_str varchar2(1024);
lv_strOfIndex varchar2(1024);
lv_length number;
begin
lv_str:=ltrim(rtrim(av_str));
lv_str:=concat(lv_str,av_split);
lv_length:=av_index;
if lv_length=0 then
lv_strOfIndex:=substr(lv_str,1,instr(lv_str,av_split)-length(av_split));
else
lv_length:=av_index+1;
lv_strOfIndex:=substr(lv_str,instr(lv_str,av_split,1,av_index)+length(av_split),instr(lv_str,av_split,1,lv_length)-instr(lv_str,av_split,1,av_index)-length(av_split));
end if;
return lv_strOfIndex;
end hazq_rjy_StrArrayOfIndex;
-- select hs_user.get_strarraystrofindex("jjfddj,33f,,ff2,i444kd",",",3) from dual; -- 这里3 是获取第4个字符串
-- 如果获取不存在的字符串，如11 返回空
-- 两个分割符连着，也是返回空
-- 3.将处理好的数据插入表中（写个游标循环，将数据插入到表中）
create or replace procedure hazq_get_roles is
v_user_id number(10);
v_branch_no number(10);
v_role_ids varchar2(1024);
v_count number(10) := 0;
cursor role_cur is select t.user_id,t.branch_no,t.role_name from hazq_rjy_tmp t;
begin
open role_cur;
loop
fetch role_cur into v_user_id,v_branch_no,v_role_ids;
if role_cur%NOTFOUND then
EXIT;
else
select length(regexp_replace(replace(v_role_ids,",","|"),"[^|]+","")) into v_count from dual;
-- 这种正则写法是 查询某个字符在字符串中出现的次数，可以使用上面的Get_StrArrayLength函数代替
for i in 0..v_count loop
insert into hazq_rjy_tmp2(user_id,branch_no,role_id) values(v_user_id,v_branch_no,to_number(hazq_rjy_StrArrayOfIndex(v_role_ids,",",i)));
end loop;
commit;
end if;
end loop;
close role_cur;
end hazq_get_roles;
附：（这个ap 可以获取分隔符分隔的字符串的个数）
create or replace function hazq_rjy_StrArrayLength
(
av_str varchar2, --要分割的字符串
av_split varchar2 --分隔符号
)
return number
is
lv_str varchar2(1000);
lv_length number;
begin
lv_str:=ltrim(rtrim(av_str));
lv_length:=0;
while instr(lv_str,av_split)<>0 loop
lv_length:=lv_length+1;
lv_str:=substr(lv_str,instr(lv_str,av_split)+length(av_split),length(lv_str));
end loop;
lv_length:=lv_length+1;
return lv_length;
end hazq_rjy_StrArrayLength;
-- select hs_user.get_strarraylength("jjfddj,33f,,ff2,i444kd",",") from dual; -- 5 获取到,分割的字符串个数。
-- 没有, 则返回1
码字不易，转发请标明出处^_^ https://www.cnblogs.com/junyouren/p/11936517.html
下面是sql学习交流群，欢迎有兴趣的朋友加入交流学习！！
合肥SQL交流群：703978998
