利用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;
/
