ORACLE 应用经验(4)-加密程序

2/9/2008来源:Oracle教程人气:5673


  -------------1. 加密程序数据库触发子scjmmm----------------------
  REM 对GHXXB制立数据库触发子(当INSERT OR UPDATE GHXXB时触发)
  drop trigger scjmmm;
  create or replace trigger scjmmm
  before insert or update of mm On ghxxb For each Row
  Begin
  :new.mm:=ENCRYPT(:new.mm,:NEW.GH,TO_CHAR(SYSDATE,'SS'));
  End;
  /
  -------------2. 密码的加密程序ENCRYPT----------------------
  Create or Replace
  Function ENCRYPT (Inpass In Varchar2,IN_GH In Varchar2,IN_SS In Varchar2)
  Return Varchar2 Is
  bcs varchar2(20);
  bcs1 number;
  cs number;
  jg number;
  m_gh VARCHAR2(4);
  m_mm VARCHAR2(20);
  Begin
  m_gh:=IN_GH;
  m_mm:=INPASS;
  cs:=TO_NUMBER(IN_SS);
  If cs<=1 then cs:=77 ;end if;
  bcs:=substr(to_char(ascii(substr(m_gh,1,1))),1,2);
  If bcs<'1' then bcs:='7' ;end if;
  m_gh:=substr(m_gh,2);
  Loop EXIT WHEN nvl(length(m_gh),0)=0 ;
  bcs:=bCSSubstr(to_char(ascii(substr(m_gh,1,1))),-1,1);
  m_gh:=substr(m_gh,2);
  End loop;
  Loop EXIT WHEN nvl(length(m_mm),0)=0 ;
  bcs:=bcssubstr(to_char(ascii(substr(m_mm,1,1))),-1,1);
  m_mm:=substr(m_mm,2);
  End loop;
  bcs1:=to_number(bcs);
  jg:=cs*bcs1;
  Loop EXIT WHEN length(to_char(jg))>13;
  jg:=jg*cs ;
  End loop;
  RETURN(IN_SSsubstr(to_char(jg),1,14));
  End;
  /
  grant execute on ENCRYPT to public;