MySQL 存储过程的游标给出错误

Question

我试图根据当前使用的单位通过从先前的读数中减去当前的读数来计算金额，但我似乎有一些语法错误和逻辑问题。请帮忙。

(我知道它可以在插入语句本身中完成，但我需要了解游标如何工作)

ERROR 1064 (42000) at line 22 in file: 'DEF.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOOP:

FETCH curBill INTO cno, pr, cr;
        IF !flag THEN
            SET unt = cr-pr;
        EL' at line 13

MariaDB v10.4.8

CREATE DATABASE Random;
USE Random;

CREATE TABLE Customer(
    `c_no`              INT,
    `name`              VARCHAR(40),
    `previous_reading`  INT,
    `current_reading`   INT,
    `amount`            INT DEFAULT 0
);

INSERT INTO Customer (`c_no`, `name`, `previous_reading`,`current_reading`)
VALUES
    (101, 'Smith', 90, 120),
    (201, 'George', 30, 250),
    (301, 'Philip', 120, 200),
    (401, 'Jasper', 10,390);

SELECT * FROM Customer;

DELIMITER $$ ;
CREATE PROCEDURE e_bills()
BEGIN
    DECLARE flag INT DEFAULT 0;
    DECLARE cno INT;
    DECLARE pr, cr, amt, unt DOUBLE;
    DECLARE curBill
        CURSOR FOR
            SELECT `c_id`, `previous_reading`, `current_reading`
            FROM Customer;
    DECLARE CONTINUE HANDLER FOR NOT found SET flag = 1;

    OPEN curBill;
    calUnit LOOP:
        FETCH curBill INTO cno, pr, cr;
        IF !flag THEN
            SET unt = cr-pr;
        ELSE IF flag THEN
            LEAVE calUnit;
        END IF;
        IF (unt <= 100) THEN
            SET amt = unt*2;
        ELSE IF (unt >= 101 AND unt <= 200) THEN
            SET amt = unt*2.5;
        ELSE IF (unt > 201 AND unt <= 300) THEN
            SET amt = unt*3;
        ELSE IF (unt > 301) THEN
            SET amt = unt*4;
        END IF;
        UPDATE Customer
        SET `amount`=amt;
    END LOOP;
    CLOSE curBill;
END $$
DELIMITER ; $$

CALL e_bills();

SELECT * FROM Customer;

DROP DATABASE Random;

Answer 1

大量语法错误，如果您不在更新语句中使用 where 子句，所有客户都将被更新。

drop table if exists t;
CREATE TABLE t(
    `c_no`              INT,
    `name`              VARCHAR(40),
    `previous_reading`  INT,
    `current_reading`   INT,
    `amount`            INT DEFAULT 0
);

INSERT INTO t (`c_no`, `name`, `previous_reading`,`current_reading`)
VALUES
    (101, 'Smith', 90, 120),
    (201, 'George', 30, 250),
    (301, 'Philip', 120, 200),
    (401, 'Jasper', 10,390);

drop procedure if exists p;
DELIMITER $$ 
CREATE PROCEDURE p()
BEGIN
    DECLARE flag INT DEFAULT 0;
    DECLARE cno INT;
    DECLARE pr, cr, amt, unt DOUBLE;
    DECLARE curBill
        CURSOR FOR
            SELECT c_no, `previous_reading`, `current_reading`
            FROM t;
    DECLARE CONTINUE HANDLER FOR NOT found SET flag = 1;

    OPEN curBill;
    calUnit: LOOP
        FETCH curBill INTO cno, pr, cr;
        IF flag <> 1 THEN
            SET unt = cr-pr;
        ELSE
            LEAVE calUnit;
        END IF;
        IF (unt <= 100) THEN
            SET amt = unt*2;
        ELSEIF (unt >= 101 AND unt <= 200) THEN
            SET amt = unt*2.5;
        ELSEIF (unt > 201 AND unt <= 300) THEN
            SET amt = unt*3;
        ELSEIF (unt > 301) THEN
            SET amt = unt*4;
        END IF;
        UPDATE t
        SET `amount`=amt
            where c_no = cno;
    END LOOP;
    CLOSE curBill;
END $$
DELIMITER ; 

CALL p();

SELECT * FROM t;

+------+--------+------------------+-----------------+--------+
| c_no | name   | previous_reading | current_reading | amount |
+------+--------+------------------+-----------------+--------+
|  101 | Smith  |               90 |             120 |     60 |
|  201 | George |               30 |             250 |    660 |
|  301 | Philip |              120 |             200 |    160 |
|  401 | Jasper |               10 |             390 |   1520 |
+------+--------+------------------+-----------------+--------+
4 rows in set (0.00 sec)