ORACLE应用经验(4)-加密程序[转载]

怀梅4483

ORACLE应用经验(4)-加密程序
-------------1.加密程序数据库触发子scjmmm----------------------
REM对GHXXB制立数据库触发子(当INSERTORUPDATEGHXXB时触发)
droptriggerscjmmm;
createorreplacetriggerscjmmm
beforeinsertorupdateofmmOnghxxbForeachRow
Begin
:new.mm:=ENCRYPT(:new.mm,:NEW.GH,TO_CHAR(SYSDATE,'SS'));
End;
/
-------------2.密码的加密程序ENCRYPT----------------------
CreateorReplace
FunctionENCRYPT(InpassInVarchar2,IN_GHInVarchar2,IN_SSInVarchar2)
ReturnVarchar2Is
bcsvarchar2(20);
bcs1number;
csnumber;
jgnumber;
m_ghVARCHAR2(4);
m_mmVARCHAR2(20);
Begin
m_gh:=IN_GH;
m_mm:=INPASS;
cs:=TO_NUMBER(IN_SS);
Ifcs<=1thencs:=77;endif;
bcs:=substr(to_char(ascii(substr(m_gh,1,1))),1,2);
Ifbcs<'1'thenbcs:='7';endif;
m_gh:=substr(m_gh,2);
LoopEXITWHENnvl(length(m_gh),0)=0;
bcs:=bcs||substr(to_char(ascii(substr(m_gh,1,1))),-1,1);
m_gh:=substr(m_gh,2);
Endloop;
LoopEXITWHENnvl(length(m_mm),0)=0;
bcs:=bcs||substr(to_char(ascii(substr(m_mm,1,1))),-1,1);
m_mm:=substr(m_mm,2);
Endloop;
bcs1:=to_number(bcs);
jg:=cs*bcs1;
LoopEXITWHENlength(to_char(jg))>13;
jg:=jg*cs;
Endloop;
RETURN(IN_SS||substr(to_char(jg),1,14));
End;
/
grantexecuteonENCRYPTtopublic;