数据库插入高校批量函数

-- 存储例程

-- 创建存储例程，若存在则不创建，因此使用前要注意是否有重复存储例程
DROP PROCEDURE IF EXISTS `存储例程名称，文章以下都称为sp_test_v6`;
DELIMITER // -- 分隔符
-- taskId 开始主键,span 分组
PROCEDURE `sp_test_v6`(IN taskId INT, IN span INT)
TASK_LABEL:BEGIN

-- 创建变量
DECLARE minId INT;
DECLARE maxId INT;
DECLARE v_relation INT;
DECLARE v_view_id BIGINT;
DECLARE v_view_type INT;  -- 视角类型 0集团视角 1 普通视角 2 单店视角
DECLARE v_area_id BIGINT;
DECLARE v_sys_customer_id BIGINT;
DECLARE v_shop_str MEDIUMTEXT;

SET @sql = '执行语句';

-- 创建临时表
DROP TABLE IF EXISTS tmp_temp;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_temp
(
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  view_id BIGINT,
  view_type INT,
  UNIQUE KEY `AK_view_id` (`view_id`)
);

-- 插入表数据,为了执行时获取临时表中的数据
INSERT IGNORE INTO tmp_temp(view_id,view_type,shop_id_str)
-- 从另一张表中获取数据
SELECT CAST(relation_key AS UNSIGNED) AS view_id,relation_flag AS view_type,relation_value AS shop_id_str
FROM `kd_relation`

SET @startId = (taskId - 1) * span + 1; -- 起始值
SET @endId = taskId * span; -- 终止值
-- 插入数据
INSERT IGNORE INTO sp_test_v6 
-- 字段必须在变量上创建
(create_time,update_time,task_id,start_id,end_id,current_id,task_type) VALUES (NOW(),NOW(),taskId,@startId,@endId,@startId,@taskType);
SELECT current_id,end_id INTO minId,maxId FROM sp_test_v6 WHERE task_id = taskId AND task_type = @taskType;
WHILE (minId <= maxId) DO
-- 赋值字段值
        SET v_sys_customer_id = 0;
          SELECT view_id,IFNULL(sys_customer_id,0) INTO v_view_id,v_sys_customer_id FROM kd_view_customer_sg_info WHERE id = minId;
          IF v_sys_customer_id IS NOT NULL AND v_sys_customer_id <> 0  THEN

                  SELECT shop_id_str INTO v_shop_str FROM tmp_view_shop WHERE view_id=v_view_id LIMIT 1;  

                  SET @runsql = REPLACE(REPLACE(@sql,'@SysCustomerId',v_sys_customer_id),'@WhereViewId',v_view_id);
                SET @runsql = REPLACE(@runsql,'AND @WhereInShopId',CONCAT(' AND k.shop_id IN (',v_shop_str,') '));
--                 select @runsql;

                IF v_shop_str<>'0' THEN
                  PREPARE stmt FROM @runsql;
                  EXECUTE stmt;
                  DEALLOCATE PREPARE stmt;
                END IF;
                
          END IF;
        IF minId % 10000 = 0 OR minId = maxId THEN
                  UPDATE sp_test_v6 SET current_id = minId, update_time = NOW() WHERE task_id = taskId AND task_type = @taskType;
          END IF;
          SET minId = minId + 1;
END WHILE;

SELECT '当前任务已完成！' AS message FROM DUAL;
END//
DELIMITER ;