Oracle中使用位運算

文件:	bitwise.sql.zip
大小:	1KB
下载:	下载

有了這個程序包,在Oracle中使用位運算就簡單了.

CREATE OR REPLACE PACKAGE bitwise IS
FUNCTION hexconverter (pi_number IN NUMBER) RETURN CHAR;
FUNCTION hexconverter (pi_hexstr IN CHAR) RETURN NUMBER;
FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER) RETURN NUMBER;
FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER) RETURN NUMBER;
FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER) RETURN NUMBER;
END bitwise;
/

CREATE OR REPLACE PACKAGE BODY bitwise
IS
FUNCTION numtohexchar (pi_number IN NUMBER) RETURN CHAR
IS
v_hextoreturn CHAR (1);
BEGIN
IF pi_number = 0 THEN
v_hextoreturn := '0';
ELSIF pi_number = 1 THEN
v_hextoreturn := '1';
ELSIF pi_number = 2 THEN
v_hextoreturn := '2';
ELSIF pi_number = 3 THEN
v_hextoreturn := '3';
ELSIF pi_number = 4 THEN
v_hextoreturn := '4';
ELSIF pi_number = 5 THEN
v_hextoreturn := '5';
ELSIF pi_number = 6 THEN
v_hextoreturn := '6';
ELSIF pi_number = 7 THEN
v_hextoreturn := '7';
ELSIF pi_number = 8 THEN
v_hextoreturn := '8';
ELSIF pi_number = 9 THEN
v_hextoreturn := '9';
ELSIF pi_number = 10 THEN
v_hextoreturn := 'A';
ELSIF pi_number = 11 THEN
v_hextoreturn := 'B';
ELSIF pi_number = 12 THEN
v_hextoreturn := 'C';
ELSIF pi_number = 13 THEN
v_hextoreturn := 'D';
ELSIF pi_number = 14 THEN
v_hextoreturn := 'E';
ELSIF pi_number = 15 THEN
v_hextoreturn := 'F';
ELSE
raise_application_error (-20000, 'Invalid value', TRUE);
END IF;
RETURN v_hextoreturn;
END numtohexchar;

FUNCTION hexchartonum (pi_hexchar IN CHAR) RETURN NUMBER
IS
v_numtoreturn NUMBER (2);
BEGIN
IF pi_hexchar = '0' THEN
v_numtoreturn := 0;
ELSIF pi_hexchar = '1' THEN
v_numtoreturn := 1;
ELSIF pi_hexchar = '2' THEN
v_numtoreturn := 2;
ELSIF pi_hexchar = '3' THEN
v_numtoreturn := 3;
ELSIF pi_hexchar = '4' THEN
v_numtoreturn := 4;
ELSIF pi_hexchar = '5' THEN
v_numtoreturn := 5;
ELSIF pi_hexchar = '6' THEN
v_numtoreturn := 6;
ELSIF pi_hexchar = '7' THEN
v_numtoreturn := 7;
ELSIF pi_hexchar = '8' THEN
v_numtoreturn := 8;
ELSIF pi_hexchar = '9' THEN
v_numtoreturn := 9;
ELSIF pi_hexchar = 'A' THEN
v_numtoreturn := 10;
ELSIF pi_hexchar = 'B' THEN
v_numtoreturn := 11;
ELSIF pi_hexchar = 'C' THEN
v_numtoreturn := 12;
ELSIF pi_hexchar = 'D' THEN
v_numtoreturn := 13;
ELSIF pi_hexchar = 'E' THEN
v_numtoreturn := 14;
ELSIF pi_hexchar = 'F' THEN
v_numtoreturn := 15;
ELSE
raise_application_error (-20000, 'Invalid value', TRUE);
END IF;
RETURN v_numtoreturn;
END hexchartonum;

FUNCTION hexconverter (pi_number IN NUMBER) RETURN CHAR
IS
i NUMBER;
v_digit NUMBER (2);
v_hexstr VARCHAR2 (16);
BEGIN
v_hexstr :='' ;
FOR i IN REVERSE 0 .. 15
LOOP
v_digit := MOD (TRUNC (pi_number / POWER (16, i)), 16);
v_hexstr := v_hexstr || numtohexchar (v_digit);
END LOOP;
RETURN v_hexstr;
END hexconverter;

FUNCTION hexconverter (pi_hexstr IN CHAR) RETURN NUMBER
IS
i NUMBER;
v_digit NUMBER (2);
v_numtoreturn NUMBER;
v_hexstr16 CHAR (16);
BEGIN
v_hexstr16 := LPAD (pi_hexstr, 16, '0');
v_numtoreturn := 0;
FOR i IN 0 .. 16
LOOP
V_digit := hexchartonum (SUBSTR (v_hexstr16, i, 1));
v_numtoreturn := v_numtoreturn + v_digit * POWER (16, 16 - i);
END LOOP;
RETURN v_numtoreturn;
END hexconverter;

FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_and (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);

RETURN v_numresult;
END bitand;

FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_or (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);
RETURN v_numresult;
END bitor;

FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER
IS
v_hex1 CHAR (16);
v_hex2 CHAR (16);
v_raw1 RAW (8);
v_raw2 RAW (8);
v_rawresult RAW (8);
v_hexresult VARCHAR2 (16);
v_numresult NUMBER;
BEGIN
v_hex1 := hexconverter (pi_num1);
v_hex2 := hexconverter (pi_num2);
v_raw1 := HEXTORAW (v_hex1);
v_raw2 := HEXTORAW (v_hex2);
v_rawresult := UTL_RAW.bit_xor (v_raw1, v_raw2);
v_hexresult := RAWTOHEX (v_rawresult);
v_numresult := hexconverter (v_hexresult);

RETURN v_numresult;
END bitxor;
END bitwise;
/