时间戳

首页 > 解决方案 > 在同一个mysql过程中使用循环和游标显示错误

mysql - 在同一个mysql过程中使用循环和游标显示错误

问题描述

以下代码工作正常。但是当我启用注释区域（光标）时，代码显示错误。请帮助解决问题。

场景：代码允许一些参数。它将准备表中的数据，然后游标将从该表中获取数据并输出该数据。

相同的参数：调用 prGetInsuranceData_Multiple(2, 'Saroar,Ahmed', '20,30')

DELIMITER $$

USE `surokkha_db`$$

DROP PROCEDURE IF EXISTS `prGetInsuranceData_Multiple`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `prGetInsuranceData_Multiple`
(
    PeopleToBeCovered INT,
    IN NAME VARCHAR(4000),
    IN AGE VARCHAR(200)
)
BEGIN
    -- declare loop variables
    DECLARE V_Name VARCHAR(255);
    DECLARE V_AGE INT;
    DECLARE X INT DEFAULT 0;
    
    -- declare cursor variables
    DECLARE Cur_Finished INTEGER DEFAULT 0;
    DECLARE Cur_Name VARCHAR(255);
    DECLARE Cur_Age INT;
    
    -- create a table with comma separated values (Name with age in table format)
    CREATE TEMPORARY TABLE TempCustomer
    (
        NAME VARCHAR(255),
        AGE INT
    );
    
    CREATE TEMPORARY TABLE TempCustomer1
    (
        NAME VARCHAR(255),
        AGE INT
    );
    
    SET X = 1;
    BEGIN 
    WHILE X <= PeopleToBeCovered DO
        SET V_Name = SUBSTRING_INDEX(SUBSTRING_INDEX(NAME,',',X),',',-1);
        SET V_Age = SUBSTRING_INDEX(SUBSTRING_INDEX(AGE,',',X),',',-1);
        SET X = X + 1;
        INSERT INTO TempCustomer VALUES(V_Name, V_Age);
    END WHILE;
    END;
    
    /*
    -- declare cursor
    DECLARE cur_NameWithAge 
        CURSOR FOR 
            SELECT NAME, AGE FROM TempCustomer;
    
    
    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
    
    OPEN cur_NameWithAge;
    GetNameWithAge: LOOP
        FETCH cur_NameWithAge INTO Cur_Name, Cur_Age;
        IF finished = 1 THEN 
            LEAVE GetNameWithAge;
        END IF;
        
        -- get data and insert into table
        INSERT INTO TempCustomer1 VALUES(Cur_Name, Cur_Age);
    END LOOP GetNameWithAge;
    CLOSE cur_NameWithAge;
    */
        
    SELECT * FROM TempCustomer;
    
    -- as after setting cursor the data is not needed, thats why drop the tables
    DROP TEMPORARY TABLE TempCustomer;
    DROP TEMPORARY TABLE TempCustomer1;
    
END$$

DELIMITER ;

标签： mysqlsql

解决方案

您需要在 BEGIn ENd 中撅起循环及其声明

我仍然需要重新编程洞的东西，因为你的代码抛出错误，我找不到

create procedure prGetInsuranceData_Multiple(
    IN PeopleToBeCovered INT,
    IN _NAME VARCHAR(4000),
    IN _AGE VARCHAR(200))
begin
    DECLARE V_Name VARCHAR(255);
    DECLARE V_AGE INT;
    DECLARE X INT DEFAULT 0;
  drop temporary table if exists TempCustomer;
  drop temporary table if exists TempCustomer1;
  create temporary table TempCustomer( NAME VARCHAR(255),AGE int );
  create temporary table TempCustomer1(NAME VARCHAR(255),AGE int);

    SET X = 1;
    BEGIN
    WHILE X <= PeopleToBeCovered DO
        SET V_Name = SUBSTRING_INDEX(SUBSTRING_INDEX(_NAME,',',X),',',-1);
        SET V_Age = SUBSTRING_INDEX(SUBSTRING_INDEX(_AGE,',',X),',',-1);
        SET X = X + 1;
        
        INSERT INTO TempCustomer VALUES(V_Name,V_Age);
    END WHILE;
    END;

  BEGIN
      DECLARE finished INTEGER DEFAULT 0;
      DECLARE v_id int ;
      DECLARE v_name varchar(255);

      declare cur_NameWithAge cursor for select NAME,AGE from TempCustomer;
              DECLARE CONTINUE HANDLER 
                  FOR NOT FOUND SET finished = 1;  
      open cur_NameWithAge;
      GetNameWithAge: LOOP 
          fetch cur_NameWithAge into v_name,v_id;
          IF finished = 1 THEN 
              LEAVE GetNameWithAge;
          END IF;
          INSERT INTO TempCustomer1 VALUES (v_name,v_id);
      END LOOP GetNameWithAge;
      CLOSE cur_NameWithAge;
  END;
  select * FROM TempCustomer;
   DROP TEMPORARY TABLE TempCustomer1;
   DROP TEMPORARY TABLE TempCustomer1;
end

call prGetInsuranceData_Multiple(2, 'Saroar,Ahmed', '20,30')

姓名 | 年龄
:----- | --:
萨罗 | 20
艾哈迈德 | 30

db<>在这里摆弄

推荐阅读