mySQL 存储过程 - 游标错误

Question

我正在尝试编写一个带有游标的 mySQL 过程来计算票价。我通过 stationid，然后我弄清楚他们在哪个区域。票价是 1 美元的固定值，每个区域行驶额外 0.20 美元。到目前为止，我运行的代码存在一个问题，即游标无法将值提取到变量中。

如有任何帮助，我们将不胜感激。谢谢

表格:

    DROP DATABASE IF EXISTS luasSystem;
    CREATE DATABASE luasSystem;
    USE luasSystem;

    CREATE TABLE IF NOT EXISTS line
    (
    line_id INT NOT NULL AUTO_INCREMENT,
    Line_colour CHAR(10) NOT NULL,
    PRIMARY KEY (line_id)
    )   ENGINE=InnoDB;

    CREATE TABLE IF NOT EXISTS zone
    (
    zone_id INT NOT NULL AUTO_INCREMENT,
    zone_name VARCHAR(20) NOT NULL,
        line INT NOT NULL,
    PRIMARY KEY (zone_id),
    FOREIGN KEY (line) REFERENCES line(line_id) ON UPDATE CASCADE ON DELETE RESTRICT
    )   ENGINE=InnoDB;

    CREATE TABLE IF NOT EXISTS station
    (
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name CHAR(20) NOT NULL,
    service CHAR(20),
        line INT NOT NULL,
        zone INT NOT NULL,
    PRIMARY KEY (station_id),
    FOREIGN KEY (line) REFERENCES line(line_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (zone) REFERENCES zone(zone_id) ON UPDATE CASCADE ON DELETE RESTRICT
    )   ENGINE=InnoDB;

存储过程:

    DROP PROCEDURE IF EXISTS calculateFare;
    DELIMITER //
    CREATE PROCEDURE calculateFare
    (
    IN stationid1 INT, IN stationid2 INT
    )
    BEGIN

    DECLARE zoneNum1 INT;
    DECLARE zoneNum2 INT;
    DECLARE num INT;
    DECLARE fare DOUBLE;

    DECLARE tableEnd BOOLEAN;

    DECLARE zoneCur CURSOR FOR
    SELECT zone, zone FROM station
    WHERE station_name = stationid1 AND station_name = stationid2;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET tableEnd = TRUE;

    OPEN zoneCur;
    the_loop: LOOP

    FETCH zoneCur
    INTO zoneNum1, zoneNum2;

    IF tableEnd THEN
    CLOSE zoneCur;
    LEAVE the_loop;
    END IF;

    SET fare = 1;
    SET num = 0;

    IF zoneNum1 < zoneNum2 THEN
    SET num = zoneNum2 - zoneNum1;
    ELSEIF zoneNum1 > zoneNum2 THEN
    SET num = zoneNum1 - zoneNum2;
    END IF;

    SET fare = (num * 0.20) + 1;
    SELECT fare;
    END LOOP the_loop;
    END //
    DELIMITER ;

    CAll calculateFare(3,5);

Answer 1

使用十进制值而不是整数会不会更容易？建议你看看:http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html