Oracle相当于MySQL代码“插入虚拟”以返回错误消息

I have a couple of hundred triggers in mysql db which i'm migrating to Oracle db. There's an statement that i see a lot which i haven't been able to find an equivalent one in oracle.

我在mysql db中有几百个触发器,我正在迁移到Oracle db。有一个声明,我看到很多我无法在oracle中找到一个等效的声明。

INSERT INTO $some_kind_of_message._BD_TRIGG$ (dummy) VALUES (value);

i've seen this also in procedures and functions, and it seems to 'return' a message to the aplication who calls the procedure (i think). i'm kind of new in both RDBMS but i've been working with oracle a couple of months.

我也在程序和功能中看到了这一点,它似乎向返回的程序“返回”了一个消息(我认为)。我在两个RDBMS中都是新手,但我已经和oracle合作了几个月。

Is there an equivalent statement to replace the mysql's one in oracle ? Thanks a lot.

是否有一个等效的声明来替换oracle中的mysql?非常感谢。

EDIT:

This is an example trigger of many. This one is used for login validation. I'm not very sure about oracle's trigger syntax but that's not the problem now. The 'case' part is still in mysql syntax. I havent been able to find information in mysql nor oracle documentation. It is not a normal insert, it is some kind of return message that mysql uses (thats what i guess) and i've seen it also in functions and procedures. ¿How can i replace it for oracle to perform the same task?

这是许多人的一个例子。这个用于登录验证。我不太确定oracle的触发器语法,但现在不是问题。 'case'部分仍然是mysql语法。我还没能找到mysql和oracle文档中的信息。它不是普通的插入,它是mysql使用的某种返回消息(这就是我猜的),我也在函数和程序中看到过它。 ¿我怎样才能为oracle替换它来执行相同的任务?

CREATE OR REPLACE TRIGGER adduser 
BEFORE INSERT
ON tbl_users
FOR EACH ROW

  DECLARE flag INTEGER;

  begin
  flag := 1;

  /* validate login */
  IF(LENGTH(TRIM(:NEW.login)) < 4) THEN
    flag := -1;
  END IF;

  /* valido clave */
  IF(flag = 1) THEN
    IF(LENGTH(:NEW.clave) < 3) THEN
      flag := -2;
    END IF;
  END IF;
    CASE flag
        WHEN -1 THEN INSERT INTO $login_less_then_4_characters._BD_TRIGG$ (dummy) VALUES ('error');
        WHEN -2 THEN INSERT INTO $pass_less_then_5_characters._BD_TRIGG$ (dummy) VALUES ('error');
        ELSE flag := 0;
    END CASE;
END;

2 个解决方案

#1

I think you need RAISE_APPLICATION_ERROR() function/procedure.

我认为你需要RAISE_APPLICATION_ERROR()函数/过程。

Syntax:

raise_application_error(error code, your error message);

Example:

raise_application_error(-20001, 'Login must have 4 characters or more');

In Oracle custom aplication error codes are between -20000 and -20999.

在Oracle中,自定义应用程序错误代码介于-20000和-20999之间。

More information here:

更多信息:

Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM

Oracle PL / SQL - 使用自定义SQLERRM引发用户定义的异常

Oracle documentation: link

Oracle文档:链接

2 个解决方案

#1

更多相关文章

随机推荐