oracle raise_application_error mysql_MySQL - oracle - Aborting Stored procedures and emulating RAISE...-CSDN博客

本文链接：https://blog.csdn.net/weixin_39809793/article/details/113299859

Discussion

When porting stored procedures from Oracle to MySQL, the Oracle specific function RAISE_APPLICATION_ERROR(code, message) is being used to transport an error code and an error message to the application and stop execution of the

procedure. MySQL does not currently have support for SIGNAL, so this functionality must be emulated.

The function raise_application_error(code, message) in Oracle terminates the execution of the current function or procedure, and raises a given error condition. MySQL does not yet implement SIGNAL, so that a direct port is not possible. The following function terminates execution of a function or procedure with an error, and delivers the error code and message as part of the result set, since without SIGNAL we cannot influence SQLSTATE or MySQL error codes internally. The error code shown is 1406, SQLSTATE 22001.DROP PROCEDURE IF EXISTS raise_application_error;

DELIMETER $$

CREATE PROCEDURE raise_application_error(

IN CODE INTEGER,

IN MESSAGE VARCHAR(255)

)

SQL SECURITY INVOKER

DETERMINISTIC

BEGIN

DECLARE ERROR CHAR(2);

SELECT CODE, MESSAGE;

SET ERROR = 'abc';

END;