Cursor traversal data table for MySQL stored procedure

Cursor traversal data table for MySQL stored procedure

Today write a MySQL stored procedures, according to their own needs to traverse a data table, because the stored procedures used not much, syntax is not very familiar with the stored procedures without debugging environment, took a lot of time to slowly fix, so leave a trace.

 1 BEGIN 2 DECLARE done INT DEFAULT 0; 3 4 DECLARE Currentlingqi INT; 5 6 DECLARE Shizuname VARCHAR (30); 7/* Declaration CURSOR */8 DECLARE RS cursor for SELECT NodeName, Lingqi from Socialrelation; 9/* Exception handling */10 DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 ' SET done = 1;11 12/* Open cursor */13 opening  Rs     14 15//* Remove the value of the current record Lingqi field one by one, you need to make the maximum judgment */16 FETCH NEXT from RS to Shizuname, Currentlingqi;               17/* Traverse data table */18 REPEAT19 IF not do THEN20 SET Currentlingqi = Currentlingqi + 60;21 /* If the Reiki value is greater than the maximum allowable value after update, set to maximum */22 if Currentlingqi >= 1800 THEN23 update s                  Ocialrelation SET lingqi = 1800 WHERE NodeName = shizuname;24 ELSE25/* Otherwise, normal update */26 UPDATE socialrelation SET Lingqi = Lingqi + WHERE NodeName = shizuname;27 END if;28 EN D if;29 FETCH NEXT from RS toShizuname, currentlingqi;31 UNTIL done END repeat;33 34/* Close cursor */35 close rs;36 END 

　　

Today write a MySQL stored procedures, according to their own needs to traverse a data table, because the stored procedures used not much, syntax is not very familiar with the stored procedures without debugging environment, took a lot of time to slowly fix, so leave a trace.

Cursor traversal data table for MySQL stored procedure