In our database, some fields have a bit represented by bit, that is, different bites have different meanings, such as different permissions or attributes of different bits, which indicates that the user has this privilege or Attributes, there is no such authority or attribute, etc. I believe there are many databases that have similar designs for efficiency.
Provides a rich bit operator in the C language, how to implement similar operations for values through the SQL statement? The two functions we use (where the function of executing the & operation is not blowing, which is better than the Bitand function provided by Oracle, and the Oracle's function will be wrong when the operand is larger.) If you have a similar need, just Referring to its design method, it can be easily done.
FUNC_BITOPER
We often have such a need in our work, requiring a certain bit or more position of a field to 1 or 0, and the input parameter in_Value is the value to be processed. What is the location to be 1, if you want BIT0? And bit2 is set to 1, then enable_mask: = power (2, 0) Power (2, 2); Enable_mask is 0 indicates that there is no need to be set to 1, which is the same as the point_mask indicates which position is 0, such as To set Bit1 and Bit3 as 0, disable_mask: = power (2, 1) Power (2, 3); Disable_Mask is 0 indicates that there is no need to be 0, and the return value is the value after the bit operation.
In other words, the Enable action is equivalent to eNable_mask or operation, which is equivalent to operation with ~ 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
Used to perform and operate two numbers, often used to determine if the user has permission.
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; /
Good experience is willing to share with you, I hope to give the role of the jade.
