代码之家  ›  专栏  ›  技术社区  ›  user1181942

创建临时表后缺少“end”错误

  •  0
  • user1181942  · 技术社区  · 6 年前

    我正在创建一个存储过程,其中我应该创建一个临时表来插入数据并在最后显示(这是一个赋值,因此我必须按照指令创建临时表)。

    我应该在这个存储过程中使用游标。我正在创建临时表,然后创建一个光标。但我犯了个错误 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 回复  |  直到 6 年前
        1
  •  0
  •   Kedar Limaye    6 年前

    游标的声明应在创建临时表之前进行

    此外,我还取消了在临时表中插入代码的注释

    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