Using cursors and temporary tables in MySQL stored procedures

Use the cursor and temporary table in the MySQL stored procedure:

1. BEGIN
2. DECLAREC_egidINTEGER;
3. DECLAREDoneINT DEFAULT0;
4. DECLAREMy_testCursorCURSOR FOR
5. (SELECTCp. tb_electricGroup_idFROMTb_chargingPoleASCpWHERECp. tb_chargeStation_id = 12 );
6. DECLARE CONTINUEHANDLERFOR NOTFOUNDSETDone = 1;
7. DROP TABLEIf exists tmp_table;
8. CREATE TEMPORARY TABLETmp_table (to create a temporary table, write it inDECLARENo syntax errors later
9. Tmp_egidINTEGERSome table data formats have changed, such as datetimeTimestamp,BitIs 0, 1, and so on.
10. );
11. OPENMy_testCursor;
12. Emp_loop: LOOP
13. FETCHMy_testCursorINTOC_egid; the number of parameters required for each column of the cursor
14. IF done = 1THENThe position of this sentence is very important. If it is not placed at the beginning, it may produce a Data repeat.
15. LEAVE emp_loop;
16. ENDIF;
17. INSERT INTOTmp_table (tmp_egid)VALUES(C_egid); insert data into a temporary table
18. ENDLOOP emp_loop;
19. CLOSEMy_testCursor;
20. Select*FromTmp_table;
21. TRUNCATETmp_table;
22. END

It is worth noting that declare continue handler for not found set done = 1

In a cursor loop, we use done 0 to determine whether to exit the loop. There is no problem in normal insert statements.

However, Handler is triggered when the select statement is used to find null in the cursor, And the done changes from 0 to 1.

Therefore, if the cursor contains a select statement, you can use other methods, such as the while loop. The following is an example of exiting the while loop by determining the length of the result set.

1. SELECT COUNT(*)INTO@ PageCountFROMTmp_bwGetChargePoleCommunicationtable;
2. SET@ Num = 0;
3. OPENMy_testCursor;
4. WHILE @ num <@ pageCount DO
5. SET@ Num = @ num + 1;
6. FETCHMy_testCursorINTOParam_id, param_ip, param_port, param_key;
8. IF param_keyIS NULL THEN
9. SELECTKiosk_keyINTO@ TmpParam_keyFROMTb_kioskWHEREKiosk_id = (SELECTTb_kiosk_idFROMTb_chargingPoleWHERECp_id = param_id );
10. UPDATETmp_bwGetChargePoleCommunicationtableSETTmp_key = @ tmpParam_keyWHERETmp_id = param_id;
11. ENDIF;
12. ENDWHILE;
13. CLOSEMy_testCursor;