"MySQL" stored procedures, cursors, circular simple instances

Sometimes the SQL statement may not be able to achieve the desired data operation purposes, it may be necessary to write some method body, through the cycle of judgment and other operations to achieve the end. Then the implementation of this method in the database needs a stored procedure, the individual think a simple example with annotations can be easily rough to solve most of the problems, of course, to learn more about the words or to see the tutorial documentation, not much said, on the code:

[SQL]View PlainCopy

2. CREATE PROCEDURE My_procedure () --Creating a stored procedure
4. Begin --Start the stored procedure
6. DECLARE my_id varchar (32); --Custom variable 1
8. DECLARE my_name varchar (50); --Custom variable 2
10. DECLARE done INT DEFAULT FALSE; --Custom control cursor loop variable, default false
14. DECLARE my_cursor Cursor for ( SELECT ID, name from t_people); --Define a cursor and enter a result set
16. DECLARE CONTINUE HANDLER for Don't FOUND SET done = TRUE; --Bind the control variable to the cursor, and the cursor loop end automatically goes true
20. OPEN My_cursor; --Open cursor
22. Myloop:loop --Starts the loop body, myloop the custom loop name, and ends the loop with
24. FETCH my_cursor into my_id, my_name; --assigns the data order of the cursor currently reading rows to the custom variable
26. If do then --determine if the loop continues
28. LEAVE Myloop; --End Loop
30. END IF;
32. --The things you want to do, use custom variables directly in SQL
34. UPDATE t_user SET c_name = my_name WHERE id = my_id and RTrim (LTrim (c_name)) = '; --left and right to go space
38. COMMIT; --Commit a transaction
40. END LOOP Myloop; --End the custom loop body
42. CLOSE My_cursor; --Close cursor
44. END; --End stored procedure

SQL Call Stored Procedure

[SQL]View PlainCopy

1. --SQL execution stored procedure
2. Call My_procedure ();

SQL Delete stored procedure

[SQL]View PlainCopy

1. --SQL Delete stored procedure
2. Drop procedure my_procedure;

End.

"MySQL" stored procedures, cursors, circular simple instances