如果条件为真，则在MySQL存储过程中运行查询

如果条件为真，则在MySQL存储过程中运行查询

这花了一些工夫，但我做了足够的调整。您的代码问题与您的逻辑无关，但与MySQL存储过程语言本身无关。在执行动态SQL时，存在范围问题。

我所做的是创建一个临时表并将返回的值存放在其中

这是一些示例数据

mysql> drop database if exists user391986;Query OK, 1 row affected (0.08 sec)mysql> create database user391986;Query OK, 1 row affected (0.00 sec)mysql> use user391986Database changedmysql> CREATE TABLE mytable (    -> ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name VARCHAr(255) NOT NULL,    -> Value VARCHAr(255) NOT NULL    -> ) ENGINE=InnoDB;Query OK, 0 rows affected (0.11 sec)mysql> INSERT INTO mytable (Name,Value) VALUES    -> ('rolando','edge'),('pamela','washington'),    -> ('dominique','wilkins'),('diamond','cutter');Query OK, 4 rows affected (0.06 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> SELECT * from mytable;+----+-----------+------------+| ID | Name      | Value      |+----+-----------+------------+|  1 | rolando   | edge       ||  2 | pamela    | washington ||  3 | dominique | wilkins    ||  4 | diamond   | cutter     |+----+-----------+------------+4 rows in set (0.00 sec)mysql>

这是为适应临时表中的返回值而进行调整的存储过程

mysql> delimiter //mysql> CREATE PROCEDURE myproc(IN myTable VARCHAr(255), IN myValue VARCHAr(255), IN myValueTwo VARCHAr(255))    -> BEGIN    ->     DECLARE foundcount INT;    ->     DECLARE retval VARCHAr(255);    ->    ->     SET @iTable=myTable;    ->     SET @iValue=myValue;    ->     SET @iValueTwo=myValueTwo;    ->    ->     CREATE TEMPORARY TABLE IF NOT EXISTS mynumber (rv VARCHAr(255)) ENGINE=MEMORY;    ->     DELETe FROM mynumber;    ->    ->     SET retval = 'nothing retrieved';    ->     SET @query = CONCAt('INSERT INTO mynumber SELECt Name FROM ', @iTable, ' WHERe Value=''', @iValue, '''');    ->     PREPARE QUERY FROM @query;    ->     EXECUTE QUERY;    ->     DEALLOCATE PREPARE QUERY;    ->     SELECt COUNT(1) INTO foundcount FROM mynumber;    ->     IF foundcount = 0 THEN    ->         SET @querytwo = CONCAt('INSERT INTO mynumber SELECt Name FROM ', @iTable, ' WHERe Value=''', @iValueTwo, '''');    ->         PREPARE QUERY FROM @querytwo;    ->         EXECUTE QUERY;    ->         DEALLOCATE PREPARE QUERY;    ->     END IF;    ->     SELECt COUNT(1) INTO foundcount FROM mynumber;    ->     IF foundcount > 0 THEN    ->         SELECt rv INTO retval FROM mynumber;    ->     END IF;    ->     SELECT retval;    ->    -> END //Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql>

好的，我三次调用了存储过程。首先什么也没有。第二个获得第二个值。第三个获得第一个值。

mysql> CALL myproc('mytable','pamela','diamond');+-------------------+| retval |+-------------------+| nothing retrieved |+-------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)mysql> CALL myproc('mytable','pamela','wilkins');+-----------+| retval    |+-----------+| dominique |+-----------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)mysql> CALL myproc('mytable','edge','wilkins');+---------+| retval  |+---------+| rolando |+---------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)mysql>

试试看 ！！！