当前位置:首页 > Oracle > 正文内容

Oracle 11g 中判断两个字符串集合的包含关系

管理员1年前 (2019-07-20)Oracle588

今天收到以前同事的一个需求,前台应用程序传过来一些权限ID,这些ID拼接成一个大字符串,用逗号分割'105,106,205,98',各个值无序且没有任何规律,在数据库中也有一张权限表,其结构为[用户ID、权限ID的集合],这个权限ID的集合也是一个字符串类型,各个值也是用逗号分割的('userid','100,102,1002,9983,1021,4500'),(显然这一设计并不符合3NF,但是在实际项目中并不少见),现在就要看一下哪个用户的权限完全包含前台传来的这些权限。

实际上,这个就是两个集合的包含关系,即表中的数据大于等于参数数据。
第一反应实际思路应该是两个,一个是 regexp_substr 去拆解参数和表中的值,二是写一个自定义函数,这里只提供第一个思路的最终实现方法。
后来又尝试着使用 TYPE 来处理这些数,但是 TYPE 能处理多个字符串,而参数和数据库中实际存储的是一个大字符,所以就放弃了这个想法。

大概想到这样的一个方法,记录一下,如果考虑效率的话,可以再进行优化工作。

SQL> create table test(userid int, roles varchar(30));

Table created
SQL> begin
  2    insert into test values(1, '1,2,3');
  3    insert into test values(2, '4,2,3');
  4    insert into test values(3, '4,9,6');
  5    insert into test values(4, 'a,9,6');
  6    insert into test values(5, 'a,b,6');
  7    insert into test values(6, 'x,b,a');
  8  end;
  9  /

PL/SQL procedure successfully completed

假定前端的程序传来的参数为 “a,b”,语句可以写成这个样子

SQL> col userid format a5;
SQL> col roles format a30;
SQL> with m as (
  2    select regexp_substr('a,b', '[^,]+', 1, level) item
  3    from dual connect by level <= regexp_count('a,b', ',') + 1
  4  )
  5  select * from test
  6  where not exists(
  7    select * from m
  8    minus
  9    select regexp_substr(roles, '[^,]+', 1, level)
 10    from dual connect by level <= regexp_count(roles, ',') + 1
 11  );

USERI ROLES
----- ------------------------------
    5 a,b,6
    6 x,b,a

SQL>

正好达到预期,因为数据表的设计原本如此,索引肯定是用不到,CPU的成本也没有测试过,在大数据量下可能会存在效率问题。

打赏 支付宝打赏 微信打赏
    扫描二维码至手机访问

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/oracle/oracle_strings_split_contains.html

    分享给朋友:

    评论列表

    访客
    访客
    2周前 (07-27)

    对字符串的长度超过4000的,有简单的方法吗?

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。