MySql Cursor - creating a procedure

Question

I am trying to create a cursor for the first time. I looked at the documentation, I understand the concept, but I can’t even declare it ...

I use:

• MySql 5.1.41
• SqlYog as a manager
• (runs locally when xampp is installed)

Even when copying inserts the example found at http://dev.mysql.com/doc/refman/5.1/en/cursors.html

CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; OPEN cur2; read_loop: LOOP FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF done THEN LEAVE read_loop; END IF; IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END LOOP; CLOSE cur1; CLOSE cur2; END; 

I get errors right away: Error code: 1064

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the right syntax to use next to '' on line 3

and a number of others,

It makes no sense to me, can any soul help me?

thanks

So, I got a sample request to work (thanks ajreal), with a DELIMITER reset. But when I run my query:

 DELIMITER## CREATE PROCEDURE RetiraPoints() BEGIN DECLARE userid BIGINT; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT uid FROM viewpoints; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO userid; IF done THEN LEAVE read_loop; END IF; INSERT INTO points (iduser, points, pointcat) VALUES (uid, -1, 1), (userid, -1, 2), (userid, -1, 3), (userid, -1, 4), (userid, -1, 5), (userid, -1, 6); END LOOP; CLOSE cur; END;## 

I get: Error code: 1064

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to "DECLARE" INT DEFAULT 0; PLEASE CURSOR FOR SELECT uid FROM viewpoints; "on line 1

my god it's hard ...

Answer 1

You will forget to reset the delimiter to NOT ;

 delimiter ## ... end## 

you need to put a space immediately after the separator

And end END does not require ;

Answer 2

I needed to do the same, so I ended up writing a stored procedure to do the job. I have included it here and it works great on MySQL Workbench. Interestingly, it will not work correctly in Navicat, since the original select statement will not skip NULL values ​​and, therefore, will delete all your indexes.

I recommend that you read the code and break a few things and run them separately so that you are sure that it will do what you want.

As it is written, it should delete every foreign key in all your databases in this connection. Do not run it as it is, if that is not what you want to do.

Use at your own risk.

 DELIMITER $$ CREATE PROCEDURE `pRemoveAllForeignKeys`() BEGIN DECLARE sName TEXT; DECLARE cName TEXT; DECLARE tName TEXT; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_SCHEMA, CONSTRAINT_NAME, TABLE_NAME FROM information_schema.key_column_usage WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL -- AND TABLE_SCHEMA = 'NameOfAParticularSchema' -- use this line to limit the results to one schema -- LIMIT 1 -- use this the first time because it might make you nervous to run it all at once. ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO sName, cName, tName; IF done THEN LEAVE read_loop; END IF; SET @s = CONCAT('ALTER TABLE ',sName, '.', tName, ' DROP FOREIGN KEY ', cName); -- SELECT @s; -- uncomment this if you want to see the command being sent PREPARE stmt FROM @s; EXECUTE stmt; END LOOP; CLOSE cur; deallocate prepare stmt; END