MySQL stored procedures

MySQL Stored procedure features

1, use temporary table, dynamic cursor can only use temporary table

2, the temporary table has more than one field, the cursor has multiple parameters to accept

3. Cyclic reading and writing data

4, if there is a record to update the record, if there is no record, insert the record

DELIMITER $$ Use' test_cases ' $$DROP PROCEDURE IF EXISTS' Proc_write_report ' $$CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Proc_write_report ' (inchP_table_nameVARCHAR( the),inchP_class_nameVARCHAR( the),inchP_method_nameVARCHAR( the),inchP_execution_flagVARCHAR( the))BEGIN        DECLAREVar_class_nameVARCHAR(280)DEFAULT NULL;--Test class name    DECLAREVar_method_nameVARCHAR( the)DEFAULT NULL;--test Method Name    DECLAREVar_moduleVARCHAR( the)DEFAULT NULL;--Test Module Chinese    DECLAREVar_case_nameVARCHAR( the)DEFAULT NULL;--test Case name Chinese    DECLAREVar_pass_amountINT DEFAULT 0;--by the number of use cases    DECLAREVar_execution_amountINT DEFAULT 0;--by the number of use cases    DECLAREVar_fail_amountINT DEFAULT 0;--number of failed cases    DECLAREVar_fail_reasonVARCHAR( -)DEFAULT NULL;--Reason for failure    DECLAREVar_fail_typeVARCHAR( -)DEFAULT NULL;--Failure Type    DECLAREVar_commentVARCHAR( -)DEFAULT NULL;--Notes    DECLAREVar_sql_stringVARCHAR( -)DEFAULT NULL;--SQL statements    DECLAREVar_sql_temp_tableVARCHAR( -)DEFAULT NULL;--SQL statements    --Traverse data End Flag    DECLAREDoneINT DEFAULTFALSE; DECLARECurCURSOR  for SELECT *  fromTmp_table_result; --binding the end flag to a cursor    DECLARE CONTINUEHANDLER for  notFOUNDSETDone=TRUE; --Cursors    --DECLARE cur CURSOR for SELECT fail_type from execution_order_price WHERE execution_flag = ' 201606091918 ' and TEST_CL The ' com.fc.htgl.testcases.TestOrderPrice ' and Test_method = ' Testydorderprice ' GROUP by Fail_type;        SET @var_cass_name =P_class_name; SET @var_method_name =P_method_name; --Delete temporary table    DROP Temporary TABLE IF EXISTSTmp_table_result; --SQL statements used to preprocess temporary tables    SET @var_sql_temp_table =CONCAT ('CREATE temporary TABLE tmp_table_result',"SelectTest_class,test_method,test_module_name,test_case_name,COUNT(*), Fail_type,comment from", P_table_name,"whereExecution_flag= '", P_execution_flag,"'  andTest_class= '", P_class_name,"'  andTest_method= '", P_method_name,"' Group  byFail_type "); --SET @var_sql_temp_table = CONCAT (' CREATE temporary table Tmp_table_result ', "SELECT count (*), fail_type from", P_tabl E_name, "where Execution_flag = '", P_execution_flag, "' and Test_class = '", P_class_name, "' and Test_method = '", P_method_ Name, "' Group by Fail_type");        --Select @var_sql_temp_table;         --preprocessing the dynamic SQL to be executed    PREPAREstmt from @var_sql_temp_table; --Execute SQL statement    EXECUTEstmt; --Release the preprocessing segment    deallocate PREPAREstmt; --select * from Tmp_table_result;--Query under temporary table        --Open Cursor    OPENcur; --Start LoopRead_loop:loop--extracting data from cursors;        --FETCH cur into var_execution_amount,var_fail_type;        FETCHCur intoVar_class_name, var_method_name,var_module,var_case_name,var_execution_amount,var_fail_type,var_comment; --at the end of the statement        IFDone ThenLEAVE Read_loop; END IF; --Loop Update Insert        --Select Var_class_name, Var_method_name,var_module,var_case_name,var_execution_amount,var_fail_type,var_ Comment;                --get the reason for failure                    SET @var_sql_string =CONCAT ("Select distinctActual_result from", P_table_name,"whereTest_method= '", P_method_name,"'  andExecution_flag= '", P_execution_flag,"'  andTest_class= '", P_class_name,"'  andIs_select= 1  andIs_execution= 1  andFail_type= '", Var_fail_type,"'"); --SELECT @var_sql_string;Call proc_get_table_column_content (@var_sql_string,@var_fail_reason); --write results to the report table                IF(SELECT COUNT(*) fromReportWHEREExecution_flag=P_execution_flag andClass_name=P_class_name andMethod_name=P_method_name andFail_type=Var_fail_type) Then            --Select ' Condition exists, update ';            UPDATEReportSETClass_name=P_class_name, Method_name=P_method_name, module=Var_module, Case_name=Var_case_name, Execution_amount=Var_execution_amount, Fail_reason= @var_fail_reason, ' COMMENT '=Var_comment, ' time '=Now ()WHEREExecution_flag=P_execution_flag andClass_name=P_class_name andMethod_name=P_method_name andFail_type=Var_fail_type; ELSE             --SELECT ' condition does not exist, insert ';            INSERT   intoReport (Execution_flag,class_name,method_name,module,case_name,execution_amount,fail_reason,fail_type, ' time ', ' comment ')VALUES(P_execution_flag,p_class_name,p_method_name,var_module,var_case_name,var_execution_amount,@var_fail_reason, Var_fail_type,now (), var_comment); END IF; ENDLOOP; --Close Cursors    CLOSEcur;END$ $DELIMITER;

MySQL stored procedures