I wrote an Oracle Conversion Function and found that the data cannot be converted when the data is negative, because the data stored in the front-end of the service is 32 bits. When JAVA is converted to the INT type with a signed character, A negative number is generated, and I want to convert it to binary for bit determination again. I have found many online sites and cannot convert signed numbers, therefore, a new function is written in the binary notation of negative numbers:
The representation of a negative binary method, for example,-5.
Step 1: first, convert 5 to the binary form of 101.
Step 2: The second step is the inverse of the security position (the first step is the formation of 1) 010
Step 3: Add 1 at the end to form: 11111111 11111111 11111111 11111011
Create or replace function NUMBER_2_BIT (V_NUM NUMBER) RETURN VARCHAR IS
V_RTN VARCHAR (2000 );
V_N1 NUMBER;
V_N2 NUMBER;
BEGIN
V_N1: = ABS (V_NUM );
-- If it is a positive number
If sign (V_NUM)> 0 THEN
LOOP
V_N2: = MOD (V_N1, 2 );
V_N1: = ABS (TRUNC (V_N1/2 ));
V_RTN: = TO_CHAR (V_N2) | V_RTN;
Exit when V_N1 = 0;
End loop;
-- Dbms_output.put_line ('positive result '| V_RTN );
-- Completion 32-bit high 0
SELECT lpad (V_RTN, 32, 0)
INTO V_RTN
FROM dual;
-- Dbms_output.put_line ('positive number completion result' | V_RTN );
ELSE
-- Convert to binary and return the result by bit
LOOP
V_N2: = MOD (V_N1, 2 );
IF V_N2 = 1 THEN
V_N2: = 0;
ELSIF V_N2 = 0 THEN
V_N2: = 1;
End if;
V_N1: = ABS (TRUNC (V_N1/2 ));
V_RTN: = TO_CHAR (V_N2) | V_RTN;
Exit when V_N1 = 0;
End loop;
-- Dbms_output.put_line ('negative result '| V_RTN );
-- Complete 32-bit high 1
SELECT lpad (V_RTN, 32, 1)
INTO V_RTN
FROM dual;
-- Dbms_output.put_line ('negative number completion 1 result' | V_RTN );
-- Binary conversion to 10 mechanism, + 1 at the same time
Select sum (data1) + 1
INTO V_N1
FROM (SELECT substr (V_RTN, rownum, 1) * power (2, length (V_RTN)-rownum) data1
FROM dual
Connect by rownum <= length (V_RTN ));
-- Dbms_output.put_line ('convert to decimal number result' | V_RTN );
---- Convert to binary
LOOP
V_N2: = MOD (V_N1, 2 );
V_N1: = ABS (TRUNC (V_N1/2 ));
V_RTN: = TO_CHAR (V_N2) | V_RTN;
Exit when V_N1 = 0;
End loop;
-- Dbms_output.put_line ('negative conversion result' | V_RTN );
-- Completion 32-bit high 0
SELECT lpad (V_RTN, 32, 0)
INTO V_RTN
FROM dual;
-- Dbms_output.put_line ('negative number completion 0 result' | V_RTN );
End if;
RETURN V_RTN;
END;