加密(encrypt)解密(decrypt)采用 Oracle DBMS_OBFUSCATION_TOOLKIT package.

利用这个包,我们可以对数据进行DES,Triple DES或者MD5加密.

DESGETKEY   --产生密钥,用于DES算法
   DES3GETKEY  -- 产生密钥,用于Triple DES算法
   DESENCRYPT  -- 用DES算法加密数据
   DESDECRYPT  -- 用DES算法解密数据
   DES3ENCRYPT -- 用Triple DES算法加密数据
   DES3DECRYPT -- 用DES算法解密数据
   MD5         -- 用MD5算法加密数据

使用DES算法和Triple DES算法的特征之一. 是输入长度必须是8的倍数,而输出也是8的倍数,所以我们的字段长度也是8的倍数.如果输入不是8的倍数,会报错.

The key specified is too short for the algorithm. DES requires a key of at least 8 bytes. Triple DES requires a key of least 16 bytes in two-key mode and 24 bytes in three-key mode.

也可以使用DESGETKEY获取Key

PROCEDURE DESGETKEY
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 SEED                           RAW                     IN
 KEY                            RAW                     OUT

create or replace package PG_ENCRYPT_DECRYPT is
  function getkey return varchar2;
  function encrypt(p_in varchar2) return varchar2;
  function decrypt(p_in varchar2) return varchar2;
end;

create or replace package body PG_ENCRYPT_DECRYPT is
  function getkey return varchar2 is
    v_type varchar2(1) := '';
    v_rtn  varchar2(40);
    v_key  varchar2(20) := 'AAAA1234';
  begin
    if (v_type = '1') then
      select to_char(sysdate, 'yyyy') into v_key from dual;
    elsif (v_type = '2') then
      select to_char(sysdate, 'yyyyQ') into v_key from dual;
    elsif (v_type = '3') then
      select to_char(sysdate, 'yyyymm') into v_key from dual;
    elsif (v_type = '4') then
      select to_char(sysdate, 'yyyyWW') into v_key from dual;
    elsif (v_type = '5') then
      select to_char(sysdate, 'yyyymmdd') into v_key from dual;
    end if;
    for i in 1 .. length(v_key) loop
      v_rtn := v_rtn || to_char(ascii(substr(v_key, i, 1)));
    end loop;
    return v_rtn;
  end;
  /* Creating function DECRYPT*/
  function encrypt(p_in varchar2) return varchar2 is
    v_in  varchar2(320);
    v_out varchar2(1000);
  begin
    v_in  := rpad(p_in, (trunc(length(p_in) / 8) + 1) * 16, chr(0));
    v_out := UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.desencrypt(input_string => v_in,
                                                                     key_string   => getkey()));
    return v_out;
  end;
  /*Creating function ENCRYPT*/
  function decrypt(p_in varchar2) return varchar2 is
    v_out varchar2(1000);
  begin
    dbms_obfuscation_toolkit.DESDECRYPT(input_string     => UTL_RAW.CAST_TO_varchar2(p_in),
                                        key_string       => getkey(),
                                        decrypted_string => v_out);
    v_out := rtrim(v_out, chr(0));
    return v_out;
  end;

end;