如果条件为真，则在 MySQL 存储过程中运行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9845171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run a query in a MySQL stored procedure if a condition is true
提问by user391986
I am using MySQL database and trying to create a stored procedure. How can I make it so that if the result of query1 has no records, then it execute a different query?
我正在使用 MySQL 数据库并尝试创建一个存储过程。我怎样才能做到，如果 query1 的结果没有记录，那么它会执行一个不同的查询？
Here is what I have so far:
这是我到目前为止所拥有的：
/* CREATE DB */
CREATE DATABASE mydata;
use mydata;
/* TABLE */
CREATE TABLE mydata (
ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Value VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
INSERT INTO mydata (Name, Value) VALUES ("testname", "testvalue");
/* STORED PROCEDURE */
delimiter //
CREATE PROCEDURE myproc(IN myTable VARCHAR(255),
IN myValue VARCHAR(255),
IN myValueTwo VARCHAR(255))
BEGIN
SET @iTable=myTable;
SET @iValue=myValue;
SET @iValueTwo=myValueTwo;
SET @query = CONCAT('SELECT Name FROM ', @iTable,
' WHERE Value="', @iValue, '"');
SET @querytwo = CONCAT('SELECT Name FROM ', @iTable,
' WHERE Value="', @iValueTwo, '"');
PREPARE QUERY FROM @query;
EXECUTE QUERY;
END //
delimiter ;
/* CALL */
call myproc("mydata", "testvalue", "");
I want to run a query, and execute a secondary query only if the first has no rows. What is the best way to do this?
我想运行一个查询，并且仅当第一个没有行时才执行辅助查询。做这个的最好方式是什么？
回答by RolandoMySQLDBA
This took some work but I made enough adjustments. The problem with your code has nothing to do with your logic but with MySQL Stored Procedure Language itself. When doing dynamic SQL It has scoping issues.
这需要一些工作，但我做了足够的调整。您的代码问题与您的逻辑无关，而与 MySQL 存储过程语言本身有关。在执行动态 SQL 时，它存在范围问题。
What I did was create a temp table and deposited the returned value in it
我所做的是创建一个临时表并将返回的值存入其中
Here is some sample data loaded
这是加载的一些示例数据
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 user391986
Database changed
mysql> 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: 0
mysql> 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>
Here is the stored procedure adjusted to catch the return values in a temp table
这是调整后的存储过程以捕获临时表中的返回值
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>
OK I called the stored procedure three time. The first gets nothing. The second gets the second value. The third gets the first value.
好的，我调用了存储过程三次。第一个什么也得不到。第二个获取第二个值。第三个获得第一个值。
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>
Give it a Try !!!
试一试 ！！！
回答by rlobban
In Sql Server, you could run the results into a temporary table and then check the temp table for rows like this:
在 Sql Server 中，您可以将结果运行到临时表中，然后检查临时表中的行，如下所示：
declare @numberResults int = 0
create table #MyTempTable(...)
insert #MyTempTable
sp_executesql Query
set @numberResults = (select count(*) from #MyTempTable)
if @numberResults = 0
begin
sp_executesql secondQuery
end
回答by Webguy
In mysql you can use the found_rows()built in procedure like this:
在 mysql 中，您可以使用found_rows()内置程序，如下所示：
el@apollo:~$ mysql -u root -p
Enter password:
mysql> use your_database;
Database changed
mysql> select id from problems;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
In mssql you can use the value @@rowcount. Then you can run the second query only if the first query returned no rows:
在 mssql 中，您可以使用 value @@rowcount。然后，只有在第一个查询未返回任何行时，您才能运行第二个查询：
EXECUTE QUERY;
IF @@rowcount = 0
BEGIN
PREPARE QUERY FROM @querytwo;
EXECUTE QUERY;
END
回答by Arnob
My simplest working code
我最简单的工作代码
BEGIN
IF test = 'null' THEN
PREPARE QUERY FROM 'SELECT username as name from login';
EXECUTE QUERY;
ELSE
PREPARE QUERY FROM 'SELECT username as name2 from login';
EXECUTE QUERY;
END IF;
END
