利用bitand實現多種位操作

利用bitand實現多種位操作

按照http://blog.itpub.net/oldwain http://blog.itpub.net/post/6/1609
的提示，
很多人都有一個疑問：ORACLE中為什麼只有BITAND而沒有BITOR, BITXOR,原因是，有了BITAND,
很容易實現BITOR和BITXOR.BITOR(x,y) = (x + y) - BITAND(x, y);
BITXOR(x,y) = BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;
實現了位操作| ,^, &, <<, >> ,取一個整數某位的值,為了簡明起見,大部分沒有判斷參數合法性,效率和系統bitand在一個數量級
請各位檢查是否有其他錯誤

create or replace function bitor0(x IN NUMBER,y IN NUMBER)
return number is
n_result number;
begin
n_result:=(x + y) - BITAND(x, y);
return n_result;
end;
/
create or replace function bitor(x IN NUMBER,y IN NUMBER)
return number is
begin
return (x + y) - BITAND(x, y);
end;
/
create or replace function bitxor0(x IN NUMBER,y IN NUMBER) --faster
return number is
begin
return (x + y) - BITAND(x, y)*2;
end;
/
create or replace function bitxor(x IN NUMBER,y IN NUMBER) --slower
return number is
begin
return (x + y) - BITAND(x, y)- BITAND(x, y);
end;
/
create or replace function bitxor(x IN NUMBER,y IN NUMBER) --fast
return number is
n_result number;
begin
n_result:= BITAND(x, y);
return (x + y) - n_result- n_result;
end;
/
create or replace function bitnot(x IN NUMBER) --~x=x^0xffff
return number is
n_result number;
begin
n_result:= BITAND(x, -1);
return (x -1 ) - n_result- n_result;
end;
/
create or replace function bitlmv(x IN NUMBER,y IN NUMBER)
return number is
begin
return x* power(2,y);
end;
/
create or replace function bitrmv(x IN NUMBER,y IN NUMBER)
return number is
begin
return trunc(x/ power(2,y));
end;
/
create or replace function bitget(x IN NUMBER,y IN NUMBER)
return number is
n_result number;
begin
n_result :=power(2,y-1);
return bitand(x,n_result)/n_result;
end;
/
create or replace function bitgeta(x IN NUMBER,y IN NUMBER)
return number is
n_result number;
begin
n_result :=power(2,y-1);
if bitand(x,n_result)>0 then
return 1;
else
return 0;
end if;
end;
/
測試語句
select sum(bitand(level,3)) from dual connect by level<=80000;
select sum(bitor0(level,3)) from dual connect by level<=80000;
select sum(bitor(level,3)) from dual connect by level<=80000;
select sum(bitxor0(level,3)) from dual connect by level<=80000;
select sum(bitxor(level,3)) from dual connect by level<=80000;
select sum(bitlmv(level,3)) from dual connect by level<=80000;
select sum(bitrmv(level,3)) from dual connect by level<=80000;
select sum(bitnot(level)) from dual connect by level<=80000;
select sum(bitget(level,3)) from dual connect by level<=80000;
select sum(bitgeta(level,3)) from dual connect by level<=80000;

SQL> select sum(bitand(level,3)) from dual connect by level<=80000;

SUM(BITAND(LEVEL,3))
--------------------
              120000

經過時間:  00: 00: 00.31
SQL> select sum(bitor0(level,3)) from dual connect by level<=80000;

SUM(BITOR0(LEVEL,3))
--------------------
          3200160000

經過時間:  00: 00: 00.42
SQL> select sum(bitor(level,3)) from dual connect by level<=80000;

SUM(BITOR(LEVEL,3))
-------------------
         3200160000

經過時間:  00: 00: 00.43
SQL> select sum(bitxor0(level,3)) from dual connect by level<=80000;

SUM(BITXOR0(LEVEL,3))
---------------------
           3200040000

經過時間:  00: 00: 00.49
SQL> select sum(bitxor(level,3)) from dual connect by level<=80000;

SUM(BITXOR(LEVEL,3))
--------------------
          3200040000

經過時間:  00: 00: 00.45
SQL> select sum(bitlmv(level,3)) from dual connect by level<=80000;

SUM(BITLMV(LEVEL,3))
--------------------
          2.5600E+10

經過時間:  00: 00: 00.42
SQL> select sum(bitrmv(level,3)) from dual connect by level<=80000;

SUM(BITRMV(LEVEL,3))
--------------------
           399970000

經過時間:  00: 00: 00.50
SQL> select sum(bitnot(level)) from dual connect by level<=80000;

SUM(BITNOT(LEVEL))
------------------
        -3.200E+09

經過時間:  00: 00: 00.42
SQL> select sum(bitget(level,3)) from dual connect by level<=80000;

SUM(BITGET(LEVEL,3))
--------------------
               40000

經過時間:  00: 00: 00.57
SQL> select sum(bitgeta(level,3)) from dual connect by level<=80000;

SUM(BITGETA(LEVEL,3))
---------------------
                40000

經過時間:  00: 00: 00.54
SQL>
比下面2種實現簡單,效率也高

操作符 描述 例子 結果
|| 串連 B'10001' || B'011' 10001011
& 按位 AND（與） B'10001' & B'01101' 00001
| 按位 OR（或） B'10001' | B'01101' 11101
^ 按位 XOR（異或） B'10001' # B'01101' 11100
~ 按位 NOT（非） ~ B'10001' 01110
<< 按位左移 B'10001' << 3 01000
>> 按位右移 B'10001' >> 2 00100

oracle的位元運算
http://ww1.blog.enorth.com.cn/article/10173.shtml
create or replace package bitops2 is

function bitand(p_dec1 number, p_dec2 number) return varchar2 ;
function bitor(p_dec1 number, p_dec2 number) return varchar2 ;
function bitxor(p_dec1 number, p_dec2 number) return varchar2 ;
function raw_ascii(p_dec number) return raw;
function ascii_raw(p_raw varchar2) return number;
function bitnot(p_dec1 number) return number;

end;

create or replace package body bitops2 is

function raw_ascii(p_dec number) return raw is
v_result varchar2(1999);
v_tmp1 number := p_dec;
begin
loop
v_result := chr(mod(v_tmp1,256)) || v_result ;
v_tmp1 := trunc(v_tmp1/256);
exit when v_tmp1 = 0;
end loop;
return utl_raw.cast_to_raw(v_result);
end;

function ascii_raw(p_raw varchar2) return number is
v_result number := 0;
begin
for i in 1 .. length(p_raw) loop
v_result := v_result * 256 + ascii(substr(p_raw,i,1));
end loop;
return v_result;
end;

function bitand(p_dec1 number, p_dec2 number) return varchar2 is
begin
return
ascii_raw(
utl_raw.cast_to_varchar2(
utl_raw.bit_and(
raw_ascii(p_dec1),
raw_ascii(p_dec2)
)
)
);
end;

function bitor(p_dec1 number, p_dec2 number) return varchar2 is
begin
return
ascii_raw(
utl_raw.cast_to_varchar2(
utl_raw.bit_or(
raw_ascii(p_dec1),
raw_ascii(p_dec2)
)
)
);
end;

function bitxor(p_dec1 number, p_dec2 number) return varchar2 is
begin
return
ascii_raw(
utl_raw.cast_to_varchar2(
utl_raw.bit_xor(
raw_ascii(p_dec1),
raw_ascii(p_dec2)
)
)
);
end;

function bitnot(p_dec1 number) return number is
begin
return (0 - p_dec1) - 1;
end;
end;

===========================
SQL陳述式完成位操作
【2005-12-14 09:27】【IT 專家網】【網路整理】
在我們的資料庫中，有些欄位其值是按位表示的，即不同的位有不同的含義，比如用不同的位代表使用者的不同許可權或屬性，該位為1時，表示使用者有此許可權或屬性，為0則無此許可權或屬性等。相信有很多資料庫為了效率也有類似的設計。
在C語言中提供了&, |, ~以及>>,<<等豐富的位操作符，如何通過SQL語句實現對值的類似操作呢?下面給出我們常用的兩個函數(其中執行&操作的函數不是吹的，比Oracle 提供的BITAND函數好用，Oracle的函數在運算元較大時會出錯)，如果大家有類似的需求，只要參照其設計方法，可以很容易完成。
1. func_bitoper
我們在工作中常常有這樣的需求，要求將某個欄位的某一位或多位置為1或0,輸入參數in_value是待處理的值，enable_mask表示要將哪幾位置為1，如要將bit0和bit2置為1，則enable_mask := POWER(2,0) + POWER(2,2); ,enable_mask為0表示沒有需要置為1的位，同理disable_mask表示要將哪幾位置為0，如要將bit1和bit3置為0，則disable_mask := POWER(2,1) + POWER(2,3); ,disable_mask為0表示沒有需要置為0的位, 傳回值為經過位操作後的值。
換句話說，enable動作相當於與enable_mask進行或操作，disable動作相當於與~disable_mask進行與操作。
create or replace function func_bitoper(in_value IN NUMBER,enable_mask IN NUMBER,
disable_mask IN NUMBER ) return NUMBER IS
l_enable number;
l_disable number;
i number;
j number;
l_outvalue number;
l_temp number;
begin
l_enable := enable_mask;
l_disable := disable_mask;
l_outvalue := in_value;
-- enable
j := 0;
while l_enable > 0 loop
if MOD(l_enable,2) = 1 then -- to do set work
l_temp :=TRUNC(l_outvalue/POWER(2,j));
if MOD (l_temp,2) = 0 then -- set it to 1
l_outvalue := l_outvalue + POWER(2,j);
end if;
end if;
l_enable := TRUNC(l_enable/2);
j := j+1 ;
end loop;
-- disable
j := 0;
while l_disable > 0 loop
if MOD(l_disable,2) = 1 then -- to do set work
l_temp :=TRUNC(l_outvalue/POWER(2,j));
if MOD (l_temp,2) = 1 then -- set it to 0
l_outvalue := l_outvalue - POWER(2,j);
end if;
end if;
l_disable := TRUNC(l_disable/2);
j := j+1 ;
end loop;
return l_outvalue;
end;
/

2. func_and
用於對兩個數進行與操作，經常用於判斷使用者是否有許可權等。
create or replace function func_and(in_value IN NUMBER,in_mask IN NUMBER)
return number is
i number;
n_result number;
n_value number;
n_mask number;
begin
n_value := in_value;
n_mask := in_mask;
i := 0;
n_result := n_value;
while n_value > 0 loop
if (mod(n_mask,2) = 0) and (mod(n_value,2) = 1) then
n_result := n_result - power(2,i);
end if;
n_value := TRUNC(n_value/2);
n_mask := TRUNC(n_mask/2);
i := i + 1;
end loop;
return n_result;
end;
/