Home >Database >Mysql Tutorial >How to debug stored procedures in mysql

How to debug stored procedures in mysql

coldplay.xixi
coldplay.xixiOriginal
2020-08-25 10:15:3611975browse

mysql调试存储过程的方法:1、利用“CREATE TEMPORARY TABLE”语句创建一张临时表,用于记录调试过程;2、在存储过程中,增加“select @xxx”语句;3、打开控制台,在控制台中查看结果,根据输出结果修改代码即可。

How to debug stored procedures in mysql

本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。

mysql调试存储过程的方法:

在navicat中调用存储过程  

1. 写语句调用

call p_next_id('t_factory',2,'0',@result); -- 上面的存储过程含有四个参数,所以这里调用的时候,也需要传递4个参数:输入参数填写值,输出参数用变量表示@result
select @result; -- 这句话是在控制台显示变量值

2. 窗口点击

直接点击运行时,在弹出输入框输入:

't_factory',2,'0',@result

追踪(调试)存储过程执行步骤

     mysql不像oracle有plsqldevelper工具用来调试存储过程,所以有两简单的方式追踪执行过程:

  • 利用“CREATE TEMPORARY TABLE”语句创建一张临时表,用于记录调试过程

  • 直接在存储过程中,增加select @xxx

  • 在控制台查看结果,根据输出结果修改代码:

例如我把上面的存储过程中加一些查询语句(注意下面的红色语句)

CREATE PROCEDURE `p_next_id`(kind_name VARCHAR(30), i_length int,currentSeqNo VARCHAR(3),OUT o_result INT)
BEGIN 
     SET @a= NULL;
     SET @b= NULL;
     SELECT id INTO @a FROM t_seq WHERE number= currentSeqNo and length= i_length ;
   SELECT @a;     
     IF (@a is null ) THEN
            select min(id) into @a FROM t_seq where length = i_length;
            select number  INTO @b FROM t_seq WHERE id = @a;
       select @b;
     ELSE
        select number  INTO @b FROM t_seq WHERE id = @a+1;        
     END IF;        
     SELECT @b INTO o_result;     
END

【相关学习推荐:mysql学习

The above is the detailed content of How to debug stored procedures in mysql. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn