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

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;
/