SQL判断逗号分隔的字符串是否都包含相同的逗号分隔的字符串-优化后速度加快了

1、创建分隔字符串函数（oracle、达梦数据库类似）

CREATE OR REPLACE TYPE strsplit_type as table of VARCHAR2(4000);
create or replace function strsplit(p_value varchar2,
                                    p_split varchar2 := ',')
 return strsplit_type
  pipelined is
  v_idx       integer;
  v_str       varchar2(500);
  v_strs_last varchar2(4000) := p_value;

begin
  loop
    v_idx := instr(v_strs_last, p_split);
    exit when v_idx = 0;
    v_str       := substr(v_strs_last, 1, v_idx - 1);
    v_strs_last := substr(v_strs_last, v_idx + 1);
    pipe row(v_str);
  end loop;
  pipe row(v_strs_last);
  return;

end strsplit;

2、针对区划类型的,使用

select * form a_fator_plan where t1.scope in ('市','县')
AND test_area_str(t1.zonal_code,#{userAreaCode}) = 1

函数SQL:

create or replace function test_area_str(zonal_code varchar2, user_area_code varchar2)
  return number
  as
  counta number;
  countb number;
  countc number;
Begin
  counta := 0;
  countb := 0;
  countc := 0;

 select count(1)
  into counta
   from (
         select  column_value from table(strsplit(zonal_code))
           union 
         select distinct code column_value from sys_area where 
         parent_ids like ('%' || (select id from sys_area where code = user_area_code) || '%')
         or code = user_area_code
   );

  select count(1)
  into countb
  from (
         select distinct code column_value from sys_area where 
         parent_ids like ('%' || (select id from sys_area where code = user_area_code) || '%')
         or code = user_area_code
  );

  if counta = countb then
  countc := 1;
  return countc;
  end if;
  return countc;
end test_area_str;

 

2、针对小流域类型的,使用

select * form a_fator_plan where t1.scope= '小流域'
AND test_basininfo_str(t1.zonal_code,#{userAreaCode}) = 1

函数SQL:

create or replace function test_basininfo_str(zonal_code varchar2, user_area_code varchar2)
  return number
  as
  counta number;
  countb number;
  countc number;
Begin
  counta := 0;
  countb := 0;
  countc := 0;

 select count(1)
  into counta
   from (
         select  column_value from table(strsplit(zonal_code))
           union 
        select 
          distinct t1.basincode column_value
        from basininfo t1
        where  SUBSTR(t1.basincode,LENGTH(t1.basincode) - 5,6) in (
             select distinct code column_value from sys_area where 
             parent_ids like ('%' || (select id from sys_area where code = user_area_code) || '%')
             or code = user_area_code
        )
   );

  select count(1)
  into countb
  from (
        select 
          distinct t1.basincode column_value
        from basininfo t1
        where  SUBSTR(t1.basincode,LENGTH(t1.basincode) - 5,6) in (
             select distinct code column_value from sys_area where 
             parent_ids like ('%' || (select id from sys_area where code = user_area_code) || '%')
             or code = user_area_code
        )
  );

  if counta = countb then
  countc := 1;
  return countc;
  end if;
  return countc;
end test_basininfo_str;