MySql Cursor - Creating a procedure

问题:

i'm trying to create a cursor for the first time. I have looked at the documentation, i understand the concept, but i can't seem to get it to even be declared...

I'm using:

• MySql 5.1.41
• SqlYog as a manager
• (running locally on a xampp instalation)

Even when copy pasting the example found in 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 your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

and a bunch of others following,

this doesn't make any sense to me, can any kind soul help me please?

Thank you

So i got the sample query to work (thanks to ajreal), with resetting the DELIMITER. 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 your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT uid FROM viewpoints; ' at line 1

my god, this is hard...

回答1:

You forget to reset the delimiter to NOT ;

delimiter ##
...
end##

need to put a space right after delimiter

And the ending END does not require ;

回答2:

I needed to do the same thing, so I ended-up writing a stored procedure to do the job. I've included it here and it runs great on MySQL Workbench. Interestingly it will not run correctly on Navicat as the original select statement won't omit the NULL values and would, therefore, delete all of your indexes.

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

The way this is written, it should delete every foreign key in all of your databases in a given connection. Don't run it the way it is unless that's 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