/*

-- @author:    Lijy

-- @function: 员工入职的信息检查程序

-- @parr: P_URID 为workshop操作账号的ID，前台通过 {U_URID} 全局参数获取

*/

CREATE OR REPLACE Procedure eSP_StaffCheck

(

    P_ID  integer,

    P_URID integer,

    P_Retval Out integer

)

As

    P_compid integer;

    P_i Integer;

    e_exception  Exception;

Begin

   P_RetVal:=0;

   P_i:=0;

   Select compid into P_compid From eStaff_Register where id = P_id;

  --数据已经确认！

    Select Count(1) Into P_i From dual

    Where

    Exists(Select 1 From eStaff_Register

            Where ID=P_ID And Nvl(Initialized,0)=1) ;

    If P_i = 1 Then

          P_Retval:=910000;

          raise e_exception;

    End if ;

  -- savepoint

  Update eStaff_Register

  Set  Initialized = 1,

       InitializedBy = P_URID,

       InitializedTime =  sysdate

  Where ID=P_ID;

  commit;

Exception

    When e_exception then

        Null;

    When others then

        P_RetVal := -1;

        dbms_output.put_line(SQLERRM);

End eSP_StaffCheck1;