mysql 查询记录并插入另一张表

最新推荐文章于 2024-09-11 12:06:01 发布

ZZ100861122

最新推荐文章于 2024-09-11 12:06:01 发布

阅读量239

点赞数

原文链接：https://blog.csdn.net/lihaiyong92/article/details/112194477?utm_medium=distribute.pc_category.none-task-blog-hot-6.nonecase&depth_1-utm_source=distribute.pc_category.none-task-blog-hot-6.nonecase&request_id=

版权

从 bd_house 查询 houseId, houseName, 插入 bd_house_customer

DROP PROCEDURE IF EXISTS insert_extra_column_test;
DELIMITER //
CREATE PROCEDURE insert_extra_column_test()
BEGIN
    -- 声明变量
	DECLARE newId LONG;
	DECLARE houseId LONG;
	DECLARE houseName VARCHAR ( 50 );
	DECLARE num integer;
    -- 声明接收结果的游标
	DECLARE
		house CURSOR FOR 
    -- 查询记录
        SELECT
		    id houseId,
	        NAME houseName
	    FROM bd_house 
	    WHERE
		id NOT IN ( SELECT house_id FROM bd_house_customer WHERE customer_id = 1111) 
	    ORDER BY  id;
 
    -- 打开游标
	OPEN house;
    -- 匹配第一个游标, 给变量赋值
	FETCH house INTO houseId, houseName;
 
    -- 这是固定遍历 100 次, 遍历全部可以用 while ( house is not null ) 判断
	SET num = 100;
	WHILE ( num > 0 ) DO
			INSERT INTO `bd_house_customer` (
				`id`,
				`tenant_id`,
				`house_id`,
				`house`,
				`customer_id`,
				`customer`,
				`change_cause`,
				`relation_state`,
				`remarks`,
				`lease_type`,
				`belong_house_name`,
				`sign_date`,
				`lease_source`,
				`asset_use_type` 
			)
			VALUES
				(
					houseId,
					1111,
					houseId,
					houseName,
					1111,
					'张三',
					'2222',
					'currentTenant',
					1,
					'NewRent',
					houseName,
					NULL,
					'PC_terminal',
					'zzzz' 
				);
		SET num = num - 1;
        -- 匹配下一个游标并赋值
		FETCH house INTO houseId, houseName;
	-- 结束循环, 关闭游标	
	END WHILE;
	CLOSE house;
 
END //  
DELIMITER ; 
 
CALL insert_extra_column_test();