Dynamic sql in Mysql

最新推荐文章于 2024-05-07 10:08:32 发布

weixin_34301132

最新推荐文章于 2024-05-07 10:08:32 发布

阅读量167

点赞数

文章标签：数据库

原文链接：https://segmentfault.com/a/1190000010470913

版权

Mysql中的Dynamic sql，类似于oracle中的Execute immediately语法，写法上稍微麻烦一点，下面是一个具体的例子。

DROP PROCEDURE IF EXISTS total_num;

CREATE PROCEDURE total_num(
IN p_begin INT,
IN p_end INT
)  
BEGIN  
    DECLARE v_begin INT DEFAULT 0;
    DECLARE v_end INT DEFAULT 0;
    DECLARE v_total INT DEFAULT 0;
    
    SET v_begin = p_begin;
    SET v_end = p_end;
    
    WHILE v_begin<=v_end DO 
      SET @sql = CONCAT('SELECT COUNT(1) INTO @item_count FROM t', v_begin);

      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      SET v_total = v_total + @item_count;

      SET v_begin = v_begin+1;
    END WHILE;
    DEALLOCATE PREPARE stmt;

    SELECT v_total;
END

调用方式：