MYSQL |SP |CURSOR - 将游标提取到变量中 return null [英] MYSQL | SP | CURSOR - Fetch cursor into variable return null

问题描述

我想:

1. 创建临时表.
2. 将部分数据插入临时表.
3. 在temporary.user_id 字段中循环运行.
4. 在数据操作和计算后更新每一行.

第 3 步的问题，我从游标中得到 user_id=NULL 而不是一个整数.

创建程序 user_demo_sp()开始DECLARE done INT DEFAULT FALSE；声明 current_user INT;DECLARE cur CURSOR FOR SELECT user_id FROM users_temp;-- 临时表上的光标DECLARE CONTINUE Handler FOR NOT FOUND SET done = TRUE;-- 创建一个表如果存在`users_temp`，则删除临时表；创建临时表`users_temp`(user_id INT(11) 非空，聚合列 INT(11) 非空);-- 填表INSERT INTO users_temp SELECT user_id from users where condition ="condition";打开电流；read_loop: 循环FETCH cur INTO current_user;如果完成 THEN离开读循环；万一;选择当前用户；-- 返回 NULL结束循环；关闭当前；结尾;

解决方案

在声明光标之前，您需要填充临时表(只需使用嵌套的 Begin...END 块):

创建程序 user_demo_sp()开始DECLARE done INT DEFAULT FALSE；声明 current_user INT;临时表DECLARE CONTINUE Handler FOR NOT FOUND SET done = TRUE;-- 创建一个表如果存在`users_temp`，则删除临时表；创建临时表`users_temp`(user_id INT(11) 非空，聚合列 INT(11) 非空);-- 填表INSERT INTO users_temp SELECT user_id from users where condition ="condition";开始DECLARE cur CURSOR FOR SELECT user_id FROM users_temp;-- 光标打开打开电流；read_loop: 循环FETCH cur INTO current_user;如果完成 THEN离开读循环；万一;选择当前用户；-- 返回 NULL结束循环；关闭当前；结尾;结尾;

<块引用>

同样在这种情况下，实际上您不需要临时表可以在你用来填充模板的选择上声明光标

I want to:

1. Create temporary table.
2. Insert partial data into temporary table.
3. Run in Loop on temporary.user_id field.
4. Update each row after data manipulation and calculation.

The problem on step #3, i get user_id=NULL instead an integer from cursor.

CREATE PROCEDURE user_demo_sp()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE current_user INT;
  DECLARE cur CURSOR FOR SELECT user_id FROM users_temp; -- Cursor on temp table 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Create a table
DROP temporary table if exists `users_temp` ;
CREATE temporary table `users_temp` (
user_id INT(11) NOT NULL,
aggregation_column INT(11) NOT NULL
);

-- Fill table
INSERT INTO users_temp SELECT user_id from users where condition ="condition";

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO current_user;
    IF done THEN
      LEAVE read_loop;
    END IF;
   select current_user; -- Return NULL

  END LOOP;

  CLOSE cur;

END;

解决方案

you need to fill your temp table before declaring the cursor on it (just use nested Begin...END blocks):

CREATE PROCEDURE user_demo_sp()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE current_user INT;
   temp table 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Create a table
DROP temporary table if exists `users_temp` ;
CREATE temporary table `users_temp` (
user_id INT(11) NOT NULL,
aggregation_column INT(11) NOT NULL
);

-- Fill table
INSERT INTO users_temp SELECT user_id from users where condition ="condition";
  Begin
  DECLARE cur CURSOR FOR SELECT user_id FROM users_temp; -- Cursor on
  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO current_user;
    IF done THEN
      LEAVE read_loop;
    END IF;
   select current_user; -- Return NULL

  END LOOP;

  CLOSE cur;
  End;
END;

Also in scenarios like this actually you don't need a temp table you can declare the cursor on the select which you used to fill temptale

这篇关于MYSQL |SP |CURSOR - 将游标提取到变量中 return null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！