/*
加密、解密包
*/
CREATE OR REPLACE PACKAGE cux_fnd_encrypt_pkg IS
FUNCTION pswd_encrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION pswd_decrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION encode_base64(p_clob_in IN CLOB) RETURN CLOB;
FUNCTION decode_base64(p_clob_in IN CLOB) RETURN CLOB;
PROCEDURE write_to_clob(p_buffer IN VARCHAR2, x_clob IN OUT CLOB);
END;
CREATE OR REPLACE PACKAGE BODY cux_fnd_encrypt_pkg IS
g_crypto_type PLS_INTEGER := dbms_crypto.encrypt_aes256 +
dbms_crypto.chain_cbc +
dbms_crypto.pad_pkcs5;
FUNCTION trim_clob(p_clob_in IN CLOB) RETURN CLOB
IS
v_offset INTEGER;
v_buffer_size BINARY_INTEGER := 4800;
v_buffer_varchar VARCHAR2(4800);
v_buffer_raw RAW(4800);
l_clob_x CLOB;
v_clob CLOB;
BEGIN
dbms_lob.createtemporary(v_clob, TRUE);
v_offset := 1;
FOR i IN 1 .. ceil(dbms_lob.getlength(p_clob_in) / v_buffer_size) LOOP
dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
v_buffer_varchar := REPLACE(REPLACE(v_buffer_varchar, chr(10), ''),
chr(13),
'');
dbms_lob.writeappend(v_clob,
length(v_buffer_varchar),
v_buffer_varchar);
v_offset := v_offset + v_buffer_size;
END LOOP;
l_clob_x := v_clob;
dbms_lob.freetemporary(v_clob);
RETURN l_clob_x;
END trim_clob;
FUNCTION pswd_encrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
RETURN VARCHAR2 IS
l_key_word VARCHAR2(100);
l_encrypted_pswd VARCHAR2(240);
l_pswd_raw RAW(2000);
BEGIN
IF lengthb(p_key) >= 32 THEN
l_key_word := substrb(p_key, 1, 32);
ELSE
l_key_word := substrb(rpad(p_key, 32, '0'), 1, 32);
END IF;
l_pswd_raw := dbms_crypto.encrypt(src => utl_raw.cast_to_raw(p_pswd),
typ => g_crypto_type,
key => utl_raw.cast_to_raw(l_key_word));
l_encrypted_pswd := l_pswd_raw;
RETURN l_encrypted_pswd;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END pswd_encrypt;
FUNCTION pswd_decrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
RETURN VARCHAR2
IS
l_key_word VARCHAR2(100);
l_encrypted_pswd VARCHAR2(240);
l_pswd_raw RAW(2000);
l_raw RAW(2000) := p_pswd;
BEGIN
IF lengthb(p_key) >= 32 THEN
l_key_word := substrb(p_key, 1, 32);
ELSE
l_key_word := substrb(rpad(p_key, 32, '0'), 1, 32);
END IF;
l_pswd_raw := dbms_crypto.decrypt(src => l_raw,
typ => g_crypto_type,
key => utl_raw.cast_to_raw(l_key_word));
l_encrypted_pswd := utl_raw.cast_to_varchar2(l_pswd_raw);
RETURN l_encrypted_pswd;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END pswd_decrypt;
FUNCTION encode_base64(p_clob_in IN CLOB) RETURN CLOB
IS
l_dest_blob BLOB;
l_amount NUMBER;
l_dest_offset NUMBER := 1;
l_src_offset NUMBER := 1;
l_lang_context NUMBER := dbms_lob.default_lang_ctx;
l_warning NUMBER;
v_clob CLOB;
v_result CLOB;
v_offset INTEGER;
v_chunk_size BINARY_INTEGER := (4800 / 4) * 3;
v_buffer_varchar VARCHAR2(7200);
v_buffer_raw RAW(7200);
BEGIN
IF p_clob_in IS NULL THEN
RETURN NULL;
END IF;
dbms_lob.createtemporary(l_dest_blob, TRUE);
l_amount := dbms_lob.getlength(p_clob_in);
l_dest_offset := 1;
l_src_offset := 1;
dbms_lob.converttoblob(dest_lob => l_dest_blob,
src_clob => p_clob_in,
amount => l_amount,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => nls_charset_id('UTF8'),
lang_context => l_lang_context,
warning => l_warning);
dbms_lob.createtemporary(v_clob, TRUE);
v_offset := 1;
FOR i IN 1 .. ceil(dbms_lob.getlength(l_dest_blob) / v_chunk_size) LOOP
dbms_lob.read(l_dest_blob, v_chunk_size, v_offset, v_buffer_raw);
v_buffer_raw := utl_encode.base64_encode(v_buffer_raw);
v_buffer_varchar := utl_raw.cast_to_varchar2(v_buffer_raw);
dbms_lob.writeappend(v_clob,
length(v_buffer_varchar),
v_buffer_varchar);
v_offset := v_offset + v_chunk_size;
END LOOP;
v_result := v_clob;
dbms_lob.freetemporary(v_clob);
RETURN v_result;
END encode_base64;
FUNCTION decode_base64(p_clob_in IN CLOB) RETURN CLOB IS
v_blob BLOB;
v_result BLOB;
v_offset INTEGER;
v_buffer_size BINARY_INTEGER := 4800;
v_buffer_varchar VARCHAR2(4800);
v_buffer_raw RAW(4800);
l_dest_clob CLOB;
l_amount NUMBER;
l_dest_offset NUMBER := 1;
l_src_offset NUMBER := 1;
l_lang_context NUMBER := dbms_lob.default_lang_ctx;
l_warning NUMBER;
l_clob_x CLOB;
BEGIN
IF p_clob_in IS NULL THEN
RETURN NULL;
END IF;
l_clob_x := trim_clob(p_clob_in);
dbms_lob.createtemporary(v_blob, TRUE);
v_offset := 1;
FOR i IN 1 .. ceil(dbms_lob.getlength(l_clob_x) / v_buffer_size) LOOP
dbms_lob.read(l_clob_x, v_buffer_size, v_offset, v_buffer_varchar);
v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);
v_buffer_raw := utl_encode.base64_decode(v_buffer_raw);
dbms_lob.writeappend(v_blob,
utl_raw.length(v_buffer_raw),
v_buffer_raw);
v_offset := v_offset + v_buffer_size;
END LOOP;
v_result := v_blob;
dbms_lob.freetemporary(v_blob);
l_amount := dbms_lob.getlength(v_result);
dbms_lob.createtemporary(l_dest_clob, TRUE);
l_dest_offset := 1;
l_src_offset := 1;
dbms_lob.converttoclob(dest_lob => l_dest_clob,
src_blob => v_result,
amount => l_amount,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => nls_charset_id('UTF8'),
lang_context => l_lang_context,
warning => l_warning);
RETURN l_dest_clob;
END decode_base64;
PROCEDURE write_to_clob(p_buffer IN VARCHAR2, x_clob IN OUT CLOB) IS
v_writing_position NUMBER := 0;
v_amount NUMBER;
BEGIN
v_amount := length(p_buffer);
v_writing_position := dbms_lob.getlength(x_clob) + 1;
dbms_lob.write(x_clob, v_amount, v_writing_position, p_buffer);
END;
END;
/*
测试1适用于加密、解密密码
*/
DECLARE
l_encrypt VARCHAR2(200);
l_decrypt VARCHAR2(200);
BEGIN
l_encrypt := cux_fnd_encrypt_pkg.pswd_encrypt(p_pswd => '123456789',
p_key => 'test');
dbms_output.put_line(l_encrypt);
l_decrypt := cux_fnd_encrypt_pkg.pswd_decrypt(p_pswd => l_encrypt,
p_key => 'test');
dbms_output.put_line(l_decrypt);
END;
/*
测试2 适用于加密、解密内容
*/
DECLARE
l_encrypt VARCHAR2(200);
l_decrypt VARCHAR2(200);
BEGIN
l_encrypt := cux_fnd_encrypt_pkg.encode_base64('123456789');
dbms_output.put_line(l_encrypt);
l_decrypt := cux_fnd_encrypt_pkg.decode_base64(l_encrypt);
dbms_output.put_line(l_decrypt);
END;