以下是包的代码和说明:
包的声明:
create or replace package PG_ENCRYPT_DECRYPT is
function GEN_RAW_KEY return raw;
function encrypt_data(p_text in varchar2) return varchar2;
function encrypt_data(p_num in number) return varchar2;
function decrypt_data(p_text varchar2) return varchar2;
end;
包的实现:
create or replace package body PG_ENCRYPT_DECRYPT is
/*Creating function GEN_RAW_KEY to get key*/
function GEN_RAW_KEY return raw
as
rawkey raw(240) := '';
fhandle utl_file.file_type;
fp_buffer varchar2(4000);
begin
fhandle := utl_file.fopen ('UTL_FILE_DIR','test.abc', 'r');
utl_file.get_line (fhandle , fp_buffer);
rawkey :=fp_buffer;
utl_file.fclose(fhandle);
return rawkey;
end;
/*Creating function encrypt_data,encrypt character*/
function encrypt_data(p_text in varchar2) return varchar2
as
v_text varchar2(4000);
v_enc varchar2(4000);
p_key varchar2(4000);
begin
v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
p_key := GEN_RAW_KEY;
sys.dbms_obfuscation_toolkit.desencrypt(
input_string => v_text,
key_string => p_key,
encrypted_string=>v_enc);
return v_enc;
end;
/*Creating function encrypt_data,encrypt number*/
function encrypt_data(p_num in number) return varchar2
as
v_text varchar2(4000);
v_enc varchar2(4000);
p_key varchar2(4000);
p_text varchar2(4000);
begin
p_text := to_char(p_num);
v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
p_key := GEN_RAW_KEY;
sys.dbms_obfuscation_toolkit.desencrypt(
input_string => v_text,
key_string => p_key,
encrypted_string=>v_enc);
return v_enc;
end;
/*Creating function decrypt_data,decrypt character*/
function decrypt_data(p_text varchar2) return varchar2
as
v_text varchar2(4000);
p_key varchar2(4000);
begin
p_key := GEN_RAW_KEY;
sys.dbms_obfuscation_toolkit.desdecrypt(
input_string => p_text,
key_string => p_key,
decrypted_string=> v_text);
return rtrim(v_text,chr(0));
end;
end;
下面举例
例1、字符数据加密
SQL> desc emp
Name Type Nullable Default Comments
------------ ------------ -------- ------- --------
ENO VARCHAR2(10) Y
ENAME VARCHAR2(64) Y
ENCRYPT_NAME VARCHAR2(64) Y
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
0004 c++
00001 sqlee
00002 lee
00003 oracle
SQL> update emp set ENCRYPT_NAME = PG_ENCRYPT_DECRYPT.encrypt_data(ename);
4 rows updated
SQL> commit;
Commit complete
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
0004 c++ 膉??.?
00001 sqlee 鍀DkR徤a
00002 lee F{蘱j0?
00003 oracle ?(?嫄?
例2、字符数据解密
SQL> update emp set ENCRYPT_NAME = PG_ENCRYPT_DECRYPT.decrypt_data(ENCRYPT_NAME);
4 rows updated
SQL> commit;
Commit complete
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
0004 c++ c++
00001 sqlee sqlee
00002 lee lee
00003 oracle oracle
例3、数字数据加密和解密
SQL> desc emp;
Name Type Nullable Default Comments
---------------- ------------ -------- ------- --------
ENO VARCHAR2(10) Y
ENAME VARCHAR2(64) Y
ENCRYPT_NAME VARCHAR2(64) Y
ACCOUNTS NUMBER Y
ENCRYPT_ACCOUNTS VARCHAR2(64) Y
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005 123 ?D泭磈?
00004 c++ 膉??.? 1000
00001 sqlee 鍀DkR徤a
00002 lee F{蘱j0?
00003 oracle ?(?嫄?
SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';
1 row updated
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005 123 ?D泭磈?
00004 c++ 膉??.? 1000 p.湇R?N
00001 sqlee 鍀DkR徤a
00002 lee F{蘱j0?
00003 oracle ?(?嫄?
SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.decrypt_data(ENCRYPT_ACCOUNTS) where eno ='00004';
1 row updated
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005 123 ?D泭磈?
00004 c++ 膉??.? 1000 1000
00001 sqlee 鍀DkR徤a
00002 lee F{蘱j0?
00003 oracle ?(?嫄?
SQL> commit;
Commit complete
SQL> update emp set ACCOUNTS = 168.50 where eno = '00004';
1 row updated
SQL> commit;
Commit complete
SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';
1 row updated
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005 123 ?D泭磈?
00004 c++ 膉??.? 168.5 玴灹.??
00001 sqlee 鍀DkR徤a
00002 lee F{蘱j0?
00003 oracle ?(?嫄?
SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.decrypt_data(ENCRYPT_ACCOUNTS) where eno ='00004';
1 row updated
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005 123 ?D泭磈?
00004 c++ 膉??.? 168.5 168.5
00001 sqlee 鍀DkR徤a
00002 lee F{蘱j0?
00003 oracle ?(?嫄?
SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';
1 row updated
SQL> rollback;
Rollback complete
SQL> update emp set ACCOUNTS = 10000.250 where eno = '00004';
1 row updated
SQL> commit;
Commit complete
SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';
1 row updated
SQL> commit;
Commit complete
SQL> select *from emp;
ENO ENAME ENCRYPT_NAME ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005 123 ?D泭磈?
00004 c++ 膉??.? 10000.25 4c訕&靾熾
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12158104/viewspace-214910/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12158104/viewspace-214910/