CREATE OR REPLACE PACKAGE PG_ENCRYPT_DECRYPT IS
iKey VARCHAR2(8) := 'oracle9i';
FUNCTION GEN_RAW_KEY (iKey IN VARCHAR2) RETURN RAW;
FUNCTION DECRYPT_3KEY_MODE (iValue IN RAW, iMode IN PLS_INTEGER) RETURN VARCHAR2;
FUNCTION ENCRYPT_3KEY_MODE (iValue IN VARCHAR2, iMode IN PLS_INTEGER) RETURN RAW;
FUNCTION FormatStr (iValue IN VARCHAR2) RETURN VARCHAR2;
FUNCTION FormatStr2 (iValue IN VARCHAR2) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY PG_ENCRYPT_DECRYPT IS
FUNCTION GEN_RAW_KEY (iKey IN VARCHAR2) RETURN RAW IS
rawkey RAW(240) := '';
BEGIN
FOR i IN 1..LENGTH(iKey) LOOP
rawkey := rawkey || HEXTORAW(TO_CHAR(ASCII(SUBSTR(iKey,i,1))));
END LOOP;
RETURN rawkey;
END;
FUNCTION ENCRYPT_3KEY_MODE (iValue IN VARCHAR2, iMode IN PLS_INTEGER) RETURN RAW IS
vEncrypted VARCHAR2(4000); vEncryptedRaw RAW(2048); rawkey RAW(240);
BEGIN
rawkey := GEN_RAW_KEY(iKey);
vEncrypted := DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT(
FormatStr(iValue), key_string => rawkey, which => iMode);
vEncryptedRaw := UTL_RAW.CAST_TO_RAW(vEncrypted);
RETURN vEncryptedRaw;
END;
FUNCTION DECRYPT_3KEY_MODE (iValue IN RAW, iMode IN PLS_INTEGER) RETURN VARCHAR2 IS
vDecrypted VARCHAR2(4000); rawkey RAW(240);
BEGIN
rawkey := GEN_RAW_KEY(iKey);
vDecrypted := DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(
UTL_RAW.CAST_TO_VARCHAR2(iValue), key_string => rawkey, which => iMode);
RETURN FormatStr2(vDecrypted);
END;
-- FormatStr/FormatStr2 處理填充/去填充,略
END;
/
調用示例:
SELECT PG_ENCRYPT_DECRYPT.ENCRYPT_3KEY_MODE('機密數據', 1) FROM dual;
SELECT PG_ENCRYPT_DECRYPT.DECRYPT_3KEY_MODE(hextoraw('...'), 1) FROM dual;
1=ECB、2=CBC。密鑰長度 8 字節自動擴展為 24 字節(3DES 要求)。
(本文共 2 頁,含填充函數完整代碼)