The easiest way to debug stored procedures in Mysql

A colleague once told me to use a temporary table to insert variable data for viewing, but this method is too troublesome, and Mysql does not have a better tool for debugging stored procedures. I googled today and found that I can use the select + variable name method to debug

Specific method:

Add the following statement to your stored procedure:
SELECT variable1,variable2;

Then use the cmd program that comes with mysql to enter mysql>.
call your stored procedure name (input parameter 1, @output parameter); (Note: Here is to help new students, if your stored procedure has output variables, then you only need to add @ and then any variable name);
You will find that your variable value has been printed under cmd. Simple, right? Haha, I hope this can help you.

There is a stored procedure as follows

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 ; 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;ELSE
        select number INTO @b FROM t_seq WHERE id = @a+1;        
     END IF;        
     SELECT @b INTO o_result;     
END

Calling stored procedures in navicat

Write statement call
call p_next_id('t_factory',2,'0',@result); -- The above stored procedure contains four parameters, so when calling it here, you also need to pass 4 parameters: fill in the value of the input parameter, and use the variable to represent the output parameter @result
select @result; -- This sentence displays the variable value in the console
2. Window click

When you click Run directly, enter in the pop-up input box: 't_factory', 2, '0', @result

Tracing stored procedure execution steps

MySQL does not have the plsqldevelper tool like Oracle to debug stored procedures, so there are two simple ways to track the execution process:

Use a temporary table to record the debugging process. Add select @xxx directly in the stored procedure and view the results in the console:
For example, I add some query statements to the above stored procedure (note the red statements below)

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

This is the end of this article about the simplest way to debug stored procedures in MySQL. For more information about debugging stored procedures in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!