# oracle位与或等比特运算

from:http://lzfhope.blog.163.com/blog/static/63639922007526327200/

1) 与and

自有函数bitand ,这个好理解.

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)

Create or replace function bitor(a in int,b in int) return int
is
begin
return a+b-bitand(a,b);
end;

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 bitxor(a in int,b in int) return int
is
begin
return a+b-2*bitand(a,b);
end;

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

BITXOR(2,10)
------------
8

4)整体左移n位

a*power(2,n);

create or replace function moveleft(a in int,n in int) return int
is

begin
return a*power(2,n);
end;

SQL> select moveleft(3,1) from dual;

MOVELEFT(3,1)
-------------
6

5)整体右移n位

floor(a/power(2,n))

create or replace function moveright(a in int,n in int) return int
is

begin
return floor(a/power(2,n));
end;

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

MOVERIGHT(7,1)
--------------
3

6) 任意设置某位(n)为1,仅限制于整数

bitor(a,power(2,n-1))

7) 任意设置某位(第n)位为0,仅仅限制于整数

a-power(2,n-1)

create or replace function setbit(a in int,pos in int,value in int) return int
is
begin
if value=1 then
return bitor(a,power(2,pos-1));
else
return a-power(2,pos-1);
end if;
end;
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

end;

7)10进制转二进制

CREATE OR REPLACE FUNCTION DectoBin(N IN Integer) RETURN VARCHAR2
IS

vQuotient INTEGER;  --商
vRemainder INTEGER; --余数
vRESULT   VARCHAR2(100):='';
BEGIN

IF N IS NULL THEN RETURN NULL;  END IF;

vQuotient:=floor(N/2);
vRemainder:=MOD(N,2);
IF vQuotient=1 THEN
vRESULT:='1'||to_char(vremainder)||vRESULT;
ELSE
vRESULT:=to_char(Dectobin(vQuotient))||to_char(vremainder);
END IF;
RETURN vRESULT;
END;

12-01

03-29 2万+
03-15 454
04-08 445
02-22 9676
05-30 1933
08-10 4626
06-02 341
07-05 1681
09-25 177
07-03 25
10-08 1590
02-10 1349
05-30 5117
10-09 2484
06-12 192