How to create an account change function in Oracle

CREATE OR REPLACE FUNCTION fun_account_change (headobject in Ebsheadobject,
          &NBS P                inaccountno in Pub_account_info. Accountno%type,
                           INACC Ounttype in Pub_account_info. Accounttype%type,
                           ino Pmoney in number,
                           ino Pprofit in number,
                           in Flag in VARCHAR] return VARCHAR2
is
   dbaccountflag        pub_account_info. Accountflag%type;
   dbstate              pub_account_info. State%type;
   dbcredit   &NBSp         Pub_account_info. Credit%type;
   DBCREDITHLD          pub_account_info. Credithld%type;
   dbprofit             Pub_account_info. Profit%type;
   DBMACCHK             Pub_account_info. Macchk%type;
   dbthreshold          pub_account_info. Threshold%type;
   dbleastcredit        pub_account_info. Leastcredit%type;

DbCredit0 Pub_account_info. Credit%type;
DbProfit0 Pub_account_info. Profit%type;

Vcredit Pub_account_info. Credit%type;
Vprofit Pub_account_info. Profit%type;

   dbconnectphone       Pub_account_info. Connectphone%type;
   VMACCHK              VARCHAR2 (128);
   ---212001 212002 214001 214003 214005 will result in an update of the lasttime time, the principle of which is to record only the transactions initiated by the agent, the Commission processing does not do a point-in-time update.
BEGIN
   --According to AccountType  accountno get account information
    BEGIN
        SELECT  accountflag,state,credit,profit,credithld,macchk,threshold,leastcredit, CONNECTPHONE
       into Dbaccountflag,dbstate,dbcredit,dbprofit,dbcredithld,dbmacchk,dbthreshold,dbleastcredit, Dbconnectphone
       from pub_account_info
       where accountno= Inaccountno and Accounttype=inaccounttype;
    EXCEPTION
       --account does not exist
       when No_data_found THEN
           return ' 31000002 ';
      &NBSP When others THEN
           return ' 90000001 ';
    end;
    IF dbstate!= 0 THEN
       --account status is not normal, modify error code
       return ' 31000003 ';
    End IF;
   -parity mac
    SELECT Rawtohex (ebs_md5 lpad (dbcredit,16, ' 0 ') | | Lpad (dbprofit,16, ' 0 ')) into the vmacchk from DUAL;
    IF vmacchk!= dbmacchk THEN
       --account Illegal change, modify error code
      &NB Sp Return ' 31000004 ';
    End IF;

--If you use the principal account, retrieve the account information


IF (Inaccounttype!=0) and (Dbaccountflag!=0) THEN


BEGIN


SELECT State,credit,profit,dbcredithld,macchk,threshold,leastcredit, Connectphone


into Dbstate,dbcredit,dbprofit0,dbcredithld,dbmacchk,dbthreshold,dbleastcredit,dbconnectphone


from Pub_account_info


WHERE Accountno=inaccountno and accounttype=0;


EXCEPTION


--account does not exist


when No_data_found THEN


return ' 31000002 ';


When others THEN


return ' 90000001 ';


end;


IF dbstate!= 0 THEN


-account status is not normal, modify error code


return ' 31000003 ';


End IF;


--Verifying Mac


SELECT Rawtohex (ebs_md5 lpad (dbcredit,16, ' 0 ') | | Lpad (dbprofit0,16, ' 0 ')) into the vmacchk from DUAL;


IF vmacchk!= dbmacchk THEN


--Account illegal change, modify error code


return ' 31000004 ';


End IF;


End IF;


--Trading Category:


Case Inflag


when '-' THEN


-Account amount reduced, commission increase (applicable to recharge business, commission amount can be negative)


IF Dbcredit < Inopmoney THEN


--Insufficient account amount


return ' 31000001 ';


End IF;


IF Dbcredit-inopmoney <dbleastcredit THEN


--Minimum amount of control


return ' 31000001 ';


End IF;


IF Dbcredit-inopmoney <dbthreshold THEN


--Alarm Valve value


INSERT into Base_sendsms_log (agtphone,corpid,optime,state,owner,message)


VALUES (Dbconnectphone,substr (inaccountno,3,4), sysdate,0,headobject.tradetype| | headobject.tradecode| | Headobject.streamid, ' margin balance insufficient, remaining ' | | dbcredit/100| | ' Please deposit in time ');


End IF;


-Update the account, update the Commission (if the principal account is used, two accounts will need to be updated, Commission records in the breakdown of households)


IF (Inaccounttype!=0) and (Dbaccountflag!=0) THEN


--Hanging machine account


INSERT into Pub_account_log (Streamid,accountno,accounttype,optime,oldcredit,oldprofit,opmoney,newcredit, Newprofit,tradetype,tradecode,chnltype,deptno,operno)


SELECT HEADOBJECT.STREAMID,INACCOUNTNO,0,SYSDATE,CREDIT,PROFIT,INOPMONEY,CREDIT-INOPMONEY,PR Ofit,headobject.tradetype,headobject.tradecode,headobject.chnltype,headobject.deptno,headobject.operno


from Pub_account_info


WHERE Accountno=inaccountno and accounttype=0;


UPDATE pub_account_info SET Credit=credit-inopmoney, Macchk=rawtohex (ebs_md5 lpad (credit-inopmoney,16, ' 0 ') | | Lpad (profit,16, ' 0 ')) WHERE Accountno=inaccountno and accounttype=0;