mysql存储过程获取sqlstate message_text

群里有人询问,在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)
咋样满足需求了吧!
posted on 2014-02-25 11:08  duanxz  阅读(5588)  评论(0编辑  收藏  举报