存储过程实现将某一结果集插入到另一张表的俩种方法*

存储过程实现将某一结果集插入到另一张表的俩种方法

-- 一 ，case 1
-- 新增存储过程之前先删除
DROP PROCEDURE IF EXISTS tracking;
-- 存储过程与其他sql的链接语句
DELIMITER $$
$$
CREATE  PROCEDURE `tracking`() -- 定义存储过程名
BEGIN -- 开始
        -- 需要定义接收游标数据的变量 
        DECLARE done int DEFAULT 0; -- 定义变量
        DECLARE a int DEFAULT 1000;
        

        DECLARE serialNo varchar(32);
        DECLARE Description varchar(4000);
        
         -- 定义游标
        DECLARE cur1 CURSOR FOR select ccc.Description  from aaa ccc;
        -- 为下面while循环建立一个退出标志，当游标遍历完后将done的值设置为1
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 如果没有数据返回，并将变量赋值为1
         -- 打开游标
        OPEN cur1;
        -- 将游标中的数据赋值给变量
        FETCH cur1 INTO Description; 
         -- 开始循环
        while done <> 1 do
        -- 设置一个自增的主键
        set serialNo = concat(concat('CMW',date_format(now(),'%Y%m%d')),a);
        -- 执行insert 语句
        INSERT into bbb(Description) 
        VALUES (Description);
   		FETCH cur1 INTO Description;
  		set a=a+1;
    end while;
        -- 循环结束
         -- 关闭游标
  CLOSE cur1 ;
end;$$
DELIMITER ;-- 链接其他SQL语句


-- 二. Case 2

CREATE  PROCEDURE `insert_cncbi_lpinformation`()
begin
        DECLARE SERIALNO bigInt;
        DECLARE ATTRIBUTE1 datetime;
        DECLARE ATTRIBUTE2 varchar(250);
        DECLARE ATTRIBUTE3 varchar(250);
        DECLARE ATTRIBUTE4 varchar(250);
        DECLARE ATTRIBUTE5 varchar(250);
        DECLARE ATTRIBUTE6 varchar(250);
        DECLARE ATTRIBUTE7 datetime;
        DECLARE ATTRIBUTE8 datetime;
        DECLARE ATTRIBUTE3TYPE VARCHAR(20);
        DECLARE ATTRIBUTE4TYPE VARCHAR(20);
        DECLARE v_sql TEXT;
        DECLARE v_i  INT;
        DECLARE v_total_count   BIGINT;
        DECLARE v_initial_value   BIGINT;
        DECLARE v_rows   BIGINT DEFAULT 120000;
        DECLARE v_count  INT;
        DECLARE selectSql TEXT;
        DECLARE insertSql TEXT;
        
        -- 给变量设置值(concat_ws(A,B,C)的意思将B和C用A分割)
        set insertSql= 'INSERT into CNCBI_CODE_LPINFORMATION(SERIALNO,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE3TYPE,ATTRIBUTE4TYPE)';
        set selectSql = concat_ws('','select SERIALNO,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE3TYPE,ATTRIBUTE4TYPE  FROM batch_LP_Demo where 1=1'); 
        set v_sql=concat_ws('','select count(*) into @count from ','(',selectSql,') t1');
        -- 可以直接在变量前面添加@符，不用定义和初始化变量
        set @v_sql=v_sql;
        -- 预编译sql
        prepare stmt from @v_sql;
        EXECUTE  stmt; -- 执行sql和填充变量EXECUTE  stmt @a,@b;
        deallocate prepare stmt; -- 释放链接
        select  @count into v_total_count;
        set v_count = v_total_count/v_rows;
        set v_i =0;
        
        while v_i<=v_count do
                        set v_initial_value=v_i*v_rows;
                        set v_sql=concat_ws(' ',insertSql,selectSql,'limit',v_initial_value,',',v_rows);
                        set @v_sql=v_sql;
                        prepare stmt from @v_sql;
                        EXECUTE  stmt;
                        deallocate prepare stmt;  
                        commit;
                        set v_i = v_i+1;
        end while;
end;