基于Fragment实现Tab的切换，滑出侧边栏

群里有人询问，在mysql的proc中如何获取错误信息、错误编号呢?我们知道在oracle、mssql中比较简单；

oracle中sqlcode,sqlerrm ;mssql中ERROR_PROCEDURE(), ERROR_MESSAGE();

那么在mysql中呢？

下面给出示例：

mysql> show create procedure proc_6\G

*************************** 1. row ***************************

Procedure: proc_6

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`192.168.6.%` PROCEDURE `proc_6`()

LANGUAGE SQL

begin

DECLARE code CHAR(5) DEFAULT '00000';

DECLARE msg TEXT;

declare pout double;

DECLARE continue HANDLER FOR SQLEXCEPTION

BEGIN

GET DIAGNOSTICS CONDITION 1 --通过GET DIAGNOSTICS这样的方式获取sqlstate message_text(当然错误信息错误编号可以自己定义)

--参考我的另外一个示例

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

drop FUNCTION if exists test.fn_get_random_str;

create function fn_get_random_str(f_len int)

returns varchar(4000)

no sql

begin

declare v_str varchar(52);

declare v_cnt int;

declare f_result varchar(4000);

/*自定义异常编码40001:value_to_large*/

declare value_to_large condition for sqlstate '40001';

if f_len>4000 then

begin

signal sqlstate '40001'

set message_text='value_to_large';/*输出错误信息40001：value_to_large*/

end ;

end if ;

set v_str='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

set v_cnt=1;

set f_result='';

while v_cnt<=f_len do

set f_result=concat(substr(v_str,round(rand()*52,0),1),f_result);

set v_cnt=v_cnt+1;

end while ;

return f_result ;

end ;

mysql> select fn_get_random_str(5000);

ERROR 1644 (40001): value_to_large

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

code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

END;

/*set pout= 1/0;*/

select 1/0 into pout from test.ttt ; --模拟了一个不存在的表错误

select concat(code,msg) ;

end

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.28 sec)

mysql> call test.proc_6();

+-------------------------------------+

| concat(code,msg) |

+-------------------------------------+

| 42S02Table 'test.ttt' doesn't exist | --看到了吧错误信息打印出来了

+-------------------------------------+

1 row in set (0.99 sec)

Query OK, 0 rows affected (0.99 sec)

咋样满足需求了吧！

巴特西

基于Fragment实现Tab的切换，滑出侧边栏

最新文章

热门文章