function FormatStr(iValue in varchar2)return varchar2
as
begin
declare
i number;
j number;
m_value varchar2(4000);
begin
m_value:=iValue;
i:=(length(m_value) mod 8);
if i<>0 then
j:=1;
for j in 1..(8-i) loop
m_value:=m_value||'#';
end loop;
end if;
return m_value;
end;
end FormatStr;
function FormatStr2(iValue in varchar2)return varchar2
as
begin
declare
i number;
j number;
m_value varchar2(4000);
begin
m_value:=iValue;
i:=instr(iValue,'#',1,1);
if i>0 then
m_value:=substr(m_value,1,i-1);
end if;
return m_value;
end;
end FormatStr2;
end PG_ENCRYPT_DECRYPT;
create table users1(
userid varchar2(50)
primary key,
password varchar2(4000), --密码原文
encrypted varchar2(4000) --加密后的密码
);
insert into users1 values ('user1','user1234yyyy',null);
insert into users1 values ('user2','abcd1234yy',null);
insert into users1 values ('user3','oracle12yyy',null);
update users1 set encrypted = PG_ENCRYPT_DECRYPT.ENCRYPT_3KEY_MODE(password,1);--加密
select * from users1;--正常查看
select userid,password,PG_ENCRYPT_DECRYPT.DECRYPT_3KEY_MODE(encrypted,1) DECRYPTED from users1;--解密后查看