如果条件为真，则在MySQL存储过程中运行查询 [英] Run a query in a MySQL stored procedure if a condition is true

问题描述

我正在使用MySQL数据库并尝试创建存储过程.我如何才能做到，如果query1的结果没有记录，那么它将执行另一个查询?

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?

这是我到目前为止所拥有的:

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?

推荐答案

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

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.

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

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>

尝试一下！

这篇关于如果条件为真，则在MySQL存储过程中运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！