17,078
社区成员
发帖
与我相关
我的任务
分享
SQL>
SQL> create table test(id, type)
2 as
3 select 1, 2 from dual union all
4 select 2, 8 from dual union all
5 select 3, 16 from dual union all
6 select 4, 32 from dual;
Table created
SQL> create or replace function bitor(p varchar2)
2 return number is
3 prev number := 0 ;
4 result number;
5 begin
6 for x in (select regexp_substr(p, '[^,]+', 1,level) + 0 value
7 from dual
8 connect by rownum <= regexp_count(p,',') + 1
9 )
10 loop
11 result := (prev + x.value) - bitand(prev, x.value);
12 prev := result;
13 end loop;
14 return result;
15 end;
16 /
Function created
SQL> select bitor(listagg(type,',') within group (order by id)) from test;
BITOR(LISTAGG(TYPE,',')WITHING
------------------------------
58
SQL> drop function bitor;
Function dropped
SQL> drop table test purge;
Table dropped
SQL>