mysql - 如果条件失败的存储过程

Question

我正在mysql中使用if else条件创建存储过程。程序在if条件下运行良好，但如果我输入if条件则失败。它甚至没有显示语法错误。

CREATE PROCEDURE inputCars()
    BEGIN
        DECLARE n INT DEFAULT 0;
        DECLARE i INT DEFAULT 0;
        DECLARE j INT DEFAULT 0;
        DECLARE d INT DEFAULT 0;
        SELECT count(*)
        FROM devtakci.tb_users AS a INNER JOIN devtakci.tb_driver_details AS b ON a.user_id = b.driver_id
        WHERE a.status = 0 AND a.reg_as = 1
        INTO n;
        SET i = 0;
        SET j = 0;
        WHILE (i < n) DO
            IF EXISTS(SELECT driver_id
                      FROM adapter.tb_available_cars
                      WHERE driver_id = (SELECT user_id
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1))
            THEN
                UPDATE adapter.tb_available_cars
                SET
                    vehicle_type      = (SELECT car_type
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1),
                    driver_phone      = (SELECT phone_no
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1),
                    vehicle_plate     = (SELECT driver_car_no
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1),
                    latitude          = (SELECT current_location_latitude
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1),
                    longitude         = (SELECT current_location_longitude
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1),
                    direction_heading = (SELECT driver_car_direction_angle
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1),
                    status            = (SELECT status
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1),
                    driver_id         = (SELECT user_id
                                         FROM devtakci.tb_users
                                         ORDER BY user_id ASC
                                         LIMIT i, 1)
                WHERE driver_id = (SELECT user_id
                                   FROM devtakci.tb_users
                                   ORDER BY user_id ASC
                                   LIMIT i, 1)
            ELSE
                INSERT INTO adapter.tb_available_cars (vehicle_type, driver_phone, vehicle_plate, latitude, longitude, direction_heading, status, driver_id, make, model, driver_first_name, driver_last_name)
                    SELECT
                        a.car_type,
                        a.phone_no,
                        a.driver_car_no,
                        a.current_location_latitude,
                        a.current_location_longitude,
                        a.driver_car_direction_angle,
                        a.status,
                        b.driver_id,
                        b.vehicle_make,
                        b.vehicle_model,
                        b.first_name,
                        b.last_name

                    FROM devtakci.tb_users AS a
                        INNER JOIN devtakci.tb_driver_details AS b ON a.user_id = b.driver_id
                    WHERE a.status = 0 AND a.reg_as = 1

                    ORDER BY a.user_id ASC
                    LIMIT i, 1;
            END IF;
            SET i = i + 1;
        END WHILE;
    END;

如果我执行它，跳过 if 条件它有效，但当我放置 IF ELSE 子句时失败

Answer 1

在 mysql 中，您只能在查询中使用 exists，而不能在复合语句本身中使用。您需要将 driver_id 获取到变量中，然后 if 可以根据变量的值做出决定。

但是整个 if 语句似乎是 在重复键更新时插入 ... statemen5 的复杂形式。