The simplest way to debug a stored procedure in MySQL

My former colleague told me to use temporary tables to insert variable data to view, but this method is too cumbersome, and MySQL does not have a better tool to debug stored procedures. Today, Google found that you can use the Select + Variable name method to debug ... I'm so embarrassed.

Specific methods:

Add the following statement to your stored procedure:
SELECT variable 1, variable 2;

Then use MySQL's own cmd program to enter the mysql>.
Call your stored procedure name (input parameter 1,@ output parameter);(Note: Here to help the new classmate, if your stored procedure has output variables, then only need to add @ and then with any variable name can be);
You can find the value of your variable is printed to the cmd, simple? Oh, I hope I can help you.

=============================

• Like the next stored procedure

CREATE PROCEDURE ' p_next_id ' (kind_name varchar (), 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) to @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 a stored procedure in Navicat　　

1. Write statement invocation

Call p_next_id (' T_factory ', 2, ' 0 ', @result); --The above stored procedure contains four parameters, so when called here, it is also necessary to pass 4 parameters: input parameters to fill in the value, output parameters to use variables to represent @resultselect @result; -This is the value of the variable displayed on the console

2. Window click

Directly click Run, enter in the popup input box: ' T_factory ', 2, ' 0 ', @result

• Track stored procedure execution steps

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

1. Record the debugging process with a temporary table
2. Directly in the stored procedure, add the Select @xxx and view the results in the console:

For example, I add some query statements to the stored procedure above (note the red statement below)

CREATE PROCEDURE ' p_next_id ' (kind_name varchar (), 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) to @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

The simplest way to debug a stored procedure in MySQL