oracle位与或等比特运算

在oracle中 位运算的函数只有bitand（与运算），可以通过该函数构造其他的位运算函数。

1) 与and

     自有函数bitand ,这个好理解（bit（位）and（与））.
     SQL> select bitand(2,4) from dual;
     BITAND(2,4)
     -----------
              0
     2=010（补码）,4=100（补码） ,与之后等于0.

2) 或or

bitor(a,b)=a+b-bitand(a,b)

建立自定义函数bitor

create or replace function bitor(a in number,b in number) return number is
  Result number;
begin
  Result:= a+b-bitand(a,b);
  return(Result);
end bitor;

SQL> select bitor(2,4) from dual;
BITOR(2,4)
----------
         6

3)异或xor

bitxor(a,b)=a+b-2*bitand(a,b);

create or replace function bitor(a in number,b in number) return number is
  Result number;
begin
  Result:= a+b-bitand(a,b);
  return(Result);
end bitor;

SQL> select bitxor(2,10) from dual;
BITXOR(2,10)
------------
           8

4)整体左移b位

a*power(2,n);

create or replace function bitmoveleft(a in number, b in number) return number is
  Result number;
begin
   Result := a*power(a,b);
  return(Result);
end bitmoveleft;

SQL> select bitmoveleft(3,1) from dual;
BITMOVELEFT(3,1)
----------------
               6

5)整体右移b位

floor(a/power(2,n))

create or replace function bitmoveright(a in number, b in number) return number is
  Result number;
begin
  Result :=floor(a/power(a,b));
  return(Result);
end bitmoveright;

SQL> select bitmoveright(7,1) from dual;

BITMOVERIGHT(7,1)
-----------------
                3

6) 任意设置某位(position )为1或0

create or replace function setbit(olddata in number, position in number, value in number) return number is
  Result number;
begin
   if value=1 then
       Result:= bitor (olddata,power(2,position-1));
   else
       Result:= bitand(olddata,bitxor(-1,power(2,position-1)));
   end if;
  return(Result);
end setbit;

SQL> select setbit(7,2,0) from dual;

SETBIT(7,2,0)
-------------
            5

SQL> select setbit(8,2,1) from dual;

SETBIT(8,2,1)
-------------
           10