MySQL 游标 : looping through multiple rows? 出现 42000 错误， "more than one row"- 但期望有多行？

Question

我认为游标的目的是循环遍历与用于定义游标的 SQL 中的条件相匹配的多行。

因此，我很困惑在执行此代码时遇到此错误，因为我的目标是从与“防御承包商”类型匹配的多行中操作和提取关键数据:

错误是“代码:1172 SQL 状态:42000 --- 结果包含多行”

我做错了什么？

CREATE DEFINER=`root`@`localhost` PROCEDURE `clientschema`.`MapDistrictsToAccounts`()
    MODIFIES SQL DATA
BEGIN
 declare finished integer default 0;
 declare v_CD  varchar(45) default null;
 declare v_SSD varchar(45) default null;
 declare v_SHD varchar(45) default null;
 /* Temporary variable */
 declare v_sfdcDistrictId varchar(45) default null;
 declare v_sfdcAccountId varchar(45) default null;

 declare account_cursor cursor for
        select externalsys_id, congressional_district, state_house_district, state_senate_district from clientschema.Account where type="Defense Contractor";

/* declare not found handler*/
declare continue handler
for not found set finished = 1;

 open account_cursor;

 get_districts : LOOP
     fetch account_cursor into v_sfdcAccountId, v_CD, v_SHD, v_SSD;

      if finished = 1 THEN
      leave get_districts;
      end if;

     /* First do congressional district */
     select externalsys_id from clientschema.District where district_number__c = v_CD into v_sfdcDistrictId;
     /*if v_sfdcDistrictId = null then iterate get_districts;*/
     insert into clientschema.Account_Junction_District (district_id,hospital_idHospital,district_type__c) values (v_sfdcDistrictId, v_sfdcAccountId, "Direct");

     /* Next do State House district */
     select externalsys_id from clientschema.District where district_number__c = v_SHD into v_sfdcDistrictId;
     /*if v_sfdcDistrictId = null then iterate get_districts;*/
     insert into clientschema.Account_Junction_District (district_id,hospital_idHospital,district_type__c) values (v_sfdcDistrictId, v_sfdcAccountId, "Direct");

     /* Last do State Senate district */
     select externalsys_id from clientschema.District where district_number__c = v_SSD into v_sfdcDistrictId;
     /*if v_sfdcDistrictId = null then iterate get_districts;*/
     insert into clientschema.Account_Junction_District (district_id,hospital_idHospital,district_type__c) values (v_sfdcDistrictId, v_sfdcAccountId, "Direct");

     END LOOP get_districts;
close account_cursor;

END

Answer 1

问题不在于光​​标。这是一个逻辑错误。

问题是这样的三个查询:

从clientschema.District中选择externalsys_id，其中district_number__c = v_CD进入v_sfdcDistrictId;

我需要对地区类型添加限定符。复合区类型中存在重复的区号。

问题解决了!