Отсутствует ошибка 'end' после создания временной таблицы

Я создаю хранимую процедуру, в которой я должен создать временную таблицу для вставки данных и отображения в конце (это назначение, поэтому я должен создать временную таблицу в соответствии с инструкцией).

Я должен использовать курсор в этой хранимой процедуре. Я создаю временную таблицу, а затем я создаю курсор. Но я получаю ошибку missing 'end' после создания таблицы.

CREATE DEFINER=`root`@`localhost` PROCEDURE `walkerMasterSummary`()

НАЧАТЬ

DECLARE rowCount INT Default 0;
DECLARE counter INT Default 0;
DECLARE WID char(6);
DECLARE WType_WTID varchar(15);
DECLARE WUID char(6);
DECLARE BattleGroup char(5);
DECLARE CubicAreaWeight decimal(10,2);
DECLARE FuelCapacity decimal(10,2);
DECLARE FuelExpenditure_Mile decimal(10,2);
DECLARE ArmorWeight decimal(10,2);
DECLARE StructureWeight decimal(10,2);
DECLARE Status VARCHAR(20);
DECLARE CostToRepair int(10);


DROP temporary table walker_master;

CREATE temporary table walker_master(
    WID char(6),
WType_WTID varchar(15),
WUID char(6),
BattleGroup char(5),
CubicAreaWeight decimal(10,2),
FuelCapacity decimal(10,2),
FuelExpenditure_Mile decimal(10,2),
ArmorWeight decimal(10,2),
StructureWeight decimal(10,2),
Status VARCHAR(20),
CostToRepair int(10)
);

 DECLARE walkerMaster CURSOR FOR
 SELECT IWA.WID, concat(IWA.WalkerType, " - ", IWT.WTypeID) AS WType_WTID
 , IWA.WUID, WU.BattleGroup, walkerCubicAreaWeight(IWA.WalkerType) AS 
 CubicAreaWeight, walkerFuelCapacity(IWA.WalkerType) AS FuelCapacity
 ,walkerFuelExpenditure(IWA.WalkerType) AS FuelExpenditure_Mile, 
  walkerArmorWeight(IWA.WalkerType) AS ArmorWeight
  ,walkerStructureWeight(IWA.WalkerType) AS StructureWeight, IWA.Status
  , CASE  WHEN IWA.Status = 'Damaged' AND IWA.WalkerType = 'AT-AT' THEN 122 
  * IWT.Weight 
    WHEN IWA.Status = 'Damaged' AND IWA.WalkerType = 'AT-ST' THEN 89 * 
  IWT.Weight
        ELSE 0 END AS CostToRepair
 FROM imperial_walkers_assign IWA
 LEFT JOIN imperial_walker_type IWT ON IWA.WalkerType = IWT.WType
 LEFT JOIN walker_units WU ON IWA.WUID=WU.WUID
ORDER BY IWA.WID;

OPEN walkerMaster;
  BEGIN
SELECT Found_Rows() INTO rowCount;
process_loop : loop
 IF counter < rowCount THEN
 FETCH walkerMaster INTO WID ,WType_WTID, WUID, BattleGroup, 
 CubicAreaWeight, FuelCapacity,
 FuelExpenditure_Mile, ArmorWeight, StructureWeight,Status, CostToRepair;
/* DROP temporary table walker_summary;
CREATE temporary table walker_summary AS SELECT WID ,WType_WTID,WUID, 
 BattleGroup, CubicAreaWeight, FuelCapacity,
 FuelExpenditure_Mile, ArmorWeight, StructureWeight,Status, CostToRepair;*/
 SELECT WID ,WType_WTID, WUID, BattleGroup, CubicAreaWeight, FuelCapacity,
 FuelExpenditure_Mile, ArmorWeight, StructureWeight,Status, CostToRepair;
 SET counter = counter +1;
 ELSE
 leave process_loop;
END IF;
END loop process_loop;
END;
CLOSE walkerMaster;

END

У меня нет возможности удалить временную таблицу или курсор. Как мне исправить эту ошибку?

Источник

1 ответ

Решение

Объявление курсора должно быть до создания временной таблицы

Также я откомментировал код вставки во временную таблицу

DECLARE rowCount INT Default 0;
    DECLARE counter INT Default 0;
    DECLARE WID char(6);
    DECLARE WType_WTID varchar(15);
    DECLARE WUID char(6);
    DECLARE BattleGroup char(5);
    DECLARE CubicAreaWeight decimal(10,2);
    DECLARE FuelCapacity decimal(10,2);
    DECLARE FuelExpenditure_Mile decimal(10,2);
    DECLARE ArmorWeight decimal(10,2);
    DECLARE StructureWeight decimal(10,2);
    DECLARE Status VARCHAR(20);
    DECLARE CostToRepair int(10);

    DECLARE walkerMaster CURSOR FOR
     SELECT IWA.WID, concat(IWA.WalkerType, " - ", IWT.WTypeID) AS WType_WTID
     , IWA.WUID, WU.BattleGroup, walkerCubicAreaWeight(IWA.WalkerType) AS 
     CubicAreaWeight, walkerFuelCapacity(IWA.WalkerType) AS FuelCapacity
     ,walkerFuelExpenditure(IWA.WalkerType) AS FuelExpenditure_Mile, 
      walkerArmorWeight(IWA.WalkerType) AS ArmorWeight
      ,walkerStructureWeight(IWA.WalkerType) AS StructureWeight, IWA.Status
      , CASE  WHEN IWA.Status = 'Damaged' AND IWA.WalkerType = 'AT-AT' THEN 122 
      * IWT.Weight 
        WHEN IWA.Status = 'Damaged' AND IWA.WalkerType = 'AT-ST' THEN 89 * 
      IWT.Weight
            ELSE 0 END AS CostToRepair
     FROM imperial_walkers_assign IWA
     LEFT JOIN imperial_walker_type IWT ON IWA.WalkerType = IWT.WType
     LEFT JOIN walker_units WU ON IWA.WUID=WU.WUID
    ORDER BY IWA.WID;

    DROP temporary table walker_master;

    CREATE temporary table walker_master(
        WID char(6),
    WType_WTID varchar(15),
    WUID char(6),
    BattleGroup char(5),
    CubicAreaWeight decimal(10,2),
    FuelCapacity decimal(10,2),
    FuelExpenditure_Mile decimal(10,2),
    ArmorWeight decimal(10,2),
    StructureWeight decimal(10,2),
    Status VARCHAR(20),
    CostToRepair int(10)
    );



    OPEN walkerMaster;
      BEGIN
    SELECT Found_Rows() INTO rowCount;
    process_loop : loop
     IF counter < rowCount THEN
     FETCH walkerMaster INTO WID ,WType_WTID, WUID, BattleGroup, 
     CubicAreaWeight, FuelCapacity,
     FuelExpenditure_Mile, ArmorWeight, StructureWeight,Status, CostToRepair;

    insert into walker_summary (WID,WType_WTID,WUID,BattleGroup,CubicAreaWeight,FuelCapacity,FuelExpenditure_Mile,ArmorWeight,StructureWeight,
    StructureWeight,Status,CostToRepair)
    SELECT WID ,WType_WTID,WUID, 
     BattleGroup, CubicAreaWeight, FuelCapacity,
     FuelExpenditure_Mile, ArmorWeight, StructureWeight,Status, CostToRepair;
     SELECT WID ,WType_WTID, WUID, BattleGroup, CubicAreaWeight, FuelCapacity,
     FuelExpenditure_Mile, ArmorWeight, StructureWeight,Status, CostToRepair;
     SET counter = counter +1;
     ELSE
     leave process_loop;
    END IF;
    END loop process_loop;
    END;
    CLOSE walkerMaster;

    END
Источник
Другие вопросы по тегам