创建用于创建用户帐户和/或检查帐户是否已存在的Oracle DB存储过程

最后发布: 2014-05-19 23:16:07


问题

我是oracle db的新手,尤其是存储过程。

我的主要目标是要有一个过程来检查并确保在我的表中创建用户之前没有帐户(用户名或电子邮件)。

我遇到的问题是:执行该过程时,没有任何内容插入数据库。 如果有人可以检查此代码并确保它正确无误,将大有帮助。

      CREATE OR REPLACE PROCEDURE A2PROXYCREATEUSER (
        in_name IN VARCHAR2  
, in_password IN VARCHAR2  
, in_email IN VARCHAR2  
, in_subscript IN NUMBER DEFAULT 1 
, customaction IN VARCHAR2  
, userdata IN VARCHAR2  
, userdatalen IN NUMBER  
, returncode OUT NUMBER )
      AS
   CodeSuccess              constant number := 0;
   CodeAlreadyExists        constant number := 1;
   CodeInvalidUserName      constant number := 2;
   CodeAccountCreationDisabled      constant number := 3;
   CodeInvalidPassword      constant number := 4;
   CodeKeyInUser    constant number := 10;
   CodeInvalidKey       constant number := 11;
   current_name VARCHAR2(32);
   current_email varchar2(12);
   wonidseq number;
   BEGIN
   wonidseq := 0;
   returncode := CodeSuccess;

        SELECT NAME
        INTO   current_name
        FROM   WONUSER
        WHERE  NAME = in_name;
  returncode := CodeAlreadyExists;

        -- If the first SELECT statement above fails to return any
        -- records at all, then the NO_DATA_FOUND exception will be
        -- signalled. The following code reacts to this exception
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          BEGIN
           SELECT EMAIL
              INTO   current_email
              FROM   WONUSER
              WHERE  EMAIL = in_email;
        returncode:=CodeAlreadyExists;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
          BEGIN
SELECT  UNIQUEID_SEQ.nextval into wonidseq from WONUSER;
              INSERT INTO WONUSER(WONUSERSEQ, NAME, PASSWORD, NEWPASSWORD, EMAIL, TRUSTLEVEL, COMMUNITYSEQ, ISBANNED, ISACTIVE)
                VALUES(wonidseq, in_name, in_password, NULL, in_email, 120, 0, 0, 1);
returncode := CodeSuccess;
        end;


          END;
    if returncode = CodeSuccess then
        commit;
    else
        rollback;
    end if;
      END;

编辑:

我设法修复了代码(对于一个非常了解语法的人来说,可能看起来像个黑客)

create or replace 
PROCEDURE A2PROXYCREATEUSER (
       in_name IN VARCHAR2  
, in_password IN VARCHAR2  
, in_email IN VARCHAR2  
, in_subscript IN NUMBER DEFAULT 1 
, customaction IN VARCHAR2  
, userdata IN VARCHAR2  
, userdatalen IN NUMBER  
, returncode OUT NUMBER )
      AS
   CodeSuccess              constant number := 0;
   CodeAlreadyExists        constant number := 1;
   CodeInvalidUserName      constant number := 2;
   CodeInvalidEmail     constant number := 7;

   CodeAccountCreationDisabled      constant number := 3;

   current_name VARCHAR2(32);
   current_email varchar2(12);
   wonidseq number;
   BEGIN

SELECT LoginName into current_name from WONUSER WHERE  loginname = in_name;
returncode := CodeAlreadyExists;
    exception
        when NO_DATA_FOUND then
            returncode := CodeSuccess;
 if returncode = CodeSuccess then
   BEGIN
 SELECT EMAIL into current_email from WONUSER where EMAIL = in_email;
 returncode := CodeAlreadyExists;
    exception
        when NO_DATA_FOUND then
            returncode := CodeSuccess;
              INSERT INTO WONUSER (WONUSERSEQ, LOGINNAME, PASSWORD, NEWPASSWORD, EMAIL, TRUSTLEVEL, COMMUNITYSEQ, ISBANNED, ISACTIVE,birthdate)
                VALUES(wonidseq, in_name, in_password, ' ', in_email, 120, 0, 0, 1, sysdate);
        commit;
    end;
else
rollback;
end if;
end;
oracle stored-procedures registration
回答

我的代码就是这样的:

PROCEDURE A2PROXYCREATEUSER (...)  AS

   CodeSuccess              constant number := 0;
   CodeAlreadyExists        constant number := 1;
   CodeInvalidUserName      constant number := 2;
   CodeInvalidEmail     constant number := 7;
   CodeAccountCreationDisabled      constant number := 3;


    CURSOR curWonUser IS
    SELECT *
    FROM WONUSER
    WHERE EMAIL = in_email OR NAME = in_name;
    WonUser curWonUser%ROWTYPE;

BEGIN

    OPEN curWonUser;
    FETCH curWonUser INTO WonUser;
    IF curWonUser%NOTFOUND THEN
        INSERT INTO WONUSER(WONUSERSEQ, NAME, PASSWORD, NEWPASSWORD, EMAIL, TRUSTLEVEL, COMMUNITYSEQ, ISBANNED, ISACTIVE)
            VALUES (UNIQUEID_SEQ.NEXTVAL, in_name, in_password, NULL, in_email, 120, 0, 0, 1);
        returncode = CodeSuccess;
        COMMIT;
    ELSE 
        returncode = CodeAlreadyExists;
    END IF;
    CLOSE curWonUser;

end;

注意,您没有使用任何其他返回代码。