mysql - 如何设置mysql存储过程以使用变量进行更新

Question

我正在寻求自动化我现在手动处理的程序。所以，我是存储过程的新手，下面的代码不起作用。

当它输出 SELECT 语句的第一组变量时，它会停止。它不执行 UPDATE，也不继续 WHILE 循环。没有报告错误。

DELIMITER //
CREATE PROCEDURE ins_die_1()

BEGIN

    DECLARE v_yr INT;
    DECLARE v_col CHAR(10);
    DECLARE v_base INT;
    DECLARE v_factor INT;
    DECLARE v_cf INT;
    DECLARE v_fuel CHAR(10);

    SET v_yr :=1;
    SET v_col := '';
    SET v_base := 2860;
    SET v_factor := 1;
    SET v_cf := 5;
    SET v_fuel := 'diesel';

    WHILE v_yr <= 5 DO
        SET v_col := CONCAT('ins_yr',v_yr);
        SELECT v_col, v_base, v_factor, v_fuel, v_cf;

        UPDATE ct_insurance 
            SET v_col = (v_base + 1254 + (v_factor * .0032 * price )) 
            WHERE fuel = v_fuel 
                AND CF=v_cf 
                AND price * v_factor < 162000 
                AND version_id = 1746;

        SET v_yr := v_yr+1;

        IF v_yr = 2 THEN SET v_factor := .8;
        ELSE SET v_factor := v_factor - 0.1;
        END IF;
    END WHILE; 

END//
DELIMITER ;

我知道有 IN 构造，在调用过程时我应该手动输入变量，但这似乎是一个尴尬的解决方案，而且效率肯定不高。

如果您想知道我有很多变量，那是因为这是一个测试过程。一旦它起作用，希望我会扩展它。

另外，我只是在寻找 mySql 解决方案，即不涉及 php。

Answer 1

UPDATE 语句中的列名称按字面意思理解，变量不能替换。您可以使用动态 SQL 来完成此操作:

SET @sql = CONCAT('UPDATE ct_insurance 
        SET ', v_col, ' = (v_base + 1254 + (v_factor * .0032 * price )) 
        WHERE fuel = v_fuel 
            AND CF=v_cf 
            AND price * v_factor < 162000 
            AND version_id = 1746');
PREPARE stmt FROM @sql;
EXECUTE stmt;

但是，您无法从准备好的语句中访问过程局部变量(请参阅 here )，因此您需要使用用户定义的 @ 变量。

DELIMITER //
CREATE PROCEDURE ins_die_1()

BEGIN

    DECLARE v_yr INT;
    DECLARE v_col CHAR(10);

    SET v_yr :=1;
    SET v_col := '';
    SET @v_base := 2860;
    SET @v_factor := 1;
    SET @v_cf := 5;
    SET @v_fuel := 'diesel';

    SELECT v_col, @v_base, @v_factor, @v_fuel, @v_cf;

    WHILE v_yr <= 5 DO
        SET v_col := CONCAT('ins_yr',v_yr);

        SET @sql = CONCAT('UPDATE ct_insurance 
                SET ', v_col, ' = (@v_base + 1254 + (@v_factor * .0032 * price )) 
                WHERE fuel = @v_fuel 
                    AND CF = @v_cf 
                    AND price * @v_factor < 162000 
                    AND version_id = 1746');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;

        SET v_yr := v_yr+1;

        IF v_yr = 2 THEN SET v_factor := .8;
        ELSE SET v_factor := v_factor - 0.1;
        END IF;
    END WHILE; 

END//