--1.在oracle里，寫一個存盤程序(獲取例外訊息)
create procedure xxx ()
as
begin
．．．
exception
when others then
dbms_output('例外代碼:'||SQLCODE ||' 例外資訊:'||SQLERRM);
END;
--2.在sqlserver 里，寫一個存盤程序(獲取例外訊息)
create procedure xxx ()
as
begin
.....
BEGIN CATCH
SELECT @Msg=錯誤訊息：' + ERROR_MESSAGE();
END CATCH
END
--得到完成資訊的例子：
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
------------
但是在mysql中，好像沒有類似的方法直接獲取 系統自帶的 例外代碼 和 例外資訊；
好像沒有；
--在mysql獲取例外的例子
create procedure xxx (p_json varchar2(2000) , out p_msg varchar(200))
begin
......
DECLARE Invalid_JSON CONDITION FOR 3141 ; --314是json格式不對；
DECLARE EXIT HANDLER FOR Invalid_JSON
BEGIN
set @errorMsg ='決議json錯誤' -- 只能自己手寫錯誤資訊，不能用類似oracle的 MESSAGE_TEXT 或 sqlserver 的 ERROR_MESSAGE()；
END;
SET @cnt= JSON_LENGTH(p_Json); --獲取json陣列的個數，如果json格式不對，就會觸發 3141這個例外；
...
END;
uj5u.com熱心網友回復：
condition_value:SQLSTATE [VALUE] sqlstate_value
| condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code
uj5u.com熱心網友回復：
多謝樓上兄弟的幫助，我也看過這樣的說明。但是好像達不到oracle或sqlserver那種效果；uj5u.com熱心網友回復：
用exit handler
MariaDB [demo]> delimiter $$
MariaDB [demo]> create or replace procedure print_exception_msg()
-> begin
-> declare exit handler for sqlexception
-> begin
-> declare code varchar(5) default '00000';
-> declare msg text;
-> get diagnostics 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]>
MariaDB [demo]> delimiter ;
MariaDB [demo]> call print_exception_msg();
+--------------------------------------+
| concat('[', code, '] ', msg) |
+--------------------------------------+
| [42S02] Table 'demo.a' doesn't exist |
+--------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.07 sec)
uj5u.com熱心網友回復：
如果覺得資訊不夠，還有些資訊可以取：
GET DIAGNOSTICS Syntax
GET [CURRENT] DIAGNOSTICS
{
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
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
condition_number, target:
(see following discussion)
uj5u.com熱心網友回復：
在mysql中可以用HANDLER來定義例外及例外處理的方法定義例外是事先定義程式執行程序中遇到的問題，例外處理定義了在遇到問題時對應采取的處理方式，并且保證存盤程序或者函式在遇到錯誤時或者警告時能夠繼續執行或者終止。
語法：
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type: CONTINUE | EXIT
condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code
參考自mysql例外捕獲 http://www.data.5helpyou.com/article219.html
uj5u.com熱心網友回復：
基本搞定了：
http://www.itpub.net/thread-2058855-1-1.html
uj5u.com熱心網友回復：
GET DIAGNOSTICS CONDITION 報錯，是不是對mysql版本有要求uj5u.com熱心網友回復：
為什么 我拿不到 RETURNED_SQLSTATE和 MESSAGE_TEXT值，都是空的轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/35012.html
標籤：MySQL
上一篇：為什么mysql資料庫安不上
下一篇：處理儲存在hive中的資料