带临时表存储过程，并将数据存储到现实表中

一、不带传参的存储过程
1、建表sql：
CREATE TABLE your_target_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        branch_no VARCHAR(50),
        shop_name VARCHAR(100),
        foodHundred varchar(50),
        hotPotConvenienceDish varchar(50),
        fruits varchar(50),
        meat varchar(50),
        vegetable varchar(50),
        call_time DATETIME
    );

2、存储过程：
创建临时表（temp_data_table），将逻辑查询到的sql存储到临时表（temp_data_table）中，将临时表（temp_data_table）中的数据存储到现实表（your_target_table）中，并将临时表（temp_data_table）删除
DELIMITER //

CREATE PROCEDURE save_data_to_diffable()
BEGIN
    -- 创建一个临时表用于存储当前数据
    CREATE TEMPORARY TABLE temp_data_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        branch_no VARCHAR(50),
        shop_name VARCHAR(100),
        foodHundred VARCHAR(50),
        hotPotConvenienceDish VARCHAR(50),
        fruits VARCHAR(50),
        meat VARCHAR(50),
        vegetable VARCHAR(50),
        call_time DATETIME
    );

    -- 插入当前数据到临时表中
    INSERT INTO temp_data_table (branch_no, shop_name, foodHundred, hotPotConvenienceDish, fruits, meat, vegetable, call_time)
    SELECT
        b.branch_no,
        b.shop_name,
        max(case b.div_name when '食百' then b.num else 0 end) as 'foodHundred',
        max(case b.div_name when '火锅方便菜' then b.num else 0 end) as 'hotPotConvenienceDish',
        max(case b.div_name when '水果' then b.num else 0 end) as 'fruits',
        max(case b.div_name when '肉类' then b.num else 0 end) as 'meat',
        max(case b.div_name when '蔬菜' then b.num else 0 end) as 'vegetable',
        NOW() as 'call_time'
    FROM (
        SELECT
            `a`.`branch_no` AS `branch_no`,
            `a`.`shop_name` AS `shop_name`,
            `a`.`div_name` AS `div_name`,
            SUM(`a`.`countNum`) AS `num`
        FROM
            (
            SELECT
                `rhd`.`branch_no` AS `branch_no`,
                `hs`.`shop_name` AS `shop_name`,
                `rhi`.`div_name` AS `div_name`,
                ((`rhd`.`pos_qty` + `rhd`.`so_qty`) - `rhd`.`pos_ret_qty`) AS `countNum`
            FROM
                `huixiaoxian_sixun`.`real_time_hxx_rpt_fresh_bhd_item_info_di` `rhi`
                JOIN `huixiaoxian_sixun`.`real_time_hxx_sync_t_da_jxc_day_sum` `rhd` ON (`rhi`.`item_no` = `rhd`.`item_no`)
                JOIN `huixiaoxian_sixun`.`hxx_bhd_dim_fresh_bhd_area_shop_di` `hs` ON (`hs`.`shop_id` = `rhd`.`branch_no`)
            WHERE
                rhd.__create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
                AND rhd.__create_time < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
                AND rhi.update_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
                AND rhi.update_date < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
            ) `a`
        GROUP BY
            `a`.`shop_name`,
            `a`.`div_name`
    ) b
    GROUP BY b.branch_no;

    -- 插入临时表中的数据到目标表中
    INSERT INTO your_target_table (branch_no, shop_name, foodHundred, hotPotConvenienceDish, fruits, meat, vegetable, call_time)
    SELECT branch_no, shop_name, foodHundred, hotPotConvenienceDish, fruits, meat, vegetable, call_time
    FROM temp_data_table;

    -- 删除临时表
    DROP TABLE temp_data_table;
END //

DELIMITER ;


3、执行存储过程
call save_data_to_diffable();

二、带条件的存储过程

DELIMITER //
CREATE PROCEDURE task_pm (IN businessType VARCHAR(50), IN servType VARCHAR(50))
BEGIN
INSERT INTO n_pm_project (
    `F_Id`, `F_ProjectNO`, `F_CustomNO`, `F_ShopId`, `F_ShortName`, `F_Title`, `F_Type`,
    `F_Area`, `F_BusinessCompany`, `F_BusinessType`, `F_Description`, `F_CreatorUserId`,
    `F_CreatorTime`, `F_Place`, `F_Region`, `F_Address`, `F_StartTime`, `F_EndTime`,
    `F_Status`, `F_business_type`, `F_IsAction`, `F_Org_Id`, `F_Project_Relation`,
    `F_AttendanceMachineId`, `F_Shop`, `F_Shop_Name`, `F_Shop_Address`, `F_Org`,
    `F_Org_Name`
)
SELECT
    a.`F_Id`, a.`F_ProjectNO`, a.`F_CustomNO`, a.`F_ShopId`, a.`F_ShortName`, a.`F_Title`,
    a.`F_Type`, a.`F_Area`, a.`F_BusinessCompany`, a.`F_BusinessType`, a.`F_Description`,
    a.`F_CreatorUserId`, a.`F_CreatorTime`, a.`F_Place`, a.`F_Region`, a.`F_Address`,
    a.`F_StartTime`, a.`F_EndTime`, a.`F_Status`, a.`F_business_type`, a.`F_IsAction`,
    a.`F_Org_Id`, a.`F_Project_Relation`, a.`F_AttendanceMachineId`, a.`F_Shop`,
    a.`F_Shop_Name`, a.`F_Shop_Address`, a.`F_Org`, a.`F_Org_Name`
FROM
    szxh_business.pm_project a
WHERE
    a.F_BusinessType = businessType AND a.F_Type IN (
        SELECT
            c.F_EnCode
        FROM
            szxh_java_boot.base_dictionarytype b
            INNER JOIN szxh_java_boot.base_dictionarydata c
                ON c.F_DictionaryTypeId = b.F_Id
        WHERE
            b.F_FullName LIKE CONCAT('%', '主数据业务线', '%')
            AND c.F_FullName LIKE CONCAT('%', servType, '%')
    )
ON DUPLICATE KEY UPDATE
    F_ProjectNO = VALUES(F_ProjectNO),
    F_CustomNO = VALUES(F_CustomNO),
    F_ShopId = VALUES(F_ShopId),
    F_ShortName = VALUES(F_ShortName),
    F_Title = VALUES(F_Title),
    F_Type = VALUES(F_Type),
    F_Area = VALUES(F_Area),
    F_BusinessCompany = VALUES(F_BusinessCompany),
    F_Description = VALUES(F_Description),
    F_CreatorUserId = VALUES(F_CreatorUserId),
    F_CreatorTime = VALUES(F_CreatorTime),
    F_Place = VALUES(F_Place),
    F_Region = VALUES(F_Region),
    F_Address = VALUES(F_Address),
    F_StartTime = VALUES(F_StartTime),
    F_EndTime = VALUES(F_EndTime),
    F_Status = VALUES(F_Status),
    F_business_type = VALUES(F_business_type),
    F_IsAction = VALUES(F_IsAction),
    F_Org_Id = VALUES(F_Org_Id),
    F_Project_Relation = VALUES(F_Project_Relation),
    F_AttendanceMachineId = VALUES(F_AttendanceMachineId),
    F_Shop = VALUES(F_Shop),
    F_Shop_Name = VALUES(F_Shop_Name),
    F_Shop_Address = VALUES(F_Shop_Address),
    F_Org = VALUES(F_Org),
    F_Org_Name = VALUES(F_Org_Name);
END // 

DELIMITER ;



带参数执行存储过程
// businessType: 业务类型
// servType: 业务线
call task_pm ('自营','保洁')