CASE中具有多個條件的存盤程序->未按預期作業[MYSQL]

我有一個我試圖在 MySQL 中創建的存盤程序，它接受 5 個引數并回傳查??詢結果。我使用 CASE/WHEN 來區分每個查詢，但是，當我運行存盤程序時：沒有任何回傳。

當我在存盤程序中單獨運行 SQL 查詢并提供所需的值時：它正常輸出。

我不確定我是否只是錯誤地使用了 CASE/WHEN，或者是否還有其他我不知道的事情。代碼是：

DELIMITER $$

CREATE PROCEDURE testSearch(
    IN Tag1 VARCHAR(10),
    IN Tag2 VARCHAR(10),
    IN Tag3 VARCHAR(10),
    IN TagTerm VARCHAR(10),
    IN SearchArea VARCHAR(50)
)
BEGIN

    DECLARE exampleVar2v INT DEFAULT 0;

    CASE
        WHEN
            Tag1 <> 0
            AND Tag2 = 0
            AND Tag3 = 0
            AND TagTerm = "Tag"
            AND SearchArea = "Responsibility"
        THEN
            SELECT DISTINCT
                R_Name
            FROM
                Responsibility
            WHERE
                R_id
            IN
                (SELECT
                    R_id
                FROM
                    Key_Tag
                WHERE
                    Tag_id
                IN
                    (Tag1))
            ;
        WHEN
            Tag1 <> 0
            AND Tag2 <> 0
            AND Tag3 = 0
            AND TagTerm = "Tag"
            AND SearchArea = "Responsibility"
        THEN
           SELECT DISTINCT
                R_Name
            FROM
                Responsibility
            WHERE
                R_id
            IN
                (SELECT
                    R_id
                FROM
                    Key_Tag
                WHERE
                    Tag_id
                IN
                    (Tag1,Tag2)
                GROUP BY
                    Tag_id
                HAVING
                    COUNT(*) = 2)
            ;
        WHEN
            Tag1 <> 0
            AND Tag2 <> 0
            AND Tag3 <> 0
            AND TagTerm = "Tag"
            AND SearchArea = "Responsibility"
        THEN
           SELECT DISTINCT
                R_Name
            FROM
                Responsibility
            WHERE
                R_id
            IN
                (SELECT
                    R_id
                FROM
                    Key_Tag
                WHERE
                    Tag_id
                IN
                    (Tag1,Tag2,Tag3)
                GROUP BY
                    Tag_id
                HAVING
                    COUNT(*) = 3)
            ;
        ELSE
            set exampleVar2v = 1;
        END CASE;
END $$

編輯：此外觀的呼叫如下：

CALL testSearch("T1","T2",0,"Tag","Responsibility");

這將觸發上面的第二個 CASE。

為了方便，我已經輸出了整個內容。任何幫助，將不勝感激。謝謝你。

uj5u.com熱心網友回復：

您正在傳遞字串但測驗整數嘗試

DELIMITER $$

CREATE PROCEDURE p( IN Tag1 VARCHAR(10), IN Tag2 VARCHAR(10), IN Tag3 VARCHAR(10), IN TagTerm VARCHAR(10),
    IN SearchArea VARCHAR(50)
)
BEGIN

    DECLARE exampleVar2v INT DEFAULT 0;

    CASE 
        WHEN Tag1 <> '0'  AND Tag2 = '0' AND Tag3 = '0'  AND TagTerm = "Tag"  AND SearchArea = "Responsibility"    THEN
            select 'one';
            /*
               SELECT DISTINCT R_Name
            FROM  Responsibility
            WHERE R_id
            IN  (SELECT
                    R_id
                FROM Key_Tag
                WHERE Tag_id
                IN   (Tag1))
            ;
            */
        WHEN Tag1 <> '0'  AND Tag2 <> '0' AND Tag3 = '0' AND TagTerm = "Tag" AND SearchArea = "Responsibility"
        THEN
              select 'two';
              /*
           SELECT DISTINCT R_Name
            FROM  Responsibility
            WHERE R_id
            IN    (SELECT R_id
                   FROM   Key_Tag
                WHERE Tag_id
                IN  (Tag1,Tag2)
                GROUP BY Tag_id
                HAVING  COUNT(*) = 2)
            ;
            */
        WHEN Tag1 <> '0' AND Tag2 <> '0' AND Tag3 <> '0' AND TagTerm = "Tag"  AND SearchArea = "Responsibility"
        THEN
                select 'three';
           /*
              SELECT DISTINCT R_Name
            FROM  Responsibility
            WHERE R_id
            IN (SELECT  R_id
                FROM   Key_Tag
                WHERE  Tag_id
                IN  (Tag1,Tag2,Tag3)
                GROUP BY Tag_id
                HAVING   COUNT(*) = 3)
            ;
            */
        ELSE
               select 'four';
            set exampleVar2v = 1;
        END CASE;
END $$

delimiter ;

CALL p("T1","T2",0,"Tag","Responsibility");

請注意，如果您想除錯，只需在適當的位置包含選擇作為開始。

這個版本確實屬于第二個的時候。

請查看https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

標籤：mysql sql 案件

上一篇：如何在資料框中附加資料

下一篇：搜索不同的表？