- 1. In oracle, write a stored procedure (for the exception message)

Create procedure (XXX)
As
The begin

.

The exception
The when others then
Dbms_output (' exception code: '| | SQLCODE | |' abnormal information: '| | SQLERRM);

The END;


- 2. Essentially, write a stored procedure (for the exception message)


Create procedure (XXX)
As
The begin

.
The BEGIN CATCH
SELECT @ Msg=error message: '+ ERROR_MESSAGE ();
END the CATCH
END

- get complete information example:

BEGIN the TRY
- Generate a divide - by - zero error.
The SELECT 1/0;
END the TRY
The BEGIN CATCH
SELECT
ERROR_NUMBER () AS ErrorNumber
ERROR_SEVERITY () AS ErrorSeverity
ERROR_STATE () AS ErrorState
AS ErrorProcedure, ERROR_PROCEDURE ()
ERROR_LINE () AS ErrorLine
ERROR_MESSAGE () AS ErrorMessage;
END the CATCH;




-- -- -- -- -- -- -- -- -- -- -- --

But in mysql, as if no such method direct access to system's own code and the anomaly information;
If not,


- in the case of mysql for abnormal

Create procedure XXX (p_json varchar2 (2000), the out p_msg varchar (200))
The begin

.
DECLARE Invalid_JSON CONDITION FOR 3141; - 314 is the json format is wrong;

DECLARE the EXIT HANDLER FOR Invalid_JSON
The BEGIN
Set @ errorMsg='parse json error - only handwritten error message and can not use like oracle MESSAGE_TEXT or essentially ERROR_MESSAGE ();

The END;
The SET @ CNT=JSON_LENGTH (p_Json); - get a json array number, if the json format is wrong, will trigger 3141 this exception;
.
The END;

CodePudding user response:

Condition_value:
SQLSTATE VALUE] [sqlstate_value
| condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

CodePudding user response:

Thanks brother's help, upstairs also have I seen such a specification, but seems to be short of oracle or used to that kind of effect;

CodePudding user response:

refer to the second floor daizhicun response:

thank you for your brother's help, upstairs also have I seen such a specification, but seems to be short of oracle or used to that kind of effect;

With the exit handler

 MariaDB/demo & gt; Delimiter $$
MariaDB/demo & gt; The create or replace procedure print_exception_msg () 
-> The begin 
-> Declare the exit handler for sqlexception 
-> The begin 
-> Declare code varchar (5) the default '00000'. 
-> Declare MSG text; 
-> Get diagnostics and condition 1 code=returned_sqlstate, MSG=message_text; 
-> Select concat ('/', code, ' ', MSG); 
-> end; 
-> Select * from a; 
-> Select 'should not be executed'; 
-> end; 
-> $$
Query OK, 0 rows affected (0.04 SEC) 

MariaDB/demo & gt; 
MariaDB/demo & gt; Delimiter. 
MariaDB/demo & gt; Call print_exception_msg (); 
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 
| concat ('/', code, ' ', MSG) | 
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 
| s02 [42] Table demo. A ' 'doesn' t exist | 
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 
1 row in the set (0.00 SEC) 

Query OK, 0 rows affected (0.07 SEC) 

CodePudding user response:

reference gikod reply: 3/f

Quote: refer to the second floor daizhicun response:

Thanks brother's help, upstairs also have I seen such a specification, but seems to be short of oracle or used to that kind of effect;

With the exit handler

 
MariaDB/demo & gt; Delimiter $$
MariaDB/demo & gt; The create or replace procedure print_exception_msg () 
-> The begin 
-> Declare the exit handler for sqlexception 
-> The begin 
-> Declare code varchar (5) the default '00000'. 
-> Declare MSG text; 
-> Get diagnostics and condition 1 code=returned_sqlstate, MSG=message_text; 
-> Select concat ('/', code, ' ', MSG) sqlerrm; 
-> end; 
-> Select * from a; 
-> Select 'should not be executed'; 
-> end; 
-> $$
Query OK, 0 rows affected (0.04 SEC) 

MariaDB/demo & gt; 
MariaDB/demo & gt; Delimiter. 
MariaDB/demo & gt; Call print_exception_msg (); 
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 
| sqlerrm | 
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 
| s02 [42] Table demo. A ' 'doesn' t exist | 
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 
1 row in the set (0.00 SEC) 

Query OK, 0 rows affected (0.03 SEC) 

If don't have enough information, also can get some information:
GET DIAGNOSTICS and Syntax

GET DIAGNOSTICS [CURRENT]
{
Statement_information_item
[statement_information_item]...
| CONDITION condition_number
Condition_information_item
[condition_information_item]...
}

Statement_information_item:
Target=statement_information_item_name

Condition_information_item:
Target=condition_information_item_name

Statement_information_item_name:
NUMBER
| ROW_COUNT

Condition_information_item_name:
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| RETURNED_SQLSTATE
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull