Multiple bitand operations

Multiple bitand operations

Http://blog.itpub.net/oldwain according to http://blog.itpub.net/post/6/1609
Prompt,
Many people have a question: why is bitand only in Oracle, instead of bitxor,
It is easy to implement bitor and bitxor. bitor (x, y) = (x + y)-bitand (x, y );
Bitxor (x, y) = bitor (x, y)-bitand (x, y) = (x + y)-bitand (x, y) * 2;
Bitwise operations are implemented |, ^, &, <,>, and a certain integer value is obtained. For the sake of conciseness, most parameters are not judged as valid, and the efficiency is equal to the system bitand by an order of magnitude.
Please check for other errors

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;
End;
/
Create or replace function bitnot (X in number )--~ X = x ^ 0 xFFFF
Return number is
N_result number;
Begin
N_result: = bitand (x,-1 );
Return (X-1)-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;
/
Test statement
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

Used time: 00: 00: 00.31
SQL> select sum (bitor0 (level, 3) from dual connect by level <= 80000;

Sum (bitor0 (level, 3 ))
--------------------
3200160000

Used time: 00: 00: 00.42
SQL> select sum (bitor (level, 3) from dual connect by level <= 80000;

Sum (bitor (level, 3 ))
-------------------
3200160000

Used time: 00: 00: 00.43
SQL> select sum (bitxor0 (level, 3) from dual connect by level <= 80000;

Sum (bitxor0 (level, 3 ))
---------------------
3200040000

Used time: 00: 00: 00.49
SQL> select sum (bitxor (level, 3) from dual connect by level <= 80000;

Sum (bitxor (level, 3 ))
--------------------
3200040000

Used time: 00: 00: 00.45
SQL> select sum (bitlmv (level, 3) from dual connect by level <= 80000;

Sum (bitlmv (level, 3 ))
--------------------
2.5600e + 10

Used time: 00: 00: 00.42
SQL> select sum (bitrmv (level, 3) from dual connect by level <= 80000;

Sum (bitrmv (level, 3 ))
--------------------
399970000

Used time: 00: 00: 00.50
SQL> select sum (bitnot (level) from dual connect by level <= 80000;

Sum (bitnot (level ))
------------------
-3.200e + 09

Used time: 00: 00: 00.42
SQL> select sum (bitget (level, 3) from dual connect by level <= 80000;

Sum (bitget (level, 3 ))
--------------------
40000

Used time: 00: 00: 00.57
SQL> select sum (bitgeta (level, 3) from dual connect by level <= 80000;

Sum (bitgeta (level, 3 ))
---------------------
40000

Used time: 00: 00: 00.54
SQL>
It is simpler and more efficient than the following two methods.

Operator Description Example result
| Connection B '000000' | B '011 '10001
& Bitwise AND (and) B '000000' & B '000000' 10001
| Bitwise OR (OR) B '000000' | B '000000' 10001
^ Bitwise XOR (exclusive or) B '100' # B' 100' 10001
~ By bit not (not )~ B '000000' 10001
<Shift B '100' left by bit <3 10001
> Shift right by bit '123'> 2 10001

Oracle bitwise operations
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 statement bit operation
[] [IT expert network] [network arrangement]
In our database, the values of some fields are represented by BITs, that is, different bits have different meanings. For example, different bits represent different permissions or attributes of users, if the value is 1, the user has this permission or attribute. If the value is 0, the user does not have this permission or attribute. I believe many databases have similar designs for efficiency.
&, |, ~ Is provided in C Language ,~ And >>,< and other rich bitwise operators, how can we use SQL statements to perform similar operations on values? The following two commonly used functions are provided. (The execution and operation functions are not blow functions, which are better than the bitand functions provided by Oracle. Oracle functions may encounter errors when their operands are large ), if you have similar requirements, you can easily follow the design method.
1. func_bitratio
We often have this requirement in our work. We need to set a certain bit or multiple positions of a field to 1 or 0, and the input parameter in_value is the value to be processed, enable_mask indicates the position to be 1. To set bit0 and bit2 to 1, enable_mask: = power () + power );, if enable_mask is set to 0, it indicates that no bit needs to be set to 1. Similarly, disable_mask indicates the position to be set to 0. To set bit1 and bit3 to 0, disable_mask: = power () + power (); disable_mask is 0, indicating that no bit needs to be set to 0. The returned value is the value after bit operation.
In other words, the enable action is equivalent to performing or operating with enable_mask, And the disable action is equivalent ~ Disable_mask.
Create or replace function func_bitoper (in_value in number, enable_mask in number,
Disable_mask in number) return number is
Rochelle enable number;
Rochelle disable number;
I number;
J number;
Rochelle outvalue number;
Rochelle temp number;
Begin
Rochelle enable: = enable_mask;
Rochelle Disable: = disable_mask;
Rochelle outvalue: = in_value;
-- Enable
J: = 0;
While l_enable> 0 Loop
If Mod (l_enable, 2) = 1 then -- to do set work
Rochelle temp: = trunc (l_outvalue/power (2, j ));
If Mod (l_temp, 2) = 0 then -- set it to 1
Rochelle outvalue: = Rochelle outvalue + power (2, J );
End if;
End if;
Rochelle 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
Rochelle temp: = trunc (l_outvalue/power (2, j ));
If Mod (l_temp, 2) = 1 then -- set it to 0
Rochelle outvalue: = Rochelle outvalue-power (2, J );
End if;
End if;
Rochelle Disable: = trunc (l_disable/2 );
J: = J + 1;
End loop;
Return l_outvalue;
End;
/

2. func_and
It is used to perform and operate on two numbers and is often used to determine whether a user has permissions.
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;
/