MariaDB存储过程中IF语句与局部变量使用问题求助

环境与核心问题 #

• 运行环境：phpMyAdmin，MariaDB版本为10.5.24-MariaDB-cll-lve
• 核心问题：
  • 单独执行SET @x = 1;这类会话变量赋值语句时，语句本身无报错，但后续执行的语句会被标记为失败；尝试使用DECLARE声明局部变量也存在问题
  • 用BEGIN/END包裹的存储过程可以成功保存，但执行时会抛出... near NULL at line 1的错误

变量声明测试案例（疑似分隔符问题） #

以下是简化后的变量声明测试代码，怀疑问题出在分隔符设置上：

#BEGIN
#   DELIMITER $$
#    $$
    #SET @try1 = 1$$
    #SET @try1 = 1, @try2 = 2, @try3 = 3$$
    #SET @try4 = 4, @try5 = 5, @try6 = 6$$ (failed on start of this line)
    
    
    SET @try1 = 1;
    SET @try2 = 2;
    SET @try3 = 3;
    
#   DELIMITER ;
#END

存储过程多变量声明报错 #

基础结构的存储过程可正常创建，但添加第二个DECLARE/SET语句时，创建直接失败：

DECLARE total_value INT;
    SET total_value = 50;
    
DECLARE total_value2 INT;
    SET total_value2 = 50;

实际需求：动态SQL存储过程 #

因静态SQL中的IF语句出现问题，改用动态SQL实现需求，导出的存储过程代码如下：

DELIMITER $$
CREATE DEFINER=`wcipporg`@`localhost`
    PROCEDURE `BBBBBBBBB`
        (IN `SinceDate` DATE,
         IN `Totals` VARCHAR(10))
BEGIN

DECLARE selectP1 VARCHAR(999);
    SET selectP1 = 'SELECT SinceDate AS `Sales Since`,';
    
IF Totals = 'N' THEN 
   SET @selectP2 = 'wp_posts.post_title AS Plant,
                    wp_terms.name AS Container,';
ELSE    
   SET @selectP2 = "COALESCE(wp_posts.post_title, '### All Plants ###')      AS `Plant Name`,
                    COALESCE(wp_terms.name,       '### All Containers ###')  AS `Container`,";
END IF;        
SET @selectP3 = 'SUM(sold_plants.how_many) AS Total
                 FROM wcipporg_Bungalook.sale AS sale
                 INNER JOIN wcipporg_Bungalook.sold_plants AS sold_plants
                         ON sale.txn_id = sold_plants.txn_id
                 INNER JOIN wcipporg_wp596.wpi2_posts AS wp_posts
                         ON sold_plants.plant_id = wp_posts.ID
                 INNER JOIN wcipporg_wp596.wpi2_terms AS wp_terms
                         ON sold_plants.container_id = wp_terms.term_id
                 WHERE sale.txn_time >= SinceDate';
IF Totals = 'N' THEN 
   SET @selectP3 = 'GROUP BY wp_posts.post_title ASC, wp_terms.name';
ELSE    
   SET @selectP3 = 'GROUP BY wp_posts.post_title ASC, wp_terms.name WITH ROLLUP';
END IF;


SET @query = CONCAT(@selectP1, @selectP2, @selectP3, @selectP4);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

额外疑问 #

尝试过多种代码变体，查阅相关资料后仍无法解决问题。错误提示要求参考对应版本的官方手册，但不知道如何找到MariaDB 10.5.24版本的官方手册。

内容的提问来源于stack exchange，提问作者Dennis Bareis