MySQL Stored procedure debugging method

• Like the next stored procedure

CREATE PROCEDURE' P_next_id ' (kind_nameVARCHAR( -), I_lengthint, currentseqnoVARCHAR(3), Out O_resultINT)BEGIN      SET @a= NULL; SET @b= NULL; SELECTId into @a  fromT_seqWHERE  Number=Currentseqno andLength=I_length;
IF(@a  is NULL) Then            Select min(ID) into @a  fromT_seqwhereLength=i_length; Select  Number   into @b  fromT_seqWHEREId= @a;ELSE        Select  Number   into @b  fromT_seqWHEREId= @a+1; END IF; SELECT @b  intoO_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, the output parameter is represented by a variable @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_nameVARCHAR( -), I_lengthint, currentseqnoVARCHAR(3), Out O_resultINT)BEGIN      SET @a= NULL; SET @b= NULL; SELECTId into @a  fromT_seqWHERE  Number=Currentseqno andLength=i_length;
　　SELECT @a;          IF(@a  is NULL) Then            Select min(ID) into @a  fromT_seqwhereLength=i_length; Select  Number   into @b  fromT_seqWHEREId= @a;
　　　　　Select @b; ELSE        Select  Number   into @b  fromT_seqWHEREId= @a+1; END IF; SELECT @b  intoO_result; END

MySQL Stored procedure debugging method