mysql - SQL 代码不工作，未知错误

Question

我写了一段代码:

CREATE PROCEDURE test()
BEGIN
  DECLARE 
    ok INT default FALSE;
    curs_r1 CURSOR FOR SELECT * FROM t WHERE (b > 1 and b < 3) and (c < 2);
    curs_r2 CURSOR FOR SELECT * FROM t WHERE (a = 1) and (b > 2);
    CONTINUE HANDLER FOR NOT FOUND SET ok = TRUE;


  SET ok = False; 
  DROP TABLE IF EXISTS t;
  CREATE TABLE IF NOT EXISTS t  (
    id int,
    a int,
    b int,
    c int
  );
  DROP TABLE IF EXISTS res;
  CREATE TABLE IF NOT EXISTS res  (
    id int not null unique,
    score float
  );

  insert into t values (0,1,2,3), (1,1,3,2), (2,3,2,1);

  --------------------------------------------
  OPEN curs_r1;
  SET score_r1 = 0.5;
  REPEAT
    FETCH curs_r1 INTO 
    id, a, b, c;
    INSERT IGNORE INTO res VALUES (id, score_r1);
  UNTIL ok END REPEAT;
  CLOSE curs_r1;

  --------------------------------------------
  SET ok = FALSE;

  OPEN curs_r2;
  SET score_r2 = 0.25;
  REPEAT
    FETCH curs_r2 INTO 
    id, a, b, c;
    INSERT IGNORE INTO res VALUES (id, score_r2);
  UNTIL ok END REPEAT;
  CLOSE curs_r2;

  

  SELECT * FROM res;
END

但这会产生太多错误，例如:

Error: near line 1: near "PROCEDURE": syntax Error

Error: near line 5: near "curs_r1": syntax Error

Error: near line 6: near "curs_r2": syntax Error

Error: near line 7: near "CONTINUE": syntax Error

Error: near line 10: near "SET": syntax Error

Error: near line 27: near "OPEN": syntax Error

Error: near line 28: near "SET": syntax Error

Error: near line 29: near "REPEAT": syntax Error

Error: near line 32: near "IGNORE": syntax Error

Error: near line 33: near "UNTIL": syntax Error

Error: near line 34: near "CLOSE": syntax Error

Error: near line 37: near "SET": syntax Error

Error: near line 39: near "OPEN": syntax Error

Error: near line 40: near "SET": syntax Error

Error: near line 41: near "REPEAT": syntax Error

Error: near line 44: near "IGNORE": syntax Error

Error: near line 45: near "UNTIL": syntax Error

Error: near line 46: near "CLOSE": syntax Error

Error: incomplete SQL: END

有人知道根本原因吗？

非常感谢您。

---

上面的错误已修复(感谢 Darwin 提供的第一条线索)，这是新的代码段:

DELIMITER $$
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test()
BEGIN
  DECLARE ok INT default FALSE;
  DECLARE score_r1 FLOAT default 0.5;
  DECLARE score_r2 FLOAT default 0.25;
  DECLARE id, a, b, c INT;
  DECLARE curs_r1 CURSOR FOR SELECT * FROM t WHERE (b > 1 and b < 3) and (c < 2);
  DECLARE curs_r2 CURSOR FOR SELECT * FROM t WHERE (a = 1) and (b > 2);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET ok = TRUE;

  SET ok = False; 
  DROP TABLE IF EXISTS t;
  CREATE TABLE IF NOT EXISTS t  (
    id int,
    a int,
    b int,
    c int
  );
  DROP TABLE IF EXISTS res;
  CREATE TABLE IF NOT EXISTS res  (
    id int not null unique,
    score float
  );

  insert into t values (0,1,2,3), (1,1,3,2), (2,3,2,1);

  OPEN curs_r1;
  OPEN curs_r2;
  
  REPEAT
    FETCH curs_r1 INTO id, a, b, c;
    INSERT IGNORE INTO res VALUES (id, score_r1);
  UNTIL ok END REPEAT;
  CLOSE curs_r1;

  SET ok = FALSE;

  
  REPEAT
    FETCH curs_r2 INTO id, a, b, c;
    INSERT IGNORE INTO res VALUES (id, score_r2);
  UNTIL ok END REPEAT;
  CLOSE curs_r2;

  SELECT * FROM res;
END;
$$
DELIMITER ;

在输出中，我希望看到 id = 2 和 id = 1 的两行但我只有一行 id=0 和 score=0.5

我在这里错过了什么？非常感谢

Answer 1

所以基本上您会在每个语句中收到一条错误消息。为了从 mysql 客户端创建过程，您必须将整个 CREATE 语句包含在 DELIMITER 指令中，如下所示:

DELIMITER $$
CREATE PROCEDURE test()
  BEGIN
  ... --code body goes here
  END;
$$
DELIMITER ;

您的代码中确实有许多其他错误，但这将解决第一个错误。

有关 DELIMITER 的更多信息，请搜索 Stack Overflow 文件。