在数据库中实现base64编码和解码

在数据库中实现base64编码和译码
作者：
Thursday, April 1 2004 10:49 AM
MIME标准定义了一种编码，我们称之为base64。

这个标准定义了一系列的ASCII字符来描述64个特定值。

在许多早期的HTTP网络服务器中，在HTTP的认证中的密码一般都使用base64进行编码的。

在数据库内能把二进制的数据编码或者解码成base64文本就可以用存储过程从e-mail和HTTP服务器认证发送数据并解码。

Oracle9i就支持一个能对base64进行编码和解码的PL/SQL的包，这就像uuencode和绰号引起来的可打印格式一样，具体操作例如以下代码：
SQL> select utl_encode.base64_encode(hextoraw('0102')) from dual;
4151493D
SQL> select utl_encode.base64_decode(hextoraw('4151493D')) from dual;
0102
这些函数的返回结果是一个RA W函数，所以你需要采用UTL_RA W.CAST_TO_V ARCHAR来把它作为
V ARCHAR2型结果来使用。

如果你不用CAST_TO_V ARCHAR的话，数据可将自动的将这些RA W值转化为十六进制数据：
SQL>select utl_raw.cast_to_varchar2_
(utl_encode.base64_encode(hextoraw('0102')))
from dual;
AQI=
当然，在Java中也提供了许多能有效对base64进行编码解码的类，任何一个都能用做存储过程，有了UTL_RA W的帮助，在纯PL/SQL方式下编码方法写起来就非常简单了（这种编码方法在Oracle8中也可以用）。

代码A
现在当需要对base64进行编码的时候，你应该能将其来回的转换：：
SQL> select base64.encode(hextoraw('0102')) from dual;
AQI=
SQL> select base64.decode('AQI=') from dual;
0102
create or replace package base64
as
function encode(p_raw in raw) return varchar2;
function decode(p_base64 in varchar2) return raw;
end base64;
/
show errors;
create or replace package body base64
as
g_base64 varchar2(64) :=
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz 0123456789+/'; --
function encode(p_raw in raw) return varchar2
is
l_len pls_integer;
l_hex varchar2(6);
l_result varchar2(32767);
-- base64 values
b1 pls_integer;
b2 pls_integer;
b3 pls_integer;
b4 pls_integer;
-- raw bytes
r1 pls_integer;
r2 pls_integer;
r3 pls_integer;
begin
l_len := utl_raw.length(p_raw);
for i in 0 .. trunc(l_len / 3)-1 loop
l_hex := rawtohex(utl_raw.substr(p_raw,(i*3)+1,3)); -- get 3 binary bytes
r1 := to_number(substr(l_hex,1,2),'FM0X');
r2 := to_number(substr(l_hex,3,2),'FM0X');
r3 := to_number(substr(l_hex,5,2),'FM0X');
-- convert into base64 characters
b1 := trunc(r1/4);
b2 := (bitand(r1,3) * 16) + trunc(r2/16);
b3 := (bitand(r2,15) * 4) + trunc(r3/64);
b4 := bitand(r3,63);
l_result := l_result
|| substr(g_base64,b1+1,1)
|| substr(g_base64,b2+1,1)
|| substr(g_base64,b3+1,1)
|| substr(g_base64,b4+1,1);
end loop;
if mod(l_len,3) = 1 then
l_hex := rawtohex(utl_raw.substr(p_raw,l_len-1,1)); r1 := to_number(l_hex,'FM0X');
b1 := trunc(r1/4);
b2 := bitand(r1,3) * 16;
l_result := l_result
|| substr(g_base64,b1+1,1)
|| substr(g_base64,b2+1,1)
|| '==';
elsif mod(l_len,3) = 2 then
l_hex := rawtohex(utl_raw.substr(p_raw,l_len-2,2)); r1 := to_number(substr(l_hex,1,2),'FM0X');
r2 := to_number(substr(l_hex,3,2),'FM0x');
b1 := trunc(r1/4);
b2 := (bitand(r1,3) * 16) + trunc(r2/16);
b3 := bitand(r2,15) * 4;
l_result := l_result
|| substr(g_base64,b1+1,1)
|| substr(g_base64,b2+1,1)
|| substr(g_base64,b3+1,1)
|| '=';
end if;
return l_result;
end encode;
--
function decode(p_base64 in varchar2) return raw
is
l_len pls_integer;
l_base64 varchar2(32767) := p_base64;
l_raw raw(24576);
-- base64 values
b1 pls_integer;
b2 pls_integer;
b3 pls_integer;
b4 pls_integer;
-- raw bytes
r1 pls_integer;
r2 pls_integer;
r3 pls_integer;
begin
l_base64 := replace(l_base64,chr(10)||chr(13),null);
l_base64 := rtrim(l_base64,'=');
l_len := length(l_base64);
for i in 0 .. trunc(l_len / 4)-1 loop
-- retrieve 4 base64 values (6-bit)
b1 := instr(g_base64,substr(l_base64,(i*4)+1,1))-1;
b2 := instr(g_base64,substr(l_base64,(i*4)+2,1))-1;
b3 := instr(g_base64,substr(l_base64,(i*4)+3,1))-1;
b4 := instr(g_base64,substr(l_base64,(i*4)+4,1))-1;
-- bit manipulate into 8-bit values
r1 := (b1 * 4) + trunc(b2 / 16);
r2 := (bitand(b2,15) * 16) + trunc(b3 / 4);
r3 := (bitand(b3,3) * 64) + b4;
-- concatenate result with raw bytes
l_raw := utl_raw.concat(l_raw,hextoraw(
to_char(r1,'FM0X')||to_char(r2,'FM0X')||to_char(r3,'FM0X'))); end loop;
if mod(l_len,4) = 2 then
b1 := instr(g_base64,substr(l_base64,l_len-1,1))-1;
b2 := instr(g_base64,substr(l_base64,l_len,1))-1;
r1 := (b1 * 4) + trunc(b2 / 16);
l_raw := utl_raw.concat(l_raw,hextoraw(to_char(r1,'FM0X'))); elsif mod(l_len,4) = 3 then
b1 := instr(g_base64,substr(l_base64,l_len-2,1))-1;
b2 := instr(g_base64,substr(l_base64,l_len-1,1))-1;
b3 := instr(g_base64,substr(l_base64,l_len,1))-1;
r1 := (b1 * 4) + trunc(b2 / 16);
r2 := (bitand(b2,15) * 16) + trunc(b3 / 4);
l_raw := utl_raw.concat(l_raw,hextoraw(
to_char(r1,'FM0X')||to_char(r2,'FM0X')));
end if;
return l_raw;
end decode;
end base64;
/。