存储过程（坑）跨库查询、关联关系处理

 

DROP PROCEDURE IF EXISTS Test1;
DELIMITER $$
create PROCEDURE x1.Test1(wxappId int(11))
BEGIN
-- 创建变量用来接收游标数据  变量的声明、游标的声明和HANDLER声明的顺序不能乱，必须是先声明变量，再申明游标，最后声明HANDLER
-- 变量的定义不要和你的select的列的键同名！否则fetch into 会失败   x1库与x2库
 DECLARE v_category_id INT DEFAULT 0;
 DECLARE v_name VARCHAR(50) DEFAULT '';
 DECLARE v_parent_id int(11) DEFAULT 0;
 DECLARE p_parent_id int(11) DEFAULT 0;
 DECLARE v_image_id int(11) DEFAULT 0;
 DECLARE v_sort int(11) DEFAULT 100;
 DECLARE v_share_id int(11);
 DECLARE v_market_info varchar(50) DEFAULT '';
 DECLARE v_modify_value varchar(11) DEFAULT '';
 DECLARE v_modify_value_type tinyint(3) DEFAULT 0;
 
 -- file 
 DECLARE f_storage varchar(20) DEFAULT '';
 DECLARE f_file_url varchar(255) DEFAULT '';
 DECLARE f_file_name varchar(255) DEFAULT '';
 DECLARE f_file_size int(11) DEFAULT 0;
 DECLARE f_file_type varchar(20) DEFAULT '';
 DECLARE f_extension varchar(20) DEFAULT '';
 
 DECLARE file_id int(11) DEFAULT 0;
 DECLARE f_total int(11) DEFAULT 0;
 DECLARE f_status int(11) DEFAULT 0;
 
 
 DECLARE v_count INT(11) DEFAULT 0;
 
 
 DECLARE createTime INT(11) DEFAULT unix_timestamp(now());
 
 DECLARE done INT DEFAULT 0;
-- 创建游标
 DECLARE rs_cursor CURSOR FOR
     SELECT category_id as share_id ,`name`,`parent_id`,`image_id` FROM x2.jhshop_category ORDER BY category_id ASC;
 -- 指定游标循环结束时的返回值 ,在游标循环到最后会将 done 设置为 1
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
 -- 打开游标
 OPEN rs_cursor;
 -- 关闭事务自动提交,这里只是临时关闭，存储过程执行完成后autocommit还是原来系统的状态
 SET autocommit=0;
 -- 开始循环
 read_loop: LOOP
     -- 提取游标里的数据到上面声明的变量中
     FETCH rs_cursor INTO v_share_id,v_name,v_parent_id,v_image_id;
      -- 声明何时结束循环
     IF done=1 THEN
         LEAVE read_loop;
     END IF;
        
         
        -- 查询图片库
        -- 查询图片库
        SELECT `jhshop_upload_file`.`storage`,`jhshop_upload_file`.`file_url`,`jhshop_upload_file`.`file_name`,`jhshop_upload_file`.`file_size`,`jhshop_upload_file`.`file_type`,`jhshop_upload_file`.`extension`,count(1) as total  FROM x2.jhshop_upload_file  where `jhshop_upload_file`.`file_id`=v_image_id INTO f_storage,f_file_url,f_file_name,f_file_size,f_file_type,f_extension,f_total;
                
        IF f_total>0 THEN
            -- 查看图片是否存在过
            SELECT jhshop_upload_file.file_id,count(1) as total INTO file_id,f_status FROM x1.jhshop_upload_file where jhshop_upload_file.share_id=v_image_id and wxapp_id=wxappId; 
            IF f_status<1 THEN
                -- 不存在则添加
                INSERT INTO x1.jhshop_upload_file VALUES (0,f_storage,0,f_file_url,f_file_name,f_file_size,f_file_type,f_extension,0,v_image_id,0,0,wxappId,createTime);
                SELECT LAST_INSERT_ID() INTO file_id;
            END IF;        
            
        END IF;
        
        
        -- 取得分类上级
        if v_parent_id>0 THEN
            SELECT jhshop_category.category_id INTO p_parent_id FROM x1.jhshop_category where jhshop_category.share_id=v_parent_id and wxapp_id=wxappId;
        END if;
        
      
       -- 循环时的事件
         -- 向表B插入数据,表B和表A结构一致,为提升插入效率每10000条commit一次
         INSERT INTO x1.jhshop_category VALUES (0,v_name,p_parent_id,file_id,v_sort,v_share_id,v_market_info,v_modify_value,v_modify_value_type,wxappId,createTime,createTime);
         SET v_count = v_count + 1;
         IF MOD(v_count,10000)=0  THEN 
             COMMIT; 
             SET v_count = 0; 
        END IF; 
 -- 关闭循环
 END LOOP read_loop;
 -- 导入数据不是10000整数倍时需要在loop结束后在提交一次
 COMMIT;
 -- 关闭游标
CLOSE rs_cursor;

END$$

call Test1(11102);