Mysql存储过程定义、输入参数、定义变量、sql语句中拼接参数、sql执行结果赋值变量等问题

一、一个procedure实例

实现循环更新一张表的排序号功能，有多少条数据，就从1排到几。

DROP PROCEDURE IF EXISTS `update_old_sortnumber_procedure`; 
CREATE DEFINER=`root`@`%` PROCEDURE `update_old_sortnumber_procedure`()
BEGIN
    DECLARE countRow INT DEFAULT 0;
    DECLARE v_i INT DEFAULT 0;
    -- 查询数据条数，确定循环次数
    set @countRow = (select count(*) from test);
    -- 打印日志数据条数
    SELECT @countRow;
    -- 循环行更新
    WHILE v_i < @countRow DO
        SET @sqlStmt = CONCAT('UPDATE test SET sort_number=',v_i+1,' WHERE id IN (SELECT id FROM(select id from test LIMIT ',v_i,',1) aaa)');
        PREPARE stmt FROM @sqlStmt;
        EXECUTE stmt;
        SET v_i := v_i+1;
    END WHILE;
END

 

二、Mysql中定义存储过程

1.全Sql语句创建

例如执行以上sql即可创建

2.Navicat中创建

第一步，函数右键 → 新建函数

第二步，填写存储过程名字，选择过程，点完成

第三步，定义存储过程

 

三、Mysql存储过程中输入参数

CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`(in username varchar(250),in id INT)
BEGIN
         -- 打印日志直接用select
         SELECT username;
         SELECT id;
END

运行

输入参数

执行结果

总结：

1. 传入参数用 in 变量名 变量类型
2. 使用传入参数直接用参数名
3. 打印日志直接用select

 

四、Mysql存储过程中自定义参数
 

CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
BEGIN
         DECLARE countRow INT DEFAULT 2;
         -- 变量赋值
         set @countRow := countRow + 1;
         SELECT @countRow;
END

输出结果

 

五、Mysql存储过程在SQL语句中嵌入参数

-- 循环行更新
WHILE v_i < @countRow DO
    SET @sqlStmt = CONCAT('UPDATE test SET sort_number=',v_i+1,' WHERE id IN (SELECT id FROM(select id from test LIMIT ',v_i,',1) aaa)');
    PREPARE stmt FROM @sqlStmt;
    EXECUTE stmt;
    SET v_i := v_i+1;
END WHILE;

总结：用concat拼接sql，执行语句执行sql

 

六、Mysql存储过程中查询语句执行结果赋值给变量

方式 1

DECLARE cnt INT DEFAULT 0;
select count(*) into cnt from test;
select cnt;

方式 2

DECLARE cnt INT DEFAULT 0;
set @cnt = (select count(*) from test);
select @cnt;

方式 3

DECLARE cnt INT DEFAULT 0;
select count(*) into @cnt from test;
select @cnt;

多个列的情况下似乎只能用 into 方式

elect max(status), avg(status) into @max, @avg from test_tbl;
select @max, @avg;

 

更多学习和交流，请关注订阅号：猿事如此

​